Send email on Google Form submission after cell formulas have calculated

Question

I'm working on a script that sends an email when a Google Form is submitted. The form includes checkboxes that correspond with equipment rentals. The data is taken from the form and sent to the Google Spreadsheet, and the spreadsheet calculates a rental price based on the equipment selected by the user.

Everything is working great and the email goes out with the data filled in as needed, except the total price is always empty.

The spreadsheet is doing all the heavy lifting for determining which equipment costs to add to the total, and the price cell is filled with the correct price every time- it just doesn't make it to the email.

I am familiar with JavaScript, but just learning Google Scripts, so there may be other optimization areas as well, but my question is how to I delay the sendEmail function until after the cell formula has completed?

Here is my original script:

function sendFormByEmail(e){  
  var sheet =   SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Totals for Email');
  var ss = SpreadsheetApp.getActive();  

  //Note: tried a sleep and flush here (see paragraph below)      

  var price = ss.getRange("K" + lastDataRow()).getValues();

  //Note: tried while loop here (see paragraph below)

  var email = "[email protected]";
  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];  
  var subject = "Rental Form: " + e.namedValues[headers[1]].toString() + " " + e.namedValues[headers[2]].toString();

  //Loop through the array and append values to the message.
  var message = "";
  for ( var i in headers ){
    message += e.namedValues[headers[i]].toString() + "\n";
  }  
  message += "Total: $" + price + "\n\n"; //This price is always empty in the received email (when the form is submitted). When running/debugging from the script, it has data.

  MailApp.sendEmail(email, subject, message); // Send the email
}
function lastDataRow(){
  var spr = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Totals for Email');
  var column = spr.getRange('A:A');
  var values = column.getValues();
  var ct = 0;
  while ( values[ct][0] != "" ){
    ct++;
  }

  return (ct);
}

I have tried adding a sleep Utilities.sleep(10000); and a flush SpreadsheetApp.flush(); as well as using both within a while loop to checks the price cell specifically. I know the while loop also works because it runs successfully from the script itself (just not on form submits).

count = 0;
while ( price == 0 ){ //This while loop doesn't work either!
  if ( count >= 3 ){
    price = "Unknown Price"; //Set to unknown if loop was unsuccessful
    break; //Leave the while loop after 3 attempts.
  }
  Utilities.sleep(10000); //Delay for 10 seconds @todo: did not allow the formula to run first...
  SpreadsheetApp.flush(); //Let the spreadsheet finish calculating before continuing. @todo: this didn't work either...
  price = ss.getRange("K" + lastDataRow()).getValues(); //Recapture cell data after the delay.
  count++;
}

I want to avoid doing all the calculations within the script if possible. Any ideas would be appreciated!

What kind of calculations your spreadsheet do? Does it include: 1) import functions (IMPORTRANGE, IMPORTHTML, IMPORTDATA), 2) functions that returns arrays like MMULT, SUMPRODUCT, FILTER, QUERY 3) non-deterministic functions like RAND, RANDBETWEEN, TODAY, NOW, 4) open cell / row / column references, custom functions, ...? — Rubén
The only functions the spreadsheet is running is COUNTIF and SUM. — GreatBlakes
flush() only force-writes changes that your script has made; it's doing nothing for you. — Mogsdad
Are you sure that there is a formula in column K of the row just populated by a form submission? (How did it get there?) — Mogsdad
Care to share a minimal version of the Sheet so we can see the actual formula? Make a copy, take out any sensitive data or sections that don't matter, change sharing permission to anyone can view, share the link. — Bryan P

MasterCrander MasterCrander · Accepted Answer · 2016-08-16T22:17:46

You can set an if(), else() condition that checks that there is a value in the cell that holds the final price. You may run into issues because the cell contains the formula even before there is visible data.

Your best bet is to run your function on a time-trigger and only execute the code you need if a certain cell is filled with visible data. A simple cell to check is the timestamp since that cell does not involve formulas. If the timestamp is filled, your code then does the final math instead of the sheet and uses that variable data for the email. Your code could also then write that price back to the sheet.

Alternatively, you can write two codes that both have time triggers. One of them will write a value to a cell that does not contain a formula. This could be the price or anything that the other function will then check by if(), else() and perform the bulk of your code.

Send email on Google Form submission after cell formulas have calculated

1 Answers