Handling edits to a Google Form response

Question

Objective:

Utilising Google Forms, I am submitting data to a spreadsheet. I am using Apps Script on the spreadsheet to trigger 'on form submit', to push the data into a table on a Google Document.
The first column in the Google Docs table contains the row ID relative to the row Id in the Google Sheet.

Problem:

For the purposes of my application, some Forms will require responses to be edited/updated.
My script currently only adds new rows to the Google Docs table (including edits which I want to only edit the relevant row not submit on a new row). Obviously the Google Sheet row updates automatically without issue.

My train of thought to create a solution:

I added the column to the Google Docs table containing a unique identifier (Spreadsheet Row ID)
Question: When you're inside and editing a Google Form (that has already previously been submitted) does that activate a .activeRow() in the Google Sheet? If it does I could check to see if it's an existing row.
Question: How can I search the Google Doc table's column containing the ID's? Once I identify the row how can I edit it? Is it something to do with .getElement(); ? Do tables in Google Docs handle differently to table in Google Sheets?

Do you think I am on the right path? Is there a far simpler solution I'm missing?

// Grab the Table
var body = DocumentApp.openById('theid').getBody(),
searchElement = body.findElement(DocumentApp.ElementType.TABLE),
element = searchElement.getElement(),
table = element.asTable();

// Get the last row ID
var range = SpreadsheetApp.getActiveSheet().getLastRow();
// Insert the Row
var cells = [range, theDate, nameWelcome, interest, value, comment, offer];
var addRow = table.appendTableRow();
cells.forEach(function(e, i){
  addRow.insertTableCell(i, e);
});

Editing a Google Form generates no events for Google Sheets. — tehhowch
@tehhowch If I were to call FormApp and get active form, could I possibly get the form id then get the active row in the google sheet? — Bjaeg
No, you'll more likely than not get a dummy reference to cell A1 of the first sheet, since (to that server instance of your project) no one is using the Google Sheet document. What you'd need to do is open the response workbook's worksheet that collects the submitted form responses, read all the data on that sheet (sheet.getDataRange().getValues()), and then inspect each row to determine if that row in the array has the same data as the form response you are editing. — tehhowch
To add to your solution, if I learn to save the active form id (example 1xHTpJVrucLR17KYx0dg_2jgCMnoax1gfVU1bolfOjC8) in a column it should hypothetically give me a fast way to search the sheet. Thank you for your help. — Bjaeg
Possible duplicate of How can an apps-script on a Form store extra data into the Sheet? — Rubén

Bjaeg Bjaeg · Accepted Answer · 2018-04-26T15:36:57

I found my answer here. How can an apps-script on a Form store extra data into the Sheet?

I need to run the code from the form script as opposed to the sheet. This way I can get active url. I also need to point to the specific sheet.

Handling edits to a Google Form response

1 Answers