Form owners may need edit a few form responses in the spreadsheet to correct some information. This topic seems rippling over and over again for years but Google still doesn’t support the feature. We used to think that by editing the cells in the linked spreadsheet, these responses will change too. But unfortunately it doesn’t. To edit the real responses, you need a link called Form Response Edit URL.
This tutorial shows you how an apps script xfanatical built can allow you edit form responses directly in spreadsheet.
Step 0. Preparation
- You have a spreadsheet connected with the form as a response destination.
- You have enabled Respondents can Edit after submit option in the Settings of form.
Step 1. Download the Apps Script
- Download the Add Form Edit Response URL apps script.
- Open the zip file. It contains a bundle.js and a useless README.md file. 😀
- Copy all the content of bundle.js to your clipboard.
Step 2. Power up the Spreadsheet
- Now open up the spreadsheet with which the form is associated.
- Click Tools in the menu bar, followed by Script editor.
- In the popped Script Editor tab
- Give the script editor a name, say Add Form Response Edit URLs
- Paste the code from Step 1.
- Click save.
- Now go back to Spreadsheet tab. Refresh the page.
- You will see a new Menu Forms > Add Form Response Edit URLs by the end of menu bar.
Step 3. Automation
It’s tiring of setting up all the pieces, isn’t it? I know, it’s boring to handle technical issues sometimes. But we’re almost there.
- Click that Add Form Edit Response URLs menu.
- Wait for the script running. If the Authorization window popups, don’t panic, just go through it. The Apps Script is safe.
- You will see the script adds a new column Form Response Edit URL at the far right of sheet (If you don’t see it, scroll right). It then adds one-by-one the urls to the responses. Amazing, huh?
- BONUS: Now ask your respondent to submit a new response. The Form Response Edit URL will be automatically attached to Spreadsheet for the new response!
Step 4. Edit responses in the spreadsheet!
- Now you have a response edit url for every submission, you are free to click those links and make changes to them.
- The changes will be reflected soon in the spreadsheet.
Thanks for reading.
I was so excited to see your script for this is exactly what I’m looking for! I followed the steps, but it did not work… I downloaded the file, extracted it, pulled it into notepad, copy and pasted it in to the Script Editor tab, named it, saved it, refreshed the spreadsheet and the Forms portion did not show up in the menu. Any tips to get it to work?
Hi Jennifer, thanks for the comment. Would you mind sharing a test sheet? I’ll help you troubleshoot the issue.
Jason, were you able to help? I have the same problem… The steps worked for one form but I tried to do it in another and couldn’t. Any suggestions?
Hi Vivian, could you share a sample sheet with me?
Actually, I figured it out. The column was added like 10 columns from the end of the last column so I just couldn’t find it. But I scrolled and finally saw it there. Is there a way to designate where the column of links is added?
Great to hear. I’ll note it to the article. When the column is generated, you’re free to move it anywhere. Thanks for sharing.
Jennifer, did you get a solution? This happened to me just now too.
This is fantastic. Worked like a charm. Thank you very much!
Well done. You’re welcome, Tom!
HI Jason, I still am having issues with the script. I added it to the form but now the problem is that the urls are added at the end of the forms in separate columns. For example, if I indicate that column “C” have the Edit URL, the url for Response 1 (in row 2) appears in Cell C2, but then the url for Response 2 (in row 3) appears not in C3 but in D3, Response 3 appears in E4, Response 4 in F5, etc. The compilation of urls looks like a ladder in the excel sheet. Is there… Read more »
Hi Vivian, have you changed the header “Form Response Edit URL”? It you did, I could reproduce the issue here too. The fix is simple. Revert the header of original column to “Form Response Edit URL” and re-run Forms > Add Form Edit Response URLs. Once it prints all links back normal in the column, clear out those laddered links.
Hope it fix your issue.
Hello, I keep getting an email with errors noted. The URL posts into the spreadsheet but I’m wondering if you can offer some advice to correct the errors.
The error is function: core, Script function not found: core, and the trigger: formSubmit thank you
Hey Jason! Awesome addon! I’m going through the script and slowly understanding the code. Did you write this all yourself? If so, would you be willing to break it down and explain at least the overall architecture?
Hi William, well done on learning apps script. I don’t write it myself. It’s a compiled code, not a source code. So reading it will be tough for you. It’s a simple apps script. Since the form and sheet are linked with each other, the sheet will get a trigger (https://developers.google.com/apps-script/guides/triggers/events#form-submit) for each response. Use the Form API (https://developers.google.com/apps-script/reference/forms/form-response) to append the link to each submission. Good luck to your learning.
I have windows 7 home and when I download the script and try to open it I get an error from the windows script host that says that the property or method is not supported. I probably don’t know enough to open it properly. Any help?
Hi Cameron, could you send a couple of screenshots to support@xfanatical.com? I’ll try to help. Thanks.
Sorry I haven’t responded. My son-in-law opened it up for me and until now it has worked wonderfully. Thank you very much. New problem, when i go to edit an individual form some of them are all blank responses, except for the first section of the form. The responses are showing up in their proper places on the sheet but not on the form.