-1

I update a google sheet using a google form, it works fine. I want to add the date of entry automatically: I then created an "onFormSubmit" function using apps script in google sheet, and a trigger "on modification". But the date is not added : the script doesn't look called.

Any idea to debug ?

My modified code :

function onFormSubmit(e) {
  console.log("onFormSubmit_",e)
  var formSheetName = "Form responses";
  var fileName = "Blood pressure";
  var file = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = file.getSheetByName(formSheetName);
  var date = new Date();
  var formattedDate = Utilities.formatDate(date, "GMT", "dd/MM/yyyy");
  var formattedTime = Utilities.formatDate(date, "GMT", "HH:mm:ss");
  var row = [formattedDate, formattedTime]; 
  sheet.appendRow(row);
}

This code works when executed but "is not called by the trigger" when the modifications are made externally by Google Forms : if I change something directly in the sheet the code is called...

Harvey
  • 135
  • 1
  • 10
  • 1
    Please show your code. – Cooper Apr 06 '23 at 21:02
  • Have you looked at the `Executions` tab in the `Project Editor` to see when your scripts are running? Regarding `add the date of entry automatically`, when the Form updates the linked spreadsheet, the first column in the Form Responses sheet is a `Timestamp`. How is this different to what you need? – Tedinoz Apr 07 '23 at 00:28
  • Related questions: [How to debug or view logs in Google App Script fired from sheet](https://stackoverflow.com/a/74292032/1330560). [How can I test a trigger function in GAS?](https://stackoverflow.com/a/16089067/1330560) contains several suggestions. – Tedinoz Apr 07 '23 at 00:30
  • @Cooper 'function onFormSubmit(e) { var formSheetName = 'Réponses au formulaire'; var fileName = 'Tension artérielle'; var file = SpreadsheetApp.getActiveSpreadsheet(); var sheet = file.getSheetByName(formSheetName); var date = new Date(); var formattedDate = Utilities.formatDate(date, 'GMT', 'dd/MM/yyyy'); var formattedTime = Utilities.formatDate(date, 'GMT', 'HH:mm:ss'); var row = [formattedDate, formattedTime, e.values[1], e.values[2], e.values[3], e.values[4]]; sheet.appendRow(row); }' – Harvey Apr 08 '23 at 06:28
  • @Cooper I've got an error : Cannot read properties of undefined (reading '1') at onFormSubmit(Code:9:52) But most of the time no error logged – Harvey Apr 08 '23 at 06:38

1 Answers1

0

Try this:

function onYourFormSubmit(e) {
  const sh = e.range.getSheet();
  sh.getRange(e.range.rowStart,e.values.length + 1).setValue(new Date());
}
Cooper
  • 59,616
  • 6
  • 23
  • 54