Przekształcanie danych w statystyki za pomocą Arkuszy i Prezentacji Google

Badacze danych mają do dyspozycji wiele narzędzi do analizy dużej ilości danych, ale czy już nie koniecznie musisz uzasadnić te wyniki w zarządzaniu? Liczby podawane na papierze lub w bazie danych nie są zazwyczaj kluczowe dla zainteresowanych osób. To szkolenie dla początkujących użytkowników Google Apps Script wykorzystuje parę platform Google dla deweloperów – G Suite i Google Cloud Platform (GCP), które pomogą Ci pokonać tę ostateczną trasę.

Narzędzia Google Cloud dla programistów umożliwiają przeprowadzenie szczegółowej analizy danych, umieszczenie wyników w arkuszu kalkulacyjnym i wygenerowanie prezentacji z danymi. Zapewnia to bardziej odpowiedni etap przekazywania wyników do zarządzania. Te ćwiczenia obejmują interfejs API BigQuery GCP (jako usługa zaawansowana Apps Script) i wbudowane usługi Apps Script w Arkuszach Google i Prezentacjach Google.

Motywacja/wcześniejsza sztuka

Ta przykładowa aplikacja z ćwiczeń z ćwiczeniami z programowania zainspirowała Cię...

Chociaż przykładowa aplikacja na potrzeby ćwiczeń z interfejsu API Prezentacji Google zawiera też BigQuery i Prezentacje, różni się ona od tej przykładowej aplikacji.

  • Aplikacja Node.js a aplikacja Apps Script
  • Używamy interfejsów API REST, gdy używamy usług Apps Script
  • używa Dysku Google, ale nie Arkuszy Google, a ta aplikacja używa Dysku.

Podczas tego ćwiczenia z programowania chcieliśmy połączyć jak najwięcej technologii z jedną aplikacją, prezentując funkcje i interfejsy API z Google Cloud w sposób bardziej zbliżony do rzeczywistego przypadku użycia. Jego celem jest inspirowanie Cię swoją wyobraźnią i rozważenie wykorzystania zarówno GCP, jak i G Suitite, aby rozwiązać trudne problemy Twojej organizacji lub klientów.

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;
  • Tworzenie pliku w Arkuszach Google i zapełnianie go danymi
  • Jak utworzyć nowy wykres 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 (konta G Suite mogą wymagać zatwierdzenia przez administratora),
  • Podstawowe umiejętności z zakresu JavaScriptu
  • Wiedza z zakresu programowania Apps Script może być pomocna, ale nie jest wymagana.

Jak będziesz korzystać z ćwiczeń lub samouczków?

Zapoznaj się z nimi w celach informacyjnych i przekaż te informacje kolegom z zespołu technicznego. Przećwicz to tak długo, jak to możliwe i przećwicz jak najwięcej ćwiczeń. Weź udział w ćwiczeniach z do krainy wody lub w ogóle.

Jak oceniasz swoje doświadczenia z narzędzi G Suite dla programistów i interfejsów API?

Początkujący Średnio zaawansowany Zaawansowany

Jak oceniasz sposób korzystania z Apps Script?

Początkujący Średnio zaawansowany Zaawansowany

Jak oceniasz swoje doświadczenia z narzędziami GCP dla programistów GCP?

Początkujący Średnio zaawansowany Zaawansowany

Wiesz już, czym są te ćwiczenia. Co dokładnie zamierzasz zrobić?

  1. Skorzystaj z już utworzonej próbki aplikacji Apps Script-BigQuery
  2. Z tej próbki dowiesz się, jak wysłać zapytanie do BigQuery i uzyskać jego wyniki
  3. Tworzenie pliku w Arkuszach Google i zapełnianie go danymi z BigQuery
  4. Wprowadź niewielkie zmiany w kodzie, aby lekko zmodyfikować dane zwrócone do arkusza
  5. Skorzystaj z usługi Arkusze w Apps Script, aby utworzyć wykres danych z BigQuery
  6. Tworzenie nowych prezentacji za pomocą Prezentacji
  7. Dodaj tytuł i podtytuł do domyślnego slajdu tytułowego utworzonego automatycznie dla wszystkich nowych prezentacji
  8. Utwórz nowy slajd z tabelą danych, a następnie zaimportuj do niego komórki z arkusza
  9. Dodaj kolejny slajd i dodaj do niego wykres arkusza kalkulacyjnego

Zacznijmy od podstawowych informacji na temat Apps Script, BigQuery, Arkuszy i Prezentacji.

Google Apps Script i BigQuery

Google Apps Script to platforma deweloperska G Suite działająca 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, ale możesz też tworzyć go lokalnie, jeśli używasz clasp – narzędzia do wdrażania dostępnego w wierszu poleceń w Apps Script;
  • Kod w wyspecjalizowanej wersji kodu JavaScriptu dostosowanej do uzyskiwania dostępu do G Suite oraz innych usług Google lub zewnętrznych (za pomocą usług Apps Script URLfetch lub Jdbc)
  • Może uniknąć pisania kodu autoryzacji, ponieważ obsługuje go za Ciebie Apps Script
  • rezygnację z hostowania utworzonej aplikacji – będzie ona działać na serwerach Google w chmurze.

UWAGA: w większości przypadków to ćwiczenie wykracza poza zakres tych ćwiczeń z programowania. Masz do dyspozycji wiele zasobów online, które Ci w tym pomogą. Oficjalna dokumentacja zawiera też omówienie z krótkimi wprowadzeniami, samouczki i filmy. Na koniec zapomnij o wstępnym ćwiczeniu z programowania, które musisz ukończyć.

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 ogólne metody dostępu do danych G Suite i usług Google oraz innych użytecznych metod. Usługa zaawansowana to po prostu cienka powłoka otaczająca interfejs API G Suite lub Google 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ą bardziej skomplikowanego kodu (wciąż jednak są łatwiejsze w obsłudze niż API REST). Usługi zaawansowane muszą być też włączone dla projektu skryptu przed ich użyciem.

W miarę możliwości deweloperzy powinni korzystać z usług wbudowanych, ponieważ są łatwiejsze w użyciu i wymagają od użytkownika większej liczby pracy niż zaawansowane. Niektóre interfejsy API Google nie mają jednak usług wbudowanych, dlatego opcje zaawansowane mogą być jedyną opcją. Przykładem może być Google BigQuery. Nie jest dostępna żadna wbudowana usługa, ale istnieje usługa zaawansowana BigQuery. To i tak lepiej, niż gdyby nie było żadnej usługi. Jeśli dopiero zaczynasz korzystać z BigQuery, jest to usługa GCP umożliwiająca wykonywanie prostych (lub złożonych) zapytań dotyczących bardzo dużych zbiorów danych (na przykład o wielkości terabajtów), która jednak zapewnia wyniki w kilka sekund.

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

W przeciwieństwie do BigQuery, Arkusze i Prezentacje Google mają usługi wbudowane (oraz usługi zaawansowane, których możesz używać tylko do korzystania z funkcji dostępnych tylko w interfejsie API). Zanim przejdziesz do kodu, zapoznaj się z dokumentacją zarówno wbudowanych usług Arkuszy, jak i Prezentacji. Oczywiście są też dokumenty dotyczące usług zaawansowanych. Są one dostępne odpowiednio dla Arkuszy i Prezentacji.

Wstęp

Na początek mamy dla Ciebie duże zadanie. Gdy wykonasz je w połowie, prawie wszystko zostanie ukończone w połowie. Wszystkie kroki są podzielone na kilka podsekcji:

  • 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, otwierając stronę script.google.com . Istnieją różne linie produktów G Suite, a sposób tworzenia nowego projektu może się różnić w zależności od tego, której wersji używasz. Jeśli dopiero zaczynasz korzystać z konta Gmail i dopiero zaczynasz tworzyć projekty, zobaczysz pusty ekran wraz z przyciskiem do utworzenia pierwszego projektu:


b) Jeśli nie, w lewym górnym rogu zobaczysz wszystkie swoje projekty i duży przycisk +Nowy. Kliknij go.



c) Jeśli żadna z powyższych opcji nie pojawi się, Twój ekran może wyglądać tak jak poniżej. Jeśli tak, poszukaj ikony menu hamburger w lewym górnym rogu i wybierz +Nowy skrypt.



) dla osób, które preferują wiersz poleceń. Narzędzie to clasp, a Ty uruchomisz polecenie clasp create.

e) Ostatnim sposobem na utworzenie nowego projektu skryptu jest po prostu kliknięcie linku skrótu: https://script.google.com/create.

  1. Niezależnie od tego, jakiej metody użyto do utworzenia nowego projektu, należy pamiętać, że należy przejść do edytora kodu Apps Script, który wygląda tak:


  2. Kliknij Plik > Zapisz i nadaj projektowi nazwę.
  3. Następnie, aby uruchomić zapytania BigQuery, musisz utworzyć projekt Google Cloud Console.
  1. Utwórz nowy projekt, nadaj mu nazwę, wybierz konto rozliczeniowe, a potem kliknij UTWÓRZ.
  1. Gdy projekt zostanie utworzony, w prawym górnym rogu strony pojawi się powiadomienie. Kliknij wpis Create Project: <Project Name>, aby otworzyć projekt.
  2. Kliknij ikonę menu w lewym górnym rogu i wybierz APIs & Services > Credentials (Interfejsy API i dane logowania). Kliknij kartę Ekran akceptacji OAuth (link bezpośredni).
  3. W polu Nazwa aplikacji wpisz "Big Data Codelab" i kliknij przycisk Save (Zapisz) u dołu.
  4. Kliknij ikonę z 3 kropkamiw prawym górnym rogu, aby rozwinąć menu i wybierz Ustawienia projektu(link bezpośredni).
  5. Skopiuj wartość z pola Numer projektu. Masz do dyspozycji osobne pole Product ID, które posłuży nam później w ćwiczeniach z programowania.
  6. W edytorze App Script kliknij Resources > Cloud Platform project (Zasoby i projekt Cloud Platform).
  7. Wpisz numer projektu w polu tekstowym i kliknij Ustaw projekt. Gdy pojawi się komunikat, kliknij Potwierdź.
  8. Gdy skończysz, kliknij przycisk Zamknij, aby zamknąć okno.
  9. Po skonfigurowaniu nowego projektu musisz włączyć zaawansowaną usługę BigQuery. W dół przewiń w dół stronę Resources -> Advanced Google Services i włącz interfejs BigQuery API.


  10. Uwaga na dole: „Te usługi też muszą być włączone w panelu” w interfejsie Google Cloud Platform API, kliknij więc link, który powoduje otwarcie kolejnej karty przeglądarki z poziomu konsoli programisty lub w skrócie „devconsole&quot”.
  11. W deweloperze kliknij przycisk + Włącz interfejsy API i usługi u góry strony, wyszukaj "bigquery" wybierz interfejs BigQuery API API (nie BigQuery Data Transfer API) i kliknij włącz, aby go włączyć. Zostaw tę kartę otwartą w przeglądarce.

    UWAGA: po włączeniu interfejsu API możesz zobaczyć na tej stronie komunikat podobny do tego: „Aby korzystać z tego interfejsu API, musisz utworzyć dane logowania...”,
  12. Po powrocie na kartę przeglądarki z edytorem kodu nadal jesteś w menu zaawansowanych usług Google, więc kliknij OK, aby zamknąć okno i pozostawić okno edytora. Kliknij nazwę projektu u góry i nazwij ją – w dowolnej chwili.

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

Prześlij i uruchom aplikację

  1. Skopiuj kod z pola poniżej i wklej go w edytorze kodu:
// 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());
}


Teraz zapisz zapisany przed chwilą plik, ale zmień jego nazwę z Code.gs na bq-sheets-slides.js. Co robi ten kod? Wcześniej informowaliśmy, że wysyła zapytanie do BigQuery i zapisuje wyniki w nowym arkuszu Google. Ale co to za zapytanie? Możesz zobaczyć go w górnej części strony 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 najczęściej występujących słów we wszystkich jego pracach, posortowanych w kolejności malejącej według popularności. 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. Prawie nam się udało, ale prawie nie jesteśmy gotowi. Jak widać na początku tego fragmentu kodu, wymagany jest prawidłowy identyfikator projektu, dlatego musimy dodać Twój identyfikator do kodu aplikacji. Aby to zrobić, wróć do okna przeglądarki lub karty ze stroną konsoli programisty. (Pamiętaliśmy, że może być już otwarte).
  2. W lewym górnym rogu awatara konta Google jest menu wyboru (). Kliknij je i wybierz Ustawienia projektu. Zobaczysz nazwę, identyfikator i numer projektu. Skopiuj identyfikator projektu i ustaw zmienną PROJECT_ID na górze strony bq-sheets-slides.js na wartość z konsoli programisty. UWAGA: jeśli selektor menu przestanie działać, odśwież stronę.
  3. Instrukcja if służy do zapobiegania nieprawidłowemu działaniu aplikacji bez identyfikatora projektu. Po dodaniu pliku zapisz go i uruchom kod, przechodząc do paska menu i wybierając Run > Run feature > runQuery, kliknij Review Permissions (Sprawdź uprawnienia), ta aplikacja jest niezweryfikowana, a poniżej widać animowany GIF (w przypadku innej aplikacji) z ilustracją następnych czynności:
  4. Po wysłaniu prośby o sprawdzenie uprawnień zobaczysz nowe okno, jak pokazano powyżej. Wybierz właściwe konto Google, które ma uruchamiać skrypt, a następnie kliknij Zaawansowane, przewiń w dół i kliknij „Przejdź do &LT;NAZWA PROJEKTU” (niebezpieczne), aby przejść na ekran autoryzacji aplikacji OAuth2. (Przeczytaj więcej o procesie weryfikacji, aby dowiedzieć się, dlaczego ekran jest między Tobą a oknem autoryzacji OAuth2).


    UWAGA: po autoryzacji aplikacji nie trzeba powtarzać tego procesu przy każdym uruchomieniu. Nie zobaczysz ponownie tego okna dialogowego z prośbą o zgodę użytkownika na tworzenie prezentacji w Prezentacjach Google i zarządzanie nimi do czasu, aż przejdziesz do zadania 3 w tym samouczku.
  5. Gdy klikniesz Zezwalaj w oknie dialogowym OAuth2, skrypt zacznie działać... u góry pojawi się żółte okno w pastelowych kolorach. Jest on uruchamiany dość szybko, więc możesz nie zauważyć, że jest uruchomiony lub że wykonanie zostało zakończone.

  6. To okno znika, gdy zostanie wykonane, więc jeśli go nie widzisz, prawdopodobnie jest gotowe. Otwórz Dysk Google (drive.google.com) i odszukaj nowy arkusz Google o nazwie „Utwory” Szekspira (lub wszystkie przypisane do zmiennej QUERY_NAME):


  7. Otwórz arkusz kalkulacyjny. Zobaczysz 10 wierszy ze słowami i ich łącznymi liczbami posortowanymi w kolejności malejącej:

Podsumowanie zadania 1

Rozpoznajmy, co właśnie się wydarzyło. Wygenerowano kod, który wysyła zapytania do wszystkich dzieł Szekspira (nie są to Olbrzymie dane, ale z pewnością tekstu jest więcej, niż można łatwo zeskanować samodzielnie, patrząc na każde słowo w każdej grze), zarządzając liczbą takich słów i sortując je w kolejności malejącej. Nie tylko poprosiliśmy BigQuery o zrobienie tego w Twoim imieniu, ale też umożliwiliśmy użycie danych z wbudowanej usługi Apps Script do Arkuszy Google.

Kod bq-sheets-slides.js (wybrana przez Ciebie nazwa pliku) wklejony powyżej (oprócz PROJECT_ID, który powinien mieć prawdziwy identyfikator projektu) znajdziesz też w folderze step1 w tym ćwiczeniu z programu GitHub na github.com/googlecodelabs/bigquery-sheets-slides. Kod był inspiracją dla przykładu na stronie zaawansowanych usług BigQuery, który zawierał nieco inne zapytanie. Oto 10 najpopularniejszych słów Szekspira (10 lub więcej znaków). Możesz też zobaczyć tę próbkę w repozytorium GitHub.

Jeśli interesują Cię inne zapytania, które możesz wypróbować w przypadku dzieł Szekspira lub innych publicznych tabel danych, odwiedź tę stronę internetową i tę. Niezależnie od używanego zapytania zawsze możesz je przetestować w konsoli BigQuery, zanim uruchomisz je w języku Apps Script. Interfejs użytkownika BigQuery jest dostępny dla programistów na stronie bigquery.cloud.google.com. Poniżej przedstawiamy przykład interfejsu BigQuery.

Opisane powyżej kroki skorzystały z edytora 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 nie udało Ci się wziąć udziału w tym wydarzeniu, zapoznaj się ponownie z linkiem do clasp i instrukcjami jego używania).

Celem aplikacji runQuery() jest rozmowa z BigQuery i przesłanie wyników w arkuszu. Teraz musimy utworzyć wykres z danymi. Aby to zrobić, utwórz nową funkcję o nazwie createColumnChart(), która wywołuje metodę newChart() Arkuszy.

  1. Utwórz wykres. Dodaj treść sekcji createColumnChart() poniżej: bq-sheets-slides.js zaraz po runQuery(). Powoduje pobranie arkusza z danymi i żądanie wykresu kolumnowego ze wszystkimi danymi. Zakres danych zaczyna się od komórki 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. Powyżej createColumnChart() wymaga obiektu arkusza kalkulacyjnego, dlatego musimy zaktualizować runQuery(), by zwrócić obiekt spreadsheet, żebyśmy mogli go przekazać do createColumnChart(). Po zarejestrowaniu pomyślnego utworzenia Arkusza Google zwróć obiekt spreadsheet na końcu elementu runQuery(), zaraz po wierszu dziennika:
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}
  1. Funkcja przejazdu createBigQueryPresentation(). Znacznie zalecamy rozdzielenie funkcji BigQuery i tworzenie wykresów. Teraz utworzymy funkcję createBigQueryPresentation(), która wywoła aplikację 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. Popraw możliwość wielokrotnego wykorzystania kodu. Wykonano 2 ważne kroki powyżej: zwrócenie obiektu arkusza kalkulacyjnego i utworzenie funkcji samochodowej. A jeśli ktoś z pracowników chciał ponownie użyć adresu runQuery() i nie chce, aby adres URL był rejestrowany? Aby tag runQuery() był bardziej przystosowany do ogólnego użytku, musimy przenieść ten wiersz dziennika. Które miejsce byłoby najlepsze? Jeśli się domyślisz, createBigQueryPresentation() to masz rację. Po przeniesieniu wiersza dziennika powinien on wyglądać tak:
/**
 * 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);
}

Powyżej te zmiany (z wyjątkiem PROJECT_ID) Twoje bq-sheets-slides.js powinny wyglądać teraz jak w poniższym folderze (oraz w folderze step2 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 następnie przejdź w górę edytora kodu i przełącz się na createBigQueryPresentation(), a nie runQuery(). Po jego uruchomieniu otrzymasz kolejny Arkusz Google, ale tym razem obok danych pojawi się wykres:

Ostatni etap ćwiczenia z programowania obejmuje utworzenie nowej prezentacji w Prezentacjach Google, wypełnienie tytułu i podtytułu na slajdzie tytułowym, a następnie dodanie 2 nowych slajdów – po jednym dla każdej komórki z danymi i kolejnego dla wykresu.

  1. Utwórz prezentację. Wszystkie czynności związane z prezentacją zostaną zrealizowane w aplikacji createSlidePresentation(), którą dodamy bq-sheets-slides.js do createColumnChart(). Zacznijmy od utworzenia nowej prezentacji, a potem dodaj tytuł i podtytuł do domyślnego slajdu tytułowego, który posługujemy się wszystkimi nowymi prezentacjami.
/**
 * 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 ramach zadania createSlidePresentation() jest zaimportowanie danych z arkusza Google do nowej prezentacji. Fragment kodu, dodaj go 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. Zaimportuj wykres. Ostatnim krokiem w przypadku createSlidePresentation() jest utworzenie kolejnego 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;
}
  1. Wykres zwrotny. Teraz, po zakończeniu działania funkcji, sprawdź jeszcze raz jej podpis. Tak. Obiekt createSlidePresentation() wymaga jednocześnie arkusza kalkulacyjnego i obiektu wykresu. Skorygowaliśmy już runQuery(), tak by zwracał obiekt Spreadsheet, ale teraz musimy dokonać podobnej zmiany w createColumnChart(), by zwracała obiekt wykresu (EmbeddedChart). Wróć do aplikacji i dodaj kod, aby dodać ostatni wiersz na końcu createColumnChart():
  // NEW: Return chart object for later use
  return chart;
}
  1. Zaktualizuj createBigQueryPresentation(). Ponieważ createColumnChart() zwraca wykres, musimy go zapisać w zmiennej, a następnie przekazać zarówno arkusz kalkulacyjny, jak i wykres do createSlidePresentation(). Rejestrujemy URL nowo utworzonego arkusza kalkulacyjnego, więc włączymy także adres URL nowego slajdu. Uaktualnij createBigQueryPresentation(), aby wyglądał 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 jednak ta aplikacja zostanie uruchomiona, uznaj, że musi ona mieć jeszcze jeden zestaw uprawnień, aby wyświetlać prezentacje Google i zarządzać nimi. Po przyznaniu tych uprawnień będzie ono działać jak dotychczas.
  2. Teraz poza utworzonym arkuszem powinna pojawić się też nowa prezentacja w Prezentacjach z 3 prezentacjami (tytułem, tabelą danych i wykresem danych), jak widać poniżej:

Gratulacje! Udało Ci się utworzyć aplikację wykorzystującą obie strony Google Cloud. Wykonując żądanie Google BigQuery, które wysyła zapytania do jednego z jego publicznych zbiorów danych, tworzysz nowy Arkusz Google do przechowywania wyników, dodajesz wykres oparty na pobranych danych i na koniec tworzysz prezentację w Prezentacjach Google z wynikami oraz wykresem w arkuszu kalkulacyjnym.

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 zainspirujesz się tym przykładem i dostosujesz do swoich projektów. Na koniec tego ćwiczenia pokażemy, jak ulepszyć tę przykładową aplikację.

Po wprowadzeniu zmian z ostatniego zadania (z wyjątkiem PROJECT_ID) Twoje bq-sheets-slides.js powinno wyglądać mniej więcej tak: (także 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 tej części ćwiczeń pełnimy funkcję „"drugi plik”, która jest plikiem manifestu Apps Script, appsscript.json. Aby uzyskać do niego dostęp, otwórz kartę przeglądarki z edytorem kodu i z menu u góry wybierz Wyświetl > Pokaż plik manifestu. Powinna ona 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 systemu, którego używa Apps Script, by dowiedzieć się, jakie środowisko wykonawcze jest dostępne dla Twojej aplikacji. Nie okrywamy zawartości pliku manifestu poza zakresem tego ćwiczenia z programowania, ale wiemy, jak to działa.

Poniżej znajdziesz dodatkowe materiały, które pomogą Ci zgłębić materiał omawiany w tym ćwiczeniu i poznać inne sposoby zautomatyzowanego dostępu do narzędzi Google dla programistów. Chcemy, aby ćwiczenia z programowania były zsynchronizowane z repozytorium.

Zasoby do tej aplikacji

Dokumentacja

  • Inny tajny klucz Google (Apps)? (Film wprowadzający do Apps Script )
  • Jak uzyskać dostęp do Map Google z poziomu arkusza kalkulacyjnego? (wideo)
  • Google Apps Script – biblioteka wideo
  • Launchpad Online – seria filmów online (pierwotnie...)
  • Seria filmów w wersji deweloperskiej G Suite

Inne ćwiczenia z programowania

Wprowadzające

Średnio zaawansowany

Aplikacje referencyjne

Poniżej znajdziesz różne wyzwania z kodem oraz sposoby na ich ulepszenie lub udoskonalenie. Ta lista nie jest oczywista, ale powinna zawierać pomysły, co możesz zrobić dalej.

  • Aplikacja. Nie chcesz używać JavaScriptu ani ograniczeń nakładanych przez Apps Script? Przenieś tę aplikację do ulubionego języka programowania, który używa interfejsów API typu REST dla Google BigQuery, Arkuszy i Prezentacji.
  • BigQuery Poeksperymentuj z innym zapytaniem w zbiorze danych Szekspira... być może znajdziesz takie, które Cię interesuje. Kolejne zapytanie znajdziesz w oryginalnej przykładowej aplikacji Apps Script w BigQuery.
  • BigQuery Eksperymentuj z kilkoma innymi publicznymi zbiorami danych BigQuery, by znaleźć zbiór danych, który może być dla Ciebie bardziej przydatny.
  • BigQuery Wcześniej wspomnieliśmy o innych zapytaniach, które możesz wypróbować w przypadku prac Szekspira i innych publicznych tabel danych, i chcemy ponownie udostępnić tę stronę internetową oraz tę.
  • Arkusze. Eksperymentuj z innymi typami wykresów.
  • Arkusze & BigQuery. Obracaj tabele... Może masz w arkuszu kalkulacyjnym duży zbiór danych? W 2016 roku zespół BigQuery wprowadził funkcję umożliwiającą użycie arkusza jako źródła danych (więcej informacji znajdziesz w poście na blogu 1 i 2).
  • Prezentacje. Dodaj inne wygenerowane slajdy, np. obrazy lub inne zasoby powiązane z analizą big data. Oto przewodnik po wbudowanych Prezentacjach.
  • G Suite Znajdź zastosowania dla innych usług wbudowanych w G Suite lub Google w Apps Script, czyli Gmail, Dysk Google, Kalendarz, Dokumenty, Mapy, Analytics, YouTube itd. Więcej informacji znajdziesz w omówieniu dokumentacji dotyczącej usług wbudowanych i zaawansowanych.