Google Sheet API V4 - Google Apps script - Sheets.Spreadsheets.Values.update - append Date

Question

I'm using Sheets.Spreadsheets.Values.update to paste values into a sheet (due to performance issues using range.setValues().

In the case of dates, the source values are date objects (which were pasted fine when I used range.setValues([[]]).

The end result now is though cells with the string version of the date, e.g., Mon Feb 28 00:00:00 GMT+01:00 2022 which do not correspond to a valid gsheet date. I've tried some of the options but cannot find a way for this to work.

Answer 1

I believe your goal as follows.

• You want to put the values to Google Spreadsheet using Sheets API with Google Apps Script.
• In your current issue, the values include the date object. When this date object is put using spreadsheets.values.update method, the value cannot be used as the date object. You want to resolve this issue.

In order to achieve your goal, how about putting the values by converting from the date object to others?

Pattern 1:

In this pattern, the date object is converted to the serial number and put to the Spreadsheet.

const spreadsheetId = "###";  // Please set the Spreadsheet ID.
const values = [[new Date(), "b1", "c1"], [new Date(), "b2", "c2"]]; // This is a sample value for replicating your issue.

// Here, the date values are converted to the serial number.
const convertedValues = values.map(r => r.map(c => c instanceof Date ? (c.getTime() / 1000 / 86400) + 25569 : c));

Sheets.Spreadsheets.Values.update({values: convertedValues}, spreadsheetId, "Sheet1", {valueInputOption: "USER_ENTERED"});

• (c.getTime() / 1000 / 86400) + 25569 for converting from the unix time to the serial number was referred from this answer.
• In this script, the date object is put as the serial number. So, please set the number format of the column "A" as the date time. By this, the serial number can be seen as the date time.

Pattern 2:

In this pattern, the date object is converted to the string value for parsing as the date object and put to the Spreadsheet.

const spreadsheetId = "###";  // Please set the Spreadsheet ID.
const values = [[new Date(), "b1", "c1"], [new Date(), "b2", "c2"]]; // This is a sample value for replicating your issue.

// Here, the date values are converted to the string value for parsing as the date object.
const convertedValues = values.map(r => r.map(c => c instanceof Date ? Utilities.formatDate(c, Session.getScriptTimeZone(), "yyyy/MM/dd HH:mm:ss") : c));

Sheets.Spreadsheets.Values.update({values: convertedValues}, spreadsheetId, "Sheet1", {valueInputOption: "USER_ENTERED"});

• In this script, the date object is put as the string value for parsing as the date object by USER_ENTERED.
• This sample uses yyyy/MM/dd HH:mm:ss as the date format. When this format cannot be parsed, please modify this for your situation.

References:

• Method: spreadsheets.values.update
• formatDate(date, timeZone, format)