Google Apps Script - zbieramy dane o filmach z playlisty na YouTube i je porównujemy

Jak z realizacją Waszych postanowień noworocznych? 18 stycznia miał miejsce najbardziej depresyjny dzień w roku - Blue Monday, gdzie jednym z argumentów w algorytmie jest niedotrzymanie postanowień noworocznych, np. nauki programowania. Jaki ma to związek z dalszą treścią wpisu? O tym za chwilę. W tym wpisie użyję Google Apps Script, który został wprowadzony do Google Sheet w 2009r. Ma on na celu zbieranie informacji o filmach na YouTube we wskazanej playliście przy użyciu YouTube Api. Może to być playlista spoza naszego konta. Informacje te możemy już dowolnie obrobić i zaprezentować. Użyję do tego Google Sheet. Mogę powiedzieć, że Google Apps Script jest czymś jak VBA dla Microsoft Office. Tutaj nadmienię, że skrypt możemy również używać z innymi narzędziami Google - np. Calendar, Docs, Drive, Gmail, Slides i z wieloma innymi. Przykłady skryptów znajdziecie w repozytorium Google Workspace. Rozwiązanie Google bazuje na JavaScripcie.

Nie pamiętam czy najpierw miałem pomysł/problem i szukałem technologii czy odwrotnie - natrafiłem na to rozwiązanie od Google i chciałem je wypróbować. Inspiracją był ten artykuł napisany przez Ricka Viscomi. Na jego kodzie rozpocząłem pracę oraz bazowałem na pliku Google Sheet, co możesz sprawdzić w historii commitów na repozytorium. W kontekście analizy ciekawe są dla mnie playlisty z kursami np. języków programowania. Często można zauważyć, że pierwszy film ma bardzo dużą ilość wyświetleń w porównaniu do innych z playlisty. Z kolei drugi notuje nagły spadek ilości wyświetleń, kolejne filmy również mają mniejszą ilość wyświetleń ale z mniejszą różnicą między sąsiednimi. I tutaj wrócę do tego co napisałem na początku, czyli motywacji. Być może jest to jakiś wyznacznik w jaki sposób podchodzimy do nauki i jak szybko wiele osób rezygnuje i odpuszcza. Patrząc na to z drugiej strony, pierwszy film może zniechęcać do oglądania kolejnych i stąd taka tendencja. Wnioski nie są oczywiste. Każdy z Was może przeanalizować interesującą dla siebie playlistę, a następnie wyciągnąć wnioski z danych, które pobierze i zobaczy.

Jak to działa?

Dane te oczywiście nie do końca muszą być rzetelne i nie mamy na to wpływu. Przykładowo, dany film z playlisty może się automatycznie odtwarzać po wejściu na wybrany kanał na YouTube. W związku z tym jego odtworzenie jest niezamierzone i niecelowe. Film z playlisty może być podany również jako przykład na danym forum. Oznacza to, że odbiorcy nie byli zainteresowani całą playlistą, a tylko jednym podanym filmem. Mimo to nabijają statystyki do tego jak filmy prezentują się względem siebie na playliście. I jeszcze jeden pomysł - film może być popularny, ze względu na obecność w tytule konkretnych rozwiązań lub popularnych i interesujących haseł. Użytkownicy trafiają do niego bezpośrednio z wyszukiwarki Google czy YouTube - tutaj ponownie nie są oni zainteresowani całą gamą filmów, całym kursem, a jedynie konkretnym filmem. Od pewnego czasu można zaobserwować pojawianie się kilkugodzinnych kursów w jednym wideo - “Full tutorial…”, “Full course...”, “All in one…”, “YYZZ in X hours”. Wtedy zainteresowanie jest widoczne w panelu YouTube w statystykach do danego filmu. Jednak przez długość wideo niemal na pewno nie będą to dane oddające rzeczywiste zainteresowanie. Nie będę się skupiał tutaj na analizie ale pokażę jak można użyć tego rozwiązania. Po wejściu na stronę https://www.google.com/script/start/ należy wejść w Start Scripting. Skrypt piszemy w edytorze online i rozwiązanie oparte jest o chmurę.

Hello World

Zróbmy szybko “Hello World” i wrócimy ponownie do opisywanego rozwiązania.

Stwórz plik Google Sheet na swoim dysku Google Drive. W pliku wybierz opcję Tools -> Script Editor (najpewniej masz te i kolejne opisane opcje w języku polskim).

Zezwól na uprawnienia skryptu na dostęp do utworzonego pliku arkusza na Google Drive.

Nasz skrypt będzie teraz powiązany z utworzonym arkuszem (Hello nazwa skryptu, HelloWorld nazwa arkusza).

Napiszmy funkcję, która łączy tekst z dwóch komórek A2 i B2. Uruchamiając ją zobaczymy że w Google Sheet zostanie ustawiona wartość w komórce D2.

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1")
  var A2 = sheet.getRange("A2").getRichTextValue().getText()
  var B2 = sheet.getRange("B2").getRichTextValue().getText()
  var cell = sheet.setActiveCell("D2")
  cell.setValue(A2+B2)
}

Funkcja została uruchomiona z widoku edytora skryptu, a nie z widoku arkusza, ponieważ nie została dodana taka opcja do menu przez skrypt.

Wracamy do skryptu który pomaga w analizie filmów w playliście na YouTube.

Napisany skrypt zawiera komentarze wewnątrz pliku, więc myślę, że nie ma sensu omawiania każdej linijki kodu po kolei. Do operacji w arkuszu mamy dostęp przez klasę SpreadsheetApp. Z ciekawszych rzeczy to słowa kluczowe z YouTube API takie jak contentDetails, statistics, snippet. Słowa te zawierają określone informacje o najróżniejszych danych, tutaj co możemy sprawdzić o playlistach, a tutaj co możemy wyciągnąć o filmach wideo, a także mnóstwo innych danych np. na temat kanału. Wygodnie możemy przetestować jakie dane dostajemy używając tego API bezpośrednio z dokumentacji i np. pobrać dane filmów z playlisty (przy wysłaniu requestu w wyskakującym oknie musimy wyrazić zgodę na to że Google APIs Explorer żąda dostępu do naszego konta Google.).

W edytorze kodu możemy uruchamiać wybrane funkcje, które nas interesują:

Dostępny jest debugger:

Możemy też użyć

Logger.log("")

:)

Zrzuty ekranu pokazane do tej pory to wersja beta nowego edytora. Można też użyć wersji legacy. Przydała mi się bardzo jako historia kontroli wersji (File -> See version history albo skrót CTRL + ALT + SHIFT + H) i możliwość cofnięcia zmian.

Nie widziałem tej opcji w nowej wersji beta. Za to wersja beta pozwala pisać w wielu liniach na raz, legacy nie umożliwia tego. Debugger w wersji legacy wygląda tak:

Pozostawiam do indywidualnej oceny, która wersja jest wygodniejsza. Bez problemu możemy się przełączać między wersjami edytorów zachowując napisany kod.

Co zyskujemy dzięki danym dostarczanym przez skrypt?

Możemy obserwować popularność i zainteresowanie kolejnymi filmami. Mierzone są:

• ilość wyświetleń,
• like,
• dislike,
• komentarze,
• czas trwania filmu,
• polubienia per ilość wyświetleń,
• dislikes per ilość wyświetleń,
• procentowy udział polubień,
• procentowy udział dislikes,
• ilość komentarzy per ilość wyświetleń.

W każdym wierszu jest informacja jakiego filmu dotyczy statystyka wraz z linkiem do tego filmu, a także informacja kiedy został on opublikowany. W arkuszu Google Sheet dodana jest opcja menu "YouTube stats". Znajdziesz tutaj opcje:

• dodania adresu URL playlisty, którą chcesz zbadać
• odświeżenia manualnego ostatnio dodanej playlisty
• wyczyszczenia danych znajdujących się w arkuszu.

Dane są prezentowane za pomocą wartości z podsumowaniem oraz czytelnych wykresów i obrazują to co dostarcza skrypt. Zobacz kolejne zakładki "Series Stats", "Series Views", "Series Stats per View". Wystarczy podać adres URL do interesującej nas playlisty i zatwierdzić:

Poprzednie dane są automatycznie usuwane aby uniknąć sytuacji że mamy wideo z różnych playlist (tak się zdarzy jeśli kolejna dodawana playlista ma mniejszą ilość filmów - zostaną nadpisane wiersze od początku ale pozostaną wiersze również ze filmami z poprzedniej playlisty). Można również manualnie wyczyścić arkusz przez opcję z menu oraz odświeżyć dane o aktualnej playliście.

Triggery

Skrypt jest automatycznie wywoływany przez trigger dzięki temu dane ostatniej playlisty są zawsze aktualne. Trigger możemy ustawić na wybrany event (nie tylko czasowy):

Mamy podgląd z podsumowaniem na temat poziomu błędów, ilości uruchomień:

Historia uruchomień skryptu z logami wygląda tak:

Po każdej zmianie pliku przez skrypt, Google Sheet zapisuje historię jego zmian. Mamy również możliwość podglądu danych historycznych, dzięki czemu można je porównywać między sobą:

Podsumowanie

Znane błędy:

GoogleJsonResponseException: API call to youtube.videos.list failed with error: The request specifies an invalid filter parameter.

Powyżej 50 filmów w playliście (max request w YouTube API to 50).

Google Apps Script daje bardzo dużo możliwości (nie tylko z Google Sheet jak już wspominałem). Istnieje opcja dodania biblioteki z poziomu edytora i skorzystania z zaawansowanych usług Google, co daje kolejne i zaawansowane możliwości.

A jeżeli ktoś uwielbia analizować czy przeglądać dane bardzo ciekawym zbiorem jest plik COVID-19 w Polsce tworzony przez Michała Rogalskiego.

Link do repozytorium GitHub z kodem źródłowym.

Link do pliku Google Sheet.