Google forms edit link does not submit existing values, only those that are changed when using google script?

1
votes

I have a google form that links to a google sheet. On form submit, the responses automatically fill out a doc template and generate a pdf, saving it on drive. I also have a script that populates a column with the edit link for the form submission if they want to change information. Everything works perfectly on the initial submission, but on editing the form, when submitting, only the information that has been changed is submitted, thus leaving the majority of informaton undefined.

Any ideas?

I collect form data like this:

function generateQuote(e) { 
//collect form responses and store in variables
  var QuoteID = Utilities.formatDate(new Date(), "CDT", "yyMMddHHss");
  var CustomerName = e.namedValues['Name of Power Plant'][0] !=''? e.namedValues['Name of Power Plant'][0] : ' ';
  var CustomerAddress = e.namedValues['Address'][0] !=''? e.namedValues['Address'][0] : ' ';
}
1
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-drive-apigoogle-forms
Perhaps if you collect email address and keep previous submission in a sheet they you can go back to the most recent one and pickup the rest of the response. – Cooper
@Cooper How can I do this with google scripts to automate the generation of the new pdf though with these details? – AnaQs
Do you store all of the submissions now? If so what does that spreadsheet look like. – Cooper
Yes I do, it is the default headers with data entered below, and additional column at the end "Edit Url" where I have the edit link populating. – AnaQs

1 Answers

2
votes

Getting all the other values that were not changed during a form edit.

I have a logsheet where I keep some of the data for the onFormSubmit function. It looks like this:

enter image description here

The one piece of data that ties all of the edits to a give form submission is the last column which is the row number of the first form entry into Form Responses 4 Sheet which was the form I was using for this code and that row number comes from e.range.rowStart;

This is the onFormSubmit function:

function testFormSubmission(ev) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('LogSheet');
  var tA=ev.values;//from the event object
  tA=tA.concat([ev.range.rowStart]);//from the event Object
  sh.appendRow(tA);//The ev.values will be replaced in a moment but the ev.range.rowStart will remain on the line allowing you to always know which entry on the Form Response Sheet is being edited
  var lr=sh.getLastRow();
  if(sh.getRange(lr,2).isBlank() || sh.getRange(lr,3).isBlank() || sh.getRange(lr,4).isBlank()) {//if any of these are blank then I assume that this is an edit.  Unfortunately, it could also be a spurious trigger.  But we haven't seen those for a while so I assumed that they are response edits.
    var vA=ev.range.getSheet().getDataRange().getValues();//These are all of the values on the Form Responses 4 sheet
    tA=[vA[ev.range.rowStart-1]];//Since I used getDataRange() and start from 1 to skip the header then I know that the correct array index is row number - 1 so that gets me the row contents of Form Responses 4 Sheet.
    sh.getRange(lr,1,1,tA[0].length).setValues(tA);//Using set values I copied that data into the last row of the logSheet into the line which I just appended thus providing all of the responses including the edited response.
  }
}