Scrivere dati su uno Spreadsheet in modo performante con Google Apps Script

Leggere e scrivere dati con Google Apps Script all'interno di un foglio di calcolo può essere fatto in diversi modi, tuttavia ci sono alcune best practice da applicare durante la scrittura del codice che consentono di ottenere prestazioni migliori, e con il termine 'migliori' intendo ottimizzazioni che possono superare anche il 1000% di riduzione di velocità di esecuzione del codice in base all'approccio utilizzato.

Prima di passare al lato tecnico vorrei fare una premessa in quanto dobbiamo considerare che tra le limitazioni di Google Apps Script una delle più restrittive è il limite imposto all'esecuzione dello script che è pari a 6 minuti ad esecuzione (l'ultimo aggiornamento ufficiale delle limitazioni correnti risale a dicembre 2016. Aderendo al programma Early Access questo limite sale a 30 minuti ad esecuzione). Questa restrizione comporta l'esigenza di generare un codice più snello e veloce nelle operazioni che devono essere eseguite tenendo di conto che uno Spreadsheet così come lo vediamo nel nostro browser, a differenza di un comune Excel, non esiste fisicamente nel nostro computer bensì esso e i suoi dati vengono memorizzati da qualche parte su un server di Google e vengono restituiti come contenuti HTML per essere interpretati da qualsiasi browser web.
Pertanto, ogni volta che apportiamo una modifica ad uno Spreadsheet, sia manualmente scrivendo in una cella che avvalendosi di uno script in Google Apps Script, il flusso prevede che le informazioni vengano inviate al server, il quale le interpreta, le elabora e le restituisce usando la nostra connessione alla rete. Un viaggio di byte che può impiegare più o meno tempo in base alle variabili in gioco, le quali possono essere prevedibili come la nostra connessione ad internet o la logica utilizzata nella scrittura del codice ma anche non note, come ad esempio la distanza dal nostro computer al server Google o il carico di lavoro che quest'ultimo sta sopportando in base alle centinaia o migliaia di richieste ricevute contemporaneamente inviate dagli altri utilizzatori del servizio sparsi per l'Europa o per il Mondo.

Tornando quindi all'aspetto tecnico, per riuscire ad ottenere migliori prestazioni per il nostro script, laddove possiamo intervenire su quei parametri che riusciamo a controllare tra i quali appunto la scrittura di un codice più performante, è necessario prestare attenzione ad utilizzare con oculata parsimonia il numero di richieste ad un qualsiasi servizio remoto.

Nell'esempio seguente è mostrato un test di velocità (per il quale ho preso spunto da un post sul forum di stackoverflow adeguandolo alla situazione attuale, ovvero adattandolo all'esigenza e andando a rimuovere le chiamate a servizi deprecati) dove viene messo a confronto il tempo di esecuzione di uno script, che scrive testo e numeri in 5000 celle di uno Spreadsheet, con 3 diverse modalità di scrittura: una riga alla volta (tradotto in 500 chiamate ad un servizio remoto), una cella alla volta (tradotto in 5000 chiamate ad un servizio remoto) e tutte le celle in una sola volta (tradotto in una sola chiamata al servizio remoto):

function testSpeed() {
  var numObj = 500;
  var numAttr = 10;
  var doFlush = false;  // Se impostato su 'true' attiva la chiamata a SpreadsheetApp.flush()
  
  var arr = buildArray(numObj, numAttr); // Crea un array bidimensionale popolato con il numero di elementi e di righe passato
  var start, stop, sheet;
  
  // Salvataggio dati in uno Spreadsheet con un oggetto (una riga) alla volta
  sheet = SpreadsheetApp.getActive().getActiveSheet().clear();
  start = new Date().getTime();
  for (var row=0; row<=numObj; row++) {
    var values = [];
    values.push(arr[row]);
    sheet.getRange(row+1, 1, 1, numAttr).setValues(values);
    if (doFlush) SpreadsheetApp.flush();
  }
  stop = new Date().getTime();
  Logger.log("Tempo trascorso per la scrittura con una riga alla volta: " + (stop - start));
  
  // Salvataggio dati in uno Spreadsheet con un attributo (una cella) alla volta
  sheet = SpreadsheetApp.getActive().getActiveSheet().clear();
  start = new Date().getTime();
  for (var row=0; row<=numObj; row++) {
    for (var cell=0; cell<numAttr; cell++) {
      sheet.getRange(row+1, cell+1, 1, 1).setValue(arr[row][cell]);
      if (doFlush) SpreadsheetApp.flush();
    }
  }
  stop = new Date().getTime();
  Logger.log("Tempo trascorso per la scrittura con una cella alla volta: " + (stop - start));

  // Salvataggio dati in uno Spreadsheet tutto in una volta
  sheet = SpreadsheetApp.getActive().getActiveSheet().clear();
  start = new Date().getTime();
  sheet.getRange(1, 1, numObj+1, numAttr).setValues(arr);
  if (doFlush) SpreadsheetApp.flush();
  stop = new Date().getTime();
  Logger.log("Tempo trascorso per la scrittura tutto in una sola volta: " + (stop - start));
}

function buildArray(numObj, numAttr) {
  numObj = numObj | 500;
  numAttr = numAttr | 10;
  var array = [];
  for (var obj = 0; obj <= numObj; obj++) {
    array[obj] = [];
    for (var attr = 0; attr < numAttr; attr++) {
      var value;
      if (obj == 0) {
        value = "Colonna" + attr;
      }
      else {
        value = ((attr % 2) == 0) ? "Testo di riempimento al solo scopo di fornire una stringa di lunghezza moderata per lo scopo" : Number.MAX_VALUE;
      }
      array[obj].push(value);
    }
  }
  return array;
}

Analizzando macroscopicamente l'anatomia del codice, quello che viene effettuato è la generazione di un array bidimensionale (500 righe x 10 colonne) costituito da stringhe di testo e valori numerici (al solo scopo esemplificativo) e l'inserimento di questi dati in un foglio di calcolo per ciascuna delle 3 modalità precedentemente descritte. Per ciascuna modalità, inoltre, un controllo inserito a inizio e fine script salverà il tempo di esecuzione necessario per il completamento dell'operazione.

Non è difficile immaginare che l'inserimento delle 5000 celle effettuato tutto in una sola volta (ovvero con una sola chiamata al servizio remoto) sia quello più veloce rispetto all'inserimento riga per riga o cella per cella, ma la differenza in termini di tempo di esecuzione con cui questo avviene è a mio avviso sinceramente strabiliante... il log mostrato di seguito (Fig. 1) evidenzia i tempi, espressi in millisecondi (ms), per i 3 tipi di scrittura dei dati:

Fig. 1 - Tempi di esecuzione per l'ottenimento dello stesso risultato con diversi approcci di scrittura dei dati in uno Spreadsheet

Nel caso vi venisse in mente di abilitare il metodo Spreadsheet.flush() per l'esempio in questione, preparatevi all'errore "Exceeded maximum execution time"... ovvero l'interruzione drastica dell'esecuzione dello script per superamento del limite dei 6 minuti di esecuzione imposto da Google Apps Script (se proprio non resistete alla curiosità vi consiglio di provare l'attivazione del metodo Spreadsheet.flush() per il terzo caso, ovvero quello più veloce, e verificarne la differenza. Per il caso specifico, con i test che ho effettuato, siamo nell'ordine di una lentezza 35 volte maggiore).

Nell'articolo "Google Apps Script: best practice per il miglioramento delle performance" sono elencati altri suggerimenti utili per il miglioramento delle prestazioni dei nostri script.