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.

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.

• javascript
• google-sheets
• google-apps-script

• Those strings are in a format that would get automatically coerced to a numeric date when put in a cell. A string is different from a date, so to retain the data as string, Google Sheets adds a ' prefix. The prefix will not affect downstream references to the strings — you can verify that with e.g. len(). If you want to get numeric dates, use numeric dates in the original data. – doubleunary Commented Feb 2 at 9:06
• Please flow below answer – Md.Jewel Mia Commented Feb 2 at 9:43
• @doubleunary I'm not sure your approach works. For example, If I leave the date column above in numeric date format I get the following error: Invalid value at 'requests[21].update_cells.rows[1].values[2].user_entered_value.number_value' (TYPE_DOUBLE), Tue Jan 28 00:00:00 GMT-06:00 2025 What can I add to the request body to address this error? – J_code Commented Feb 2 at 18:35

5 Answers 5

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

Please you can use this code

 function _get_batch_update_type(input) {
  if (Array.isArray(input)) {
    return input.map(row => ({
      values: row.map(element => ({
        userEnteredValue: convertToSheetValue(element)
      }))
    }));
  }

  return [{
    values: [{
      userEnteredValue: convertToSheetValue(input)
    }]
  }];
}

function convertToSheetValue(value) {
  if (typeof value === "string") {
    let parsedDate = new Date(value);
    if (!isNaN(parsedDate.getTime())) {
      return { numberValue: parsedDate.getTime() / 86400000 + 25569 };
    }
    return { stringValue: value }; // If not a valid date, keep as string
  }
  
  return { numberValue: value };
}

PLease Flow this

  function _get_batch_update_type(input) {
  if (Array.isArray(input)) {
    return input.map(row => ({
      values: row.map(element => ({
        userEnteredValue: convertToSheetValue(element)
      }))
    }));
  }

  return [{
    values: [{
      userEnteredValue: convertToSheetValue(input)
    }]
  }];
}

function convertToSheetValue(value) {
  if (typeof value === "string") {
    let dateSerial = parseDateToSerial(value);
    if (dateSerial !== null) {
      return { numberValue: dateSerial };
    }
    return { stringValue: value }; // Keep as string if not a valid date
  }
  
  return { numberValue: value };
}

// Convert a date string (e.g., "4 October 2025") into a Google Sheets serial number
function parseDateToSerial(dateString) {
  let parts = dateString.match(/^(\d{1,2}) (\w+) (\d{4})$/);
  if (!parts) return null; // Return null if format is invalid

  let day = parseInt(parts[1], 10);
  let month = getMonthIndex(parts[2]);
  let year = parseInt(parts[3], 10);

  if (month === -1) return null; // Invalid month name

  // Calculate Google Sheets serial number (ignoring timezone issues)
  let date = new Date(year, month, day);
  return Math.floor(date.getTime() / 86400000) + 25569;
}

// Map month names to their index (0 = Jan, 11 = Dec)
function getMonthIndex(monthName) {
  const months = [
    "january", "february", "march", "april", "may", "june",
    "july", "august", "september", "october", "november", "december"
  ];
  return months.indexOf(monthName.toLowerCase());
}

**Manually extracts the date components (day, month, year) to avoid timezone shifts.

✅ Calculates the Google Sheets serial number manually using Math.floor(date.getTime() / 86400000) + 25569.

✅ Ensures date-only values (no hidden time component).

✅ Works regardless of the spreadsheet's timezone settings.**

function convtodates() {
  const mA = ["January","February","March","April","May","June","July","August","September","October","November","December"]
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getDataRange().getDisplayValues();
  const dts = vs.map(e => {
    let t = e.toString().split(" ");
    let d = Number(t[0].trim());
    let m = mA.indexOf(t[1].trim());
    let y = Number(t[2].trim());
    return [new Date(y,m,d)]
  })
  Logger.log(JSON.stringify(dts).replace(/],/,'],\n'))
}

Execution log
10:44:34 AM Notice  Execution started
10:44:32 AM Info    [["2025-10-04T06:00:00.000Z"],
["2025-01-06T07:00:00.000Z"]]
10:44:36 AM Notice  Execution completed

Try it in your code this way:

function convtodates(datestring) {
  const mA = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]
  let t = datestring.split(" ");
  let d = Number(t[0].trim());
  let m = mA.indexOf(t[1].trim());
  let y = Number(t[2].trim());
  return new Date(y, m, d);
}

I use a function like this when setting rows:

const valuesToRowData = (values) => {
    return values.map((row) => {
      return {
        values: row.map((cell) => {
          const value = {};

          if (cell === null || cell === undefined) {
            value.stringValue = "";
          } else if (cell instanceof Date) {
            value.numberValue = dateToSerial_(cell);
          } else if (typeof cell === "string" && cell.startsWith("=")) {
            value.formulaValue = cell;
          } else if (typeof cell === "boolean") {
            value.boolValue = cell;
          } else if (typeof cell === "number") {
            value.numberValue = cell;
          } else if (typeof cell === "object") {
            // Handle JSON objects by stringify them
            value.stringValue = JSON.stringify(cell);
          } else if (typeof cell === "string") {
            value.stringValue = cell;
          } else {
            value.stringValue = String(cell);
          }

          return {
            userEnteredValue: value,
          };
        }),
      };
    });
  };

const dateToSerial_ = (date) => {
  if (!(date instanceof Date)) {
    date = new Date(date);
  }
  const timestamp = date.getTime();
  let serial =
    timestamp / MILLISECONDS_IN_A_DAY + DAYS_OFFSET_BETWEEN_SERIAL_AND_UNIX;
  // Subtract ET offset to align with Eastern Time
  serial -= ET_OFFSET_HOURS / HOURS_IN_DAY;
  return serial;
};