How do I edit the response of a form submission in Google Forms/Sheets?

Question

Recently, I've been conducting a survey. I began creating it in Excel Forms Online but switched to Google Forms after I realized several of its shortcomings. I hit a few snags but I thought I was all set to go.

Unfortunately, I began adding questions to the form and changing responses (misspellings, etc...) and this began causing a whole bunch of problems. It's enough that I have to go back and ask users for their responses to new questions if applicable. What's worse is I can't modify a submission at all. I can't edit the spreadsheet connected to the form because results are stored in the form. If I regenerate the spreadsheet, then the response submitted in the form replaces whatever was there before. So I can't edit the spreadsheet at all.

I've been looking into a way to try to manually edit a user's submitted response. I found a resource here on Stack Overflow, and here on Google Groups at the Products Forum, but unfortunately, I tried all of the examples and none of them worked (yes, I put my form's URL in and everything and replaced the name with the spreadsheet name).

The error I continued receiving after I filled in my info was TypeError: Cannot call method "getDataRange" of null. (line 8, file "Code"). But the sheet is not empty and I know that.

Is there some other way to make this work, or a better way to potentially edit sheet responses?

The only way to do this right now is to manually go back and re-submit the form again as that user. It's kind of a pain because it's 10 minutes of checking boxes and copying and pasting. Plus, last night I accidentally skipped copying 1 character and now I'd have to do it all over again.

From what I've seen online, this can be done, but nothing I have tried works. The form is currently live and accepting responses right now. Any solution is acceptable as long as there would be a way to edit their responses, either with a script or some other tool, so I don't need to manually re-do everything myself.

Answer 1

The code suggested here works, as I just tried it myself on Feb 13, 2017.

Here are the steps I followed to get this to work

• First, load your form as if you were going to edit the form.
• Look for the "form id" for your form in the url in the address bar. It should look something like this:

https://docs.google.com/... /forms/d/1ZIrWiRZQrUsz1y8OBoeB7AtCOM4Ax4FxAQm8xAR1OYo/edit

• So the id in this example would've been "1ZIrWiRZQrUsz1y8OBoeB7AtCOM4Ax4FxAQm8xAR1OYo"
• Now go to https://script.google.com

• Replace all the text that shows up in the Code.js section with the following, AFTER you've edited it and put your form id in place of YOUR_FORM_ID_HERE below.

  function dumpEditResponseUrlsForYourForm() {
     // Change this next line to use the id of your form
     var myFormId = "YOUR_FORM_ID_HERE";
  
     var form = FormApp.openById(myFormId); 
     var formResponses = form.getResponses();
     for (var i = 0; i < formResponses.length; i++) {
       var formResponse = formResponses[i];
       Logger.log(formResponse.getEditResponseUrl());
     }
  }
  

• Click "View | Logs" in the Google Script web page. It'll pop up a window with links to your form responses.