Google Sheets Date Conversion Issue

Other Questions Google docs
1

Three spreadsheet scripts trigger when an ‘x’ is entered in column H: they initially insert dates, but later numbers replace the dates. Why?

function handleUpdateOne(eventObj) {
  let targetSheet = eventObj.source.getSheetByName('Orders');
  let cellEdited = eventObj.range;
  if (targetSheet.getName() === 'Orders' && cellEdited.getColumn() === 6 && cellEdited.getValue() !== '') {
    targetSheet.getRange(cellEdited.getRow() + 1, 1, 1, 5).setBackground('#ffeeba');
  }
}

function handleUpdateTwo(eventObj) {
  let activeSheet = eventObj.source.getSheetByName('Orders');
  let changedCell = eventObj.range;
  if (activeSheet.getName() === 'Orders' && changedCell.getColumn() === 3 && changedCell.getValue() !== '') {
    activeSheet.insertRowAfter(changedCell.getRow());
    activeSheet.getRange(changedCell.getRow() + 1, 1, 1, 7).setBackground('#d1ecf1');
  }
}

function handleDateEntry(eventObj) {
  let currentSheet = eventObj.source.getActiveSheet();
  let activeCell = eventObj.range;
  if (activeCell.getColumn() === 8 && activeCell.getValue() === 'x') {
    activeCell.setNumberFormat('MM/dd/yyyy');
    activeCell.setValue(Utilities.formatDate(new Date(), 'GMT', 'yyyy/MM/dd'));
  }
}
2

I encountered a similar issue where overlapping scripts resulted in unexpected date conversion behavior. In my case, the problem was due to the order in which the scripts executed. The date was set correctly, but another process later updated the cell, causing the format to revert to a numeric representation. I resolved this by ensuring the date setting action was the final update on the cell and by verifying no other triggers were modifying the cell afterwards. Adjusting the execution sequence helped maintain the proper format throughout.

3

i had a simlar prob, check if other triggers are messing with that cell. i solved mine by adding a delay and reordering trigger fire. could be that a second script is overwriting your date. cheers

4

In my experience, these kinds of issues usually stem from the device timing of script execution and the way Google Sheets handles value and format changes. It appears that even though the cell is being formatted correctly at first, a subsequent update (likely by another script or even the same script on a second trigger) is resetting the date formatting. This could be due to the cell’s value being overwritten after the date is set. Combining the script operations or carefully controlling their order can help prevent the date from being replaced by a simple number, as I’ve encountered in similar scenarios.