I'm using Google Apps Script batchupdate method to update cell values. My 2d array has a column of date values formatted as strings so they look like:
["4 October 2025", "6 January 2025"]
I'm setting the request as follows:
{
updateCells: {
range:
{
sheetId: sheetID
startRowIndex: startRow - 1,
endRowIndex: startRow + array.length,
startColumnIndex: startColumn - 1,
endColumnIndex: startColumn - 1 + array[0].length
},
rows: _get_batch_update_type(array),
fields: "userEnteredValue"
}
}
function _get_batch_update_type(input) {
//Dealing with array
if (input.constructor === Array) {
return input.map(row => ({
values: row.map(element => ({
userEnteredValue: (typeof element === "string" || Number.isNaN(element) ? { stringValue: element } : { numberValue: element })
}))
}));
}
return [{ values: [{
userEnteredValue: (typeof input === "string" || Number.isNaN(input) ? { stringValue: input } : { numberValue: input })
}]}];
}
When I run the request, the values are set to my sheet with a leading apostrophe
+----------------+
| A |
+----------------+
|'4 October 2025 |
+----------------+
|'6 January 2025 |
+----+-----------+
The column in the sheet is formatted as a date. How can I modify the request so that apostrophe is not pasted as part of the output?
EDIT:
Based upon a suggestion below, I've modified my _get_batch_update_type()
function to:
function _get_batch_update_type(input) {
//Dealing with array
if (input.constructor === Array) {
return input.map(row => ({
values: row.map(element => ({
userEnteredValue: (typeof element === "string" || Number.isNaN(element) ? { stringValue: element } :
element instanceof Date ? {formulaValue: `=--"${_convert_date_to_string(element)}"`} : { numberValue: element })
}))
}));
}
return [{ values: [{
userEnteredValue: (typeof input === "string" || Number.isNaN(input) ? { stringValue: input } :
input instanceof Date ? {formulaValue: `=--"${_convert_date_to_string(input)}"`} : { numberValue: input })
}]}];
}
which fixes the leading apostrophe and enables the date to be parsed as a date object.
If all of the input is going to be parseable dates, then try formulaValue
type, and forcing it to parse dates as serial numbers using doubleunary:
userEnteredValue: { formulaValue: `=--"${element}"` }
If you use stringValue
, dates or even numbers won't be parsed as numbers, as written in the docs:
Represents a string value. Leading single quotes are not included. For example, if the user typed '123 into the UI, this would be represented as a stringValue of "123".
A better option would be to not use updateCells
request, but use spreadsheets.values.batchUpdate
with valueInputOption
set to USER_ENTERED