javascriptgoogle-apps-scriptgoogle-sheetsutilities

How to compare timeStamp whe i can't seem to bring them to a common format using GAS?


So, on form submit, I got a timestamp, which looks like this: "values":["2/18/2022 14:11:25"]

I then need to compare it with the one on the spreadsheet, so that I can set a number to an adjacent column. Then, I'm using the code below, but I'm facing an error on ```Utilities.formatDate()````

The code:

function onSubmit(e) {
  Logger.log("%s", JSON.stringify(e));
  const timeStamp = e.values[0]
  const formRespSheet = e.source.getSheetByName('Form Responses 1')
  var maxNumber = Math.max.apply(null, formRespSheet.getRange(2, 14, formRespSheet.getLastRow(), 1).getValues());
  maxNumber = maxNumber + 1

  Utilities.sleep(1000);//Tried it
  const allTimeStamps = formRespSheet.getRange(2, 1, formRespSheet.getLastRow(), 1).getValues();

  for (let a = 0; a < allTimeStamps.length; a++) {
    let sheetTimeStamp = allTimeStamps[a]
    sheetTimeStamp = Utilities.formatDate(sheetTimeStamp, Session.getTimeZone(), "MM/dd/yyyy HH:mm:ss")
    if (sheetTimeStamp.valueOf() == timeStamp.valueOf()) {
      const row = a + 1
      formRespSheet.getRange(row, 14).setValue(maxNumber)
    }
  }
}

The error says:

The parameters (number[],String,String) don't match the method signature for Utilities.formatDate.

Thanks for your help.


Solution

  • sheetTimeStamp = Utilities.formatDate(sheetTimeStamp, Session.getTimeZone(), "MM/dd/yyyy HH:mm:ss")
    

    change into:

    sheetTimeStamp = Utilities.formatDate(new Date(sheetTimeStamp), Session.getTimeZone(), "MM/dd/yyyy HH:mm:ss")
    

    it should work