My project is using Google Sheets to create a register system for childcare in a school. Because of the sensitive information within the document, I'm not at liberty to share access to the project.

'Week 1' has been decided as starting on 31/08/2020; subsequent weeks being calculated from that value. So, Week 10's starting date would be (( [10-1] * 7 ) + 31/08/2020 ). This works fine.

This is how I'm returning the cell value in my script:

var date = sheet.getRange("B2").getValue();

But when I return that cell value (in this instance, 14/09/2020) in my google script function, it is comes back like this: 'Sun Sep 13 2020 19:00:00 GMT-0400 (Eastern Daylight Time'). Why is this happening and how can it be resolved? I presume it's something to do with cell formatting - the cell is currently set to automatic, but changing it to other date formats doesn't have any effect on the outcome.

All I want my script to do is to return the value as displayed in the Google Sheet.


Short answer: To get a value as it's shown in a cell, instead of getValue() use getDisplayValue().

You should be aware that getValue() / getValues() return a JavaScript Date object for cells containing a Date, Date-Time, Time, Duration Google Sheets values.

If you are seeing something like Sun Sep 13 2020 19:00:00 GMT-0400 (Eastern Daylight Time) is because the tool that you are using is parsing the JavaScript Date object as a string for displaying purposes.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.