Display updated data from a Google Form

Question

I have a Google Form where I want to display data from a linked spreadsheet. Here's the process:

1. student submits FormA

2. teacher enters a 'passcode' (via section with data validation) to provide feedback. Then, various emails / Google Doc merge happen - wonderful

Our wish:

3. but - If we allow the student to "Edit after submission" on FormA , they could read the teacher's passcode once the teacher does their work.

We are able to update the 'passcode' field in the linked spreadsheet with the following script and onformsubmit trigger:

function myFormUpdates(e) {
  var spreadsheet = SpreadsheetApp.getActive()
  //select the sheet you're form is going to post data to
  var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheets()[1])
  //select the last row and a unused column
  var cell = sheet.getRange(sheet.getLastRow(), 9)
  //set data
  cell.setValue('PSW')
}

So the passcode in the spreadsheet is 'PSW' but, when the student views FormA via Edit, they see the original passcode not the updated data "PSW".

We understand that Google Forms holds data in the Form itself, as well as Insert into FormA's Google Sheets. And everything we've read in research says Google Forms Inserts but doesn't Read from a Forms' linked Sheets.

Any suggestions / workarounds (outside of custom developed forms)?

At this time, we will either have to turn off the Forms' "Edit after submission" or live with the students knowing the passcode to the feedback section.

Answer 1

Form responses stored in the form can't be edited programmatically, so if you want that your students be able to edit the form response but do not have see the response items filled up by a teacher, then use two different forms, one for the student, another for the teacher.

You could use the student submitted responses to create a pre filled up form response for the teacher (see Pre-populated form answer) or create new responses programmatically by using the withItemResponse(response) method.