GSP240

Opis

Badacze danych mają dostęp do wielu narzędzi, które pozwalają na przeprowadzanie analiz big data. Narzędzia te nie dają jednak możliwości odpowiedniego objaśniania lub uzasadniania wyników tych analiz kierownictwu i innym zainteresowanym osobom. Zaprezentowanie takiemu gronu jedynie kolumn liczb na papierze lub w tabeli bazy danych raczej się nie sprawdzi. Z tego modułu dotyczącego Google Apps Script dowiesz się więc, jak nadawać analizom danych formę nadającą się do skutecznej prezentacji, korzystając z 2 platform Google dla programistów: Workspace i Google Cloud.

Narzędzia dla programistów Google Cloud umożliwiają zbieranie i analizę danych. Potem za pomocą slajdów i arkuszy kalkulacyjnych możesz stworzyć prezentację, która nie tylko będzie bardzo atrakcyjna, ale przede wszystkim przekona zarząd i inne zainteresowane osoby do wyciągniętych przez Ciebie wniosków.

W tym module poznasz dostępny w Google Cloud (jako zaawansowana usługa Apps Script) interfejs API BigQuery oraz wbudowane usługi Apps Script w Arkuszach Google i Prezentacjach Google.

W module realizowany jest scenariusz, który mógłby wystąpić w rzeczywistości. W użytej aplikacji zastosowano funkcje i interfejsy API pochodzące z wielu usług Google Cloud. Chcemy Ci pokazać, jak można wykorzystać możliwości Google Cloud i Workspace do rozwiązywania skomplikowanych problemów, z którymi borykają się Twoja organizacja lub klienci.

Czego się nauczysz

• Jak używać Google Apps Script z wieloma usługami Google
• Jak za pomocą BigQuery przeprowadzać analizy big data
• Jak utworzyć arkusz Google i zapełnić go danymi, a także jak utworzyć wykres na podstawie danych z arkusza kalkulacyjnego
• Jak przenieść wykres i dane z arkusza kalkulacyjnego do osobnych slajdów Prezentacji Google

Konfiguracja

Zanim klikniesz przycisk Rozpocznij moduł

Zapoznaj się z tymi instrukcjami. Moduły mają limit czasowy i nie można ich zatrzymać. Gdy klikniesz Rozpocznij moduł, na liczniku wyświetli się informacja o tym, na jak długo udostępniamy Ci zasoby Google Cloud.

W tym praktycznym module możesz spróbować swoich sił w wykonywaniu opisywanych działań w prawdziwym środowisku chmury, a nie w jego symulacji lub wersji demonstracyjnej. Otrzymasz nowe, tymczasowe dane logowania, dzięki którym zalogujesz się i uzyskasz dostęp do Google Cloud na czas trwania modułu.

Do ukończenia modułu potrzebne będą:

• dostęp do standardowej przeglądarki internetowej (zalecamy korzystanie z przeglądarki Chrome).

Uwaga: uruchom ten moduł w oknie incognito lub przeglądania prywatnego. Dzięki temu unikniesz konfliktu między swoim kontem osobistym a kontem do nauki, co mogłoby spowodować naliczanie oddatkowych opłat na koncie osobistym.

• Odpowiednia ilość czasu na ukończenie modułu – pamiętaj, że gdy rozpoczniesz, nie możesz go wstrzymać.

Uwaga: jeśli masz już osobiste konto lub projekt w Google Cloud, nie używaj go w tym module, aby uniknąć naliczania opłat na koncie.

Wstęp

Google Apps Script i BigQuery

Google Apps Script to rozwiązanie programistyczne dla narzędzi Workspace, które pozwala działać na wyższym poziomie niż interfejsy API Google typu REST. Jest to bezserwerowe środowisko do programowania i hostingu aplikacji, odpowiednie dla programistów o bardzo różnym poziomie zaawansowania. Apps Script można opisać jednym zdaniem jako bezserwerowe środowisko wykonawcze języka JavaScript służące do automatyzacji, rozszerzania i integracji funkcji 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 Workspace i innymi usługami Google. Ponadto środowisko programistyczne Apps Script może całkowicie różnić się od innych używanych przez Ciebie do tej pory. Apps Script umożliwia:

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

Uwaga: więcej informacji o Apps Script znajdziesz w oficjalnej dokumentacji, która zawiera również omówienie z krótkimi wprowadzeniami, samouczki i filmy.

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 zapewniające dostęp do danych Workspace i usług Google oraz inne przydatne metody narzędziowe. Usługa zaawansowana to tylko cienka otoka dla API typu REST w aplikacji Workspace lub innej usłudze Google. Usługi zaawansowane umożliwiają użycie wszystkich funkcji typowych dla interfejsu API typu REST i często ich możliwości są większe niż usług wbudowanych, ale wymagają one bardziej skomplikowanego kodu (wciąż jednak są łatwiejsze w obsłudze niż API REST).

Użycie usług zaawansowanych wymaga ich wcześniejszego włączenia w projekcie skryptu. Wszędzie tam, gdzie to możliwe, preferowane jest korzystanie z usług wbudowanych, ponieważ są łatwiejsze w użyciu i wykonują za programistów więcej złożonych zadań niż usługi zaawansowane. Jednak niektóre interfejsy API Google nie mają usług wbudowanych i w takiej sytuacji użycie usługi zaawansowanej może być jedyną możliwością. Takim przykładem jest BigQuery. Dostępna jest tylko usługa zaawansowana BigQuery – nie ma żadnej usługi wbudowanej. To i tak lepiej, niż gdyby nie było ich wcale.

Uwaga: jeśli jeszcze tego nie wiesz, BigQuery to usługa Google Cloud umożliwiająca wykonywanie prostych (lub złożonych) zapytań do bardzo dużych zbiorów danych (rzędu wielu terabajtów), która zwraca wyniki w czasie liczonym w sekundach, a nie godzinach lub nawet dniach.

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

Usługa BigQuery jest dostępna tylko jako usługa zaawansowana Apps Script. Jednak zarówno Arkusze, jak i Prezentacje Google mają usługi wbudowane Apps Script oraz usługi zaawansowane, na przykład pozwalające na korzystanie z funkcji, które są dostępne wyłącznie w interfejsie API. Jeśli tylko jest to możliwe, wybieraj usługę wbudowaną zamiast równoważnej usługi zaawansowanej. Usługi wbudowane udostępniają konstrukcje wyższego poziomu i wygodne wywołania, które upraszczają programowanie.

Uwaga: zanim zagłębimy się w kod, zapoznaj się z usługą Arkuszy oraz usługą Prezentacji.

Zadanie 1. Tworzenie zapytania BigQuery i zapisywanie wyników w pliku Arkuszy Google

To pierwsze zadanie obejmuje dużą część tego modułu. Gdy zakończysz tę sekcję, zostanie Ci jeszcze mniej więcej połowa całego dostępnego materiału.

W tej sekcji:

• 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.

Tworzenie nowego projektu Apps Script

1. Aby utworzyć nowy projekt Apps Script, wejdź na script.google.com. Na potrzeby tego modułu kliknij Utwórz Apps Script.

2. Otworzy się edytor kodu Apps Script:

3. Nazwij swój projekt, klikając nazwę projektu u góry (na grafice powyżej jest to „Untitled project”, czyli projekt bez nazwy).

4. W oknie Zmień nazwę projektu wpisz wybraną nazwę projektu (np. „BigQuery”, „Arkusze”, „wersja demonstracyjna Prezentacji”), a potem kliknij Zmień nazwę.

Włączanie usługi zaawansowanej BigQuery

Włącz w swoim nowym projekcie usługę zaawansowaną BigQuery i włącz BigQuery API.

1. Kliknij ikonę dodawania usług obok opcji Usługi.

2. W oknie Dodaj usługę wybierz odpowiednie usługi i interfejsy APIs.

3. Otwórz konsolę Cloud i kliknij Menu nawigacyjne > Interfejsy API i usługi > Biblioteka.

4. W polu wyszukiwania wpisz lub wklej BigQuery API, a potem kliknij BigQuery API.

5. Jeśli trzeba, kliknij Włącz, aby włączyć BigQuery API.

6. Wróć do swojego projektu. Okno Dodaj usługę powinno być wciąż otwarte.

7. Wybierz BigQuery API i kliknij Dodaj, aby zamknąć okno.

Wpisywanie i uruchamianie kodu aplikacji

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

1. Skopiuj kod znajdujący się w polu poniżej i wklej go w edytorze kodu, zastępując całą wcześniejszą zawartość:

/** * 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 = '<YOUR_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/gsuitedevs/apps-script-samples/blob/master/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()); }

2. Zapisz utworzony plik, wybierając ikonę zapisywania projektu na pasku menu lub naciskając Ctrl + S.

3. Zmień nazwę pliku, wybierając 3 kropki obok jego nazwy i klikając Zmień nazwę.

4. Zmień nazwę pliku na bq-sheets-slides.gs i naciśnij Enter.

Co robi ten kod? Wiesz, że wykonuje zapytanie BigQuery i zapisuje wyniki w nowym arkuszu Google. Ale co to za zapytanie?

5. Spójrz na kod zapytania w funkcji 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.

Prawie gotowe. W zmiennej PROJECT_ID na początku pliku bq-sheets-slides.gs musisz jeszcze wstawić prawidłowy identyfikator projektu.

6. Zastąp ciąg znaków <YOUR_PROJECT_ID> identyfikatorem projektu znajdującym się w panelu po lewej stronie.

Oto przykładowy kod z przykładowym identyfikatorem projektu. Rzeczywista wartość zmiennej PROJECT_ID w Twoim projekcie będzie inna.

Przykładowy kod:

// 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 = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); Uwaga: w tym fragmencie kodu instrukcja if służy do tego, by działanie aplikacji nie było kontynuowane bez identyfikatora projektu. Uwaga: jeśli selektor menu przestanie działać, załaduj stronę ponownie.

7. Zapisz plik i uruchom kod, klikając Uruchom na pasku menu.

8. Potem kliknij Przejrzyj uprawnienia.

Uwaga: jeśli zobaczysz błąd Wyjątek: usługa BigQuery API nie została włączona w projekcie zarządzanym przez Apps Script(...), usuń usługę BigQuery API i dodaj ją jeszcze raz.

9. W oknie Choose an account from qwiklabs.net kliknij swoją nazwę użytkownika i kliknij Zezwól.

Uwaga: gdy raz autoryzujesz tę aplikację, nie będziesz musiał(a) tego robić przy każdym jej uruchomieniu. Zobaczysz to okno ponownie dopiero, gdy dojdziesz do sekcji „Umieszczanie wyników w prezentacji” w dalszej części tego modułu. Pojawi się tam prośba o utworzenie prezentacji Google i nadanie uprawnień do zarządzania nią.

10. Po uruchomieniu funkcji u góry wyświetli się pole wiadomości.

Pole wiadomości znika po zakończeniu działania funkcji, więc jeśli go nie widzisz, być może funkcja została już wykonana.

11. Otwórz swój Dysk Google i znajdź nowy arkusz Google o nazwie Most common words in all of Shakespeare's works (Słowa występujące najczęściej we wszystkich dziełach Szekspira) lub innej przypisanej do zmiennej QUERY_NAME:

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

Kliknij Sprawdź postępy, aby zobaczyć, jak Ci poszło. Utworzenie zapytania BigQuery i zapisanie wyników w arkuszu Google

Podsumowanie

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

Zanim uruchomisz zapytanie w Apps Script, zawsze możesz je przetestować w konsoli BigQuery. Interfejs użytkownika usługi BigQuery jest dostępny dla programistów.

1. Otwórz konsolę Cloud i kliknij Menu nawigacyjne > BigQuery.

2. W oknie Witamy w usłudze BigQuery w konsoli Cloud kliknij GOTOWE.

Otworzy się konsola BigQuery.

3. Wpisz swój kod w edytorze zapytań i kliknij Uruchom:

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

Zadanie 2. Tworzenie wykresu w Arkuszach Google

Wróć do edytora skryptów. Na razie masz gotową aplikację, która przeszukuje dzieła Szekspira, sortuje wyniki i wyświetla je w Arkuszach. Funkcja runQuery() w kodzie komunikuje się z BigQuery i wysyła wyniki do arkusza. Teraz dodasz kod, który tworzy wykres na podstawie danych. W tej sekcji utworzysz nową funkcję o nazwie createColumnChart(), która wywołuje metodę newChart() arkusza w celu utworzenia wykresu z danymi.

Funkcja createColumnChart() pobiera arkusz z danymi i wysyła żądanie utworzenia wykresu kolumnowego zawierającego wszystkie dane. Początek zakresu danych to komórka A2, ponieważ pierwszy wiersz zawiera nagłówki kolumn, a nie dane.

1. Utwórz wykres: dodaj funkcję createColumnChart() do pliku bq-sheets-slides.gs zaraz po funkcji runQuery() {za ostatnim wierszem kodu}:

/** * 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); }

2. Zwróć arkusz kalkulacyjny: w kodzie przedstawionym powyżej funkcja createColumnChart() wymaga obiektu spreadsheet (arkusz kalkulacyjny), dlatego dostosuj aplikację w taki sposób, by zwracała obiekt spreadsheet, który można przekazać do funkcji createColumnChart(). Po zalogowaniu pomyślnego utworzenia arkusza Google zwróć obiekt na końcu funkcji runQuery().

3. Zastąp ostatni wiersz (zaczynający się od Logger.log) tym fragmentem kodu:

Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // Return the spreadsheet object for later use. return spreadsheet; }

4. Wykorzystaj funkcję createBigQueryPresentation(): bardzo dobrym pomysłem jest logiczne oddzielenie funkcji wykonywania zapytania BigQuery od funkcji tworzenia wykresu. Utwórz funkcję createBigQueryPresentation(), która będzie realizować działanie aplikacji, wywołując zarówno zapytanie, jak i funkcję createColumnChart(). Dodaj kod podobny do tego:

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

5. Umieść funkcję createBigQueryPresentation() zaraz za tym blokiem 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 = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup');

6. Przygotuj kod do możliwości wielokrotnego użycia: powyżej wykonywane są 2 ważne kroki – zwracany jest obiekt arkusza kalkulacyjnego i tworzona jest funkcja odpowiedzialna za działanie aplikacji. Łatwo sobie wyobrazić, że ktoś chciałby skorzystać z funkcji runQuery(), ale bez konieczności logowania URL-a.

Jeśli chcesz, by funkcja runQuery() była bardziej uniwersalna, możesz przenieść w inne miejsce wiersz logowania. Które miejsce byłoby najlepsze? Jeśli uważasz, że najlepiej byłoby umieścić ją w ramach funkcji createBigQueryPresentation(), to masz rację.

Po przeniesieniu wiersza logowania funkcja powinna wyglądać podobnie do tej:

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

Po wprowadzeniu powyższych zmian Twój plik bq-sheets-slides.js powinien wyglądać podobnie do tego (naturalnie z wyjątkiem zmiennej PROJECT_ID):

/** * 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 = '<YOUR_PROJECT_ID>'; if (!PROJECT_ID) throw Error('Project ID is required in setup'); /** * 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); } /** * 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/gsuitedevs/apps-script-samples/blob/master/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()); // 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); }

7. Zapisz plik.

8. Na pasku menu kliknij runQuery i wybierz z menu createBigQueryPresentation.

9. Następnie kliknij Uruchom.

Po uruchomieniu na Twoim Dysku Google pojawi się kolejny arkusz Google, ale tym razem obok danych będzie zawierał wykres:

Zadanie 3. Umieszczanie wyników w prezentacji

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

1. Utwórz prezentację: zacznij od utworzenia nowej prezentacji, następnie dodaj tytuł i podtytuł na domyślnym slajdzie tytułowym, który mają wszystkie nowe prezentacje. Wszystkie operacje związane z prezentacją są wykonywane w funkcji createSlidePresentation(), którą dodasz do pliku bq-sheets-slides.gs zaraz za kodem funkcji createColumnChart():

/** * 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');

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

// 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])); } }

3. Zaimportuj wykres: ostatnim krokiem w funkcji createSlidePresentation() jest utworzenie kolejnego slajdu, zaimportowanie wykresu z arkusza kalkulacyjnego i zwrócenie obiektu Presentation. Dodaj do funkcji ten ostatni fragment kodu:

// Add a chart slide and insert the chart on it. var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK); chartSlide.insertSheetsChart(chart); // Return the presentation object for later use. return deck; }

4. Zwróć wykres: teraz gdy ostatnia funkcja jest gotowa, jeszcze raz przyjrzyj się jej deklaracji. Tak, funkcja createSlidePresentation() wymaga przekazania obiektów spreadsheet (arkusz kalkulacyjny) i chart (wykres). Skorygowaliśmy już funkcję runQuery() tak, że zwraca obiekt Spreadsheet, a teraz trzeba wprowadzić podobną zmianę do funkcji createColumnChart() – musi ona zwracać obiekt wykresu (EmbeddedChart). Cofnij się w kodzie aplikacji i dodaj jeszcze jeden wiersz na końcu funkcji createColumnChart():

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

5. Zaktualizuj funkcję createBigQueryPresentation(): ponieważ funkcja createColumnChart() zwraca wykres, musisz zapisać ten wykres w zmiennej i przekazać do funkcji createSlidePresentation() obydwa obiekty: spreadsheet (arkusz kalkulacyjny) i chart (wykres). Logujesz URL nowo utworzonego arkusza kalkulacyjnego, dlatego możesz również logować URL nowej prezentacji. Zastąp ten blok kodu:

/** * 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); }

Tym blokiem:

/** * 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()); }

Po wszystkich aktualizacjach plik bq-sheets-slides.gs powinien wyglądać podobnie do tego (z wyjątkiem zmiennej PROJECT_ID):

bq-sheets-slides.gs - final version

// 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 = '<YOUR_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/gsuitedevs/apps-script-samples/blob/master/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()); }

6. Zapisz i jeszcze raz uruchom funkcję createBigQueryPresentation(). Zanim się wykona, jeszcze raz pojawi się prośba o ustawienie uprawnień do wyświetlania prezentacji Google i zarządzania nimi.
7. Przejdź do folderu Mój dysk. Zobaczysz, że oprócz utworzonego arkusza jest w nim także nowa prezentacja zawierająca 3 slajdy (tytułowy, z tabelą danych i z wykresem danych) podobne do przedstawionych poniżej:

Gratulacje!

Udało Ci się utworzyć aplikację wykorzystującą obie funkcjonalności Google Cloud. Zapytanie BigQuery przeszukało jeden z publicznych zbiorów danych, wyniki tego zapytania zostały zapisane w nowym arkuszu Google, dodany został wykres utworzony na podstawie pobranych danych, a na koniec powstała prezentacja Google zawierająca wyniki zapytania w postaci arkusza kalkulacyjnego oraz wykresu.

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. Teraz możesz dostosować ten moduł do własnych projektów.

Ukończ kurs

Ten moduł do samodzielnego ukończenia jest częścią kursów Workspace: Integrations for Data i BigQuery Basics for Data Analysts. Każdy kurs składa się z zestawu powiązanych ze sobą modułów, które razem tworzą ścieżkę szkoleniową. Za ukończenie kursu otrzymujesz odznakę – stanowi ona potwierdzenie Twojego osiągnięcia. Swoje odznaki możesz ustawiać jako widoczne publicznie, a także podać do nich linki w swoim CV lub w mediach społecznościowych. Zarejestruj się na dowolny kurs zawierający ten moduł, a zostanie on automatycznie zaliczony. Wszystkie dostępne kursy znajdziesz w katalogu Google Cloud Skills Boost.

Poszukujesz modułu praktycznego typu Challenge Lab (moduł-wyzwanie), aby wykazać się nowymi umiejętnościami i sprawdzić swoją wiedzę w zakresie BigQuery? Po ukończeniu kursu przejdź ten moduł Challenge Lab, aby otrzymać cyfrową odznakę Google Cloud.

Więcej informacji

Kod zaprezentowany w tym module jest również dostępny na GitHubie. Staramy się na bieżąco uwzględniać w tym module wszystkie zmiany wprowadzane w repozytorium. Poniżej znajdziesz dodatkowe zasoby, pozwalające pogłębić informacje przedstawione w tym module i poznać inne sposoby korzystania z narzędzi Google dla programistów.

Dokumentacja

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

Filmy dotyczące zagadnień powiązanych i ogólnych

• Another Google (Apps) secret (Kolejny sekret Google (Apps)) – film wprowadzający do Apps Script
• Accessing Google Maps from a spreadsheet (Dostęp do Map Google z poziomu arkusza kalkulacyjnego) – film
• Biblioteka filmów o korzystaniu z Google Apps Script
• Cykl filmów Launchpad Online
• Cykl filmów The Google Workspace Dev Show

Najnowsze informacje dotyczące zagadnień powiązanych i ogólnych

• BigQuery integrates with Google Drive (Integracja Google BigQuery z Dyskiem Google, artykuły z 2016 roku: link1, link2)
• Blog dla programistów Google Developers
• Blog poświęcony Google Cloud Platform
• Blog Google Cloud poświęcony big data i systemom uczącym się
• Konto na Twitterze Google Developers (@GoogleDevs)
• Blog dla programistów Workspace Developers
• Konto na Twitterze Workspace developers (@GSuiteDevs)
• Miesięczny newsletter dla deweloperów Workspace
• Centrum edukacji Google Workspace

Ostatnia aktualizacja instrukcji: 6 marca 2023 r.

Ostatni test modułu: 6 marca 2023 r.

Copyright 2024 Google LLC. Wszelkie prawa zastrzeżone. Google i logo Google są znakami towarowymi Google LLC. Wszelkie inne nazwy firm i produktów mogą być znakami towarowymi odpowiednich podmiotów, z którymi są powiązane.