0

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:

  1. 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.

  • Are you using the new version of Google Forms? I am and edit after submission just puts me back in the form view w my data rather than the SS view. Am I misunderstanding? – Tom Woodward Oct 3 '16 at 19:43
  • Yes. Using current Google Forms. The 'Edit after submission' shows a field (eg passcode) I want to hide / obfuscate from the students IF the students happens to Edit after the teacher entered their passcode. If we obfuscate an entered passcode, the teacher can re-enter the passcode on Edit. We do realize GForms is a basic survey/form tool. We have nothing critical to protect. Just want to keep students from entering their own feedback or editing feedback. The script does update that field in Sheets, but unfortunately Forms doesn't read from Sheets but from some hidden Forms db. – SamU Oct 3 '16 at 21:37
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.