Javascript, HTML, CSS e... !
0 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

0 Commenti

Non ci sono commenti

Nessuno ha ancora commentato questo articolo, fallo tu per primo!

scrivi un commento

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]http://www.appsscript.it[/url] se devi riferirti ad un indirizzo web