Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...