Esistono molti strumenti per i data scientist per eseguire analisi dei big data, ma alla fine della giornata non devi comunque giustificare i risultati al management? Molti numeri su carta o in un database sono difficilmente presentabili alle principali parti interessate. Questo codelab intermedio di Google Apps Script sfrutta due piattaforme per sviluppatori di Google, G Suite e Google Cloud (GCP), per aiutarti a compiere questo ultimo passaggio.
Gli strumenti per sviluppatori di Google Cloud ti consentono di eseguire l'analisi approfondita dei dati, quindi di inserire i risultati in un foglio di lavoro e generare una presentazione con diapositive con questi dati, fornendo una fase più adatta per la consegna dei risultati al management. Questo codelab tratta l'API BigQuery di GCP (in quanto servizio avanzato di Apps Script) e i servizi integrati di Apps Script per Fogli Google e Presentazioni Google.
Motivazione/arte nota
L'app di esempio in questo codelab è stata ispirata da questi altri esempi di codice...
- L'app di esempio del servizio BigQuery di Google Apps Script e open source su GitHub
- L'app di esempio mostrata nel video per sviluppatori Generare slide dai dati del foglio di lavoro e pubblicata in questo post del blog
- L'app di esempio descritta nel codelab dell'API Google Slides
Sebbene l'app di esempio del codelab dell'API Slides includa anche BigQuery e Slides, differisce dall'app di esempio di questo codelab in diversi modi:
- App Node.js e app Apps Script
- Utilizza le API REST mentre utilizziamo i servizi Apps Script
- Utilizza Google Drive ma non Fogli Google, mentre questa app utilizza Fogli ma non Drive
Per questo codelab, abbiamo voluto riunire il maggior numero possibile di tecnologie in un'unica app, mostrando al contempo funzionalità e API di Google Cloud in un modo che assomigli di più a un caso d'uso reale. L'obiettivo è ispirarti a usare la tua immaginazione e a valutare la possibilità di sfruttare sia GCP che G Suite per risolvere problemi complessi della tua organizzazione o dei tuoi clienti.
Obiettivi didattici
- Utilizzare Google Apps Script con più servizi Google (GCP e G Suite)
- Utilizzare Google BigQuery per eseguire l'analisi dei big data
- Creare un foglio Google e compilarlo con i dati
- Come creare un nuovo grafico in Fogli
- Come trasferire grafici e dati da Fogli in una presentazione Google
Che cosa ti serve
- Accesso a internet e a un browser web
- Un Account Google (gli account G Suite potrebbero richiedere l'approvazione dell'amministratore)
- Competenze di base di JavaScript
- La conoscenza dello sviluppo di Apps Script può essere utile, ma non è obbligatoria
Come utilizzerai questo codelab/tutorial?
Come valuti la tua esperienza con gli strumenti e le API per sviluppatori di G Suite?
Come valuteresti la tua esperienza con Apps Script in particolare?
Come valuteresti la tua esperienza con gli strumenti e le API per sviluppatori di GCP?
Ora che sai di cosa tratta questo codelab, cosa farai esattamente?
- Prendere un esempio esistente di Apps Script-BigQuery e farlo funzionare
- Da questo esempio, scopri come inviare una query a BigQuery e ottenere i risultati
- Crea un foglio Google e inserisci i risultati di BigQuery
- Modifica leggermente il codice per alterare leggermente i dati restituiti e aggiunti al foglio
- Utilizzare il servizio Fogli in Apps Script per creare un grafico per i dati di BigQuery
- Utilizzare il servizio Presentazioni per creare una nuova presentazione
- Aggiungi un titolo e un sottotitolo alla slide predefinita per il titolo creata automaticamente per tutte le nuove presentazioni
- Crea una nuova slide con una tabella di dati, quindi importa le celle di dati del foglio
- Aggiungi un'altra nuova slide e aggiungici il grafico del foglio di lavoro
Iniziamo con alcune informazioni di base su Apps Script, BigQuery, Fogli e Presentazioni.
Google Apps Script e BigQuery
Google Apps Script è una piattaforma di sviluppo di G Suite che opera a un 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. Per riassumere, "Apps Script è un runtime JavaScript serverless per l'automazione, l'estensione e l'integrazione di G Suite".
È JavaScript lato server, simile a Node.js, ma è dedicato alla stretta integrazione con G Suite 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 G Suite e ad altri servizi, sia Google sia esterni (tramite i servizi
URLfetchoJdbcdi Apps Script) - Puoi 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 la maggior parte, l'insegnamento di Apps Script non rientra nell'ambito di questo codelab. Esistono numerose risorse online per aiutarti. La documentazione ufficiale contiene anche una panoramica con guide rapide, tutorial e video. Infine, non dimenticare il codelab introduttivo di Apps Script, che deve essere completato prima di iniziare questo.
Apps Script si interfaccia con altre tecnologie Google in due modi diversi:
- servizio integrato/nativo
- servizio avanzato
Un servizio integrato offre metodi di alto livello che puoi utilizzare per accedere ai dati dei prodotti G Suite o Google, oltre ad altri pratici metodi di utilità. Un servizio avanzato non è altro che un wrapper leggero che incapsula G Suite o l'API REST Google. 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, gli sviluppatori devono preferire un servizio integrato perché è più facile da usare e richiede meno lavoro rispetto ai servizi avanzati. Tuttavia, alcune API di Google non hanno servizi integrati, quindi un servizio avanzato potrebbe essere l'unica opzione. Ne è un esempio Google BigQuery, che non è disponibile attraverso servizi integrati, ma soltanto come servizio avanzato BigQuery . È comunque meglio di niente! BigQuery è un servizio GCP che consente di eseguire query semplici (o complesse) su corpus di dati molto vasti, dell'ordine di vari terabyte, e che riesce tuttavia a restituire risultati nel giro di qualche secondo.
Accedi a Fogli e Presentazioni Google da Apps Script
A differenza di BigQuery, sia Fogli che Presentazioni Google hanno servizi integrati (oltre a servizi avanzati, che utilizzeresti solo per accedere a funzionalità disponibili solo nell'API). Consulta la documentazione dei servizi Fogli e Presentazioni integrati prima di iniziare a lavorare con il codice. Naturalmente, esistono anche documenti per i servizi avanzati. Ecco quelli per Fogli e Presentazioni, rispettivamente.
Introduzione
Questa attività costituisce una parte significativa di questo codelab. Infatti, una volta terminato, avrai quasi completato la metà di tutto il codelab. Suddiviso in diverse sottosezioni, dovrai svolgere tutte queste attività:
- 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
Configurazione
- a) Crea un nuovo progetto Apps Script andando su
script.google.com. Esistono diverse linee di prodotto di G Suite e il metodo per creare un nuovo progetto può variare a seconda della versione che utilizzi. Se utilizzi solo il tuo account Gmail e non hai mai sviluppato progetti, vedrai una schermata vuota con un pulsante per creare il tuo primo progetto:
b) In caso contrario, potresti vedere tutti i tuoi progetti e un grande pulsante +Nuovo in alto a sinistra, quindi fai clic.
c) Se non rientri in nessuno dei casi precedenti, la schermata potrebbe avere l'aspetto riportato di seguito. In questo caso, cerca l'icona del menu hamburger nell'angolo in alto a sinistra e seleziona + Nuovo script.
d) Per chi preferisce la riga di comando. Lo strumento è clasp, in particolare eseguirai il comando clasp create.
e) L'ultimo modo per creare un nuovo progetto di script è semplicemente andare al link di scelta rapida: https://script.google.com/create.
- Indipendentemente dalla tecnica utilizzata per avviare un nuovo progetto, il risultato finale è che dovresti ritrovarti nell'editor di codice di Apps Script, una schermata simile a questa:
- Fai clic su File > Salva e assegna un nome al progetto.
- Successivamente, devi creare un progetto nella console Google Cloud per eseguire query BigQuery.
- Crea un nuovo progetto, assegnagli un nome, seleziona un account di fatturazione e fai clic su CREA.
- Al termine della creazione del progetto, viene visualizzata una notifica in alto a destra della pagina. Fai clic sulla voce Crea progetto: <nome del progetto> per aprire il progetto.
- Fai clic sull'icona del menu
in alto a sinistra e vai a API e servizi > Credenziali. Fai clic sulla scheda Schermata per il consenso OAuth (link diretto). - Nel campo Nome applicazione, inserisci "Big Data Codelab" e fai clic sul pulsante Salva in basso.
- Fai clic sull'icona con tre puntini
in alto a destra per espandere il menu e seleziona Impostazioni progetto (link diretto). - Copia il valore elencato in Numero progetto. Esiste un campo separato ID prodotto che utilizzeremo più avanti nel codelab.
- Torna all'editor di Apps Script e fai clic su Risorse > Progetto Cloud Platform.
- Inserisci il numero di progetto nella casella di testo e fai clic su Imposta progetto. Quando richiesto, fai clic su Conferma.
- Al termine, fai clic sul pulsante Chiudi per chiudere la finestra di dialogo.
- Ora che hai configurato un nuovo progetto, devi attivare il servizio avanzato BigQuery. Per farlo, seleziona Risorse -> Servizi Google avanzati e attiva l'opzione per l'API BigQuery.
- Nella parte inferiore è riportata la nota "Questi servizi devono essere abilitati anche nella "Dashboard dell'API di Google Cloud Platform", quindi fai clic su questo link che apre un'altra scheda del browser alla console per gli sviluppatori o "devconsole" in breve.
- Nella console per gli sviluppatori, fai clic sul pulsante +Abilita API e servizi in alto, cerca "bigquery", seleziona l'API BigQuery (non l'API BigQuery Data Transfer) e fai clic su Abilita per attivarla. Lascia aperta questa scheda del browser.
NOTA: dopo aver attivato l'API, potresti visualizzare una nota in questa pagina che dice qualcosa del tipo "Per utilizzare questa API, devi creare le credenziali...", ma non preoccuparti per ora: Apps Script si occuperà di questo passaggio.
- Nella scheda del browser dell'editor di codice, ti trovi ancora nel menu Servizi avanzati di Google, quindi fai clic su Ok per chiudere la finestra di dialogo e tornare all'editor di codice. Fai clic sul nome del progetto nella parte superiore e assegna il nome che preferisci, ad esempio "Demo BigQuery" o simile. Noi abbiamo scelto "Ultimo step".
A questo punto puoi inserire il codice dell'applicazione. Completa il processo di autorizzazione e inizia a utilizzare la prima versione di questa applicazione.
Carica l'applicazione ed eseguila
- Copia il codice riportato nella casella qui sotto e incollalo sopra eventuali elementi nell'editor 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 = '';
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());
}
Ora salva il file che hai appena creato, ma rinominalo da Code.gs a bq-sheets-slides.js. Che cosa fa questo codice? Ti abbiamo già detto che esegue una query in BigQuery e scrive i risultati in un nuovo foglio Google, ma qual è la query? Puoi vederlo nella parte superiore di 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.
- Siamo quasi pronti per provare questa funzionalità. Come vedi nella parte superiore di questo snippet di codice, è necessario un ID progetto valido, quindi dobbiamo aggiungere il tuo al codice dell'applicazione. Per ottenerlo, torna alla finestra o alla scheda del browser con la pagina della console per gli sviluppatori. (Ti abbiamo detto di lasciarlo aperto, ricordi?)
- In alto a sinistra dell'avatar del tuo Account Google, si trova il selettore del menu a discesa (
). Fai clic e seleziona Impostazioni progetto. Vedrai il nome, l'ID e il numero del progetto. Copia l'ID progetto e imposta la variabile PROJECT_IDnella parte superiore dibq-sheets-slides.jssul valore ottenuto dalla console per gli sviluppatori. NOTA: se il selettore del menu si blocca e non funziona, ricarica la pagina. - L'istruzione
ifserve a evitare che l'applicazione continui prima che sia stato impostato un ID progetto. Dopo aver aggiunto il tuo, salva il file ed esegui il codice. Per farlo, torna alla barra dei menu e seleziona Esegui > Esegui funzione > runQuery, fai clic su Rivedi autorizzazioni nella finestra di dialogo Questa app non è verificata. Di seguito è riportata una GIF animata (per un'altra app) che illustra i passaggi successivi:
- Una volta richiesta la revisione delle autorizzazioni, verrà visualizzata una nuova finestra di dialogo, come mostrato sopra. Scegli l'account Google corretto che eseguirà lo script, seleziona Avanzate, scorri verso il basso e fai clic su "Vai a <YOUR PROJECT NAME> (non sicuro)" per visualizzare la schermata di autorizzazione dell'applicazione OAuth2. (Scopri di più sul processo di verifica per capire perché questa schermata si trova tra te e la finestra di dialogo di autorizzazione OAuth2 riportata di seguito.)
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 all'attività 3, più avanti in questo tutorial, in cui viene chiesta l'autorizzazione dell'utente per la creazione e la gestione delle presentazioni Google. - Dopo aver fatto clic su Consenti nella finestra di dialogo OAuth2, lo script inizia a essere eseguito e nella parte superiore viene visualizzata una finestra di dialogo giallo pastello. Viene eseguito abbastanza rapidamente, quindi potresti non notare che è in esecuzione o che l'esecuzione è stata completata.
- La finestra di dialogo scompare al termine dell'operazione. Se non la vedi, probabilmente è stata completata. Vai a Google Drive (
drive.google.com) e cerca un nuovo foglio Google denominato "Le parole più comunemente utilizzate in tutte le opere di Shakespeare" o con il nome che hai assegnato alla variabileQUERY_NAME:
- Apri il foglio di lavoro, dovresti vedere dieci righe di parole e per ciascuna il numero totale di occorrenze in ordine decrescente:
Riepilogo attività 1
Riconosci cosa è appena successo: 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.
Il codice per bq-sheets-slides.js (il nome file scelto) che hai incollato sopra (a parte PROJECT_ID, che deve avere un ID progetto reale) è disponibile anche nella cartella step1 del repository GitHub di questo codelab all'indirizzo github.com/googlecodelabs/bigquery-sheets-slides. Il codice è stato ispirato dall'esempio originale nella pagina dei servizi avanzati di BigQuery, che eseguiva una query leggermente diversa: quali sono le parole più utilizzate da Shakespeare con 10 o più caratteri. Puoi anche visualizzare l'esempio nel suo repository GitHub.
Se ti interessano altre query che puoi provare sulle opere di Shakespeare o su altre tabelle di dati pubblici, consulta questa pagina web e questa. Indipendentemente dalla query che utilizzi, puoi sempre provarla nella console BigQuery prima di eseguirla in Apps Script. L'interfaccia utente di BigQuery è disponibile per gli sviluppatori all'indirizzo bigquery.cloud.google.com. Ad esempio, ecco come appare la nostra query utilizzando l'interfaccia utente di BigQuery:
Sebbene i passaggi precedenti abbiano sfruttato l'editor di codice di Apps Script, puoi anche scegliere di sviluppare localmente tramite la riga di comando. Se preferisci, crea uno script denominato bq-sheets-slides.js, incolla il codice riportato sopra e caricalo su Google con il comando clasp push. Se l'hai perso in precedenza, ecco di nuovo il link a clasp e come utilizzarlo.
Lo scopo di runQuery() è comunicare con BigQuery e inviare i risultati a un foglio. Ora dobbiamo creare un grafico con i dati. Creiamo una nuova funzione chiamata createColumnChart() che chiama il metodo newChart() di Fogli per farlo.
- Crea grafico. Aggiungi il corpo di
createColumnChart()riportato di seguito abq-sheets-slides.jssubito doporunQuery(). 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.
/**
* 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);
}- Restituisci foglio di lavoro. Sopra,
createColumnChart()richiede l'oggetto del foglio di lavoro, quindi dobbiamo aggiornarerunQuery()per restituire l'oggettospreadsheetin modo da poterlo passare acreateColumnChart(). Dopo aver registrato l'avvenuta creazione del foglio Google, restituisci l'oggettospreadsheetalla fine dirunQuery(), subito dopo la riga di log:
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// NEW: Return the spreadsheet object for later use.
return spreadsheet;
}- Funzione
createBigQueryPresentation()Guida. È una buona idea tenere logicamente separate la funzionalità di BigQuery e quella di creazione del grafico. Ora creiamo una funzionecreateBigQueryPresentation()per gestire l'app, chiamando siarunQuery()checreateColumnChart(). Il codice che aggiungi dovrebbe avere un aspetto simile a questo:
/**
* Runs a BigQuery query, adds data and a chart in a Sheet.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
createColumnChart(spreadsheet);
}- Rendere il codice più riutilizzabile. 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 rendererunQuery()più idoneo all'uso generico, dobbiamo spostare la riga di inserimento nel log. Dove? Se hai rispostocreateBigQueryPresentation(), 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()); // MOVED HERE
createColumnChart(spreadsheet);
}Con queste modifiche riportate sopra (di nuovo, a parte PROJECT_ID), il codice in bq-sheets-slides.js ora dovrebbe corrispondere al seguente (e si trova anche nella cartella step2 del repository 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);
}Salva il file, quindi vai nella parte superiore dell'editor di codice e passa all'esecuzione di createBigQueryPresentation() anziché runQuery(). Dopo l'esecuzione, otterrai un altro foglio Google, ma questa volta apparirà un grafico nel foglio accanto ai dati:
La parte finale del codelab 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.
- Crea un file di Presentazioni. Tutto il lavoro nella presentazione verrà svolto in
createSlidePresentation(), che aggiungeremo abq-sheets-slides.jssubito dopocreateColumnChart(). Iniziamo con la creazione di un nuovo file di presentazione, quindi aggiungi un titolo e un sottotitolo alla slide predefinita del titolo, che viene sempre visualizzata quando si avvia una nuova presentazione.
/**
* 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');- Aggiungi tabella di dati. Il passaggio successivo in
createSlidePresentation()consiste nell'importare i dati delle celle dal foglio Google nel nuovo file di presentazione. Questo snippet di codice, quindi aggiungilo alla funzione:
// 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]));
}
}- Importa grafico. L'ultimo passaggio in
createSlidePresentation()consiste nel creare un'altra slide, importare il grafico dal nostro foglio di lavoro e restituire l'oggettoPresentation. 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;
}- Grafico dei rendimenti. Ora che la nostra funzione finale è completa, dai un altro sguardo alla sua firma. Sì,
createSlidePresentation()richiede sia un oggetto spreadsheet sia un oggetto grafico. Abbiamo già modificatorunQuery()in modo che restituisca un oggettoSpreadsheet, ora dobbiamo apportare una modifica analoga acreateColumnChart()per restituire l'oggetto chart (EmbeddedChart). Torna alla tua applicazione per aggiungere un'ultima riga di codice alla fine dicreateColumnChart()per farlo:
// NEW: Return chart object for later use
return chart;
}- Aggiornamento
createBigQueryPresentation(). PoichécreateColumnChart()restituisce il grafico, dobbiamo salvarlo in una variabile e poi passare sia il foglio di lavoro che il grafico acreateSlidePresentation(). Poiché registriamo l'URL del foglio di lavoro appena creato, registriamo anche l'URL del nuovo file di Presentazioni. Aggiorna il tuocreateBigQueryPresentation()in modo che sia simile a 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); // UPDATED
var deck = createSlidePresentation(spreadsheet, chart); // NEW
Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}- Salva ed esegui di nuovo
createBigQueryPresentation(). Prima dell'esecuzione, però, tieni presente che la tua app ora ha bisogno di un'altra serie di autorizzazioni da parte dell'utente per visualizzare e gestire le tue presentazioni Google. Una volta concessa questa autorizzazione, l'app funzionerà come prima.
- Ora, oltre al foglio Google che è stato creato, dovresti ricevere anche una nuova presentazione Google con tre slide (titolo, tabella dati, grafico dati), come illustrato di seguito:
Complimenti! Ora hai creato un'applicazione che sfrutta entrambi i lati di Google Cloud eseguendo una richiesta Google BigQuery che esegue query su uno dei suoi set di dati pubblici, crea un nuovo foglio Google per archiviare i risultati, aggiunge un grafico basato sui dati appena recuperati e infine crea un file Google Slides con i risultati e il 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. Ci auguriamo che questo esempio ti ispiri a personalizzarlo per i tuoi progetti. Al termine di questo codelab, ti forniremo alcuni suggerimenti su come migliorare ulteriormente questa app di esempio.
Con le modifiche riportate sopra nell'attività finale (di nuovo, a parte PROJECT_ID), il codice in bq-sheets-slides.js ora dovrebbe corrispondere al seguente (e si trova anche nella cartella final nel repository 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());
}Il "secondo file" di questo codelab, che non svolge alcun ruolo, è il file manifest di Apps Script, appsscript.json. Puoi accedervi andando alla scheda del browser dell'editor di codice e selezionando Visualizza > Mostra file manifest dal menu in alto. I contenuti dovrebbero avere un aspetto simile a questo:
appsscript.json
{
"timeZone": "America/Los_Angeles",
"dependencies": {
"enabledAdvancedServices": [{
"userSymbol": "BigQuery",
"serviceId": "bigquery",
"version": "v2"
}]
},
"exceptionLogging": "STACKDRIVER"
}Il file manifest è un file di configurazione a livello di sistema che Apps Script utilizza per sapere quale ambiente di esecuzione deve essere disponibile per la tua applicazione. La copertura dei contenuti di un file manifest non rientra nell'ambito di questo codelab, ma puoi farti un'idea di cosa fa.
Di seguito puoi trovare ulteriori risorse per approfondire il materiale trattato in questo codelab ed esplorare altri modi di accedere agli strumenti per sviluppatori Google attraverso la programmazione. Il nostro obiettivo è mantenere questo codelab sincronizzato con il repository.
Risorse per questa applicazione
- Questo link del codelab: g.co/codelabs/bigquery-sheets-slides
- repo del codice sorgente
- Video (e) post del blog per sviluppatori
- Sessione di Google Cloud NEXT '18
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
- Un altro segreto di Google (Apps)? (video introduttivo su Apps Script)
- Accessing Google Maps from a spreadsheet?!? (video)
- Raccolta video di Google Apps Script
- Launchpad Online serie di video (predecessore di...)
- G Suite Dev Show serie di video
Novità e aggiornamenti correlati e generali
- Connettore dati Fogli Google per BigQuery (annuncio)
- Google BigQuery integrates with Google Drive (link1, link2)
- Blog per sviluppatori Google
- Blog della piattaforma Google Cloud
- Blog su big data e machine learning Google Cloud
- Google Developers Twitter (@GoogleDevs)
- Blog per sviluppatori G Suite
- Sviluppatori G Suite su Twitter (@googleworkspace)
- Newsletter mensile per gli sviluppatori di G Suite
Altri codelab
Introduttivo
- [API REST] API G Suite e Google (API Drive)
- [App Maker] Crea un'app web di database in App Maker
Intermedio
- [Apps Script] Strumento da riga di comando CLASP Apps Script
- [Apps Script] Componenti aggiuntivi di Gmail
- [Apps Script] Componente aggiuntivo per Documenti e API Natural Language GCP
- [Apps Script] Framework per bot di Hangouts Chat
- [API REST] Strumento di generazione di report personalizzati (API Sheets)
- [API REST] Generatore di slide personalizzate per l'analizzatore BigQuery delle licenze GitHub (API Slides + BigQuery)
App per la consultazione
- Convertitore da Markdown a Presentazioni Google (API Slides)
Di seguito sono riportate varie "sfide di programmazione", diversi modi per migliorare o ampliare l'esempio che abbiamo creato in questo codelab. Questo elenco non è esaustivo, ma dovrebbe fornire alcune idee su dove puoi fare il passo successivo.
- Applicazione. Non vuoi essere limitato dall'utilizzo di JavaScript o dalle restrizioni imposte da Apps Script? Porta questa applicazione nel tuo linguaggio di programmazione preferito che utilizza le API REST per Google BigQuery, Fogli e Presentazioni.
- BigQuery. Prova una query diversa per il set di dati di Shakespeare... magari trovi una query che ti interessa. Un'altra query di esempio è disponibile nell'app di esempio BigQuery di Apps Script originale.
- BigQuery. Prova alcuni degli altri set di dati pubblici di BigQuery… magari trovi un set di dati più significativo per te.
- BigQuery. In precedenza, abbiamo menzionato altre query che puoi provare sulle opere di Shakespeare o su altre tabelle di dati pubblici e volevamo ricondividere questa pagina web e questa.
- Fogli. Sperimenta con altri tipi di grafici.
- Fogli e BigQuery. Inverti la situazione: magari hai un set di dati di grandi dimensioni in un foglio di lavoro. Nel 2016, il team di BigQuery ha introdotto la funzionalità che consente agli sviluppatori di utilizzare un foglio come origine dati (per ulteriori informazioni, consulta i post del blog uno e due).
- Presentazioni. Aggiungi altre slide alla presentazione generata, ad esempio immagini o altri asset collegati all'analisi dei big data. Ecco una guida al servizio integrato di Presentazioni per iniziare.
- G Suite. Trovare un utilizzo per altri servizi integrati di G Suite o Google da Apps Script, ad esempio: Gmail, Google Drive, Calendar, Documenti, Maps, Analytics, YouTube e così via, nonché altri servizi avanzati. Per ulteriori informazioni, consulta la panoramica di riferimento per i servizi integrati e avanzati.