UTC vs GMT vs getSpreadsheetTimeZone()

Il contenuto di questo articolo è focalizzato su una questione che può generare incongruenze quando si lavora con le date in Google Apps Script ovvero, la loro formattazione. Il motivo per il quale ho deciso di parlarne in questo blog, più centrato su temi che interessano la Digital Analytics, è proprio perché la manipolazione delle date è un aspetto con il quale è quasi inevitabile imbattersi quando si tenta di accedere ai dati di Google Analytics tramite le Analytics API da un Google Sheets con Google Apps Script.

Lo scenario

Una situazione tipica si ha quando, da un Foglio di Google, si recupera una data presente all’interno di una cella (Fig. 1) tramite una funzione di Apps Script, per utilizzarla al fine di interrogare un report di Google Analytics o effettuare una query in BigQuery.

Considerando che la data così recuperata dallo script viene convertita in un formato non utilizzabile per la maggior parte delle operazioni di uso comune (nel caso specifico Sun Sep 01 00:00:00 GMT+02:00 2019, Fig. 2), è richiesta la conversione in un formato dedicato, ovvero con una struttura di tipo Anno-Mese-Giorno: YYYY-MM-DD.

Da notare che la data all’interno del Foglio di Google non contiene l’ora, in quanto non necessariamente utile per gli scopi in oggetto. Per questo motivo il suo valore convertito all’interno del Log in Apps Script viene considerato come 00:00:00. Questo dettaglio è il fulcro della questione.

Per convertire una data nel formato di interesse, con Google Apps Script, è possibile utilizzare il metodo formatDate della Classe Utilities. Tale metodo accetta come parametri la data da convertire, il timeZone e il formato di conversione. Il timeZone indica il fuso orario con il quale sarà gestito il risultato.
Alcuni esempi sono i seguenti:

• Utilities.formatDate(date, ‘UTC‘, ‘yyyy-MM-dd’);
• Utilities.formatDate(date, ‘GMT+1‘, ‘yyyy-MM-dd’);
• Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), ‘yyyy-MM-dd’);

È molto importante prestare attenzione al fuso orario utilizzato nella funzione in quanto il risultato della conversione del formato della data può essere imprevisto e inaspettato.

Quello mostrato in Fig. 3 è quanto ottenuto utilizzando in un caso la stringa UTF come valore del timeZone, nell’altro il metodo getSpreadsheetTimeZone() associato all’istanza del Foglio di Google dal quale viene recuperata la data dalla cella.

Nel primo caso la data convertita non corrisponde a quella di origine!

Diversi valori in timeZone danno diverse risposte

L’utilizzo dei valori proposti nell’esempio, per la conversione della data nel formato desiderato, può tradursi in un risultato non previsto. Questo significa che, se nella cella dello Spreadsheet recupero il valore della data 2019/09/01 per utilizzarlo in una funzione dal file di script (ad esempio per interrogare Google Analytics), il rischio dopo la sua conversione è quello di andare ad interrogare nella piattaforma di analisi il giorno sbagliato, nel caso dell’esempio 2019/08/31, che è appunto il giorno precedente a quello selezionato.
Vediamo il motivo di questa incongruenza.

timeZone: UTC, GTM o qualsiasi stringa

Normalmente la rappresentazione delle date è relativa all’ora di Greenwich (Greenwich Mean Time o GMT) conosciuta anche come Universal Time Coordinate (UTC). Utilizzando il valore UTC per definire il timeZone nel metodo di formattazione della data, viene considerata l’ora rispetto a dove si trova il server di Google Apps Script nel quale risiede lo script. Dall’Italia, trovandosi in un fuso orario diverso, sottrarrà alla data una o due ore (in base all’ora legale) scalando al giorno precedente e formattando quest’ultima data.
Lo stesso si ottiene se come valore di timeZone utilizziamo una stringa fittizia come ‘UTF‘ oppure ‘pippo‘. Questo è importante saperlo perché, anziché generare un errore, il metodo ignorerà tale valore considerando quello di default.

Forzando manualmente il valore di timeZone, ovvero inserendo GMT+1, noteremo che la data verrà convertita correttamente, ma questo solo fino al 31 marzo dopodiché, a causa dell’ora legale, necessiterebbe di essere modificato in GMT+2.

Questo inconveniente può essere evitato con un metodo dedicato dello Spreadsheet.

timeZone: getSpreadsheetTimeZone()

Applicando il metodo in questione all’istanza del Foglio di Google interrogato per recuperare la data, verrà recuperato il timeZone effettivo dello Spreadsheet.

var ss = SpreadsheetApp.openById('SPREADSHEET_ID');
Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd');

La formattazione della data elaborerà e restituirà la data corretta.

Soluzione

Quando si lavora con le date in Google Sheets, e relative elaborazioni con Google Apps Script, è consigliato definire sempre lo stesso timeZone dello strumento dal quale si recuperano i valori, appunto lo Spreadsheet. Per farlo sarà sufficiente applicare il metodo getSpreadsheetTimeZone() all’istanza del Foglio di Google all’interno del file di script.