Do you need to have someone edit form responses AFTER they’ve already submitted them? What if they closed the form and need to edit later? If this is something you’re dealing with, then this article is where you need to be!
I recently had a student ask me how he could have people come back to his form and edit their responses. In this article we’ll take a look at how to set up a form that can immediately be edited, as well as how we can get the link for editing a form that has already been closed.
For the first method, we’ll simply set the form to include an ‘edit response’ link after the form is submitted.
To do this, click on the settings icon in the upper right.
Then check the “Edit after submit” box, and save it.
Now you can preview the form and fill it out. Once you submit the form, you will see that there is a link to edit your response.
Clicking on that link will bring the form back up to edit. If you have a need for people to come back to the form and edit it later, you can tell them to copy this edit form link and save it somewhere.
That’s a simple way to do it. Now what about the people who have already filled out a form and closed it, but need to go back and edit their response? This is a bit more complex to set up, but once it’s done you can use it over and over.
For this method, you’ll set up a script in the response spreadsheet that will insert the edit form links for each response.
Begin by linking your form to a response spreadsheet. This is done by clinking on the “Responses” tab, then clicking on the green spreadsheet icon.
Once that is done, open the spreadsheet and click on the Tools menu item then Script Editor.
This will open a new tab with the Google Script Editor.
Delete the text that’s already there and replace it with this script:
function assignEditUrls() var form = FormApp.openById(' Your form key goes here ');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(' Your responses Google Sheet name goes here - The tab name, not the file name ');
var data = sheet.getDataRange().getValues();
var urlCol = Column number where URLs get entered goes here ;
var responses = form.getResponses();
var timestamps = [], urls = [], resultUrls = [];
for (var i = 0; i < responses.length; i++) timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
urls.push(responses[i].getEditResponseUrl());
>
for (var j = 1; j < data.length; j++) resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
>
sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
>
The red text is what you need to update with information relevant to your form.
The first thing you need to update is the form key. Looking at the form, you’ll see a string of numbers and text in the URL. This is what you need to copy, and paste into the script where it says, “ Your form key goes here “.
Next you’ll need the the sheet name. This is referring to the sheet that your responses are stored on, not the file itself. Copy the sheet name and paste it into the script where it says, ” Your responses Google Sheet name goes here – The tab name, not the file name “.
The last thing you’ll need to do is tell the script what column should contain the edit URLs. In this example, the next empty column in this spreadsheet is the sixth one, which means I would enter “6” for the column. You can enter any empty column number you like. Make sure not to accidentally delete the semi-colon. If you do, the script won’t work.
Once you’re done entering your form information, you can save the script. If you haven’t already, give it a name that relates to your form. Now you just need to run the script by clicking on Run,> Run Function > assignEditURLs
The first time you run the script, it will tell you that Authorization is required. Click on “Review Permissions”, choose your account and click on “Allow”. You may get one more warning about safety. If you do, you still want to allow it. You made the script, so you know it’s safe.
When you go back to the spreadsheet you will see that the URLs have been populated in the column that you designated. If you open one, it will take you directly to the form for that response and allow you to edit it.
After you’ve set this up, all you need to do is run the script any time you want to populate the URLs. You now have the edit URLs to give out to the people who filled out you form if they need to edit them.
PLEASE NOTE – I realize that in the video I’m copying the FormKey from the form’s preview. Make sure that you are using the FormKey from the form while in Edit Mode, not from the preview.
IF YOU DON’T – You will see an error that looks something like this, “TypeError: Cannot read property ‘getSheetByName’ of null (line 4, file “Code”)”
Watch the video on how to edit form responses in Google Forms to see it in action.
I hope you found this article to be helpful. Comment below to and let me know what you think.
Learn more about Google’s amazing office apps with the best selling Google Cloud Productivity course on Udemy.
