Ta strona została przetłumaczona przez Cloud Translation API.

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

1. Od analizy big data po slajdy

Badacze danych mają dostęp do wielu narzędzi, które pozwalają im przeprowadzać analizy big data, ale w końcu analitycy muszą uzasadnić wyniki dla zarządzania. Dużo danych na papierze lub w bazie danych trudno jest przedstawić kluczowym zainteresowanym osobom. W tym ćwiczeniu z programowania na poziomie średnio zaawansowanym dotyczącym Google Apps Script wykorzystywane są 2 platformy Google dla programistów (Google Workspace i Google Cloud Console), które pomagają zautomatyzować ten ostatni etap.

Narzędzia dla programistów w Google Cloud umożliwiają przeprowadzanie dogłębnej analizy danych. Wyniki możesz następnie zapisać, wstawić do arkusza kalkulacyjnego i wygenerować prezentację slajdów z danymi. Zapewnia to bardziej odpowiedni sposób przekazywania danych do zarządzania. W ramach tego ćwiczenia w Codelabs dowiesz się, jak używać interfejsu API BigQuery w Cloud Console (jako zaawansowana usługa Apps Script) oraz wbudowane usługi Apps Script w Arkuszach Google i Prezentacjach Google.

Motywacja

Inspiracją do stworzenia przykładowej aplikacji objętej tym ćwiczeniem w programowaniu były te inne przykłady:

Przykładowa aplikacja usługi Google Apps Script BigQuery dostępna na licencji open source na GitHubie.
Przykładowa aplikacja przedstawiona w filmie dla programistów Generowanie slajdów na podstawie danych arkusza kalkulacyjnego i opublikowana w tym poście na blogu.
Przykładowa aplikacja przedstawiona w ćwiczeniu z programowania w interfejsie API Prezentacji Google.

Chociaż przykładowa aplikacja z ćwiczeniami w Codelabs API zawiera również BigQuery i Prezentacje, różni się ona od przykładowej aplikacji z tego ćwiczenia na kilka sposobów:

Ich aplikacja Node.js a nasza aplikacja Apps Script.
Używają interfejsów API REST, a my używamy 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 ramach tego ćwiczenia z programowania chcieliśmy połączyć wiele technologii w jedną aplikację, a jednocześnie zaprezentować funkcje i interfejsy API z całej Google Cloud w sposób zbliżony do rzeczywistego użycia. Chcemy zainspirować Cię do wykorzystania wyobraźni i rozważenia korzystania zarówno z Cloud Console, jak i Google Workspace do rozwiązywania skomplikowanych problemów, z którymi borykają się Twoja organizacja i jej klienci.

Czego się nauczysz

Jak używać Google Apps Script z wieloma usługami Google
Jak używać Google BigQuery do analizowania 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 Google

Czego potrzebujesz

Przeglądarka z dostępem do internetu
konto Google (konta Google Workspace mogą wymagać zatwierdzenia przez administratora),
Podstawowa znajomość Arkuszy Google
możliwość odczytywania notacji A1 w Arkuszach;
Podstawowe umiejętności JavaScript
Znajomość języka Apps Script jest przydatna, ale nie jest wymagana

2. Ankieta

W jaki sposób będziesz używać tego ćwiczenia z programowania/samouczka?

Zapoznaj się z nim w celach informacyjnych, na przykład przekaż go współpracownikom technicznym.

Przejdź przez cały czas i wypróbuj jak najwięcej ćwiczeń.

Nie dajcie się dogonić – zajmę się całym ćwiczeniem z programowania

Jak oceniasz swoje wrażenia z korzystania z narzędzi dla programistów Google Workspace Interfejsy API?

Początkujący

Poziom średnio zaawansowany

Biegły

Jak oceniasz swój poziom zadowolenia z Apps Script?

Początkujący

Poziom średnio zaawansowany

Biegły

Jak oceniasz swoje wrażenia z używania narzędzi dla programistów w konsoli Cloud Interfejsy API?

Początkujący

Poziom średnio zaawansowany

Biegły

3. Omówienie

Wiesz już, o co chodzi w ćwiczeniach z programowania. Oto co musisz zrobić:

Pobierz istniejącą próbkę Apps Script-BigQuery i zacznij działać.
Z przykładu dowiesz się, jak wysłać zapytanie do BigQuery i uzyskać wyniki.
Utwórz arkusz Google i wstaw w nim wyniki z BigQuery.
Zmodyfikuj kod, aby nieznacznie zmienić dane zwracane i wstawione do arkusza.
Korzystając z usługi Arkusze w Apps Script, możesz utworzyć wykres na dane BigQuery.
Utwórz prezentację w usłudze Prezentacje Google.
Dodaj tytuł i podtytuł do domyślnego slajdu tytułowego.
Utwórz slajd z tabelą danych i wyeksportuj do niej komórki danych arkusza.
Utwórz kolejny slajd i wstaw do niego wykres z arkusza kalkulacyjnego.

Zacznijmy od ogólnych informacji o Apps Script, BigQuery, Arkuszach i Prezentacjach.

Google Apps Script i BigQuery

Google Apps Script to platforma deweloperska Google Workspace działająca na wyższym poziomie niż interfejsy API Google typu REST. Jest to bezserwerowe środowisko do programowania i hostingu aplikacji dostępne dla programistów o każdym poziomie zaawansowania. Zasadniczo Apps Script to bezserwerowe środowisko wykonawcze JavaScript umożliwiające automatyzację, rozszerzenie i integrację Google Workspace.

Wykorzystuje JavaScript po stronie serwera, podobnie jak Node.js, ale koncentruje się na ścisłej integracji z Google Workspace i innymi usługami Google, a nie na szybkim, asynchronicznym hostingu aplikacji opartym na zdarzeniach. Ponadto środowisko programistyczne Apps Script może różnić się od używanego przez Ciebie do tej pory. Apps Script umożliwia:

Skrypty można tworzyć za pomocą edytora kodu działającego w przeglądarce lub lokalnie, używając narzędzia wiersza poleceń clasp do wdrażania skryptów Apps Script.
Napisz kod w wyspecjalizowanej wersji języka JavaScript, która umożliwia dostęp do Google Workspace i innych usług Google lub usług zewnętrznych (przy użyciu usług URL Fetch lub JDBC Apps Script).
Pozwala uniknąć pisania kodu autoryzacji, bo Apps Script zajmuje się nim za Ciebie.
Nie musisz hostować swojej 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 zaawansowane metody interakcji z danymi użytkowników, innymi systemami Google i systemami zewnętrznymi. Usługa zaawansowana to zasadniczo cienka powłoka otaczająca interfejs Google Workspace API lub interfejs API Google typu REST. Usługi zaawansowane pozwalają w pełni korzystać z interfejsu API REST i często mają więcej możliwości niż usługi wbudowane, ale wymagają bardziej skomplikowanego kodu (wciąż jednak są łatwiejsze w obsłudze niż pełny interfejs API REST). Zanim zaczniesz korzystać z usług zaawansowanych, musisz włączyć je w projekcie skryptu.

Deweloperzy w miarę możliwości powinni używać usługi wbudowanej, ponieważ są łatwiejsze w użyciu i dają więcej możliwości 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 wbudowanej usługi, ale istnieje usługa BigQuery. BigQuery to usługa konsoli Google Cloud, która pozwala używać interfejsu Google BigQuery API do wykonywania zapytań na dużych zbiorach danych (na przykład do wielu terabajtów), jednak nadal może zapewnić wyniki w ciągu kilku sekund.

Dostęp do Arkuszy i Prezentacje z Apps Script

W przeciwieństwie do BigQuery zarówno Arkusze, jak i Prezentacje mają usługi wbudowane. Mają też dostęp do funkcji zaawansowanych, które zapewniają dostęp do funkcji dostępnych tylko w interfejsie API. Zanim zagłębimy się w kod, przejrzyj dokumentację wbudowanych usług Arkuszy i Prezentacji. Dostępne są też dokumenty dotyczące usług zaawansowanych zarówno w Arkuszach, jak i w Prezentacjach.

4. Zadanie 1. Uruchom BigQuery i zapisz wyniki w Arkuszach

Wprowadzenie

To pierwsze zadanie obejmuje dużą część tego ćwiczenia z programowania. Gdy ukończysz ćwiczenia, będziesz w połowie całego ćwiczenia. Są one podzielone na kilka podsekcji:

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

Konfiguracja

Aby utworzyć projekt Apps Script, otwórz script.google.com i kliknij Nowy projekt.
Aby zmienić nazwę projektu Apps Script, kliknij Untitled project (Projekt bez tytułu), wpisz tytuł projektu i kliknij Zmień nazwę.

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

Aby utworzyć projekt w Cloud Console, utwórz projekt, nadaj mu nazwę i kliknij Utwórz za pomocą tego linku.

Po zakończeniu tworzenia projektu na stronie pojawi się powiadomienie. Na liście projektów u góry strony wybierz nowy projekt.
Kliknij Menu i otwórz Interfejsy API & Usługi > Ekran zgody OAuth (link bezpośredni).
Kliknij Wewnętrzny > Utwórz, aby utworzyć aplikację dla użytkowników Google Workspace w organizacji.
W polu App name (Nazwa aplikacji) wpisz „Big Data Codelab”.
Wpisz kontaktowe adresy e-mail 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 (link bezpośredni).
Skopiuj wartość podaną w sekcji Numer projektu. W dalszej części ćwiczeń z programowania użyjemy osobnego pola Identyfikator projektu.

Następnie połączysz swój projekt Apps Script z projektem w konsoli Cloud.

Otwórz edytor App Script i kliknij Ustawienia projektu .
W sekcji Projekt Google Cloud Platform (GCP) kliknij Zmień projekt.
Wpisz numer projektu i kliknij Set project (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 konsoli Cloud.

Aby to zrobić, przełącz się na konsolę Cloud i kliknij Interfejsy API Usługi > Panel. Pamiętaj, aby nadal korzystać z projektu utworzonego w kroku 3.
Kliknij Włącz interfejsy API i usługi.
Wyszukaj „big query”, wybierz interfejs 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 przeprowadzić pierwszą iterację tej aplikacji.

Przesyłanie aplikacji i uruchamianie jej

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 Code.gs kliknij Więcej > Zmień nazwę. Zmień tytuł z Code.gs na bq-sheets-slides.js.
Teraz sprawdzimy kod, który wysyła zapytanie do BigQuery i zapisuje wyniki w arkuszu Google. Możesz je zobaczyć 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 dziełach, posortowanych w kolejności malejącej według popularności. Gdy wyobrażasz sobie, ile pracy wymaga ręcznego wykonania tej kompilacji, okazuje się, jak przydatna może być usługa BigQuery.

Funkcja deklaruje również zmienną PROJECT_ID, która wymaga prawidłowego identyfikatora projektu w Cloud Console. Instrukcja if pod tą zmienną znajduje się po to, aby uniemożliwić aplikacji kontynuowanie bez identyfikatora projektu.

Przełącz się na projekt Cloud Console, kliknij Więcej na pasku nawigacyjnym i wybierz Ustawienia projektu.
Skopiuj wartość podaną w polu Identyfikator projektu.
Wróć do edytora App Script, znajdź zmienną PROJECT_ID w elemencie bq-sheets-slides.js i dodaj wartość.
Kliknij Zapisz > Uruchom.
Aby kontynuować, kliknij Sprawdź uprawnienia.

Po uruchomieniu skryptu otworzy się wbudowany dziennik wykonywania, który będzie rejestrować działania skryptu w czasie rzeczywistym.

Gdy w dzienniku wykonania pojawi się komunikat „Execution complete” (Ukończenie wykonania), otwórz Dysk Google (drive.google.com) i znajdź arkusz Google o nazwie „Najpopularniejsze słowa we wszystkich dziełach Szekspira”. (lub nazwę przypisaną do zmiennej QUERY_NAME, jeśli została zaktualizowana):
Otwórz arkusz kalkulacyjny, aby wyświetlić 10 najczęściej używanych słów i ich łączne liczby posortowane w kolejności malejącej:

Podsumowanie zadania 1

Aby je sprawdzić, uruchomiliśmy kod, który przeszukał wszystkie dzieła Szekspira, analizując każde słowo w każdej sztuki. Policzył słowa i posortował je w kolejności malejącej pod względem występowania. Dane te zostały też wyświetlone w ramach wbudowanej usługi Apps Script w Arkuszach Google.

Kod użyty na potrzeby bq-sheets-slides.js możesz też znaleźć w folderze step1 repozytorium GitHub tego ćwiczenia na stronie github.com/googlecodelabs/bigquery-sheets-slides. Inspiracją dla tego kodu był pierwotny przykład na stronie usług zaawansowanych BigQuery, w którym uruchomiono nieco inne zapytanie pobierając najpopularniejsze słowa z co najmniej 10 znakami używanymi przez Szekspira. Ten przykład znajdziesz też w repozytorium GitHub.

Jeśli interesują Cię inne zapytania, które możesz utworzyć na podstawie dzieł Szekspira lub innych publicznych tabel danych, przeczytaj artykuł Jak tworzyć zapytania do przykładowych tabel BigQuery i to repozytorium GitHub.

Zapytania możesz też uruchamiać na stronie BigQuery w konsoli Cloud, zanim wykonasz je w Apps Script. Aby go znaleźć, kliknij Menu i otwórz Interfejs BigQuery > Obszar roboczy SQL (link bezpośredni). Nasze zapytanie w interfejsie graficznym BigQuery wygląda na przykład tak:

BigQueryUI

5. Zadanie 2. Utwórz wykres w Arkuszach Google

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

W edytorze Apps Script dodaj funkcję createColumnChart() do pola bq-sheets-slides.js po runQuery(). Kod pobiera arkusz i wysyła żądanie utworzenia wykresu kolumnowego ze wszystkimi danymi. 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ł obiekt spreadsheet, który możemy przekazać do funkcji createColumnChart(). Na końcu funkcji runQuery() zwróć obiekt spreadsheet po zarejestrowaniu udanego utworzenia arkusza:

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

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

Utwórz funkcję createBigQueryPresentation() do wywoływania zarówno runQuery(), jak i createColumnChart(). Sprawdzoną metodą jest oddzielenie funkcji tworzenia BigQuery od funkcji tworzenia wykresów:

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

Powyżej wykonano 2 ważne kroki: zwrócenie obiektu arkusza kalkulacyjnego i utworzenie funkcji wpisu. Aby ułatwić sobie korzystanie z runQuery(), musimy przenieść wiersz dziennika z runQuery() do createBigQueryPresentation(). Twoja 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 (oprócz PROJECT_ID) Twój element typu bq-sheets-slides.js powinien teraz 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);
}

Zapisz projekt skryptu w edytorze skryptów. Następnie wybierz createBigQueryPresentation z listy funkcji i kliknij Uruchom. Po jego zakończeniu na Dysku Google jest tworzony kolejny arkusz kalkulacyjny, ale tym razem w arkuszu obok danych jest umieszczony wykres:

Arkusz z wykresem

6. Zadanie 3. Umieszczanie wyników w prezentacji

Końcowa część ćwiczeń z programowania obejmuje utworzenie prezentacji Google, dodanie tytułu i podtytułu do slajdu tytułowego, a następnie utworzenie slajdów z komórkami danych i wykresem.

W edytorze Apps Script dodaj funkcję createSlidePresentation() do pola bq-sheets-slides.js po createColumnChart(). W ramach tej funkcji wykonywana jest cała praca związana z prezentacją. Zacznijmy od utworzenia prezentacji, a następnie dodaj 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 usłudze createSlidePresentation() jest zaimportowanie danych komórek z Arkuszy Google do nowej prezentacji. Dodaj ten fragment kodu 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 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 narzędziu createSlidePresentation() jest dodanie jeszcze jednego slajdu, zaimportowanie wykresu z arkusza kalkulacyjnego i zwrócenie obiektu Presentation. Dodaj ten fragment kodu do funkcji:

  // 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, przyjrzyj się jeszcze raz jej podpisu. createSlidePresentation() wymaga parametrów obiektu arkusza kalkulacyjnego i wykresu. Dostosowaliśmy już funkcję runQuery() tak, aby zwracała obiekt Spreadsheet, ale musimy wprowadzić podobną zmianę do createColumnChart(), tak aby zwracała obiekt wykresu (EmbeddedChart). Wróć do createColumnChart() i dodaj na końcu funkcji ten fragment kodu:

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

Ponieważ funkcja createColumnChart() zwraca teraz obiekt wykresu, musimy zapisać wykres w zmiennej. Następnie przekazujemy zarówno zmienne arkusza kalkulacyjnego, jak i zmienne wykresu do narzędzia createSlidePresentation(). Ponieważ rejestrujemy adres URL nowo utworzonego arkusza kalkulacyjnego, zapiszemy też adres URL nowej prezentacji. Zaktualizuj urządzenie createBigQueryPresentation(), aby wyglądało 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 ponownie uruchom polecenie createBigQueryPresentation(). Zanim jednak aplikacja zostanie uruchomiona, potrzebuje jeszcze jednego zestawu uprawnień, aby umożliwić wyświetlanie prezentacji Google i zarządzanie nimi. Gdy je sprawdzisz i zezwolisz, będzie ono działać tak jak wcześniej.
Teraz oprócz utworzonego arkusza kalkulacyjnego powinna być też dostępna nowa prezentacja zawierająca 3 slajdy (tytuł, tabela z danymi, wykres z danymi), jak pokazano poniżej:

7. Podsumowanie

Gratulujemy – udało Ci się utworzyć aplikację, która korzysta z obu stron Google Cloud. Wykonuje żądanie Google BigQuery, które wysyła zapytanie do jednego z publicznych zbiorów danych, tworzy arkusz kalkulacyjny Arkuszy Google do zapisania wyników, dodaje wykres na podstawie danych i na koniec tworzy prezentację Google zawierającą dane i wykresy z tego arkusza kalkulacyjnego.

Czynności te zostały wykonane technicznie. Mówiąc prościej, analiza big data została przekształcona w wynik, który można przedstawić zainteresowanym osobom, a wszystko to automatycznie przy użyciu kodu. Mamy nadzieję, że ten fragment zainspiruje Cię do dostosowania go do własnych projektów. Na zakończenie tego ćwiczenia z programowania podamy kilka sugestii, które pomogą Ci udoskonalić tę przykładową aplikację.

Po wprowadzeniu zmian w ostatnim zadaniu (z wyjątkiem zadania PROJECT_ID) element bq-sheets-slides.js powinien teraz 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 możesz też znaleźć w folderze final w repozytorium GitHub.

8. Dodatkowe materiały

Poniżej znajdziesz więcej zasobów, które pomogą Ci pogłębić informacje przedstawione w tym ćwiczeniu z programowania i poznać inne sposoby korzystania z narzędzi Google dla programistów.

Zasoby dotyczące tej aplikacji

Dokumentacja

Strona dokumentacji Google Apps Script
Apps Script: usługa arkuszy kalkulacyjnych
Apps Script: usługa Prezentacje
Apps Script: usługa zaawansowana BigQuery

Filmy

Wiadomości i aktualizacje

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

Inne ćwiczenia z programowania

Wprowadzające

[Arkusze Google] Podstawy Apps Script w Arkuszach Google
[Interfejsy API typu REST] Korzystanie z Google Workspace Interfejsy API Google zapewniające dostęp do plików foldery na Dysku Google.

Średnio zaawansowany

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

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

Poniżej znajdziesz listę różnych sposobów na ulepszenie lub rozszerzenie przykładu, który udało nam się utworzyć w ramach tego ćwiczenia. Ta lista nie jest wyczerpująca, ale zawiera inspirujące pomysły na to, jak zrobić kolejny krok.

Aplikacja – nie chcesz ograniczać się do języka JavaScript ani ograniczeń nałożonych przez Apps Script? Przenieś tę aplikację do swojego ulubionego języka programowania, który korzysta z interfejsów API typu REST dla Google BigQuery, Arkuszy i Prezentacji.
BigQuery – eksperymentuj z różnymi zapytaniami dotyczącymi zbioru danych Szekspira, który Cię interesuje. Kolejne przykładowe zapytanie znajdziesz w oryginalnej przykładowej aplikacji Apps Script BigQuery.
BigQuery – poeksperymentuj z innymi publicznymi zbiorami danych BigQuery, aby znaleźć taki, który bardziej Ci odpowiada.
BigQuery – wspomnieliśmy wcześniej o innych zapytaniach, które można tworzyć na podstawie dzieł Szekspira lub innych publicznych tabel danych. Znajdziesz je na tej stronie i w tym repozytorium GitHub.
Arkusze – możesz eksperymentować z innymi typami wykresów w Galerii wykresów.
Arkusze BigQuery – użyj własnego dużego zbioru danych arkusza kalkulacyjnego. W 2016 roku zespół BigQuery wprowadził funkcję, która umożliwia programistom używanie arkusza jako źródła danych. Więcej informacji znajdziesz w artykule Google BigQuery integruje się z Dyskiem Google.
Prezentacje – dodaj do wygenerowanej prezentacji inne slajdy, na przykład obrazy lub inne zasoby powiązane z analizą big data. Oto dokumentacja usługi wbudowanej Prezentacje.
Google Workspace – korzystaj z innych usług Google Workspace lub usług wbudowanych w Google Apps Script. Na przykład Gmail, Kalendarz, Dokumenty, Dysk, Mapy, Analytics, YouTube i inne usługi zaawansowane. Więcej informacji znajdziesz w omówieniu plików referencyjnych dotyczących usług wbudowanych i zaawansowanych.