Passing custom Spreadsheets column to onFormSubmit trigger

Question

I have a Google Form with two numbers field, so in the Answer destination spreadsheets I have three columns: SUBMIT-DATE, NUM1, NUM2

I add a fourth column TOT, not field in the form, with this formula:

ARRAYFORMULA(B2:B+C2:C)

So in the fourth column i have a SUM for every row

I bound this script to spreadsheet:

function onFormSubmit(e){
  var subject = "TRY GAS";
  var body = e.namedValues['NUM1']+ "+" +e.namedValues['NUM2']+ "=" +e.namedValues[TOT];
  MailApp.sendEmail("[email protected]", subject, body);
}

And I set trigger for onFormSubmit

But the email i received is:

34+43=

While in the spreadsheet under column TOTAL, 77 is displayed.

I think the problem is var e.namedValues that doesn't contain TOT because it isn't a form field

How can I pass this value on form submit??

Answer 1

When you use e.namedValues['NUM1'], you are getting the "namedValues" property of the e object which is actually the response coming from the form.

I' m quite surprised by the result you get since nor property in this object is called "TOT"...

normally it should return "undefined" obviously.

Nevertheless, this is not you actual question, what you want is simply the numeric sum of both values, you can get them like this :

var sum = Number(e.namedValues['NUM1'])+Number(e.namedValues['NUM2']);// Number is mandatory because object properties are strings in this context.

and your email body would become

var body = e.namedValues['NUM1']+ "+" +e.namedValues['NUM2']+ "=" +sum;

Answer 2

Your guess is right; since the column you're interested in is outside the range of columns that are filled with form responses, it won't be in the event object. However, the range property of the event object will tell you the row that received the submission, and you can select additional cells relative to that.

Which column contains "TOT"? If that column is just to the right of our last form response column, then we can use the number of response items (e.values.length) to offset to that column.

function onFormSubmit(e){
  var subject = "TRY GAS";
  var total = e.range.offset(0, e.values.length, 1, 1).getValue();
  var body = e.namedValues['NUM1']+ "+" +e.namedValues['NUM2']+ "=" +total;
  MailApp.sendEmail("[email protected]", subject, body);
}

Caveat: At the time that this trigger function runs, the total may not have been calculated and stored back to the spreadsheet yet. You may find that you need to delay the getValue() call by a few seconds. Even with that, it may be unreliable - in which case you'll find that the calculate-in-the-function approach suggested by @Sergeinsas will be more effective.

Answer 3

I just found the same solution of @Mogsdad, so i write this:

function onFormSubmit(e){
  //THE SHEETS WHERE RESPONSE IS STORED
  var sheet = e.range.getSheet();

  //SHEET HEADERS
  var headers = sheet.getRange("1:1").getValues()[0];

  //THE DIFFERENCE BETWEEN SHEET WIDTH AND FORM COLUMNS
  var off = headers.length;

  //WAIT DATA
  Utilities.sleep(30000);

  //OFFSET FORM RANGE ON RIGHT BY off TO ADD ALL CUSTOM COLUMN
  var data = e.range.offset(0, 0, 1, off).getValues()[0];

  //CREATE OBJECT WITH NAMED VALUES
  var namedValues = {};
  for(var col in headers)
      namedValues[headers[col]] = data[col]; 

  //LOG
  var subject = "TRY GAS";
  var body = namedValues["NUM1"] + ' + ' + namedValues["NUM2"] + ' = ' +    namedValues["TOT"];
  MailApp.sendEmail("[email protected]", subject, body);
}

Thanks all, I need this to create PDF from template using replaceText()