Como ler os valores de hora corretos da planilha do Google

Eu estou tentando obter a partir de uma hora formatada Cell (hh: mm: ss) o valor da hora, os valores podem ser maiores 24:00:00 por exemplo 20000: 00: 00 deve dar 20000:

Mesa:

duas planilhas do Google Spread

se você leu o valor de E1:

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

O resultado é:

Sábado 12 de abril, às 07:09:21 GMT + 00: 09 1902

Agora eu tentei convertê-lo em um object Date e obter o carimbo de tempo Unix dele:

  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.000000

381702.1866722222

A questão é como obter o valor correto de 20000?

Para futuros leitores. Há duas novas funções getDisplayValue() e getDisplayValues() fornecidas pelo pessoal do App Script que retorna o valor de exibição. Confira a documentação aqui

Expandindo o que Serge fez, escrevi algumas funções que devem ser mais fáceis de ler e levar em conta as diferenças de fuso horário entre a planilha e o 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; } 

Você pode usar essas funções da seguinte forma:

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

Escusado será dizer que isto é muito trabalho para obter o número de horas de um intervalo. Por favor, marque a edição 402, que é uma solicitação de recurso para obter a capacidade de obter o valor da string literal de uma célula.

O valor que você vê (Sábado, 12 de abril, 07:09:21 GMT + 00: 09, 1902) é a data equivalente no tempo padrão de Javascript que é 20000 horas depois da data de referência.

você deve simplesmente remover o valor de referência da planilha do seu resultado para obter o que deseja.

Este código faz o truque:

 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) } 

insira a descrição da imagem aqui

nota: este código só recebe horas, se você tiver minutos e / ou segundos, então deve ser desenvolvido para lidar com isso também … avise-nos se precisar.


EDIT: uma palavra de explicação …

As planilhas usam uma data de referência de 12/30/1899 enquanto o Javascript está usando 01/01/1970, o que significa que há uma diferença de 25568 dias entre as duas referências. Tudo isso assumindo que usamos o mesmo fuso horário em ambos os sistemas. Quando convertemos um valor de data em uma planilha para um object de data javascript, o mecanismo GAS adiciona automaticamente a diferença para manter a consistência entre as datas.

Neste caso, não queremos saber a data real de algo, mas sim um valor absoluto de horas, ou seja, uma “duração”, portanto, precisamos remover o deslocamento de 25568 dias. Isso é feito usando o método getTime() que retorna milissegundos contados a partir da data de referência JS, a única coisa que precisamos saber é o valor em milissegundos da data de referência da planilha e subtrair esse valor do object de data real. Então, um pouco de matemática para obter horas em vez de milissegundos e estamos a fazer.

Eu sei que isso parece um pouco complicado e eu não tenho certeza se minha tentativa de explicar realmente esclarecerá a questão, mas vale sempre a pena tentar, não é?

De qualquer forma, o resultado é o que precisávamos, desde que (conforme indicado nos comentários) se ajustasse o valor do offset de acordo com as configurações de fuso horário da planilha. É claro que seria possível deixar o script lidar com isso automaticamente, mas isso tornaria o script mais complexo, não tendo certeza se é realmente necessário.

Para planilhas simples, você pode alterar o fuso horário da planilha para GMT sem o horário de verão e usar essa function de conversão curta:

 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; } 

A resposta de Eric Koleda é, em muitos aspectos, mais geral. Escrevi isso ao tentar entender como ele lida com os casos de canto com o fuso horário da planilha, o fuso horário do navegador e as alterações de fuso horário em 1900 no Alasca e em Estocolmo.