javascriptgoogle-sheetsgoogle-apps-script

Google Apps Script BatchUpdate appending single apostrophe to output


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.


Solution

  • 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