Why is Google Sheets or Google App Script not providing me with the same time values as what is displayed?

1
votes

Context

I have an RESTful API built with Google App Script on a Google Sheet. One of the doGet() requests will retrieve data from one of the sheet tabs stored in a traditional column/row (database-like) fashion (structure described later). Simply, the doGet() will evoke another function to get the data, then it will encode that data into JSON response for the client.

The Data in the Sheet

This is what the data looks like on the sheet.

DATE LOCATION SHIFT NAME START END
4-1-2021 WP KANTOOR I Danny 9:00 17:00
4-1-2021 BK BAKKERIJ I Naomi 9:00 17:00
4-1-2021 CK KOK I Fidel 7:00 16:00
... ... ... ... ... ...

The Google App Script Function

The function iterates over the table above and gets the the row that matches the name passed into the function against the name in the NAME column.

function getContactSchedule(name) {
  var payload = { schedule: [] };
  var rows = scheduleSheet // schedule sheet is the .getSheetByName() for the above sheet
    .getRange(2, 1, scheduleSheet.getLastRow(), scheduleSheet.getLastColumn())
    .getValues();


  for (var i = 0, l = rows.length; i < l; i++) {
    var shift = { date: null, location: null, shift: null, name: null, start: null, end: null };
    const scheduleItem = rows[i];
    if (name != scheduleItem[3]) continue;

    shift.date = scheduleItem[0];
    shift.location = scheduleItem[1];
    shift.shift = scheduleItem[2];
    shift.name = scheduleItem[3];
    shift.start = scheduleItem[4];
    shift.end = scheduleItem[5];
    shift.index = i;

    // add to payload.
    payload.schedule.push(shift);
  }
  return payload;
}

Problem

When I get the data in the table and convert it to the Object, I see different timestamps than store (other the timezone or epoch difference).

Here is some driver code:

function testGetContactSchedule() {
  var data = getContactSchedule('Danny')['schedule'];
  console.log(data[0]);
}

Output:

{ date: Sun Jan 03 2021 16:00:00 GMT-0700 (Mountain Standard Time),
  location: 'WP',
  shift: 'KANTOOR I',
  name: 'Danny',
  start: Sat Dec 30 1899 01:40:28 GMT-0700 (Mountain Standard Time),
  end: Sat Dec 30 1899 09:40:28 GMT-0700 (Mountain Standard Time),
  canUpdate: true,
  index: 0 }

The first row of the table is the same data here, just converted into the JS Object. But the time difference is unusual.

DATE LOCATION SHIFT NAME START END
4-1-2021 WP KANTOOR I Danny 9:00 17:00

Question

Why is there this unusual difference in time? As if it adds about 40 minutes to the time stamp rather than the 00:00 that I think I'm expecting?

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

The reason you are receiving the date Sat Dec 30 1899 01:40:28 GMT-0700 is because getValues returns an object of type date. However, this object is adapted from Sheets - 12/30/1899 0:00:00 representing the zero mark of time for it.

Therefore, a solution for this is to use getDisplayValues instead of getValues. This will end up returning the actual values displayed in Sheets.

Reference

1
votes

dates are always a bit complex in JS.

You can try this script, it worked for me. You can change the format of the date and also the time zone.

const sheet = SpreadsheetApp.openById("ZZZZZZZ");
const data = sheet.getSheetByName("XXX").getDataRange().getValues();
function getData(){
  data.forEach(dates => {
    var date = Utilities.formatDate(new Date(dates), "GMT+2", "MM/dd/yyyy");
    console.log(date);
  })
}

Sheet:

input

Output:

output