17

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.

InterLinked
  • 1,247
  • 2
  • 18
  • 50
  • 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 Jun 12 '16 at 15:27
  • 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 Jun 12 '16 at 17:22
  • 1
    @SandyGood: There is an answer with the code to do that but it's not working anymore. See http://stackoverflow.com/questions/20410497/use-app-scripts-to-open-form-and-make-a-selection/20510224#20510224 – Rubén Oct 10 '16 at 12:43
  • @InterLinked: Regarding the error that you got on your code to get the edit response URL, add more details. Also check [mcve]. – Rubén Oct 10 '16 at 14:49
  • 1
    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 Oct 12 '16 at 23:54
  • 1
    Related (if the form is connected to a spreadsheet): http://webapps.stackexchange.com/questions/89551/show-url-used-to-edit-responses-from-a-google-form-in-a-google-spreadsheet-by-us – Sphinxxx Feb 17 '17 at 21:17

1 Answers1

21

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.
Community
  • 1
  • 1
Brad Parks
  • 66,836
  • 64
  • 257
  • 336
  • 3
    If needed, an improved version can be found here: http://webapps.stackexchange.com/a/89566/151861 – Dvir Berebi Apr 06 '17 at 11:08
  • 1
    This is really awesome! Thank you! – aaiezza May 24 '17 at 14:51
  • 1
    I first got "No logs found. Use Logger API to add logs to your project." Turned out I needed to click "Run" > Run Function > dumpEditResponseUrlsForYourForm and then grant permissions, and then it worked. – krubo Jan 21 '20 at 02:41