CODEWITHSUNDEEP
google-apps-script
D3FTY
D3FTY

Reputation: 135

Saving data from Google Spreadsheet into an Array

for(var i=0; i<sheet.getMaxColumns(); i++){
   var params[i] = e.values[i]; 
  }

console.log(params);

for(var z=0; z<paramsInfo.length; z++){
    var paramsInfoCheck = paramsInfo[z];


    var template = HtmlService.createTemplateFromFile(paramsInfoCheck.name);
    template[paramsInfoCheck.name] = params[paramsInfoCheck.number];
    template.recorded = params[51];
    template.remarks = params[52];

    if((params[paramsInfoCheck.number] < paramsInfoCheck.min) || (params[paramsInfoCheck.number] > paramsInfoCheck.max)){

      MailApp.sendEmail(recipients,
    "Parameter Out of Range Notification",
     "",{htmlBody: template.evaluate().getContent() + spreadsheetlink});
     }
    }

enter image description here

As you can see on the picture, it ran 2 console.log when I only have 1 console.log, the first log captures those values while the second one doesn't.

Currently I have a form that I use to collect some data. It has over 50+ columns. I have another for loop and an if loop that I use to send an email if some of the values that are keyed in is not within a range. I have using a onFormSubmit event trigger.

When I console.log my array of values. Some values are null however in the spreadsheet there are values at the specific column number. I'm assuming due to the large data, the program does not have enough time to process it to put in the array and run the MailApp function. Have this ever happen to anyone?

My execution logs shows completed without errors but some values are missing or rather null

Upvotes: 0

Views: 77

Answers (1)

Diego
Diego

Reputation: 9571

Because you wrote that this is executing twice, and the second time with empty data, this sounds like an issue where the onFormSubmit() trigger is running twice. This seems to be an Apps Script bug, but Stack Overflow users J.G. and Cooper provided a useful workaround: check the value of a required question. So simply include if (e.values && !e.values[1]) { return; } at the very beginning and it should prevent extra executions.

function onFormSubmit(e) {

  // Reject spurious triggers by checking the value of a required question.
  // In this case, we assume  e.value[1] is required.
  if (e.values && !e.values[1]) { return; }

  var sheet = e.range.getSheet(); // Assuming this is how you defined sheet

  // This is your original code
  for (var i = 0; i < sheet.getMaxColumns(); i++) {
    var params[i] = e.values[i]; 
  }

  console.log(params);

  for (var z = 0; z < paramsInfo.length; z++) {
    var paramsInfoCheck = paramsInfo[z];
    var template = HtmlService.createTemplateFromFile(paramsInfoCheck.name);
    template[paramsInfoCheck.name] = params[paramsInfoCheck.number];
    template.recorded = params[51];
    template.remarks = params[52];

    if ((params[paramsInfoCheck.number] < paramsInfoCheck.min) || (params[paramsInfoCheck.number] > paramsInfoCheck.max)) {
      MailApp.sendEmail(recipients, "Parameter Out of Range Notification", "",{htmlBody: template.evaluate().getContent() + spreadsheetlink});
    }
  }
}

Upvotes: 2

Related Questions