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.

I think that UrlFetchApp.fetch(url) would need to be used to make an HTTPS GET or POST request to edit the data in the Form. Where url is the url that does the edit. I deleted my previous comments. — Alan Wells
I can't find any "built-in" way to edit an existing response with code. I've been trying to figure out the url and payload when the edit is sent to the Form, but haven't got anything definite. — Alan Wells
@SandyGood: There is an answer with the code to do that but it's not working anymore. See stackoverflow.com/questions/20410497/… — Rubén
The error isn't saying your sheet has no data, it's saying the object you're trying to use getDataRange() on is null. As @Rubén suggested: we'd need to see more code to see why. — Mogsdad
Related (if the form is connected to a spreadsheet): webapps.stackexchange.com/questions/89551/… — Sphinxxx

Brad Parks Brad Parks · Accepted Answer · 2017-02-13T19:20:51

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.

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

1 Answers

Here are the steps I followed to get this to work