0

I have a Google sheet connected to a Google form that is set up to send an email to the respondent; this works fine. I have created a column that isn't from the Google Form that is populated with a unique value. "Case 002376" this is made with an array formula. How do a I obtain the last value submitted in this column AI (34) to add as the subject in an email?

=arrayformula( if( len(A2:A), "case " & text(row(A2:A) - row(A2) + 2, "000") & RIGHT(VALUE(A2:A), 3), iferror(1/0) ) )   


function onFormSubmit(e) {

SpreadsheetApp.flush(); 

var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange(sheet.getLastRow(), 1, 1, 34);
var data = dataRange.getValues();
var row = data[0];
var emailAddress = e.values[27];   // Column AB of last row
var message = "Your report has been received."; 
var subject = e.values[34];  // Column AI (data verification cell) 
 of last row

 MailApp.sendEmail(emailAddress, subject, message);
}

The email i receive says undefined. When i use e.values it works for other columns that pre exist from the Google Form, just not the one i created.

TheSeaHawk
  • 1
  • Welcome. Column AI (Col 34) isn't updated by onFormSubmit, so `e.values[34]` doesn't exist; hence it is undefined. But you do capture the value in `var=data`, so data[34][0] is probably the value you want for the subject. A little trial and error will confirm. – Tedinoz Feb 11 '19 at 22:18
  • Hi thanks for the response, unfortunately that didn't solve my problem.var dataRange = sheet.getRange(sheet.getLastRow(), 1, 1, 34); – TheSeaHawk Feb 13 '19 at 11:33
  • Apologies, my bad - the value in the 34th column is expressed as `data[x][33]` (where x=row number: starting counting from zero) but... Your question refers to "_column AI (34)_" and `var dataRange = sheet.getRange(sheet.getLastRow(), 1, 1, 34);`. However counting from column 1 (A), column 34 = "AH", not "AI". You can confirm this with `Logger.log("range is: "+dataRange.getA1Notation());`. So, you many need to reflect on the actual column that you are trying to retrieve and possibly experiment with the range dimensions and the column number that will retrieve the relevant value. – Tedinoz Feb 13 '19 at 16:59
  • Oh my response got cut off... I've tried different ways of notation to get the correct column, but the problem is with the arrayformula in the sheet. .getLastRow doesn't play well with them. if i get a range of data without the getlastrow command i can retrieve whatever i want, but as soon as i try to use getLastRow all goes to piddle :( I feel very stuck, i really need both 1. The array to create the unique value in the sheet 2. it to be mailed out. My brain is fried iv'e spent all week almost trying to get this to work... it doesnt usually take me this long to solv a problem. – TheSeaHawk Feb 15 '19 at 11:20
  • We have reached the point where you will have to share your spreadsheet, or at least a version of it. The only way to truly understand and resolve the problem is to see the data and the layout. – Tedinoz Feb 15 '19 at 13:47
  • We still need the spreadsheet but... 1) Do some troubleshooting on ranges. `getA1Notation()` is a handy tool in a Logger statement to figure out the "actual" range you're using. 2) You mentioned `getLastRow`. Argh. FWIW, check this brilliant solution by Mogsdad [Determining the last row in a single column](https://stackoverflow.com/a/17637159/1330560). The method delivers finesse based on a specific column (`Alast`=last row containing data). I don't use `getlastRow` unless in case of a nuclear holocaust, but I use `Avals` and `Alast` all the time. – Tedinoz Feb 16 '19 at 11:07

0 Answers0