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

1. Od analizy big data do prezentacji

Badacze danych mają dostęp do wielu narzędzi, które pozwalają na przeprowadzanie analiz big data. Analitycy muszą jednak uzasadniać wyniki tych analiz przed kierownictwem. Wiele liczb na papierze lub w bazie danych trudno przedstawić kluczowym interesariuszom. Ten moduł praktyczny dotyczący Google Apps Script na poziomie średnio zaawansowanym wykorzystuje 2 platformy Google dla programistów (Google Workspace i konsola Google Cloud), aby pomóc Ci zautomatyzować ten ostatni etap.

Narzędzia dla programistów Google Cloud umożliwiają przeprowadzanie szczegółowych analiz danych. Następnie możesz wstawić wyniki do arkusza kalkulacyjnego i wygenerować prezentację slajdów z danymi. Jest to bardziej odpowiedni sposób przekazywania danych kierownictwu. W tym module poznasz dostępny w konsoli Cloud (jako zaawansowana usługa Apps Script) interfejs API BigQuery oraz wbudowane usługi Apps Script w Arkuszach Google i Prezentacjach Google.

Motywacja

Przykładowa aplikacja w tym ćwiczeniu Codelabs została zainspirowana tymi przykładowymi kodami:

Przykładowa aplikacja usługi BigQuery w Google Apps Script, która jest dostępna na GitHubie jako oprogramowanie typu open source.
Przykładowa aplikacja przedstawiona w filmie dla deweloperów Generowanie slajdów na podstawie danych z arkusza kalkulacyjnego i opublikowana w tym poście na blogu.
Przykładowa aplikacja przedstawiona w samouczku dotyczącym interfejsu Google Slides API.

Przykładowa aplikacja w module Slides API również korzysta z BigQuery i Prezentacji, ale różni się od tej z tego modułu w kilku aspektach:

ich aplikacja Node.js a nasza aplikacja Apps Script.
Korzystają z interfejsów API REST, a my z usług Apps Script.
Korzystają one 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ć wiele technologii w jednej aplikacji, prezentując funkcje i interfejsy API z różnych usług Google Cloud w sposób przypominający rzeczywisty przypadek użycia. Chcemy Cię zainspirować do wykorzystania wyobraźni i rozważenia użycia zarówno konsoli Cloud, jak i Google Workspace do rozwiązywania skomplikowanych problemów, z którymi borykają się Twoja organizacja i klienci.

Czego się nauczysz

Jak używać Google Apps Script z wieloma usługami Google
jak za pomocą Google BigQuery analizować big data;
Jak utworzyć arkusz Google i wstawić do niego dane
Jak utworzyć wykres w Arkuszach
Jak przenieść dane i wykresy z Arkuszy do prezentacji w Prezentacjach Google

Czego potrzebujesz

przeglądarki internetowej z dostępem do internetu,
konto Google (w przypadku kont Google Workspace może być wymagana zgoda administratora);
Podstawowa znajomość Arkuszy Google.
Możliwość odczytywania notacji A1 w Arkuszach
Podstawowe umiejętności w zakresie JavaScriptu
Znajomość programowania w Apps Script jest pomocna, ale nie jest wymagana.

2. Ankieta

Jak zamierzasz wykorzystać ten codelab lub samouczek?

Przeczytaj go, aby zdobyć informacje, i możliwe, że przekaż go kolegom z działu technicznego

Przejdź przez niego tak daleko, jak to możliwe, i wykonaj jak najwięcej ćwiczeń

Za wszelką cenę ukończę cały codelab

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

Początkujący

Średnio zaawansowany

Zaawansowany

Jak oceniasz korzystanie z Apps Script?

Początkujący

Średnio zaawansowany

Zaawansowany

Jak oceniasz swoje doświadczenia z narzędziami dla deweloperów i interfejsami API w konsoli Cloud?

Początkujący

Średnio zaawansowany

Zaawansowany

3. Przegląd

Teraz, gdy wiesz już, czego dotyczy to ćwiczenie, zobacz, co będziesz robić:

Skorzystaj z istniejącego przykładu Apps Script i BigQuery i uruchom go.
Z przykładowego kodu dowiesz się, jak wysłać zapytanie do BigQuery i uzyskać wyniki.
Utwórz arkusz Google i wstaw do niego wyniki z BigQuery.
Zmodyfikuj kod, aby nieznacznie zmienić dane zwracane i wstawiane do arkusza.
Użyj usługi Arkusze w Apps Script, aby utworzyć wykres na podstawie danych BigQuery.
Użyj usługi Prezentacje, aby utworzyć prezentację Google.
Dodaj tytuł i podtytuł do domyślnego slajdu tytułowego.
Utwórz slajd z tabelą danych i wyeksportuj do niego komórki danych z arkusza.
Utwórz kolejny slajd i wstaw do niego wykres z arkusza kalkulacyjnego.

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

Google Apps Script i BigQuery

Google Apps Script to platforma programistyczna Google Workspace, która 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 to bezserwerowe środowisko wykonawcze języka JavaScript służące do automatyzacji, rozszerzania i integracji funkcji Google Workspace.

Jako implementacja języka JavaScript po stronie serwera Apps Script przypomina 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 Google Workspace i innymi usługami Google. Ponadto środowisko programistyczne Apps Script może różnić się od innych używanych przez Ciebie do tej pory. Apps Script umożliwia:

Tworzenie skryptów w edytorze kodu działającym w przeglądarce, ale też 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 Google Workspace oraz innych usług Google lub pozostałych firm (za pomocą narzędzi Apps Script URL Fetch lub JDBC);
Nie musisz pisać kodu autoryzacji, ponieważ Apps Script zapewnia jego obsługę.
rezygnację z hostowania utworzonej aplikacji – będzie ona działać na serwerach Google w chmurze.

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

Jako usługa wbudowana
Jako usługa zaawansowana

Usługa wbudowana udostępnia metody wysokiego poziomu do interakcji z danymi użytkownika, innymi systemami Google i systemami zewnętrznymi. Usługa zaawansowana to w zasadzie cienka otoka dla interfejsu Google Workspace API lub interfejsu API Google typu REST. 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ż pełny interfejs 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 korzystać z usług wbudowanych, ponieważ są łatwiejsze w użyciu i wykonują więcej 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ą. Na przykład Google BigQuery nie ma usługi wbudowanej, ale istnieje usługa BigQuery. Usługa BigQuery to usługa Cloud Console, która umożliwia korzystanie z interfejsu Google BigQuery API do wykonywania zapytań dotyczących dużych zbiorów danych (np. wielu terabajtów), która zwraca wyniki w czasie liczonym w sekundach.

Dostęp do Arkuszy i Prezentacji z poziomu Apps Script

W przeciwieństwie do BigQuery zarówno Arkusze, jak i Prezentacje mają usługi wbudowane. Mają też usługi zaawansowane, które umożliwiają korzystanie z funkcji dostępnych tylko w interfejsie API. Zanim zagłębisz się w kod, zapoznaj się z dokumentacją wbudowanych usług Arkuszy i Prezentacji. Pamiętaj, że istnieją też dokumenty dotyczące usług zaawansowanych Arkuszy i Prezentacji.

4. Zadanie 1. Uruchomienie zapytania BigQuery i zapisanie wyników w Arkuszach

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. Podzielony na kilka podsekcji, w których:

Utwórz projekt Google Apps Script i projekt w konsoli Google Cloud.
Włącz dostęp do usługi zaawansowanej BigQuery.
Otwórz edytor skryptów i wpisz kod źródłowy aplikacji.
Przejdziesz proces autoryzacji aplikacji (OAuth2).
Uruchom aplikację wysyłającą żądanie do BigQuery.
Sprawdź nowy arkusz Google utworzony na podstawie wyników pochodzących z BigQuery.

Konfiguracja

Aby utworzyć projekt Apps Script, wejdź na script.google.com i kliknij Nowy projekt.
Aby zmienić nazwę projektu Apps Script, kliknij Projekt bez tytułu, wpisz tytuł projektu i kliknij Zmień nazwę.

Następnie musisz utworzyć projekt w konsoli Cloud, aby wykonywać zapytania dotyczące danych w BigQuery.

Aby utworzyć projekt w konsoli Cloud, użyj tego skrótu do utworzenia projektu, nadaj mu nazwę i kliknij Utwórz.

Gdy projekt zostanie utworzony, na stronie pojawi się powiadomienie. Sprawdź, czy nowy projekt jest zaznaczony na liście projektów u góry strony.
Kliknij Menu i otwórz Interfejsy API i usługi > Ekran zgody OAuth (link bezpośredni).
Kliknij Wewnętrzne > Utwórz, aby utworzyć aplikację dla użytkowników Google Workspace w organizacji.
W polu Nazwa aplikacji wpisz „Big Data Codelab”.
Wpisz adresy e-mail do kontaktu w polach Pomoc dla użytkowników i Informacje kontaktowe dewelopera.
Kliknij Zapisz i kontynuuj > Zapisz i kontynuuj.
Na pasku nawigacyjnym kliknij Więcej i wybierz Ustawienia projektu (bezpośredni link).
Skopiuj wartość podaną w sekcji Numer projektu. (Osobne pole identyfikator projektu zostanie użyte później w tym przewodniku).

Następnie połącz projekt Apps Script z projektem Cloud Console.

Przejdź do edytora skryptów Apps Script i kliknij Ustawienia projektu .
W sekcji Projekt Google Cloud Platform (GCP) kliknij Zmień projekt.
Wpisz numer projektu i kliknij Ustaw projekt.
Następnie kliknij Edytor , aby rozpocząć dodawanie usługi zaawansowanej BigQuery.
Obok opcji Usługi kliknij Dodaj usługę .
W oknie Dodaj usługę wybierz BigQuery API i kliknij Dodaj.

Ostatnim krokiem jest włączenie interfejsu BigQuery API w Cloud Console.

Aby to zrobić, przejdź do konsoli Cloud i kliknij Interfejsy API i usługi > Panel. (Upewnij się, że nadal pracujesz nad tym samym projektem, który został utworzony w kroku 3).
Kliknij Włącz interfejsy API i usługi.
Wyszukaj „big query”, wybierz BigQuery API (nie BigQuery Data Transfer API) i kliknij Włącz, aby go włączyć.

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

Prześlij aplikację i uruchom ją

W edytorze skryptów zastąp domyślny blok kodu myFunction() tym kodem:

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

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://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 BigQuery 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 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());
}

Kliknij Zapisz .
Obok ikony Code.gs kliknij Więcej > Zmień nazwę. Zmień tytuł z Code.gs na bq-sheets-slides.js.
Następnie sprawdźmy kod, który wysyła zapytanie do BigQuery i zapisuje wyniki w arkuszu Google. Możesz go zobaczyć u góry sekcji 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.

Funkcja deklaruje też zmienną PROJECT_ID, która wymaga prawidłowego identyfikatora projektu konsoli Cloud. Instrukcja if pod zmienną służy do tego, by działanie aplikacji nie było kontynuowane bez identyfikatora projektu.

Przejdź do projektu w konsoli Google Cloud, na pasku nawigacyjnym kliknij Więcej i wybierz Ustawienia projektu.
Skopiuj wartość podaną w sekcji Identyfikator projektu.
Wróć do edytora Apps Script, znajdź zmienną PROJECT_ID w bq-sheets-slides.js i dodaj wartość.
Kliknij Zapisz > Uruchom.
Aby kontynuować, kliknij Przejrzyj uprawnienia.

Gdy skrypt zacznie działać, otworzy się wbudowany dziennik wykonania, w którym będą rejestrowane działania skryptu w czasie rzeczywistym.

Gdy w dzienniku wykonania pojawi się komunikat „Execution completed” (Wykonanie zakończone), otwórz Dysk Google (drive.google.com) i znajdź 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 (jeśli została zaktualizowana):
Otwórz arkusz kalkulacyjny, aby wyświetlić 10 najczęściej występujących słów i ich łączną liczbę wystąpień posortowaną w kolejności malejącej:

Podsumowanie zadania 1

Przypomnijmy, że uruchomiliśmy kod, który przeszukał wszystkie dzieła Szekspira, sprawdzając każde słowo w każdej sztuce. Zliczył słowa i posortował je w kolejności malejącej według liczby wystąpień. Do wyświetlania tych danych użyto też wbudowanej w Apps Script usługi Arkuszy Google.

Kod użyty w bq-sheets-slides.js znajdziesz też w step1 folderze repozytorium GitHub tego ćwiczenia: github.com/googlecodelabs/bigquery-sheets-slides. Kod został zainspirowany tym oryginalnym przykładem na stronie usług zaawansowanych BigQuery, który uruchamiał nieco inne zapytanie pobierające najpopularniejsze słowa używane przez Szekspira, składające się z co najmniej 10 znaków. Przykład znajdziesz też w repozytorium na GitHubie.

Jeśli interesują Cię inne zapytania, które możesz utworzyć na podstawie dzieł Szekspira lub innych publicznych tabel danych, zapoznaj się z artykułem Jak wysyłać zapytania do przykładowych tabel BigQuery i tym repozytorium GitHub.

Zanim uruchomisz zapytania w Apps Script, możesz je też uruchomić na stronie BigQuery w Cloud Console. Aby go znaleźć, kliknij Menu i otwórz interfejs BigQuery > obszar roboczy SQL (link bezpośredni). Oto jak nasze zapytanie wygląda w graficznym interfejsie BigQuery:

BigQueryUI

5. Zadanie 2. Tworzenie wykresu w Arkuszach Google

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

W edytorze skryptów Apps Script dodaj funkcję createColumnChart() do bq-sheets-slides.js po runQuery(). Kod pobiera arkusz 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.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) 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 values.
  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);
}

Funkcja createColumnChart() wymaga parametru obiektu arkusza kalkulacyjnego, więc musimy zaktualizować runQuery(), aby zwracała obiekt spreadsheet, który możemy przekazać do createColumnChart(). Na końcu funkcji runQuery() zwróć obiekt spreadsheet po zalogowaniu pomyślnego utworzenia arkusza:

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

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

Utwórz funkcję createBigQueryPresentation(), która wywołuje funkcje runQuery() i createColumnChart(). Logicznym oddzieleniem funkcji wykonywania zapytania BigQuery od funkcji tworzenia wykresu jest najlepsza praktyka:

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

Powyżej wykonywane są 2 ważne kroki – zwracany jest obiekt arkusza kalkulacyjnego i tworzona jest funkcja wejścia. Aby funkcja runQuery() była bardziej użyteczna, musimy przenieść wiersz logowania z runQuery() do createBigQueryPresentation(). Metoda powinna teraz wyglądać tak:

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

Po wprowadzeniu powyższych zmian (z wyjątkiem PROJECT_ID) plik bq-sheets-slides.js powinien wyglądać tak: Ten kod znajdziesz też w step2 repozytorium GitHub.

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

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://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 BigQuery 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 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 a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) 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 values.
  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 to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

W edytorze skryptów zapisz projekt skryptu. Następnie wybierz createBigQueryPresentation z listy funkcji i kliknij Uruchom. Po zakończeniu działania skryptu na Twoim Dysku Google pojawi się kolejny arkusz kalkulacyjny, ale tym razem obok danych będzie zawierał wykres:

Arkusz z wykresem

6. Zadanie 3. Umieszczanie wyników w prezentacji

W ostatniej części modułu utworzysz prezentację Google: dodasz tytuł i podtytuł na slajdzie tytułowym, a potem utworzysz slajdy z komórkami danych i wykresem.

W edytorze skryptów Apps Script dodaj funkcję createSlidePresentation() do bq-sheets-slides.js po createColumnChart(). Wszystkie operacje związane z prezentacją są wykonywane w tej funkcji. Zacznijmy od utworzenia prezentacji, a następnie dodajmy tytuł i podtytuł na domyślnym slajdzie tytułowym.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the 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('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

Następnym krokiem w funkcji createSlidePresentation() jest zaimportowanie danych z komórek arkusza Google do nowej prezentacji. Dodaj do funkcji ten fragment kodu:

  // 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 with
  // the dimensions of the data range; fails if the sheet is 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]));
    }
  }

Ostatnim krokiem w funkcji createSlidePresentation() jest dodanie kolejnego slajdu, zaimportowanie wykresu z arkusza kalkulacyjnego i zwrócenie obiektu Presentation. Dodaj do funkcji ten 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;
}

Teraz gdy funkcja jest gotowa, jeszcze raz przyjrzyj się jej deklaracji. Funkcja createSlidePresentation() wymaga podania parametrów obiektu arkusza kalkulacyjnego i wykresu. Skorygowaliśmy już funkcję runQuery() tak, że zwraca obiekt Spreadsheet, ale musimy wprowadzić podobną zmianę do funkcji createColumnChart(), aby zwracała obiekt wykresu (EmbeddedChart). Wróć do funkcji createColumnChart() i dodaj na końcu ten fragment kodu:

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

Funkcja createColumnChart() zwraca teraz obiekt wykresu, więc musimy zapisać wykres w zmiennej. Następnie przekazujemy do createSlidePresentation() zarówno arkusz kalkulacyjny, jak i zmienne wykresu. Ponieważ rejestrujemy adres URL nowo utworzonego arkusza kalkulacyjnego, zarejestrujmy też adres URL nowej prezentacji. Zaktualizuj createBigQueryPresentation(), aby wyglądał tak:

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * 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
}

Zapisz i uruchom ponownie createBigQueryPresentation(). Zanim się wykona, aplikacja potrzebuje jeszcze jednej grupy uprawnień od użytkownika, aby wyświetlać prezentacje Google i nimi zarządzać. Gdy sprawdzisz te uprawnienia i na nie zezwolisz, aplikacja będzie działać jak wcześniej.
Oprócz utworzonego arkusza kalkulacyjnego powinna pojawić się nowa prezentacja zawierająca 3 slajdy (tytułowy, z tabelą danych i z wykresem danych) podobne do przedstawionych poniżej:

7. Podsumowanie

Gratulacje! Utworzyliśmy aplikację, która korzysta z obu stron Google Cloud. Wysyła żądanie do Google BigQuery, które wysyła zapytanie do jednego z publicznych zbiorów danych, tworzy arkusz kalkulacyjny Arkuszy Google do przechowywania wyników, dodaje wykres na podstawie danych i na koniec tworzy prezentację w Prezentacjach Google zawierającą dane i wyniki wykresu 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 w zautomatyzowany sposób przy użyciu kodu. 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 możesz ulepszyć tę przykładową aplikację.

Po wprowadzeniu zmian z ostatniego zadania (z wyjątkiem PROJECT_ID) Twój plik bq-sheets-slides.js powinien wyglądać tak:

/**
 * 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.
 */

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

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://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 BigQuery 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 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 a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) 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 values.
  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 and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the 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('using Google Cloud Console and Google Workspace 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 with
  // the dimensions of the data range; fails if the sheet is 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 to a spreadsheet,
 * 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());
}

Ten przykładowy kod znajdziesz też w folderze final w repozytorium GitHub.

8. Dodatkowe materiały

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.

Zasoby tej aplikacji

Dokumentacja

Strona dokumentacji poświęconej Google Apps Script
Apps Script: usługa Arkuszy
Apps Script: usługa Prezentacji
Apps Script: zaawansowana usługa BigQuery

Filmy

Wiadomości i aktualności

Blog Google Cloud Platform
Blog Google Cloud Data Analytics
Blog Google Developers
Twitter: Google Developers (@googledevs)
Blog dla programistów Google Workspace
Twitter: Google Workspace Developers (@workspacedevs)

Inne ćwiczenia z programowania

Początkowy

[Arkusze Google] Podstawy Apps Script w Arkuszach Google
[Interfejsy API typu REST] Korzystanie z Google Workspace i interfejsów API Google w celu uzyskiwania dostępu do plików i folderów na Dysku Google

Średnio zaawansowany

[Apps Script] Narzędzie wiersza poleceń CLASP Apps Script
[Apps Script] Dodatki Google Workspace do Gmaila
[Apps Script] Niestandardowe boty do Hangouts Chat
[Interfejsy API REST] Używanie Arkuszy Google jako narzędzia do raportowania w aplikacji
[Interfejsy API REST] Generowanie prezentacji w Prezentacjach Google za pomocą interfejsu BigQuery API

9. Następny krok: wyzwania związane z kodowaniem

Poniżej znajdziesz różne sposoby ulepszania lub rozszerzania próbki, którą utworzyliśmy w tym laboratorium. Ta lista nie jest wyczerpująca, ale zawiera kilka inspirujących pomysłów na to, jak możesz zrobić kolejny krok.

Aplikacja – nie chcesz być ograniczony do używania JavaScriptu ani ograniczeń narzuconych przez Apps Script? Przenieś tę aplikację na swój 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, które Cię interesuje. Inne przykładowe zapytanie znajdziesz w oryginalnej przykładowej aplikacji Apps Script BigQuery.
BigQuery – wypróbuj inne publiczne zbiory danych BigQuery, aby znaleźć ten, który jest dla Ciebie bardziej przydatny.
BigQuery – wcześniej wspomnieliśmy o innych zapytaniach, które możesz utworzyć na podstawie dzieł Szekspira lub innych publicznych tabel danych. Znajdziesz je na tej stronie i w tym repozytorium GitHub.
Arkusze – wypróbuj inne typy wykresów w Galerii wykresów.
Arkusze i BigQuery – używaj własnego dużego zbioru danych z arkusza kalkulacyjnego. W 2016 r. zespół BigQuery wprowadził funkcję, która umożliwia deweloperom używanie arkusza jako źródła danych. Więcej informacji znajdziesz w artykule Integracja Google BigQuery z Dyskiem Google.
Slajdy – dodaj do wygenerowanej prezentacji inne slajdy, np. obrazy lub inne komponenty powiązane z analizą big data. Oto dokumentacja referencyjna wbudowanej usługi Prezentacji.
Google Workspace – korzystanie z innych usług Google Workspace lub wbudowanych usług Google w Apps Script. Na przykład Gmail, Kalendarz, Dokumenty, Dysk, Mapy, Analytics, YouTube itp., a także inne usługi zaawansowane. Więcej informacji znajdziesz w omówieniu usług wbudowanych i zaawansowanych.