javascript - Trigger sometimes does not work, I do not understand why

Question

I need your help.

I have an active google form where customers register. When I submit the form I have 3 activators that run me scripts, especially they convert the ITALIAN date format (dd-mm-yyyy) to USA (yyyy-mm-dd).

Start of action: Upon submitting the form

The problem I find is this. These scripts do not always work even if from the control panel I find that it has been executed correctly without reporting errors.

A code example:

function respondToFormSubmit() {
  var ss = SpreadsheetApp.openById("xxxxxxxxxxxxxxxxxxxxxxxx");
  var sheet = ss.getSheets()[0];


  // Format column I
  var column1 = sheet.getRange("F:F");
  var column2 = sheet.getRange("J:J");
  var column3 = sheet.getRange("K:K");
  var column4 = sheet.getRange("A:A");


  // Set new date format on column I
  column1.setNumberFormat('yyyy-mm-dd');
  column2.setNumberFormat('yyyy-mm-dd');
  column3.setNumberFormat('yyyy-mm-dd');
  column4.setNumberFormat('yyyy-mm-dd');

};

When the problem occurs, all 3 triggers fail. The non-functioning occurs 20/30% of the time and this discontinuous "error" does not make me understand what the problem is.

Do you have any suggestions for me? Thank you so much for your invaluable help. Mauro

Answer 1

The problem with your script running on trigger are likely propagation issues

It takes some time for a new form response to be inserted into the spreadsheet, so your number formatting functionality might be run before the new row gets inserted.

One thing you can do is implement some waiting time at the beginning at the function, e.g. with sleep().

However, if you bind your script to the destination spreadsheet instead of the form itself - you will be able to use the Google Sheets event objects for Form submit which include range.

Sample usage:

function respondToFormSubmit(e) {   
 var row = e.range.getRow();   
 var sheet = e.range.getSheet();  
 sheet.getRange("F" + row).setNumberFormat('yyyy-mm-dd');  
 sheet.getRange("J" + row).setNumberFormat('yyyy-mm-dd');  
 sheet.getRange("K" + row).setNumberFormat('yyyy-mm-dd');  
 sheet.getRange("A" + row).setNumberFormat('yyyy-mm-dd'); 
};