Run script on last line of google form responses

Question

I'm new to Google Apps Script and I'm trying without luck to automate a previously manual process. The script is within a sheet which takes form responses, converts them to a document and then emails the respondent with a copy. Here's the code in question:

function createDocFromSheet(){
    var templateid = "1E7zzpvDF0U66aNqJdkUqjONx4wQRarkcWDy28NVqafU"; // get template file id
    var folder = DriveApp.getFolderById("1-8lae1z_Z-Sy1IczUyB2JvCqCBV8zB5D")// folder name of where to put completed quotes

  // get the data from sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues();

    var username = Session.getActiveUser(); // get their email
    var dateNow = new Date(); // get date, clumsily
    var dateD = dateNow.getDate();
    var dateM = dateNow.getMonth();
    var dateY = dateNow.getYear();


  // for each row, fill in the template with response data 

    for (var i in data){
    var row = data[i];

  // make copy and set active
    var docid = DriveApp.getFileById(templateid).makeCopy(row[7]+" - Postal Quote",folder).getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();

  // date - working
    body.replaceText("%D%", dateD);
    body.replaceText("%M%", dateM+1);
    body.replaceText("%Y%", dateY);

  // address - working
    body.replaceText("%FNAME%", row[2]);
    body.replaceText("%SNAME%", row[3]);
    body.replaceText("%ADDL1%", row[4]);
    body.replaceText("%ADDL2%", row[5]);

This is setup to trigger on form submit but instead of running the script on the last row it runs for all previous responses as well. This was fine previously when responses were copied to a separate sheet and processed in bulk but I'm lost with how to make it run on only the new response.

Can anyone point me in the right direction?

James D James D · Accepted Answer · 2018-01-11T13:15:36

Line 8; var data = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).getValues(); in this line you are getting the range A2:whatever the last row and column is, that's why it's going over the entire range.

Changing it to var data = sheet.getRange(sheet.getLastRow()-1, 1, 1, sheet.getLastColumn()).getValues(); should work.

or you can use the e parameter of the onFormSubmit event.

var getSubmission = e.range.getValues();

Have a look at the documentation for the e parameter, it's can be difficult to use in the beginning when debugging but it's very useful when you get the hang of it.

Run script on last line of google form responses

1 Answers