Import data from YouTube and Vimeo to Google.Docs

UPDATE (May 12, 2015):

I am forced to report that since May 1, 2015 the data is outdated. Youtube changed the rules of the API: now it is impossible to make anonymous requests. Authorization API-key is required to get the data.

---

Prehistory

Most recently, we had a situation where all YouTube and Vimeo links to videos, the number of which we need to display on the site, were “lost”. It is necessary for potential investors and partners to see what the current indicators of our serial projects are. The script updates the data once a day, although I have repeatedly asked to update the indicators in real time. I haven't been programming 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 data loss, scattered, etc., but this has nothing to do with the matter.

The links were restored, but the counter confused me. While the restoration was going on, I collected the numbers in large groups manually, and now after the script went around them and gave the result, they didn’t catastrophically beat. To my complaints to programmers, I received the answer that everything is OK, at least check it out. Not wanting to bother much, I decided to follow a very strange path: do a crawling of links using Google.Sheets and look at the results, and at the same time check if this is really such an agonizing process: crawling a few hundred links and getting data about views. I spent several hours on my experiment, found out that this is not a tricky business, collecting real-time data is quite simple, and the script on our website really works crookedly and collects inaccurate data, losing millions of views on the way.

ImportXML and Vimeo

Acknowledging that I somehow made a withdrawal of the current dollar and euro rate, I found an old document and looked at the convenient Google function in it. Sheets is ImportXML (in Google there are 4 functions conveniently drawing data from the network: ImportXML, ImportDATA, ImportHTML, ImportFEED) . In the cell of the table, we simply write "= ImportXML" and Google gives a hint how to correctly extract the necessary data. I went to the Vimeo website and found a webpage with an API and a description of where to get the XML with the video data. Everything is trite:
')
http://vimeo.com/api/v2/video/00000000.xml
where 00000000 is the id of the video (taken from the address bar on Vimeo)

Opening the XML, I saw that everything I need. In the container of the third order <stats_number_of_plays> lay the data about viewing. Thus, you can get viewing data on Google.Sheets, for example, this video , you can simply indicate the following value in the cell:

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

In the first quotes the reference to XML, in the second quotes we specify the XPath to the required container. Read more about the XPath syntax here .

ImportJSON and YouTube

Slightly harder was from YouTube. Yes, they give data about the video, but in JSON containers. Finding a consolidated file was no more difficult than on Vimeo. Everything is also not difficult:

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

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

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

Having opened JSON from YouTube, we can find all the data we need about the video. In the container <yt $ statistics> there is an attribute viewCount, which we need. Thus, you can get viewing data on Google.Sheets, for example, this video , you can simply indicate 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 counts data from hundreds of videos in ... less than in one minute. I was right. Programmers just drove me around. Retrieving data from XML and JSON containers using PHP is even less tricky than the one I set for myself. Not only that, both YouTube and Vimeo offer ready-made PHP solutions for developers.

Good luck to all! I hope this short article will be useful.