GSP240

Panoramica

Sono molti gli strumenti che i data scientist hanno a disposizione per analizzare i big data, ma quale di questi può aiutarti a spiegare e giustificare le analisi al management e agli stakeholder? Di certo la soluzione non è un elenco di numeri non elaborati presentati su fogli di carta o in un database. Questo lab di Google Apps Script sfrutta due piattaforme per sviluppatori di Google, Workspace e Google Cloud, per aiutarti a compiere questo ultimo passaggio.

Con gli strumenti per sviluppatori di Google Cloud puoi raccogliere ed elaborare dati, quindi generare una presentazione con slide e fogli di lavoro per stupire il management e gli stakeholder con analisi eccellenti e informazioni utili e approfondite.

In questo lab verranno trattati l'API BigQuery di Google Cloud (in quanto servizio avanzato di Apps Script) e i servizi integrati di Apps Script per Fogli Google e Presentazioni Google.

Nel lab verrà anche configurato uno scenario che ricorda da vicino la vita reale. L'app esemplifica l'utilizzo di funzionalità e API del mondo di Google Cloud. L'obiettivo è dimostrare come poter sfruttare sia Google Cloud che Workspace per risolvere problemi complessi dell'organizzazione o dei clienti.

Obiettivi didattici

• Utilizzare Google Apps Script con più servizi Google
• Utilizzare BigQuery per eseguire l'analisi dei big data
• Creare un foglio Google, compilarlo con i dati e creare un grafico a partire da quei dati
• Trasferire i dati e il grafico del foglio di lavoro in slide separate di una presentazione Google

Configurazione

Prima di fare clic sul pulsante Avvia lab

Leggi le seguenti istruzioni. I lab sono a tempo e non possono essere messi in pausa. Il timer si avvia quando fai clic su Avvia lab e ti mostra per quanto tempo avrai a disposizione le risorse Google Cloud.

Con questo lab pratico avrai la possibilità di completare le attività in prima persona, in un ambiente cloud reale e non di simulazione o demo. Riceverai delle nuove credenziali temporanee che potrai utilizzare per accedere a Google Cloud per la durata del lab.

Per completare il lab, avrai bisogno di:

• Accesso a un browser internet standard (Chrome è il browser consigliato).

Nota: utilizza una finestra del browser in incognito o privata per eseguire questo lab. Ciò evita eventuali conflitti tra il tuo account personale e l'account Studente, che potrebbero causare addebiti aggiuntivi sul tuo account personale.

• È ora di completare il lab: ricorda che, una volta iniziato, non puoi metterlo in pausa.

Nota: se hai già un account o un progetto Google Cloud personale, non utilizzarlo per questo lab per evitare addebiti aggiuntivi al tuo account.

Introduzione

Google Apps Script e BigQuery

Google Apps Script è una piattaforma di sviluppo di Workspace che opera a livello superiore rispetto all'utilizzo delle API REST Google. Si tratta di un ambiente di hosting delle applicazioni e di sviluppo serverless accessibile a sviluppatori con livelli di abilità molto diversi. In una sola frase, "Apps Script è un runtime JavaScript serverless per l'automazione, l'estensione e l'integrazione di Workspace".

È JavaScript lato server, simile a Node.js, ma è dedicato alla stretta integrazione con Workspace e altri servizi Google invece che all'hosting di applicazioni rapido e asincrono basato su eventi. Apps Script comprende anche un ambiente di sviluppo che potrebbe essere completamente diverso da quello che usi di solito. Con Apps Script, puoi:

• Sviluppare all'interno di un editor di codice basato su browser. Puoi anche scegliere di farlo localmente se utilizzi clasp, lo strumento di deployment a riga di comando per Apps Script
• Scrivere codice in una versione specializzata di JavaScript personalizzata per l'accesso a Workspace e ad altri servizi, sia Google sia esterni (tramite i servizi URLfetch o Jdbc di Apps Script)
• Evitare di scrivere codice di autorizzazione, perché se ne occupa Apps Script al posto tuo
• Fare a meno di trovare una soluzione per l'hosting della tua app, che risiede e viene eseguita nei server Google sul cloud

Nota: per ulteriori informazioni su Apps Script, fai riferimento alla documentazione ufficiale, che contiene anche una panoramica con guide rapide, tutorial e video.

Apps Script si interfaccia con altre tecnologie Google in due modi diversi:

• Servizio integrato
• Servizio avanzato

Un servizio integrato fornisce metodi di alto livello che puoi usare per accedere ai dati dei prodotti Workspace o Google, oltre ad altri pratici metodi di utilità. Un servizio avanzato non è altro che un sottile wrapper che riveste l'API REST Google o Workspace. I servizi avanzati offrono copertura completa dell'API REST e spesso possono fare più cose dei servizi integrati, ma richiedono una maggiore complessità di codice (pur rimanendo più facili da usare rispetto all'API REST).

Inoltre, prima di poter essere utilizzati, i servizi avanzati devono essere abilitati nel progetto di uno script. Quando è possibile, è da preferire un servizio integrato in quanto è molto più facile da utilizzare e non richiede altrettanto lavoro rispetto ai servizi avanzati. Tuttavia, alcune API di Google non hanno i servizi integrati, perciò il servizio avanzato potrebbe essere l'unica opzione. BigQuery ne è un esempio: nessun servizio integrato disponibile, ma esiste un servizio avanzato BigQuery. È comunque meglio di niente!

Nota: BigQuery è un servizio di Google Cloud utilizzato per eseguire query semplici (o complesse) su corpus di dati molto vasti, dell'ordine di vari terabyte, in grado di restituire risultati in pochi secondi, non ore o giorni.

Accedi a Fogli e Presentazioni Google da Apps Script

BigQuery è disponibile solo come servizio avanzato di Apps Script. Tuttavia, sia Fogli sia Presentazioni Google comprendono servizi Apps Script integrati e avanzati, ad esempio per accedere a funzionalità che sono disponibili solo nell'API e non sono integrate. Quando è possibile, scegli un servizio integrato invece di un servizio avanzato equivalente, in quanto il servizio integrato offre costrutti di livello superiore e pratiche chiamate che semplificano lo sviluppo.

Nota: per un ripasso prima di iniziare a lavorare con il codice, vedi il servizio Fogli e il servizio Presentazioni.

Attività 1: esegui le query in BigQuery e registra i risultati in Fogli

Questa attività costituisce una parte significativa di questo lab. Al termine della sezione, infatti, avrai quasi completato la metà di tutto il lab.

In questa sezione proverai a:

• Iniziare un nuovo progetto Google Apps Script
• Abilitare l'accesso al servizio avanzato di BigQuery
• Andare all'editor di sviluppo e inserire il codice sorgente dell'applicazione
• Eseguire il processo di autorizzazione dell'app (OAuth2)
• Eseguire l'applicazione che invia una richiesta a BigQuery
• Visualizzare un nuovo foglio Google creato a partire dai risultati di BigQuery

Crea un nuovo progetto Apps Script

1. Per creare un nuovo progetto Apps Script vai a script.google.com. Per questo lab, fai clic sul link Crea Apps Script.

2. Si apre l'editor di codice di Apps Script:

3. Assegna un nome al progetto facendo clic sul nome del progetto nella parte superiore ("Progetto senza titolo" nella figura sopra).

4. Nella finestra di dialogo Rinomina progetto, assegna un nome al progetto (ad esempio, "BigQuery", "Fogli", "Demo Presentazioni", ecc.), quindi fai clic su Rinomina.

Abilita il servizio avanzato di BigQuery

Abilita il servizio avanzato di BigQuery per il tuo nuovo progetto e attiva l'API BigQuery.

1. Fai clic sull'icona Aggiungi un servizio accanto a Servizi.

2. Nella finestra di dialogo Aggiungi un servizio, seleziona qualsiasi servizio e API pertinente.

3. Vai alla console Cloud e seleziona Menu di navigazione > API e servizi > Libreria.

4. Digita o incolla API BigQuery nella casella di ricerca, quindi seleziona API BigQuery.

5. Fai clic su Abilita per abilitare l'API BigQuery se necessario.

6. Torna al tuo progetto, la finestra di dialogo Aggiungi un servizio dovrebbe essere ancora aperta.

7. Seleziona API BigQuery e fai clic su Aggiungi per chiudere.

Inserisci ed esegui il codice della tua applicazione

A questo punto puoi inserire il codice dell'applicazione. Completa il processo di autorizzazione e inizia a utilizzare la prima versione di questa applicazione.

1. Copia il codice riportato nella casella qui sotto e incollalo sopra eventuali elementi nell'editor di codice:

/** * 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. Salva il file che hai appena creato selezionando l'icona Salva progetto nella barra dei menu oppure mediante Ctrl + S.

3. Rinomina il file facendo clic sui tre punti accanto al nome del file e poi su Rinomina.

4. Modifica il nome del file in bq-sheets-slides.gs e fai clic su Invio.

Che cosa fa questo codice? Sai che esegue una query in BigQuery e scrive i risultati in un nuovo foglio Google, ma qual è la query?

5. Osserva il codice della query nella funzione 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

Questa query esegue una ricerca nelle opere di Shakespeare, che fanno parte del set di dati pubblico di BigQuery, e restituisce le dieci parole che compaiono più di frequente in tutte le opere, in ordine decrescente per numero di ricorrenze. Pensa a che impresa immane sarebbe fare questa ricerca a mano per avere un'idea di quanto sia utile BigQuery.

Ci siamo quasi. Per impostare la variabile PROJECT_ID nella parte superiore di bq-sheets-slides.gs è necessario un ID progetto valido.

6. Sostituisci <TUO_ID_PROGETTO> con il tuo ID progetto che trovi nel riquadro a sinistra.

Ecco un esempio di codice con un esempio di ID progetto. Il valore effettivo del tuo ID progetto sarà diverso.

Esempio di codice:

// 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'); Nota: in questo snippet di codice, l'istruzione if serve a evitare che l'applicazione continui prima che sia stato impostato un ID progetto. Nota: se il selettore del menu si blocca e non funziona, ricarica la pagina.

7. Salva il file ed esegui il codice facendo clic sull'opzione Esegui nella barra dei menu.

8. Fai quindi clic su Rivedi autorizzazioni

Nota: se visualizzi un messaggio di errore Eccezione: il servizio API BigQuery non è stato abilitato per le tue app, rimuovi il servizio API BigQuery e aggiungilo di nuovo.

9. Nella finestra di dialogo Scegli un account da qwiklabs.net, fai clic sul tuo Nome utente e quindi su Consenti.

Nota: dopo aver autorizzato l'app non dovrai più ripetere questa procedura a ogni esecuzione. Vedrai di nuovo questa finestra di dialogo solo quando arriverai alla sezione "Inserisci i dati dei risultati in una presentazione", più avanti in questo lab, in cui viene chiesta l'autorizzazione dell'utente per la creazione e la gestione delle presentazioni Google.

10. Quando la funzione viene eseguita, nella parte superiore compare una casella con un messaggio.

La casella scompare quando l'esecuzione della funzione è completa. Se non la vedi, è probabile che l'esecuzione della funzione sia stata completata.

11. Vai al tuo Google Drive e cerca un nuovo foglio Google denominato Le parole più comunemente utilizzate in tutte le opere di Shakespeare o con la denominazione che hai assegnato alla variabile QUERY_NAME.

12. Apri il foglio di lavoro, dovresti vedere dieci righe di parole e per ciascuna il numero totale di occorrenze in ordine decrescente.

Fai clic su Controlla i miei progressi per verificare l'obiettivo. Esegui le query in BigQuery e registra i risultati in Fogli.

Riepilogo

Riassumiamo quanto fatto finora. Hai eseguito tramite codice una query su tutte le opere di Shakespeare; non certo una quantità di dati enorme, ma sicuramente più testo di quanto potresti esaminare manualmente cercando ogni parola di ogni opera, tenendone il conto e quindi ordinando le parole in base alla frequenza di apparizione in ordine decrescente. Non solo hai chiesto a BigQuery di svolgere questa operazione per te, ma hai anche utilizzato il servizio integrato in Apps Script per far sì che Fogli Google organizzasse quei dati per renderli facilmente fruibili.

Puoi sempre provare la query nella console di BigQuery prima di eseguirla in Apps Script. L'interfaccia utente di BigQuery è a disposizione degli sviluppatori.

1. Vai alla console Cloud e seleziona Menu di navigazione > BigQuery.

2. Fai clic su FINE nella finestra di dialogo Ti diamo il benvenuto in BigQuery nella console Cloud.

Si aprirà la console di BigQuery.

3. Inserisci il tuo codice nell'editor query e quindi fai clic su Esegui:

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

Attività 2: crea un grafico in Fogli Google

Torna all'Editor di script. Finora hai programmato un'app che esegue una query sulle opere di Shakespeare, quindi ordina e presenta i risultati in Fogli. Nel codice, la funzione runQuery() comunica con BigQuery e invia i risultati a un foglio Google. A questo punto aggiungerai codice per creare un grafico con i dati. In questa sezione creerai una nuova funzione denominata createColumnChart() che chiama il metodo newChart() di Fogli per organizzare i dati in un grafico.

La funzione createColumnChart() recupera il foglio che contiene i dati e richiede un grafico a colonne con tutti i dati. L'intervallo di dati inizia dalla cella A2 perché la prima riga è occupata dalle intestazioni di colonna e non dai dati.

1. Crea il grafico: aggiungi la funzione createColumnChart() a bq-sheets-slides.gs subito dopo runQuery() {dopo l'ultima riga di codice}:

/** * 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. Restituisci il foglio di lavoro: nel codice sopra riportato, createColumnChart() richiede l'oggetto del foglio di lavoro, quindi modifica l'app affinché restituisca l'oggetto spreadsheet per passarlo a createColumnChart(). Una volta registrata l'avvenuta creazione del foglio Google, torna all'oggetto alla fine di runQuery().

3. Sostituisci l'ultima riga (inizia con Logger.log) con quanto segue:

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

4. Funzione Drive createBigQueryPresentation(): è una buona idea tenere logicamente separate la funzionalità di BigQuery e quella di creazione del grafico. Crea una funzione createBigQueryPresentation() per far funzionare l'app chiamando entrambe le funzioni runQuery() e createColumnChart(). Il codice che aggiungi dovrebbe avere questo aspetto:

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

5. Inserisci la funzione createBigQueryPresentation() subito dopo questo blocco di codice:

// 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. Aumenta la riusabilità del codice: hai eseguito due importanti passaggi, ossia hai restituito l'oggetto del foglio di lavoro e creato una funzione guida. E se un collega volesse riutilizzare runQuery() senza che l'URL venga inserito nel log?

Per rendere runQuery() più idoneo all'uso generico, sposta la riga di inserimento nel log. Dove? Se hai risposto createBigQueryPresentation(), hai indovinato.

Dopo aver spostato la riga di inserimento nel log, il codice dovrebbe avere l'aspetto seguente:

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

Con le modifiche riportate sopra, il codice in bq-sheets-slides.js ora dovrebbe corrispondere al seguente, a parte 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. Salva il file.

8. Nella barra dei menu, fai clic su runQuery e seleziona createBigQueryPresentation dal menu a discesa.

9. Fai clic su Esegui.

Dopo l'esecuzione, otterrai un altro foglio Google su Google Drive, ma questa volta apparirà un grafico nel foglio accanto ai dati:

Attività 3: inserisci i dati dei risultati in una presentazione

La parte finale del lab consiste nel creare una nuova presentazione Google, inserire il titolo e il sottotitolo nella slide del titolo e quindi aggiungere due nuove slide, una per ciascuna cella di dati e un'altra per il grafico.

1. Crea la presentazione: inizia con la creazione di una nuova presentazione, quindi aggiungi un titolo e un sottotitolo alla slide predefinita per il titolo che trovi in ogni nuova presentazione. Tutto il lavoro nella presentazione viene svolto dalla funzione createSlidePresentation(), che aggiungi a bq-sheets-slides.gs subito dopo il codice della funzione 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. Aggiungi la tabella di dati: il passaggio successivo in createSlidePresentation() consiste nell'importare i dati delle celle dal foglio Google nella nuova presentazione. Aggiungi questo snippet di codice alla funzione 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. Importa il grafico: l'ultimo passaggio in createSlidePresentation() consiste nel creare un'altra slide, importare il grafico dal nostro foglio di lavoro e restituire l'oggetto Presentation. Aggiungi questo snippet finale alla funzione:

// 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. Restituisci il grafico: ora che la nostra funzione finale è completa, dai un altro sguardo alla sua firma. createSlidePresentation() richiede sia un oggetto spreadsheet sia un oggetto chart. Hai già modificato runQuery() in modo che restituisca un oggetto Spreadsheet, ora devi apportare una modifica analoga a createColumnChart() per restituire l'oggetto chart (EmbeddedChart). Per farlo, torna alla tua applicazione e aggiungi un'ultima riga alla fine di createColumnChart():

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

5. Aggiorna createBigQueryPresentation(): dal momento che createColumnChart() restituisce il grafico, devi salvare il grafico su una variabile quindi passare il foglio di lavoro e il grafico a createSlidePresentation(). Poiché inserisci nel log l'URL del nuovo foglio di lavoro creato, puoi fare lo stesso per la nuova presentazione. Sostituisci questo blocco di codice:

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

Con questo:

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

Dopo tutti gli aggiornamenti, ora il codice in bq-sheets-slides.gs dovrebbe corrispondere al seguente, ad eccezione di 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. Salva ed esegui di nuovo createBigQueryPresentation(). Prima dell'esecuzione, saranno visualizzati i prompt di una o più serie di autorizzazioni di visualizzazione e gestione delle tue presentazioni Google.
7. Vai a Il mio Drive e nota che, oltre al foglio Google che è stato creato, c'è anche una nuova presentazione Google con tre slide (titolo, tabella dati, grafico dati) come illustrato di seguito:

Complimenti!

Hai creato un'applicazione che sfrutta entrambi i lati di Google Cloud eseguendo una richiesta di BigQuery che esegue una query su uno dei suoi set di dati pubblici, hai creato un nuovo foglio Google in cui salvare i risultati, aggiunto un grafico basato sui dati recuperati e infine hai creato una presentazione Google con i risultati, nonché un grafico nel foglio di lavoro.

Tecnicamente hai fatto tutto questo. In termini generali, da un'analisi dei big data hai creato materiale che puoi presentare agli stakeholder, il tutto mediante codice in modo automatizzato. Ora puoi utilizzare questo lab e personalizzarlo per i tuoi progetti.

Completa la Quest

Questo self-paced lab fa parte delle Quest Workspace: integrazione per i dati e BigQuery Basics for Data Analysts. Una Quest è una serie di lab correlati che formano un percorso di apprendimento. Il completamento di una Quest ti permette di ottenere un badge come riconoscimento dell'obiettivo raggiunto. Puoi rendere pubblici i tuoi badge inserendone i link nel tuo CV online o sui social media. Iscriviti a una delle Quest contenenti il lab e ricevi un riconoscimento subito dopo averlo completato. Per vedere tutte le Quest disponibili, consulta il catalogo di Google Cloud Skills Boost.

Stai cercando un Challenge Lab pratico per dimostrare le tue abilità con BigQuery e convalidare le tue conoscenze? Dopo aver completato questa Quest, termina il Challenge Lab aggiuntivo per ricevere un esclusivo badge digitale Google Cloud.

Scopri di più

Il codice utilizzato in questo lab è disponibile anche in GitHub. Il lab cerca di rimanere sincronizzato con il repository. Di seguito puoi trovare ulteriori risorse per approfondire il materiale trattato in questo lab ed esplorare altri modi di accedere agli strumenti per sviluppatori Google attraverso la programmazione.

Documentazione

• Sito di documentazione di Google Apps Script
• Servizio fogli di lavoro di Apps Script
• Servizio Presentazioni di Apps Script
• Servizio avanzato BigQuery di Apps Script

Video correlati e generali

• Another Google (Apps) secret - (video introduttivo su Apps Script)
• Accessing Google Maps from a spreadsheet - (video)
• Altri video disponibili nella raccolta video di Google Apps Script
• Launchpad Online - serie di video
• G Suite Dev Show - serie di video

Novità e aggiornamenti correlati e generali

• BigQuery integrates with Google Drive (2016: link1, link2)
• Blog per sviluppatori Google
• Blog della piattaforma Google Cloud
• Blog su big data e machine learning Google Cloud
• Google Developers su Twitter (@GoogleDevs)
• Workspace blog per sviluppatori
• Sviluppatori Workspace Twitter (@GSuiteDevs)
• Newsletter mensile per gli sviluppatori di Workspace
• Centro didattico Google Workspace

Ultimo aggiornamento del manuale: 6 marzo 2023

Ultimo test del lab: 6 marzo 2023

Copyright 2024 Google LLC Tutti i diritti riservati. Google e il logo Google sono marchi di Google LLC. Tutti gli altri nomi di società e prodotti sono marchi delle rispettive società a cui sono associati.