Come leggere i valori temporali corretti da Google Spreadsheet

Sto cercando di ottenere da una cella formattata nel tempo (hh: mm: ss) il valore dell’ora, i valori possono essere maggiori 24:00:00 per esempio 20000: 00: 00 dovrebbe dare 20000:

Tavolo:

due fogli di diffusione di Google

se leggi il valore di E1:

var total = sheet.getRange("E1").getValue(); Logger.log(total); 

Il risultato è:

Sab Apr 12 07:09:21 GMT + 00: 09 1902

Ora ho provato a convertirlo in un object Date e ottenere il timestamp Unix di esso:

  var date = new Date(total); var milsec = date.getTime(); Logger.log(Utilities.formatString("%11.6f",milsec)); var hours = milsec / 1000 / 60 / 60; Logger.log(hours) 

1374127872020,000 mila

381.702,1866,722222 millions

La domanda è come ottenere il valore corretto di 20000?

Per i futuri lettori. Ci sono due nuove funzioni getDisplayValue() e getDisplayValues() fornite da persone Script di app che restituiscono il valore di visualizzazione. Controlla la documentazione qui

Espandendo ciò che Serge ha fatto, ho scritto alcune funzioni che dovrebbero essere un po ‘più facili da leggere e tenere conto delle differenze di fuso orario tra il foglio di calcolo e lo script.

 function getValueAsSeconds(range) { var value = range.getValue(); // Get the date value in the spreadsheet's timezone. var spreadsheetTimezone = range.getSheet().getParent().getSpreadsheetTimeZone(); var dateString = Utilities.formatDate(value, spreadsheetTimezone, 'EEE, d MMM yyyy HH:mm:ss'); var date = new Date(dateString); // Initialize the date of the epoch. var epoch = new Date('Dec 30, 1899 00:00:00'); // Calculate the number of milliseconds between the epoch and the value. var diff = date.getTime() - epoch.getTime(); // Convert the milliseconds to seconds and return. return Math.round(diff / 1000); } function getValueAsMinutes(range) { return getValueAsSeconds(range) / 60; } function getValueAsHours(range) { return getValueAsMinutes(range) / 60; } 

Puoi usare queste funzioni in questo modo:

 var range = SpreadsheetApp.getActiveSheet().getRange('A1'); Logger.log(getValueAsHours(range)); 

Inutile dire che questo è un sacco di lavoro per ottenere il numero di ore da un intervallo. Per favore, vedi il numero 402 che è una richiesta di funzionalità per avere la possibilità di ottenere il valore di stringa letterale da una cella.

Il valore che vedi (Sab Apr 12 07:09:21 GMT + 00: 09 1902) è la data equivalente in tempo standard JavaScript che è 20000 ore dopo la data di riferimento.

dovresti semplicemente rimuovere il valore di riferimento del foglio di calcolo dal risultato per ottenere quello che desideri.

Questo codice fa il trucco:

 function getHours(){ var sh = SpreadsheetApp.getActiveSpreadsheet(); var cellValue = sh.getRange('E1').getValue(); var eqDate = new Date(cellValue);// this is the date object corresponding to your cell value in JS standard Logger.log('Cell Date in JS format '+eqDate) Logger.log('ref date in JS '+new Date(0,0,0,0,0,0)); var testOnZero = eqDate.getTime();Logger.log('Use this with a cell value = 0 to check the value to use in the next line of code '+testOnZero); var hours = (eqDate.getTime()+ 2.2091616E12 )/3600000 ; // getTime retrieves the value in milliseconds, 2.2091616E12 is the difference between javascript ref and spreadsheet ref. Logger.log('Value in hours with offset correction : '+hours); // show result in hours (obtained by dividing by 3600000) } 

inserisci la descrizione dell'immagine qui

nota: questo codice ottiene solo ore, se hai minuti e / o secondi, allora dovrebbe essere sviluppato per gestire anche questo … facci sapere se ne hai bisogno.


EDIT: una parola di spiegazione …

I fogli di lavoro utilizzano una data di riferimento del 30/12/1899 mentre Javascript utilizza il 01/01/1970, il che significa che c’è una differenza di 25568 giorni tra i due riferimenti. Tutto ciò presupponendo di utilizzare lo stesso fuso orario in entrambi i sistemi. Quando convertiamo un valore di data in un foglio di calcolo in un object data javascript, il motore GAS aggiunge automaticamente la differenza per mantenere la coerenza tra le date.

In questo caso non vogliamo conoscere la data reale di qualcosa, ma piuttosto un valore di ore assolute, cioè una “durata”, quindi dobbiamo rimuovere lo sfasamento di 25568 giorni. Questo viene fatto usando il metodo getTime() che restituisce i millisecondi contati dalla data di riferimento JS, l’unica cosa che dobbiamo sapere è il valore in millisecondi della data di riferimento del foglio elettronico e sottrarre questo valore dall’object data reale. Quindi un po ‘di matematica per ottenere ore invece di millisecondi e abbiamo finito.

So che questo sembra un po ‘complicato e non sono sicuro che il mio tentativo di spiegare chiarirà davvero la domanda, ma vale sempre la pena provare, no?

Ad ogni modo il risultato è quello di cui avevamo bisogno fino a quando (come indicato nei commenti) si regola il valore di offset in base alle impostazioni del fuso orario del foglio di calcolo. Ovviamente sarebbe ansible lasciare che lo script gestisse automaticamente, ma avrebbe reso lo script più complesso, non sicuro che fosse davvero necessario.

Per fogli di calcolo semplici potresti essere in grado di modificare il fuso orario del foglio di lavoro in GMT senza l’ora legale e utilizzare questa funzione di conversione breve:

 function durationToSeconds(value) { var timezoneName = SpreadsheetApp.getActive().getSpreadsheetTimeZone(); if (timezoneName != "Etc/GMT") { throw new Error("Timezone must be GMT to handle time durations, found " + timezoneName); } return (Number(value) + 2209161600000) / 1000; } 

La risposta di Eric Koleda è per molti versi più generale. Ho scritto questo mentre cercavo di capire come gestisce i casi d’angolo con il fuso orario del foglio di calcolo, il fuso orario del browser e le variazioni del fuso orario nel 1900 in Alaska e Stouchlma.