Saving data from Google Spreadsheet into an Array

Question

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

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.

IGNORE THE NULL AFTER 'Test' - As those values are not meant to be captured, hence its null since there are nothing on the form.

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

Your question is very difficult to understand and there's no `console.log()` in your code snippet, so it seems the most important part is missing. Please review and clarify. — Diego, Nov 11 '19 at 05:49
Sorry I have missed that ``console.log()`` out. I have edited the code. Basically, I only have 1 ``console.log``, however on my execution log, the first execution captures all my values in, but on the second execution, it shows as blanks. As mentioned, after the 'Test' ignore the nulls as they are additional columns I added manually for my own reference. @Diego — D3FTY, Nov 11 '19 at 07:28

score 2 · Accepted Answer · answered Nov 11 '19 at 07:53

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

Does the required question have to be a legit required question in the form? Or can we just assume any question as reqiured? @Diego — D3FTY, Nov 11 '19 at 09:12
@D3FTY It should be a legit required question, but that doesn't mean the _first_ question should be required (as in my answer). If the question in the form isn't required and someone submits without answering that question, then your trigger would skip over that response. — Diego, Nov 11 '19 at 09:19
Got it on that. I'll try this out and get the required question proper and update here. Thanks! — D3FTY, Nov 11 '19 at 09:31

Saving data from Google Spreadsheet into an Array

1 Answers1