JavaScript, HTML, CSS e... !
7 commenti

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:



esempio di un data source in un foglio di google

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:



risultato di una query effettuata su un data source in un foglio di google tramite le google visualization api

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

Tags

Michele Pisani

Michele Pisani

Sviluppatore Javascript ed esperto in Digital Analytics

L'esperienza nel settore Digital Analytics unita ad anni di sviluppo in Javascript ha trovato la massima espressione in Google Apps Script che mi ha permesso, con estrema facilità e poche righe di codice, di realizzare potenti applicazioni interattive e processi automatizzati integrati con i prodotti della G Suite.

Come contattarmi
scrivi un commento

7 Commenti

  1. martedì 25 agosto 2020 alle ore 16.53 sergio

    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:

    var token = "MIO TOKEN";
    var SheetID = "ID FOGLIO";
    function doPost(e) {
    var stringJson = e.postData.getDataAsString();
    var updates = JSON.parse(stringJson);

    if(updates.message.text){
    sendText(updates.message.chat.id,searchFoglio1ById(updates.message.text));
    }

    }
    function getRows(){
    var rangeName = 'Foglio1!A2:B';
    var rows = Sheets.Spreadsheets.Values.get(SheetID, rangeName).values;
    return rows;
    }
    function searchFoglio1ById(idFoglio1){
    var dataFoglio1 = getRows();
    for (var row = 0; row < dataFoglio1.length; row++) {
    if(dataFoglio1[row][0]==idFoglio1){
    return dataFoglio1[row][1];
    }
    }
    return "Non trovato";
    }
    function testgetrow(){
    var nome= searchFoglio1ById(1003);
    var x = "";

    }
    function sendText(chatid,text,replymarkup){
    var data = {
    method: "post",
    payload: {
    method: "sendMessage",
    chat_id: String(chatid),
    text: text,
    parse_mode: "HTML",
    reply_markup: JSON.stringify(replymarkup)
    }
    };
    UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', data);
    }



    Source code standard:

    var token = "MIO TOKEN";
    function doPost(e) {
    var stringJson = e.postData.getDataAsString();
    sendText("98058903",stringJson);
    var updates = JSON.parse(stringJson);

    if(updates.message.new_chat_participant){
    //invia messaggio di benvenuto updates.message.chat.id
    sendText(updates.message.chat.id,"Ciao" + updates.message.new_chat_participant.first_name);
    } else if(updates.message.left_chat_participant){
    //invia messaggio di saluto updates.message.chat.id
    sendText(updates.message.chat.id,"Alla prossima" + updates.message.left_chat_participant.first_name);
    } else if(updates.message.photo){
    //invia messaggio di saluto updates.message.chat.id
    sendText(updates.message.chat.id,"Questa è la foto");
    }else if(updates.message.text){

    if(updates.message.text[0]=="/"){
    //inviare un messaggio di risposta al comando

    sendText(updates.message.chat.id,"Questo è il testo del comando");
    } else {
    //inviare un normale messaggio di testo di risposta
    sendText(updates.message.chat.id,"Questo è un testo normale");
    }

    }

    }
    function sendText(chatid,text,replymarkup){
    var data = {
    method: "post",
    payload: {
    method: "sendMessage",
    chat_id: String(chatid),
    text: text,
    parse_mode: "HTML",
    reply_markup: JSON.stringify(replymarkup)
    }
    };
    UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/', data);
    }



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

    Rispondi a questo commento
    • martedì 25 agosto 2020 alle ore 23.12 Michele PisaniAutore

      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.

      Rispondi a questo commento
      • mercoledì 26 agosto 2020 alle ore 16.53 sergio

        Grazie della risposta, l'errore è in Source code ......

      • mercoledì 26 agosto 2020 alle ore 19.50 sergio

        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

      • mercoledì 26 agosto 2020 alle ore 19.54 Michele PisaniAutore

        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.

      • mercoledì 26 agosto 2020 alle ore 21.13 sergio

        Ho eliminato la riga, salvato lo script aggiornando poi il runtime ma .... ottengo

        {"ok":true,"result":true,"description":"Webhook is already deleted"}

      • mercoledì 26 agosto 2020 alle ore 21.29 Michele PisaniAutore

        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.

Scrivi un commento

Il tuo indirizzo email non sarà pubblicato.I campi contrassegnati da un * sono obbligatori
Puoi utilizzare i seguenti tag nei commenti:
[bold]testo[/bold] se vuoi evidenziare un testo con il grassetto[code]function helloworld() { }[/code] se vuoi pubblicare una porzione di codice[url]https://www.appsscript.it[/url] se devi riferirti ad un indirizzo web