How can I prevent a leading apostrophe when inserting date strings using Google Apps Script BatchUpdate?

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:

JAVASCRIPTCopied
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

I use a function like this when setting rows:

JAVASCRIPTCopied
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;
};

Please you can use this code

JAVASCRIPTCopied
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

JAVASCRIPTCopied
  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.

JAVASCRIPTCopied
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:

JAVASCRIPTCopied
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);
}