Przekształcanie big data w statystyki przy użyciu Arkuszy i Prezentacji Google

Badacze danych mają dostęp do wielu narzędzi, które pozwalają na przeprowadzanie analiz big data. Jednak ostatecznie i tak trzeba uzasadnić wyniki tych analiz przed kierownictwem. Wiele liczb na papierze lub w bazie danych trudno przedstawić kluczowym interesariuszom. Ten moduł dotyczący Google Apps Script na poziomie średnio zaawansowanym wykorzystuje 2 platformy Google dla programistów: G Suite i Google Cloud Platform (GCP).

Narzędzia dla programistów Google Cloud umożliwiają przeprowadzenie szczegółowej analizy danych, a następnie umieszczenie wyników w arkuszu kalkulacyjnym i wygenerowanie na ich podstawie prezentacji, która będzie bardziej odpowiednia do przedstawienia zarządowi. W tym module poznasz dostępny w Google Cloud (jako zaawansowana usługa Apps Script) interfejs API BigQuery oraz wbudowane usługi Apps Script w Arkuszach Google i Prezentacjach Google.

Motywacja/wcześniejsze wykorzystanie pomysłu

Przykładowa aplikacja w tych ćwiczeniach z programowania została zainspirowana tymi innymi przykładami kodu...

Przykładowa aplikacja z ćwiczenia programistycznego dotyczącego interfejsu Slides API również korzysta z BigQuery i Prezentacji, ale różni się od aplikacji z tego ćwiczenia pod kilkoma względami:

  • Aplikacja Node.js a aplikacja Apps Script
  • Korzysta z interfejsów API REST, a my używamy usług Apps Script.
  • korzysta z Dysku Google, ale nie z Arkuszy Google, a ta aplikacja korzysta z Arkuszy, ale nie z Dysku;

W tym ćwiczeniu z programowania chcieliśmy połączyć jak najwięcej technologii w jednej aplikacji, prezentując funkcje i interfejsy API z wielu usług Google Cloud w sposób, który lepiej odzwierciedla rzeczywisty przypadek użycia. Chcemy Cię zainspirować do wykorzystania wyobraźni i rozważenia użycia GCP i G Suite do rozwiązywania skomplikowanych problemów, z którymi borykają się Twoja organizacja lub klienci.

Czego się nauczysz

  • Jak używać Google Apps Script z wieloma usługami Google (GCP i G Suite)
  • jak za pomocą Google BigQuery przeprowadzić analizę big data;
  • Jak utworzyć arkusz Google i zapełnić go danymi
  • Tworzenie nowego wykresu w Arkuszach
  • Jak przenieść wykresy i dane z Arkuszy do prezentacji w Prezentacjach Google

Czego potrzebujesz

  • dostęp do internetu i przeglądarki;
  • konto Google (w przypadku kont G Suite może być wymagana zgoda administratora);
  • Podstawowe umiejętności w zakresie JavaScriptu
  • Znajomość tworzenia skryptów Apps Script może być przydatna, ale nie jest wymagana.

Jak zamierzasz wykorzystać ten codelab lub samouczek?

Przeczytaj go, aby zdobyć informacje, i możliwe, że przekażę go kolegom z zespołu technicznego Przejdę go w całości i wykonam jak najwięcej ćwiczeń Za wszelką cenę ukończę cały codelab

Jak oceniasz narzędzia dla programistów i interfejsy API G Suite?

Początkujący Średnio zaawansowany Zaawansowany

Jak oceniasz korzystanie z Apps Script?

Początkujący Średnio zaawansowany Zaawansowany

Jak oceniasz narzędzia i interfejsy API dla deweloperów GCP?

Początkujący Średnio zaawansowany Zaawansowany

Wiesz już, czego dotyczy to ćwiczenie. Co dokładnie zamierzasz zrobić?

  1. Skorzystaj z istniejącego przykładu Apps Script i BigQuery i uruchom go.
  2. Z tego przykładu dowiesz się, jak wysłać zapytanie do BigQuery i uzyskać jego wyniki.
  3. Utworzenie arkusza Google i wypełnienie go wynikami z BigQuery
  4. Nieznacznie zmodyfikuj kod, aby nieco zmienić zwracane dane i dodać je do arkusza.
  5. Używanie usługi Arkusze w Apps Script do tworzenia wykresu na podstawie danych z BigQuery
  6. Tworzenie nowej prezentacji w Prezentacjach
  7. Dodaj tytuł i podtytuł do domyślnego slajdu tytułowego, który jest automatycznie tworzony we wszystkich nowych prezentacjach.
  8. Utwórz nowy slajd z tabelą danych, a następnie zaimportuj do niego komórki danych z arkusza.
  9. Dodaj kolejny nowy slajd i umieść na nim wykres z arkusza kalkulacyjnego.

Zacznijmy od kilku informacji o Apps Script, BigQuery, Arkuszach i Prezentacjach.

Google Apps Script i BigQuery

Google Apps Script to rozwiązanie programistyczne dla narzędzi G Suite, które pozwala działać na wyższym poziomie niż interfejsy API Google typu REST. Jest to bezserwerowe środowisko do programowania i hostingu aplikacji, odpowiednie dla programistów o bardzo różnym poziomie zaawansowania. Apps Script można opisać 1 zdaniem jako bezserwerowe środowisko wykonawcze języka JavaScript służące do automatyzacji, rozszerzania i integracji funkcji G Suite.

Jako implementacja języka JavaScript po stronie serwera, Apps Script jest podobny do Node.js. Jednak w przeciwieństwie do tego środowiska wykonawczego, które wykorzystywane jest do szybkiego, asynchronicznego hostingu aplikacji opartego na zdarzeniach, Apps Script służy do programowania rozwiązań w ścisłej integracji z G Suite i innymi usługami Google. Implementacja ta jest także wyposażona w środowisko programistyczne, które może całkowicie różnić się od innych używanych przez Ciebie do tej pory. Apps Script umożliwia:

  • programowanie w edytorze kodu działającym w przeglądarce oraz możliwość pracy lokalnej i późniejszego przesłania plików do Apps Script przy użyciu narzędzia wiersza poleceń clasp;
  • tworzenie kodu w wyspecjalizowanej wersji języka JavaScript zapewniającej dostęp do G Suite oraz innych usług Google lub zewnętrznych (za pomocą usług Apps Script URLfetch lub Jdbc);
  • pominięcie ręcznego pisania kodu autoryzacji, ponieważ Apps Script zapewnia jego obsługę;
  • rezygnację z hostowania utworzonej aplikacji – będzie ona działać na serwerach Google w chmurze.

UWAGA: nauka Apps Script nie jest głównym celem tego ćwiczenia. W internecie znajdziesz wiele materiałów, które Ci w tym pomogą. Oficjalna dokumentacja zawiera również omówienie z krótkimi wprowadzeniami, samouczki i filmy. Nie zapomnij też o wprowadzającym ćwiczeniu z Apps Script, które należy wykonać przed rozpoczęciem tego kursu.

Apps Script współpracuje z innymi technologiami Google na 2 różne sposoby:

  • usługa wbudowana/natywna,
  • usługa zaawansowana.

Usługa wbudowana udostępnia metody wysokiego poziomu zapewniające dostęp do danych G Suite i usług Google oraz inne przydatne metody narzędziowe. Usługa zaawansowana to tylko cienka powłoka otaczająca API typu REST w aplikacji G Suite lub innej usłudze Google. Usługi zaawansowane umożliwiają użycie wszystkich funkcji typowych dla interfejsu API typu REST i często ich możliwości są większe niż usług wbudowanych, ale wymagają one bardziej skomplikowanego kodu (wciąż jednak są łatwiejsze w obsłudze niż API REST). Użycie usług zaawansowanych wymaga ich wcześniejszego włączenia w projekcie skryptu.

Wszędzie tam, gdzie to możliwe, programiści powinni preferować usługi wbudowane, ponieważ są łatwiejsze w użyciu i wykonują za programistów więcej złożonych zadań niż usługi zaawansowane. Jednak niektóre interfejsy API Google nie mają usług wbudowanych i w takiej sytuacji użycie usługi zaawansowanej może być jedyną możliwością. Takim przykładem jest Google BigQuery. Nie ma żadnej usługi wbudowanej, ale jest usługa zaawansowana BigQuery. To i tak lepiej, niż gdyby nie było ich wcale. Jeśli jeszcze tego nie wiesz, BigQuery to usługa GCP umożliwiająca wykonywanie prostych (lub złożonych) zapytań dotyczących bardzo dużych zbiorów danych (rzędu wielu terabajtów), która zwraca wyniki w czasie liczonym w sekundach.

Dostęp do Arkuszy i Prezentacji Google z poziomu Apps Script

W przeciwieństwie do BigQuery zarówno Arkusze, jak i Prezentacje Google mają usługi wbudowane (a także usługi zaawansowane, których używasz tylko do uzyskiwania dostępu do funkcji dostępnych tylko w interfejsie API). Zanim zagłębimy się w kod, zapoznaj się z dokumentacją wbudowanych usług Arkuszy i Prezentacji. Oczywiście są też dokumenty dotyczące usług zaawansowanych. Znajdziesz je w przypadku Arkuszy i Prezentacji.

Wprowadzenie

To pierwsze zadanie obejmuje dużą część tego modułu. Gdy skończysz tę sekcję, zostanie Ci jeszcze mniej więcej połowa całego dostępnego materiału. W ramach kilku podsekcji będziesz wykonywać te czynności:

  • Rozpoczniesz nowy projekt Google Apps Script.
  • Włączysz dostęp do usługi zaawansowanej BigQuery.
  • Otworzysz edytor programistyczny i wpiszesz kod źródłowy aplikacji.
  • Przejdziesz proces autoryzacji aplikacji (OAuth2).
  • Uruchomisz aplikację wysyłającą żądanie do BigQuery.
  • Wyświetlisz nowy arkusz Google utworzony na podstawie wyników pochodzących z BigQuery.

Konfiguracja

  1. a) Utwórz nowy projekt Apps Script, przechodząc na stronę script.google.com . Istnieją różne linie produktowe G Suite i sposób tworzenia nowego projektu może się różnić w zależności od używanej wersji. Jeśli korzystasz tylko z konta Gmail i dopiero zaczynasz tworzyć projekty, zobaczysz pusty ekran z przyciskiem umożliwiającym utworzenie pierwszego projektu:


b) W przeciwnym razie w lewym górnym rogu zobaczysz wszystkie projekty i duży przycisk +Nowy . Kliknij go.



c) Jeśli nie widzisz żadnej z tych opcji, Twój ekran może wyglądać jak poniżej. Jeśli tak, poszukaj ikony menu hamburgera w lewym górnym rogu i kliknij +Nowy skrypt.



d) Dla osób, które wolą wiersz poleceń. Twoje narzędzie to clasp, a konkretnie polecenie clasp create.

e) Ostatni sposób utworzenia nowego projektu skryptu to po prostu przejście do skrótu: https://script.google.com/create.

  1. Niezależnie od tego, jakiej techniki użyjesz, aby rozpocząć nowy projekt, przejdziesz do edytora kodu Apps Script, czyli ekranu podobnego do tego:


  2. Kliknij Plik > Zapisz i nadaj projektowi nazwę.
  3. Następnie musisz utworzyć projekt w konsoli Google Cloud, aby móc uruchamiać zapytania BigQuery.
  1. Utwórz nowy projekt, nadaj mu nazwę, wybierz konto rozliczeniowe i kliknij UTWÓRZ.
  1. Gdy tworzenie projektu zostanie ukończone, w prawym górnym rogu strony pojawi się powiadomienie. Kliknij wpis Utwórz projekt: <nazwa projektu>, aby otworzyć projekt.
  2. W lewym górnym rogu kliknij ikonę menu  i wybierz Interfejsy API i usługi > Dane logowania. Kliknij kartę Ekran zgody OAuth (bezpośredni link).
  3. W polu Nazwa aplikacji wpisz „Big Data Codelab” i kliknij przycisk Zapisz u dołu.
  4. W prawym górnym rogu kliknij ikonę z 3 kropkami, aby rozwinąć menu, i wybierz Ustawienia projektu (bezpośredni link).
  5. Skopiuj wartość podaną w sekcji Numer projektu. (Istnieje osobne pole Identyfikator produktu, którego użyjemy później w tym laboratorium).
  6. Wróć do edytora Apps Script i kliknij Zasoby > Projekt Cloud Platform.
  7. Wpisz numer projektu w polu tekstowym i kliknij Ustaw projekt. Gdy pojawi się komunikat, kliknij Potwierdź.
  8. Po zakończeniu kliknij przycisk Zamknij, aby zamknąć okno.
  9. Po skonfigurowaniu nowego projektu musisz włączyć usługę zaawansowaną BigQuery. W tym celu kliknij Zasoby –> Zaawansowane usługi Google i włącz interfejs BigQuery API.


  10. U dołu znajduje się informacja: „Te usługi muszą też być włączone w „panelu interfejsów API Google Cloud Platform”, więc kliknij ten link, który otworzy w innej karcie przeglądarki konsolę dewelopera lub „devconsole”.
  11. W konsoli deweloperskiej kliknij u góry przycisk + Włącz interfejsy API i usługi, wyszukaj „bigquery”, wybierz BigQuery API (nie BigQuery Data Transfer API) i kliknij Włącz, aby go włączyć. Pozostaw tę kartę przeglądarki otwartą.

    UWAGA: po włączeniu interfejsu API na tej stronie może pojawić się komunikat w stylu „Aby używać tego interfejsu API, musisz utworzyć dane logowania…”, ale na razie się tym nie przejmuj – Apps Script zajmie się tym za Ciebie.


  12. Wróć do karty przeglądarki z edytorem kodu. Nadal jesteś w menu Advanced Google Services (Zaawansowane usługi Google), więc kliknij OK, aby zamknąć okno i wrócić do edytora kodu. Kliknij nazwę projektu u góry i wpisz dowolną nazwę, np. „Demonstracja BigQuery”. My nazwaliśmy nasz projekt „ostatnia prosta”.

Możesz teraz wpisać kod aplikacji, przejść przez proces autoryzacji i zobaczyć swoją aplikację po raz pierwszy w działaniu.

Przesyłanie i uruchamianie aplikacji

  1. Skopiuj kod znajdujący się w polu poniżej i wklej go w edytorze kodu, zastępując całą wcześniejszą zawartość:
// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BQ job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the new results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}


Zapisz utworzony plik, ale zmień jego nazwę z Code.gs na bq-sheets-slides.js. Co robi ten kod? Wiesz już, że wykonuje zapytanie BigQuery i zapisuje wyniki w nowym arkuszu Google. Ale co to za zapytanie? Znajduje się ona w górnej części runQuery():

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10


To zapytanie przeszukuje dzieła Szekspira (część publicznego zbioru danych BigQuery) i generuje 10 słów najczęściej występujących we wszystkich jego tekstach, posortowanych pod względem częstotliwości występowania w kolejności malejącej. Jeśli wyobrazisz sobie, ile wysiłku trzeba włożyć, by zrobić to ręcznie, z łatwością przekonasz się o przydatności BigQuery.

  1. Jesteśmy prawie gotowi, ale jeszcze nie do końca. Jak widać u góry tego fragmentu kodu, wymagany jest prawidłowy identyfikator projektu, więc musimy dodać go do kodu aplikacji. Aby go uzyskać, wróć do okna lub karty przeglądarki ze stroną konsoli dewelopera. (Pamiętasz, że mieliśmy zostawić je otwarte?)
  2. U góry po lewej stronie awatara konta Google znajduje się menu (). Kliknij je i wybierz Ustawienia projektu. Zobaczysz nazwę, identyfikator i numer projektu. Skopiuj identyfikator projektu i ustaw zmienną PROJECT_ID u góry pliku bq-sheets-slides.js na wartość uzyskaną z konsoli dewelopera. UWAGA: jeśli selektor menu przestanie działać, załaduj stronę ponownie.
  3. Instrukcja if służy do tego, by działanie aplikacji nie było kontynuowane bez identyfikatora projektu. Po dodaniu własnego kodu zapisz plik i uruchom kod, przechodząc do paska menu i wybierając Uruchom > Uruchom funkcję > runQuery, a następnie kliknij okno Przejrzyj uprawnienia. Ta aplikacja nie jest zweryfikowana. Poniżej znajduje się animowany GIF (innej aplikacji) ilustrujący kolejne kroki:
  4. Gdy poprosisz o sprawdzenie uprawnień, pojawi się nowe okno dialogowe (jak pokazano powyżej). Wybierz właściwe konto Google, na którym będzie uruchamiany skrypt, kliknij Zaawansowane, przewiń w dół, a następnie kliknij „Otwórz <NAZWA PROJEKTU> (niebezpieczne)”, aby przejść do ekranu autoryzacji aplikacji OAuth2. (Więcej informacji o procesie weryfikacji znajdziesz w artykule, który wyjaśnia, dlaczego ten ekran znajduje się między Tobą a oknem autoryzacji OAuth2 poniżej).


    UWAGA: gdy raz autoryzujesz aplikację, nie będziesz musiał(a) tego robić przy każdym jej uruchomieniu. Zobaczysz to okno ponownie dopiero, gdy dojdziesz do zadania 3 w dalszej części tego samouczka. Pojawi się tam prośba o utworzenie prezentacji Google i nadanie uprawnień do zarządzania nią.
  5. Gdy w oknie dialogowym OAuth2 klikniesz Zezwól, skrypt zacznie działać. U góry zobaczysz pastelowe żółte okno dialogowe. Działa dość szybko, więc możesz nie zauważyć, że jest uruchomiony lub że jego wykonanie zostało zakończone.

  6. To okno zniknie po zakończeniu działania funkcji, więc jeśli go nie widzisz, być może funkcja została już wykonana. Otwórz Dysk Google drive.google.com i znajdź nowy arkusz Google o nazwie „Most common words in all of Shakespeare's works” (Słowa występujące najczęściej we wszystkich dziełach Szekspira) lub innej przypisanej do zmiennej QUERY_NAME:


  7. Otwórz arkusz kalkulacyjny. Powinien zawierać 10 wierszy ze słowami i ich łącznymi liczbami wystąpień, posortowanymi w kolejności malejącej:

Podsumowanie zadania 1

Zastanów się, co się właśnie stało… Uruchomiono kod, który przeszukał wszystkie dzieła Szekspira. (Ilość danych może nie jest OLBRZYMIA, ale z pewnością tekstu jest więcej, niż można w rozsądnym czasie samodzielnie przeczytać, licząc wystąpienia poszczególnych słów w każdej sztuce, by na koniec posortować je w kolejności malejącej). Większość pracy wykonała za Ciebie usługa BigQuery, a za przygotowanie danych do łatwego użycia w Arkuszach Google odpowiada usługa wbudowana w Apps Script.

Kod dla pliku bq-sheets-slides.js (wybrana nazwa pliku) wklejony powyżej (z wyjątkiem PROJECT_ID, który powinien mieć prawdziwy identyfikator projektu) znajdziesz też w folderze step1 w repozytorium GitHub tego modułu pod adresem github.com/googlecodelabs/bigquery-sheets-slides. Kod został zainspirowany oryginalnym przykładem na stronie usług zaawansowanych BigQuery, który uruchamiał nieco inne zapytanie: jakie są najpopularniejsze słowa używane przez Szekspira, które mają co najmniej 10 znaków. Przykładowy kod znajdziesz też w repozytorium GitHub.

Jeśli interesują Cię inne zapytania, które możesz uruchomić w odniesieniu do dzieł Szekspira lub innych publicznych tabel danych, zajrzyj na tę stronę i tę. Niezależnie od tego, jakiego zapytania używasz, zawsze możesz je przetestować w konsoli BigQuery, zanim uruchomisz je w Apps Script. Interfejs użytkownika BigQuery jest dostępny dla programistów pod adresem bigquery.cloud.google.com. Tak wygląda na przykład nasze zapytanie w interfejsie BigQuery:

Powyższe kroki wykorzystują edytor kodu Apps Script, ale możesz też programować lokalnie za pomocą wiersza poleceń. Jeśli wolisz, utwórz skrypt o nazwie bq-sheets-slides.js, wklej do niego powyższy kod, a następnie prześlij go do Google za pomocą polecenia clasp push. (Jeśli wcześniej Ci umknęło, jeszcze raz podajemy link do clasp i wyjaśniamy, jak z niego korzystać).

Funkcja runQuery() służy do komunikowania się z BigQuery i wysyłania wyników do arkusza. Teraz musimy utworzyć wykres na podstawie tych danych. Utwórzmy nową funkcję o nazwie createColumnChart(), która wywołuje metodę newChart() arkusza.

  1. Utwórz wykres Dodaj treść createColumnChart() poniżej do bq-sheets-slides.js tuż po runQuery(). Pobiera arkusz z danymi i wysyła żądanie utworzenia wykresu kolumnowego zawierającego wszystkie dane. Początek zakresu danych to komórka A2, ponieważ pierwszy wiersz zawiera nagłówki kolumn, a nie dane.
/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above params.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}
  1. Zwróć arkusz kalkulacyjny. W powyższym przykładzie funkcja createColumnChart() wymaga obiektu arkusza kalkulacyjnego, dlatego musimy zaktualizować funkcję runQuery(), aby zwracała obiekt spreadsheet, który można przekazać do funkcji createColumnChart(). Po zalogowaniu pomyślnego utworzenia arkusza Google zwróć obiekt spreadsheet na końcu funkcji runQuery(), tuż po wierszu dziennika:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Funkcja createBigQueryPresentation()samochodu. Bardzo dobrym pomysłem jest logiczne oddzielenie funkcji wykonywania zapytania BigQuery od funkcji tworzenia wykresu. Teraz utwórzmy funkcję createBigQueryPresentation(), która będzie sterować aplikacją, wywołując funkcje runQuery() i createColumnChart(). Dodany kod powinien wyglądać mniej więcej tak:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}
  1. Zwiększanie możliwości ponownego użycia kodu Powyżej wykonywane są 2 ważne kroki – zwracany jest obiekt arkusza kalkulacyjnego i tworzona jest funkcja odpowiedzialna za działanie aplikacji. Łatwo sobie wyobrazić, że ktoś chciałby skorzystać z funkcji runQuery(), ale bez konieczności logowania URL-a. Jeśli chcesz, by funkcja runQuery() była bardziej uniwersalna, możesz przenieść w inne miejsce wiersz logowania. Które miejsce byłoby najlepsze? Jeśli uważasz, że najlepiej byłoby umieścić ją w ramach funkcji createBigQueryPresentation(), to masz rację. Po przeniesieniu wiersza logowania funkcja powinna wyglądać podobnie do tej:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

Po wprowadzeniu powyższych zmian (ponownie z wyjątkiem PROJECT_ID) Twój plik bq-sheets-slides.js powinien wyglądać podobnie do tego (znajdziesz go też w folderze step2 w repozytorium GitHub):

// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Sheet} Returns a sheet with results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BQ job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the new results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above params.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

Zapisz plik, a potem przejdź na początek edytora kodu i zmień wykonywanie z runQuery() na createBigQueryPresentation(). Po uruchomieniu na Twoim Dysku Google pojawi się kolejny arkusz Google, ale tym razem obok danych będzie zawierał wykres:

W ostatniej części modułu utworzysz nową prezentację Google: wpiszesz tytuł i podtytuł na slajdzie tytułowym, a potem dodasz 2 nowe slajdy – jeden ze wszystkimi komórkami danych, a drugi z wykresem.

  1. Utwórz prezentację. Wszystkie operacje związane z prezentacją będą wykonywane w funkcji createSlidePresentation(), którą dodamy do pliku bq-sheets-slides.js zaraz za funkcją createColumnChart(). Zacznij od utworzenia nowej prezentacji, następnie dodaj tytuł i podtytuł na domyślnym slajdzie tytułowym, który mają wszystkie nowe prezentacje.
/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Slide deck with results
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');
  1. Dodaj tabelę danych. Następnym krokiem w funkcji createSlidePresentation() jest zaimportowanie danych z komórek arkusza Google do nowej prezentacji. Ten fragment kodu dodaj do funkcji:
  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it of
  // the dimensions of the data range; fails if Sheet empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }
  1. Importuj wykres Ostatnim krokiem w funkcji createSlidePresentation() jest utworzenie kolejnego slajdu, zaimportowanie wykresu z arkusza kalkulacyjnego i zwrócenie obiektu Presentation. Dodaj do funkcji ten ostatni fragment kodu:
  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}
  1. Wykres zwrotów Teraz gdy ostatnia funkcja jest gotowa, jeszcze raz przyjrzyj się jej deklaracji. Tak, funkcja createSlidePresentation() wymaga przekazania obiektów spreadsheet (arkusz kalkulacyjny) i chart (wykres). Skorygowaliśmy już funkcję runQuery() tak, że zwraca obiekt Spreadsheet, a teraz musimy wprowadzić podobną zmianę do funkcji createColumnChart() – musi ona zwracać obiekt wykresu (EmbeddedChart). Cofnij się w kodzie aplikacji i dodaj jeszcze jeden wiersz na końcu funkcji createColumnChart():
  // NEW: Return chart object for later use
  return chart;
}
  1. ZaktualizujcreateBigQueryPresentation(). Funkcja createColumnChart() zwraca wykres, więc musimy zapisać go w zmiennej, a następnie przekazać zarówno arkusz kalkulacyjny, jak i wykres do funkcji createSlidePresentation(). Ponieważ rejestrujemy adres URL nowo utworzonego arkusza kalkulacyjnego, zarejestrujmy też adres URL nowej prezentacji. Zaktualizuj urządzenie createBigQueryPresentation(), aby wyglądało tak:
/**
 * Runs a BigQuery query, adds data and a chart in a Sheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}
  1. Zapisz i ponownie uruchom createBigQueryPresentation(). Zanim się wykona, jeszcze raz pojawi się prośba o ustawienie uprawnień do wyświetlania prezentacji Google i zarządzania nimi. Gdy zezwolisz na to uprawnienie, będzie działać jak wcześniej.
  2. Oprócz utworzonego arkusza powinna pojawić się nowa prezentacja zawierająca 3 slajdy (tytułowy, z tabelą danych i z wykresem danych) podobne do przedstawionych poniżej:

Gratulacje! Udało Ci się utworzyć aplikację, która wykorzystuje obie strony Google Cloud. Wykonuje ona żądanie Google BigQuery, które wysyła zapytanie do jednego z publicznych zbiorów danych, tworzy nowy Arkusz Google do przechowywania wyników, dodaje wykres na podstawie właśnie pobranych danych i wreszcie tworzy prezentację w Prezentacjach Google, która zawiera wyniki oraz wykres z arkusza kalkulacyjnego.

Tak to wygląda z technicznego punktu widzenia. Mówiąc prościej, analiza dużego zbioru danych została przekształcona do postaci, którą łatwo zaprezentować wszystkim zainteresowanym. Zostało to wykonane przy użyciu kodu i w zautomatyzowany sposób. Mamy nadzieję, że ten przykład zainspiruje Cię do dostosowania go do własnych projektów. Na zakończenie tego ćwiczenia podamy kilka sugestii, jak jeszcze bardziej ulepszyć tę przykładową aplikację.

Po wprowadzeniu powyższych zmian z ostatniego zadania (ponownie z wyjątkiem PROJECT_ID) Twój plik bq-sheets-slides.js powinien wyglądać tak (znajdziesz go też w folderze final w repozytorium GitHub):

bq-sheets-slides.js

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into Sheet. You must enable
 * the BigQuery advanced service before you can run this code.
 * @see http://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see http://github.com/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BQ job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the new results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} visualizing the results
 * @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first and only) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above params.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);

  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data & chart
 * @param {Spreadsheet} Spreadsheet with results data
 * @param {EmbeddedChart} Sheets chart to embed on slide
 * @returns {Presentation} Returns a slide deck with results
 * @see http://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the new presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it of
  // the dimensions of the data range; fails if Sheet empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart in a Sheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

W tym samouczku nie będziemy używać „drugiego pliku”, czyli pliku manifestu Apps Script appsscript.json. Aby uzyskać do niego dostęp, otwórz kartę przeglądarki edytora kodu i w menu u góry wybierz Widok > Pokaż plik manifestu. Zawartość powinna wyglądać mniej więcej tak:

appsscript.json

{
  "timeZone": "America/Los_Angeles",
  "dependencies": {
    "enabledAdvancedServices": [{
      "userSymbol": "BigQuery",
      "serviceId": "bigquery",
      "version": "v2"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

Plik manifestu to plik konfiguracyjny na poziomie systemu, którego Apps Script używa, aby określić, jakie środowisko wykonawcze ma być dostępne dla aplikacji. Omówienie zawartości pliku manifestu wykracza poza zakres tych ćwiczeń, ale możesz się z nim zapoznać.

Poniżej znajdziesz dodatkowe zasoby, które pomogą Ci pogłębić informacje przedstawione w tym module i poznać inne sposoby korzystania z narzędzi Google dla programistów. Staramy się na bieżąco uwzględniać w tym module wszystkie zmiany wprowadzane w repozytorium.

Zasoby tej aplikacji

Dokumentacja

Inne ćwiczenia z programowania

Początkowy

Średnio zaawansowany

Aplikacje z materiałami źródłowymi

Poniżej znajdziesz różne „wyzwania związane z kodem”, czyli sposoby na ulepszenie lub rozszerzenie przykładu, który został utworzony w tym samouczku. Ta lista nie jest wyczerpująca, ale powinna dostarczyć Ci inspiracji do podjęcia kolejnych kroków.

  • Aplikacja Nie chcesz być ograniczony przez JavaScript lub ograniczenia narzucone przez Apps Script? Przenieś tę aplikację na ulubiony język programowania, który korzysta z interfejsów API REST do Google BigQuery, Arkuszy i Prezentacji.
  • BigQuery. Wypróbuj inne zapytanie dotyczące zbioru danych Szekspira… może znajdziesz takie, które Cię zainteresuje. Inne przykładowe zapytanie znajdziesz w oryginalnej przykładowej aplikacji Apps Script BigQuery.
  • BigQuery. Wypróbuj inne publiczne zbiory danych BigQuery. Być może znajdziesz zbiór danych, który będzie dla Ciebie bardziej przydatny.
  • BigQuery. Wcześniej wspominaliśmy o innych zapytaniach, które możesz uruchomić w odniesieniu do dzieł Szekspira lub innych publicznych tabel danych. Chcemy ponownie udostępnić tę stronę internetową oraz tę.
  • Arkusze. Eksperymentuj z innymi typami wykresów.
  • Arkuszach i BigQuery. Odwróć sytuację – być może masz gdzieś w arkuszu kalkulacyjnym duży zbiór danych. W 2016 r. zespół BigQuery wprowadził funkcję umożliwiającą programistom używanie arkusza jako źródła danych (więcej informacji znajdziesz w postach na blogu 1 i 2).
  • Prezentacje. Dodaj do wygenerowanej prezentacji inne slajdy, np. obrazy lub inne komponenty powiązane z analizą Big Data. Aby zacząć, zapoznaj się z przewodnikiem po wbudowanej usłudze Prezentacji.
  • G Suite znajdować zastosowania dla innych usług wbudowanych G Suite lub Google w Apps Script, np. Gmail, Dysk Google, Kalendarz, Dokumenty, Mapy, Analytics, YouTube itp., a także inne usługi zaawansowane. Więcej informacji o usługach wbudowanych i zaawansowanych znajdziesz w omówieniu.