Estrarre dati da uno Spreadsheet tramite la relativa funzione Query con Google Apps Script

Nei Fogli di Google esiste una funzione, QUERY(), che permette di effettuare un'interrogazione sui dati tramite il linguaggio di query della Google Visualization API.
Il vantaggio di utilizzare tale funzione è evidente, basti pensare alle sorgenti di dati con le quali abbiamo solitamente a che fare e che, nella maggioranza dei casi, non hanno la struttura desiderata pronta per essere utilizzata oppure necessitano di una manipolazione del dato per effettuare una pulizia di alcune colonne, righe o valori non necessari o per isolarne una determinata porzione.
Ad esempio potrei avere uno Spreadsheet con 5 colonne di dati, magari alcune superflue o non necessarie, e per ottenere un grafico per una ipotetica dashboard ho bisogno che la struttura sia a 2 colonne, oppure ho una serie di righe corrispondenti alle richieste di ferie, permessi e malattia dei dipendenti di un'azienda ed ho la necessità di isolare solo quelle di un determinato dipendente per una determinata tipologia di richiesta.

Il presente tutorial mostra un approccio, che definirei semplificato, all'utilizzo della funzione QUERY() in modo programmatico con Google Apps Script.
Dico semplificato in quanto le righe di codice proposte non si avvalgono della Google Visualization API bensì simulano l'uso diretto della funzione QUERY in uno Spreadsheet inserendola in una cella di un nuovo foglio per poi eliminarlo dopo aver recuperato il range di dati interrogato.

Per l'esempio invece più tecnico e professionale, ovvero che fa uso della Google Visualization API e del relativo metodo setQuery della classe google.visualization.Query, rimando all'articolo dedicato: Estrarre dati da uno Spreadsheet tramite query SQL con Apps Script e la Google Visualization API.

Tornando all'approccio semplificato, poniamo di avere un Foglio di Google con i seguenti dati, Fig. 1:

Fig. 1 - Esempio di un data source in un Foglio di Google

L'intenzione è quella di effettuare una query che recuperi tutte le righe del set di dati le cui visualizzazioni sono comprese tra 0 e 100.
L'idea, in questo caso, è quella di creare un nuovo foglio nello Spreadsheet tramite il metodo insertSheet, inserire la funzione QUERY con la relativa interrogazione all'interno della prima cella tramite il metodo setFormula, recuperare (con getDataRange().getValues()) l'intervallo dei risultati all'interno di una variabile in Google Apps Script ed eliminare il foglio appena creato, con il metodo deleteSheet, in modo che lo Spreadsheet risulti inalterato.

Lo script che effettua le operazioni di cui sopra è il seguente:

function startQuerySimpleMethod() {
  var req = query("=QUERY(Foglio1!A:C; "select A, C where C >= 0 and C <= 100")");
  Logger.log(req);
}

function query(request) { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
  var r = sheet.getRange(1, 1).setFormula(request);

  var reply = sheet.getDataRange().getValues();
  SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet);

  return reply;
}

Da notare i parametri obbligatori nella query, ovvero l'intervallo di celle all'interno del foglio specificato e la query.

Quello che succede, in modo relativamente trasparente all'utente (in quanto per una frazione di secondo è possibile vedere che viene creato un nuovo foglio e subito dopo questo viene cancellato), è mostrato nella Fig. 2: (ovvero quello che avviene al momento dell'esecuzione del codice):

Fig. 2 - Foglio creato temporaneamente in modo programmatico per eseguire la funzione QUERY in una cella

Il risultato dell'esecuzione è poi mostrato nel file di log, Fig. 3, ed è pronto per essere elaborato da codice Apps Script.

Fig. 3 - Risultato della query mostrato nel file di log di Apps Script

Il metodo del Foglio di Google che permette l'inserimento della funzione in una cella è setFormula, inutile dire che l'esempio in questione è centrato sull'utilizzo della funzione QUERY ma che con il metodo appena presentato può essere gestita qualsisi formula nativa dello Spreadsheet.