Search code examples
javascriptgoogle-sheetsgoogle-sheets-api

What is the proper syntax for formatting date on spreadsheets?

I have been reading about sheet formats. I am using fetch and tokens to write data.

rows: [{
  values: [
  {
    userEnteredValue: { numberValue: Math.round(new Date().getTime() / 1000) },
    userEnteredFormat: {
      numberFormat: {
        type: 'DATE', pattern: 'ddd dd hh:mm'
      }
    }
  }],
  fields: 'userEnteredValue, userEnteredFormat'
}]

After posting the data when I click on the cell a calender shows but the time shown in not in the correct format its in epoc

Solution

  • In this case, it is required to convert from the Unix time to the serial number. And, I think that type might be type: "DATE_TIME". When these points are reflected in your showing script, how about the following modification?

    Modified script:

    var unixTime = new Date().getTime();
var serialNumber = (unixTime / 1000 / 86400) + 25569; // Ref: https://stackoverflow.com/a/6154953
var requests = {
  requests: [{
    updateCells: {
      rows: [{ values: [{ userEnteredValue: { numberValue: serialNumber }, userEnteredFormat: { numberFormat: { type: "DATE_TIME", pattern: "ddd dd hh:mm" } } }] }],
      range: { sheetId: 0 },
      fields: "userEnteredValue, userEnteredFormat",
    }
  }]
};
    • In this cacse, the value is put into the cell "A1" of the sheet ID 0.

    References: