!
Con il il metodo setQuery della classe google.visualization.Query
Estrarre dati da uno Spreadsheet tramite SQL con Apps Script e la Google Visualization API
Come descritto nel metodo semplificato per 'Estrarre dati da uno Spreadsheet tramite la relativa funzione Query con Google Apps Script', i vantaggi nell'ottenere una struttura dati filtrata, tramite un'interrogazione SQL ad un set di dati, pronta per essere utilizzata o mostrata a video in una tabella in formato HTML, sono molteplici.
Nell'articolo appena menzionato viene simulato l'uso diretto della funzione QUERY in uno Spreadsheet tramite Apps Script come se l'operazione fosse effettuata manualmente, ovvero inserendo la funzione con la query in una cella di un nuovo foglio per poi eliminarlo dopo aver recuperato il range di dati interrogato. Nel presente tutorial invece mostrerò un approccio senza dubbio più professionale, ma anche più performante, che si avvale della Google Visualization API.
Quello che il codice mostrato di seguito andrà ad effettuare è utilizzare il metodo setQuery della classe google.visualization.Query per effettuare un'interrogazione SQL sui dati presenti all'interno di un foglio di uno Spreadsheet e restituire il risultato in una pagina web all'interno di una tabella HTML tramite la classe google.visualization.Table.
Per poter usufruire di questo metodo è necessario distribuire lo script come applicazione web, pertanto il nostro codice sarà composto da un file di script in formato .gs e da un file .html che conterrà il template e le funzioni google per accedere all'API.
Prendiamo in considerazione il seguente set di dati, Fig. 1:
Fig. 1 - Esempio di un data source in un Foglio di Google
L'intenzione, come già adottato nel tutorial semplificato, è quella di effettuare una query che recuperi tutte le righe del set di dati le cui visualizzazioni sono comprese tra 0 e 100.
Senza dilungarmi ulteriormente mostro di seguito gli script necessari all'ottenimento del risultato per poi arricchire le spiegazioni con riferimenti e note utili.
File Codice.gs
function doGet() {
var output = HtmlService.createTemplateFromFile('index');
output.token = ScriptApp.getOAuthToken();
return output.evaluate().setTitle('SQL Query da Google Apps Script');
}
function getCurrentSpreadsheet() {
return SpreadsheetApp.getActiveSpreadsheet();
}
function getCurrentSpreadsheetID() {
return getCurrentSpreadsheet().getId();
}
function getCurrentSheetName() {
return getCurrentSpreadsheet().getActiveSheet().getName();
}File index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div id="dataTable"><h4>Caricamento...</h4></div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
google.load('visualization', '1.0', {packages: ['corechart','table']});
google.setOnLoadCallback(loadEditor);
function loadEditor() {
var queryString = encodeURIComponent("select A, C where C >= 0 and C <= 100");
var SSID = "<?=getCurrentSpreadsheetID();?>"
var SHEET_NAME = "<?=getCurrentSheetName();?>"
var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key='+SSID+'&sheet='+SHEET_NAME+'&oauth_token=<?=ScriptApp.getOAuthToken()?>&headers=1&tq=' + queryString);
query.send(handleSampleDataQueryResponse);
}
function handleSampleDataQueryResponse(response) {
console.log(response);
var data = response.getDataTable();
var chartTbl = new google.visualization.Table(document.getElementById('dataTable'));
chartTbl.draw(data);
}
</script>
</body>
</html>Per pubblicare l'applicazione e verificare il risultato in una pagina web, una volta che il codice è pronto, sarà necessario cliccare sul menu 'Pubblica -> Distribuisci come applicazione web...' nell'editor di script di Apps Script (un esempio con screenshot da cui prendere spunto per i passaggi necessari, per quanto semplici, per la distribuzione di una web app si trova nell'articolo 'Creare un Web Service o una RESTful API con Google Apps Script').
In caso positivo verrà mostrata la tabella contenente il risultato estratto dal set di dati dello Spreadsheet in base alla query effettuata, come mostrato in Fig. 2 dove a supporto del risultato ho evidenziato anche il contenuto in console:
Fig. 2 - Risultato di una query effettuata su un data source in un Foglio di Google tramite le Google Visualization API
Se nel codice .html siete stati incuriositi dalla presenza dei tag di apertura e chiusura che ricordano quelli del PHP, vi consiglio la lettura del seguente articolo: Scriptlets: combinare HTML e codice Apps Script per creare pagine dinamiche.
Attenzione che, in caso di bound script, ovvero un Google Script incorporato all'interno di uno Spreadsheet, tutto dovrebbe funzionare correttamente senza ulteriori adeguamenti, nel caso invece di script standalone (script autonomo) che, per intendersi, richiama lo Spreadsheet dal quale recuperare i dati tramite il metodo openById(), al fine di ottenere l'autorizzazione necessaria all'esecuzione del codice dovrà essere modificato manualmente il File Manifest (per maggiori informazioni sull'argomento fare riferimento all'articolo 'Il file Manifest in Google Apps Script: cos'è e come gestirlo') aggiungendo l'opportuno OAuth scope, nel caso specifico il seguente:
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets"
]La presenza dello scope è verificabile dall'editor di script cliccando sul menu File -> Proprietà del progetto e nella finestra che si aprirà visualizzare la tab 'Ambiti'. Nel caso in cui l'autorizzazione non sia presente il risultato di ScriptApp.getOAuthToken() avrà valore null anziché un token nel formato desiderato, ad esempio ya29.GlsXBl_CqvyCvJg2AnYTb4UKPidkEaipemCMZVeyPNniJdYh484r0Z..., e verrà restituito un messaggio di errore dovuto all'accesso negato, come il srguente:
// Messaggio di errore in caso di assenza del token in querystring:
/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"access_denied","message":"ACCESS_DENIED","detailed_message":"This spreadsheet is not publicly viewable and requires an OAuth credential. For more information, see https://support.google.com/docs/?p=gviz_tq_auth"}]});In caso di esito positivo, l'URL dell'API interrogato avrà tutti i parametri correttamente compilati e la risposta conterrà il contenuto desiderato:
// Messaggio con esito positivo (dove nel parametro 'table' è presente il risultato dell'interrogazione SQL):
/*O_o*/
google.visualization.Query.setResponse({"version":"0.6","reqId":"0","status":"ok","sig":"796079848","table":{"cols":[{"id":"A","label":"Video ID","type":"string"},{"id":"C","label":"Visite","type":"number","pattern":"General"}],"rows":[{"c":[{"v":"V6noZ1BAeRA"},{"v":59.0,"f":"59"}]},{"c":[{"v":"GM5hA2AdI7k"},{"v":16.0,"f":"16"}]},{"c":[{"v":"RVGlBfx02Do"},{"v":29.0,"f":"29"}]},{"c":[{"v":"Fc4Tvp0Y-2M"},{"v":74.0,"f":"74"}]}]}});Nota: Gli esempi di risposta (esito positivo, "status":"ok" ed esito negativo, "status":"error") appena mostrati sono stati ottenuti a scopo illustrativo eseguendo direttamente nella barra degli indirizzi del browser il rispettivo URL interrogato (in tal caso viene scaricato un file chiamato json.txt con il contenuto appena mostrato a seconda della situazione). A livello di rappresentazione visiva in pagina tali messaggi non sono mostrati tuttavia l'eventuale esito negativo sarà indicato da un breve messaggio in rosso.
Nella documentazione ufficiale della Google Visualization API è possibile trovare altri riferimenti utili per eseguire varie manipolazioni di dati con le query SQL nonché per la visualizzazione degli stessi nella tabella di output per l'aggiunta di colonne, di righe, dell'ordinamento dei contenuti, dell'abilitazione di contenuto HTML all'interno delle celle della tabella, ecc...
Ciao,
seguendo alcuni video sul web ho provato a creare un bot telegram che interrogato fornisca dati presi da un foglio googlesheet.
Ho utilizzato il codice:
Source code standard:
Ricevo messaggio d'errore
SyntaxError: Unexpected identifier (riga 50, file "Codice.gs")
per la riga SOURCE CODE e non riesco a risolvere.
Grazie dell'aiuto (se puoi).
Ciao Sergio,
dal codice che hai riportato non si riesce a capire che comando ci sia alla riga 50. Verifica quella riga di codice nel tuo file di script Codice.gs, in alternativa, se non ci sono anomalie evidenti prova a disattivare il nuovo runtime Apps Script basato su Chrome V8 dal menu Esegui dell'IDE di Apps Script.
Grazie della risposta, l'errore è in Source code ......
Disattivando il runtime ottengo questo
; mancante prima dell'istruzione. (riga 50, file "Codice")
mi pare di capire quindi che ci sia una incompatibilità .... ma come la risolvo ?
Grazie
Ciao Sergio,
ma "Source code", così come indicato, è una stringa scritta all'interno del codice che non ha motivo di essere di lì, non è un comando. Se effettivamente l'errore ti viene dato in quel punto ti basta eliminare il testo "Source code" dal file.
Ho eliminato la riga, salvato lo script aggiornando poi il runtime ma .... ottengo
{"ok":true,"result":true,"description":"Webhook is already deleted"}
Ciao Sergio,
credo sia una questione di ContentType, ad ogni modo non è un problema strettamente legato ad Apps Script senza contare che apre, o può aprire, lunghi scenari che non si risolvono facilmente in questo canale e con le poche informazioni a disposizione.
Se hai bisogno di ulteriore supporto lato codice puoi provare a scrivere nel gruppo su Facebook "Fatti di Apps Script": https://www.facebook.com/groups/AppsScript, il mio suggerimento comunque è quello di approfondire laddove hai prelevato il codice per capire meglio cosa aspettarsi da quello script e gestire configurazioni correlate.
Ciao a tutti,
ho usato questa guida per visualizzare su una webapp parte dei dati contenuti in un foglio.
Il foglio contiene una colonna con indirizzi email degli utilizzatori. Ho creato una query che visualizza solo se alla colonna 8 la mail corrisponde a quella del effectiveuser.
Ho fatto deploy come utente che accede purchè nel dominio...però funziona solo se sono io a entrare, con altri account mi rimanda la tabella vuota, solo header...
Ideee?
Grazie!
ah...stessa cosa usando activeuser anzichè effectiveuser
Ciao Matteo,
in questo caso cercherei di visualizzare in un log se effettivamente la corrispondenza delle email dell'utente che esegue lo script viene trovata tra gli indirizzi presenti in colonna 8.
Sì, se inserisco il valore direttamente (var user, vedi codice sotto), nel log visualizzo le righe corrispondenti. Il problema ce l'ho quando cerco recuperare la mail dallo user che esegue lo script. Se il browser non è sincronizzato col mio account la tabella risulta vuota. Può darsi che sia un problema di autorizzazioni ad eseguire il codice?
Grazie ancora
Farei una prova con un altro account per visualizzare che utente viene rilevato e se questo viene riconosciuto dalla tua lista. Nel caso, come dici, lavorerei sulle autorizzazioni al Foglio. Ad ogni modo quello che farei è verificare se il confronto dataRow[7] == user sta effettivamente confrontando quello che ti aspetti.
Risolto facendo deploy come me stesso e non come utente che accede all'applicazione.
Grazie dell'attenzione.
Grazie del feedback Matteo,
in questo caso funziona perché risulterà sempre il tuo indirizzo ad accedere. Ad ogni modo, se non hai più bisogno di verificare gli indirizzi abilitati definiti nella colonna 8, direi che è la soluzione migliore.