---
title: "Automatické statistiky videí díky Google Sheets, Apps Script a YouTube Data API"
id: "244"
type: "post"
slug: "automaticke-statistiky-videi-diky-google-sheets-apps-script-a-youtube-data-api"
published_at: "2015-10-20T10:54:59+00:00"
modified_at: "2023-08-13T15:30:56+00:00"
url: "https://www.kapler.cz/automaticke-statistiky-videi-diky-google-sheets-apps-script-a-youtube-data-api/"
markdown_url: "https://www.kapler.cz/automaticke-statistiky-videi-diky-google-sheets-apps-script-a-youtube-data-api.md"
excerpt: "Před pár dny jsem tu psal příspěvek o tom, jak si snadno udělat automaticky aktualizovaný přehled (nejen) vašich video kanálů s využitím Google Sheets, Apps Script a YouTube Data API. Mělo to jen jednu chybku – videím chyběly statistiky, tak..."
taxonomy_category:
  - "Programování"
taxonomy_post_tag:
  - "Google Apps Script"
  - "Google Sheets"
  - "YouTube"
---

# Automatické statistiky videí díky Google Sheets, Apps Script a YouTube Data API

Před pár dny jsem tu psal příspěvek o tom, jak si snadno udělat automaticky aktualizovaný přehled (nejen) vašich video kanálů s využitím Google Sheets, Apps Script a YouTube Data API. Mělo to jen jednu chybku – videím chyběly statistiky, tak to dnes napravíme. Chybějící statistiky si vyžádaly nutnost přepsání celého skriptu, protože API YouTube.PlaylistItems neposkytuje statistická data,…

Uveřejněno

20. 10. 2015

Rubrika

[Programování](https://www.kapler.cz/category/programovani/)

Autor

Tomáš Kapler

Značky:

[Google Apps Script](https://www.kapler.cz/tag/google-apps-script/)
, [Google Sheets](https://www.kapler.cz/tag/google-sheets/)
, [YouTube](https://www.kapler.cz/tag/youtube/)

Před pár dny jsem tu psal příspěvek o tom, jak[si snadno udělat automaticky aktualizovaný přehled (nejen) vašich video kanálů s využitím Google Sheets, Apps Script a YouTube Data API](http://kapler.cz/mejte-prehled-ve-svych-videich-diky-google-scripts-a-sheets-a-youtube-data-api/)
. Mělo to jen jednu chybku – videím chyběly statistiky, tak to dnes napravíme.

Chybějící statistiky si vyžádaly nutnost přepsání celého skriptu, protože API [YouTube.PlaylistItems](https://developers.google.com/youtube/v3/docs/playlistItems)
 neposkytuje statistická data, bylo potřeba použít další část YouTube Data API a to [YouTube.Videos](https://developers.google.com/youtube/v3/docs/videos)
. Zároveň bylo potřeba skript optimalizovat, aby se nedotazoval na statistiky každého videa najednou a rovnou jsem skript také zpřehlednil.

Přidal jsem 2 vylepšení – nyní je možné z menu aktualizovat i jen jeden (aktuální) list, a nově se také nemaže celý list, ale pouze sloupce A:K, kam se ukládají data, takže můžete do dalších sloupců dát nějaké vzorečky (např. jestli popisek obsahuje nějaké slovo, či nějakou chytristiku nad statistikou) a při aktualizaci se vám nesmažou. BTW doporučuji k těmto účelům použít [skvělou funkci ARRAYFORMULA, o které jsem již psal](http://kapler.cz/arrayformula-duvod-proc-si-zamilujete-google-sheets/)
.

Stále ovšem zůstává základní myšlenka – automatizovaný přehled YouTube videí, jejich screenshot, popisek a nově i tagy a statistiky – co list to jeden kanál – stačí přidat list, pojmenovat jej jako název kanálu na YouTube a videa skript se o zbytek postará.

Tentokrát nedoplním text dalšími komentáři, jsou přímo v poznámkách skriptu a v prvním článku. Nezapomeňte, že je třeba skript autorizovat a povolit mu přístup k příslušným API, a případně nastavit mu automatickou aktualizaci, více [viz předchozí článek](http://kapler.cz/mejte-prehled-ve-svych-videich-diky-google-scripts-a-sheets-a-youtube-data-api/)
.

**P.S.: Nemám od vás odezvu, jestli vás tyto návody a nápady zajímají (viz [všechny Užitečnosti](http://kapler.cz/category/uzitecnosti/)
), takže nevím, jestli mám publikovat další, či s tím neztrácet čas. Jestli chcete, abych pokračoval, sdílejte tento (či i další posty) ve svých kruzích a přidejte si mě:**

- [Facebook](https://www.facebook.com/tomas.kapler)
- [Twitter](https://twitter.com/tkapler)
- [Google Plus](https://plus.google.com/u/0/+TomasKapler/posts)
- [LinkedIN](https://cz.linkedin.com/in/kapler)

**Mám i další podobné skripty, návody a užitečnosti pro GA, AdWords, Sklik, Excel, Google Apps… Když nebude mít tento post dostatek sdílení, budu vědět, že vás to nezajímá.**

Pakliže chcete něco přímo pro vás, můžete si mě taky najmout (ale jsem teda spíš digitální marketingový manažer, programuji v sebeobraně).

```
/**
* Helping function for testing return values from single channel. Ctrl+Enter to see the log
**/
function testChannel() {
 var videos = getVideos ('TefalCZSK')
 Logger.log(videos[0]);
}

/**
* Ad Update menu during Spreadsheet open
**/
function onOpen() {
 var ui = SpreadsheetApp.getUi();
 // Or DocumentApp or FormApp.
 ui.createMenu('Aktualizace')
 .addItem('Aktualizace aktuálního listu', 'updateCurrentSheet')
 .addItem('Aktualizace všech videí', 'updateAllSheets')
 .addToUi();
}

/**
* Update videos and formats on all sheets in active Spreadsheet.
**/
function updateAllSheets () {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheets = ss.getSheets();
 
 for (var n = 0; n < sheets.length ; n++ ) {
 var sheet = ss.getSheets()[n];
 updateSheetVideos(sheet);
 updateSheetStyle(sheet);
 }
}

/**
* Update videos and formats on current sheet.
**/
function updateCurrentSheet() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getActiveSheet();
 updateSheetVideos(sheet);
 updateSheetStyle(sheet);
 
}

/**
* Update videos on given sheet with videos from the YouTube channels with the same channel URL as sheet name
*
* @param {object} Spreadsheet Sheet
*/
function updateSheetVideos(sheet) {
 var sheetName = sheet.getName();
 var videos = (getVideos (sheetName));
 // if there is no YouTube channel that match the sheet name, return back (so we can have helping sheets)
 if (typeof videos === 'undefined') return;
 
 var columnNames = ['URL',
 'Thumbnail',
 'Title',
 'Description',
 'Tags',
 'Published at',
 'Views',
 'Likes',
 'Dislikes',
 'Comments',
 'Favorites',
 ];
 
 // we want to clear only the columns, that will be updated, not all sheet
 // this way we can add formulas to adjoining columns and keep them intact after update
 var lastColumn = "abcdefghijklmnopqrstuvwxyz".charAt(columnNames.length-1);
 sheet.getRange("A:"+lastColumn).clearContent();
 
 //fill column names to first row 
 sheet.getRange(1, 1, 1, columnNames.length).setValues([columnNames]) ;
 
 //put all the values to single array for later single call update, what is much faster then update row one by one
 var values = [];
 videos.forEach (function (video) {
 var tags = video.snippet.tags;
 tags = (typeof tags === 'undefined') ? '' : tags.join(', ');
 values.push([ "https://www.youtube.com/watch?v="+video.id,
 //'=image("https://i.ytimg.com/vi/'+video.id+'/default.jpg")', //"default" is restricted object name, so it would need this ugly way to use default (small) size of thumbnail
 '=image("'+video.snippet.thumbnails.medium.url+'")', //instead we use "medium" format
 video.snippet.title,
 video.snippet.description,
 tags,
 new Date(video.snippet.publishedAt),
 video.statistics.viewCount,
 video.statistics.likeCount,
 video.statistics.dislikeCount,
 video.statistics.commentCount,
 video.statistics.favoriteCount,
 ]);
 });
 sheet.getRange(2, 1, videos.length , values[0].length).setValues(values);
}

/**
* Update styles and formats of given sheet. Not very sexy, but it fits the needs
*
* @param {object} SpreadsheetApp Sheet
**/
function updateSheetStyle(sheet) {
 var sheetName = sheet.getName();
 sheet.getDataRange().setVerticalAlignment("middle");
 sheet.setColumnWidth(1, 310);
 sheet.setColumnWidth(2, 120); //this will affect the size of thumbnail
 sheet.setColumnWidth(3, 300);
 sheet.setColumnWidth(4, 400);
 sheet.setColumnWidth(5, 200);
 sheet.autoResizeColumn(6);
 sheet.autoResizeColumn(7);
 sheet.autoResizeColumn(8);
 sheet.autoResizeColumn(9);
 sheet.autoResizeColumn(10);
 sheet.autoResizeColumn(11);
 sheet.getRange("F:F").setNumberFormat('d.m.yyyy');
 sheet.getRange("G:K").setNumberFormat('#,##0');
 sheet.getRange("C:E").setWrap(true);
 sheet.getRange("1:1").setFontWeight("bold");
 sheet.getRange("C:C").setFontWeight("bold");
 for(var i=2; i<=sheet.getLastRow()-1; i++){
 sheet.setRowHeight(i, 90); //this will affect the size of thumbnail
 };
}

/**
* Return YouTube videos details for given channel
*
* @param {string} username YouTube channel name (username) - see channel URL
* @return Array representing key params of YouTube videos in given channel
*/
function getVideos(username) {
 var results = YouTube.Channels.list('contentDetails', {forUsername: username});
 var array = [];
 if (results.pageInfo.getTotalResults() == 0) return;
 var item = results.items[0];
 // Get the playlist ID, which is nested in contentDetails, as described in the
 // Channel resource: https://developers.google.com/youtube/v3/docs/channels
 var playlistId = item.contentDetails.relatedPlaylists.uploads;
 
 var nextPageToken = '';
 
 // This loop retrieves a set of playlist items and checks the nextPageToken in the
 // response to determine whether the list contains additional items. It repeats that process
 // until it has retrieved all of the items in the list.
 while (nextPageToken != null) {
 var playlistResponse = YouTube.PlaylistItems.list('contentDetails', {
 playlistId: playlistId,
 maxResults: 50,
 pageToken: nextPageToken
 });
 //get all ids (max 50) from given playlist response and joint them
 var ids = []
 ids = playlistResponse.items.map(function(video) {
 return video.contentDetails.getVideoId();
 })
 ids = ids.join(',');
 //get snippet and statistics data for given ids and add them to array
 var data = YouTube.Videos.list('snippet, statistics', {id: ids});
 array = array.concat(data.items);
 nextPageToken = playlistResponse.nextPageToken;
 }
 
 return array;
}
```