Import data from YouTube and Vimeo into Google.Docs

UPDATE (May 12, 2015):

Forced to report that since May 1, 2015, the data is outdated. Youtube has changed the rules of the API: now it is impossible to make anonymous requests. To obtain data, an Authorization API-key is required.

---

Background

Most recently, we had a situation where all YouTube and Vimeo links to videos were lost, the number of views of which we need to display on the site. This is necessary for potential investors and partners to see what current indicators our serial projects have. The script updates the data once a day, although I repeatedly asked to update the indicators in real time. I have not programmed for many years, I went into a completely different kind of activity and at the moment I am heading an animation studio. Of course, I was discouraged by the situation with the loss of data, arranged a distribution, etc., but this has nothing to do with the matter.

The links were restored, but the counter confused me. While the recovery was in progress, I manually collected the numbers in large groups, and now, after the script went around them and returned the result, they did not catastrophically beat. To my complaints to programmers, I received an answer that everything is ok, at least check. Not wanting to bother much, I decided to take a very strange path: make a crawl of links using Google.Sheets and look at the results, and at the same time check whether this is really such a painful process: bypass several hundreds of links and get viewing data. I spent several hours on my experiment, I found out that this is not a tricky thing, collecting real-time data is quite simple, and the script on our site really works crookedly and collects data inaccurately, losing millions of views along the way.

ImportXML and Vimeo

Bearing in mind that I somehow concluded the current dollar and euro exchange rate, I found an old document and looked at the convenient Google function in it. Sheets is ImportXML (Google has 4 functions that conveniently drag out data from the network: ImportXML, ImportDATA, ImportHTML, ImportFEED) . In the table cell, we simply write "= ImportXML" and Google gives a hint on how to correctly extract the necessary data. I went to the Vimeo website and found a page with an API and a description of where to get XML with the video data. Everything is commonplace:

http://vimeo.com/api/v2/video/00000000.xml
where 00000000 is the id of the clip (taken from the address bar on Vimeo)

Having opened XML, I saw that everything I needed. In a third order containerlay data about viewing. Thus, to get data on viewing for example this video from Google.Sheets , you can simply specify the following value in the cell:

=IMPORTXML("http://vimeo.com/api/v2/video/17589726.xml", "//videos/video/stats_number_of_plays")

In the first quotation marks, a reference to XML, in the second quotation marks indicate XPath to the desired container. Read more about XPath syntax here .

ImportJSON and YouTube

It turned out a bit more complicated with YouTube. Yes, they give data about the video, but in JSON containers. Finding a summary file was no more difficult than on Vimeo. It's also not complicated:

http://gdata.youtube.com/feeds/api/videos/00000000?v=1&alt=json
where 00000000 is the id of the clip (taken from the address bar on YouTube)

But here JSON for me personally - a new story. I tried to contact him this way and that, as a result I thought that it was worth looking for something like “ImportJSON” and was not mistaken. I found a script to add such a feature to Google.Sheet, added it and became friends with JSON right away. The text of the script itself is here .

Install the script in Google.Sheets as follows:
1. Create a new Google Spreadsheet.
2. Click on Tools -> Script Editor.
3. Click on Create script for Spreadsheet.
4. Delete the blank of the new script and paste the text of our script .
5. Rename it to ImportJSON.gs and save.
6. Return to your document, where the new function “= ImportJSON ()” in full already works.

By opening JSON from YouTube, we can find all the data we need about the video. In containerThere is a viewCount attribute that we need. Thus, to get data on viewing for example this video from Google.Sheets , you can simply specify the following value in the cell:

=ImportJSON("http://gdata.youtube.com/feeds/api/videos/GRNZ0wwNOTc?v=2&alt=json", "/entry/yt$statistics/viewCount", "noHeaders")

That's all. By the way, in the end it turned out that Google.Sheets considers the data of hundreds of videos for ... in less than one minute. I was right. The programmers just drove me around the finger. Retrieving data from XML and JSON containers using PHP is even less tricky than the one I set for myself. Not only that, YouTube and Vimeo offer ready-made PHP solutions for developers.

Good luck to all! Hope this short article will be helpful.