Get the last updated CELL in Google sheets and then grab the row. GAS

Question

So I have a google form feeding into a sheet, and from the spreadsheet I'm making google docs from a template with the answers in the spreadsheet.

The form has the ability to save mid way and come back later via a custom question asking if they want to save and comeback (this submits the form). The script on my spreadsheet activates onFormSubmit so when they quit, the template gets created with half their answers. When they eventually come back and finish it off, I want the script to know where to create the template from.

For instance, 5 more rows were added since they quit and the script creates the template from a manual change of the line 'var tactics' by changing the numbers to the row. e.g. if I was about to test another entry, I'd change the numbers to the next empty row first, then when the form is submitted, it would use that row. Not practical. It wouldn't work.

I've looked around a bit and found onEdit(e) but that doesn't work unless it's a manual entry.

My question is, is there another way other than onEdit to find the last cell UPDATED, not ADDED else it'll grab the last row in the sheet, which I don't want. If it grabs the last cell updated then it will grab the correct row to run the script for. I'll add my script to the bottom if it'll help. ID's etc. have obviously been removed.

Any ideas?

function onFormSubmit(e) {

var Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var headers = Sheets.Spreadsheets.Values.get('myID', 'A1:U1');
var tactics = Sheets.Spreadsheets.Values.get('myID', 'A6:U6');
var templateID = "myID"

for(var i = 0; i < tactics.values.length; i++){

var Timestamp = tactics.values[i][0];
var IDCFREF = tactics.values[i][2];
var raisedby = tactics.values[i][4];
var AccMan = tactics.values[i][6];
var Contrib = tactics.values[i][7];
var Contract = tactics.values[i][8];
var CompName = tactics.values[i][9];
var ValidFrom = tactics.values[i][10];
var ValidTo = tactics.values[i][11];
var Freq = tactics.values[i][12];
var PDetailFreq = tactics.values[i][13];
var BillType = tactics.values[i][14];
var TypeOfRebate = tactics.values[i][15];
var RebateDetails = tactics.values[i][16];
var RTarget = tactics.values[i][17];
var GiveDeets = tactics.values[i][19];
var WhyGiveRebate = tactics.values[i][20];
var documentID = DriveApp.getFileById(templateID).makeCopy().getId();

DriveApp.getFileById(documentID).setName('Rebate ' + IDCFREF + ' Request');

var body = DocumentApp.openById(documentID).getBody();
var header = DocumentApp.openById(documentID).getHeader();

header.replaceText('##IDCF##', IDCFREF)
body.replaceText('##REF##', IDCFREF)
body.replaceText('##RAISED##', raisedby)
body.replaceText('##ACCMAN##', AccMan)
body.replaceText('##CONTRIB##', Contrib)
body.replaceText('##SIGNED##', Contract)
body.replaceText('##NAME##', CompName)
body.replaceText('##FROM##', ValidFrom)
body.replaceText('##TO##', ValidTo)
body.replaceText('##FREQ##', Freq)
body.replaceText('##BESPOKE##', PDetailFreq)
body.replaceText('##BILL##', BillType)
body.replaceText('##TYPE##', TypeOfRebate)
body.replaceText('##DEETS##', RebateDetails)
body.replaceText('##TARGET##', RTarget)
body.replaceText('##FULL##', GiveDeets)
body.replaceText('##ELAB##', WhyGiveRebate)

}
}

What's the frequency it a given individual response? Any identifying info in the form snippet you receive? Is this form a native Google Form, or an HTML form with a Google Form as the response store? My thought is that you can add a custom question response with a uuid on early exit, then just find that. — tehhowch
The ##REF## seems to be a id. Couldn't you use that as a unique ID? — TheMaster
@tehhowch2 Hi guys, the ##ref## is a unique ID and is, I guess, identifying info, but how would I add that into a script so that it knows which row to go to. I need it so that when any old form is edited, the script will know where to go on the sheet to get the information for the template document, instead of me manually changing the rows or using the last row in the sheet. And it's a native google form. — RichTea

RichTea RichTea · Accepted Answer · 2018-08-31T15:42:52

So for anyone who has the same issue, here was my solution:

function onFormSubmit(e) {

var range = e.range;
var ss = range.getSheet();
var row = range.getRowIndex();

var tactics = Sheets.Spreadsheets.Values.get('ID', "A:AQ"+row);

The way it works is just delete every row in the form responses sheet up until the last entry, and then voila. ("A:AQ"+row) gets the row index from the variable 'row' created from 'e.range' upon form submission.

It works great for me, if 3 forms were filled in and 3 lines were added to the sheet before another came back to edit his responses, the script knows what row to find the data on in the sheet, thus creating the correct template.

Get the last updated CELL in Google sheets and then grab the row. GAS

1 Answers