Create a Google Doc for Changed Row on Sheet When Editing Form Responses

Question

Here's a link to the build project: https://drive.google.com/open?id=1IbmEWv_y60n-IefIrjmTcND-E6Rv35vb

---

Goal

Create a document when the form is submitted initially. Then, create a new document for each affected row on the sheet "Form Responses 1" when using the form to edit responses.

---

Current Results

Documents are created for all rows on the sheet every time the script runs. The individual documents include the correct data, but documents are also created for rows that didn't change values.

---

Problem

I'm inexperienced with scripts and cannot find a way to create a document only if values within the row have changed.

I tried a method other than the script below using "On form submit" as a trigger, but editing responses resulted in creating documents that ignored any response that was left unedited. In other words, if the form collected data for Q1 and Q2, the first document created would include everything entered on the form, as expected. If I edited the form response for Q1, however, the newly created document ignored Q2 entirely and only displayed Q1.

I also tried using "On edit" as a trigger. That didn't work at all when using the form. It required editing the sheet "Form Responses 1" directly, so it didn't meet my needs.

Help?

---

Current Trigger and Script

Trigger: On form submit

function setUp() {
  //IDs to get and open
  var tid = "1Us7qyNjFTeTrrA2Xt_Yigks1Yn-n0KGre9rUn3UV5yc";
  var fid = "1HVy7J7EsgKfX-BjgzOGgXlcmYDuLs5C_";
  var sid = "1FrJpfE9L2ZRE76ABDWA0gfYaZd6RFkvGQkSipHFCZCU";
  var sname = "Form Responses 1";

  //Get the template doc
  var template = DriveApp.getFileById(tid);

  //Get the output folder
  var folder = DriveApp.getFolderById(fid);

  //Open the form response worksheet
  var ws = SpreadsheetApp.openById(sid).getSheetByName(sname);

  //Get the worksheet range
  //starting row #, starting column #, last row, # of columns
  var range = ws.getRange(2,1,ws.getLastRow()-1,7).getValues();

  range.forEach(function(r){
    //Name the data
    var ts = r[0];
    var email = r[1];
    var q1 = r[2];
    var q2 = r[3];
    var q3 = r[4];
    var q4 = r[5];
    var q5 = r[6];

    //Format the timestamp
    var date = Utilities.formatDate(new Date(ts), "GMT-5", "yyyy-MM-dd");
    createDoc(template,folder,date,email,q1,q2,q3,q4,q5);
  });
}

function createDoc(template,folder,date,email,q1,q2,q3,q4,q5) {
  //Copy the template, name the doc, and put it in the folder
  var copy = template.makeCopy(q1 + ' - ' + date, folder);

  //Open the new document
  var doc = DocumentApp.openById(copy.getId());

  //Get and update the body of the document
  var body = doc.getBody();
  body.replaceText('##Date##', date);
  body.replaceText('##Email##', email);
  body.replaceText('##Question1##', q1);
  body.replaceText('##Question2##', q2);
  body.replaceText('##Question3##', q3);
  body.replaceText('##Question4##', q4);
  body.replaceText('##Question5##', q5);

  //Save and close the document to persist our changes
  doc.saveAndClose(); 
}

Answer 1

You were in the right way when you were using the onFormSubmit(e) and yes, the e object gives you the edited cells, but not the other values, what I did to solve that issue was to use the range that brings the e object to locate the edited row and then find it using e.range.getRow(), the rest would be done as you were doing it.

// Create a new document with the submitted data in the sheet
function createDoc(data){
 
  //Get the output folder
  var folder = DriveApp.getFolderById("your folder ID");
  //Get the template doc
  var template = DriveApp.getFileById("your file ID");
  
  //Copy the template, name the doc, and put it in the folder
  var copy = template.makeCopy(data[0][2] + ' - ' + data[0][0], folder);
  //Open the new document
  var doc = DocumentApp.openById(copy.getId());
  
  //Get and update the body of the document
  var body = doc.getBody();
  body.replaceText('##Date##',  data[0][0]);
  body.replaceText('##Email##',  data[0][1]);
  body.replaceText('##Question1##',  data[0][2]);
  body.replaceText('##Question2##',  data[0][3]);
  body.replaceText('##Question3##',  data[0][4]);
  body.replaceText('##Question4##',  data[0][5]);
  body.replaceText('##Question5##',  data[0][6]);

  //Save and close the document to persist our changes
  doc.saveAndClose();
  
}


// This will be trigger every time the form is submitted
function onFormSubmit(e) {
  // Get active sheet in the active spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var numberCols = 7;
  // Get values in the row that was edited 
  var editedRow = sheet.getRange(e.range.getRow(), 1, 1, numberCols).getValues();
  // Create a new doc
  createDoc(editedRow);
}

As you can see, I made several changes to your code, you can use it like that or adapt it in the way you would want to. Just keep in mind in the future you could check the values inside the data array to verify if they are empty or not.

Docs

For more info about you can check these and be careful about the Instabble Triggers restriccions:

• onFormSubmit().

• Event Objects - Form Submit.

• Class Range.

• Installable Triggers.

Answer 2

I was just trying to provide a way to store your last configuration so that you could determinine whether a change has been made between triggers so that you would know whether or not to generate a new document.

SpreadsheetApp.getActive().getSheetByName('ConfigSheet').appendRow([tid,fid,date,email,q1,q2,q3,q4,q5]); 

I'd do it this way because getting the last configure is as simple as

var sh=SpreadsheetApp.getActive().getSheetByName('ConfigSheet'); 
sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn()).getValues()[0];` 

and now you have all of your latest configuration setting in a flat array. But I don't know what sort of changes merits creating a new document.