How to edit Google Form responses directly from GOOGLE SHEETS

[@practicalsheets]

The code has a bug as it is: when you change a past row, the latest one also gets the url of the past one, resulting in mismatched urls. This is the fix Replace this let lastRow=dataSS.getLastRow() dataSS.getRange(lastRow,COL_EDIT_LINK).setValue(responseURL) with this let lastRow=dataSS.getLastRow() let url=dataSS.getRange(lastRow,COL_EDIT_LINK).getValue() if(url=="") dataSS.getRange(lastRow,COL_EDIT_LINK).setValue(responseURL) Thank to @BradWilson-gf3ny and @SondraDally for pointing out in their comments.

[@aumkarshah1055]

This fix is still giving an error. When we have made say 10 entries. and i wish to go back and edit the 3rd one, it just gets replaced with a bank cell. COuld you please look into this?

[@krpdi3m]

dataSS variable wins for me this time. xD Thanks for the tutorial! Will try to add this code to the others in one script.

[@practicalsheets]

Let me know how it goes! Kind Regards

[@Raju-q2u]

Thank you so much for this video. After watching your video, it is encouraging me to start learning scripting too. Many thanks Instead of keeping the link last column, how do i put it in first column, and also make it visible as 'EDIT' instead of full link? This will help me expand the form fields. Otherwise, when i add new fields in to form, it is overwriting the link column, in such case, i need to update the script every time i add fields. Thanks

[@practicalsheets]

for changing the edit column to the beginning, then we eould need to change everything and do a manual connection I will put in my queue a video addressing this Kind Regards

[@big1ne]

Great, worked amazingly

[@practicalsheets]

Thank you!

[@GiuseppeCristino]

Thank you so much for this. I am actually using it for a very long form that I have created with multiple sections. I do have a challenge so far... The edit link URL is created, but if you don't go through all responses that you have previously submitted they will be stored as blank. Do you have any suggestion on how I could solve this?

[@practicalsheets]

Even if you had already filled them? you should obtain all previous responses Kind Regards

[@sandeepkumarkamboj]

for me it is not populating EditURLLink at all. What to do?

[@practicalsheets]

Does it throw an error? Kind Regards

[@khannansilvasamy7478]

Sir you are legend

[@practicalsheets]

Thx for the kind words!

[@SondraDally]

This works well, thank you so much. Your instruction was excellent and easy to follow for a novice like me. I need to test what happens if I go back and edit a row that is not the last row. I wonder if the code will update the last row with the url for the edited row.

[@SondraDally]

I just tested this and yes, if you have several rows and go back to edit say row1, when you submit, the last record is updated with the url for row1.

[@SondraDally]

This is what I did to resolve: You may have a better way?? const Sheet_ID = "[file identifier from sheet link]"; const Data_SS_Name = "FileMigration"; const Col_Edit_Link = 37; function onFormSubmit(e) { try { // Retrieve the edit response URL let responseURL = e.response.getEditResponseUrl(); if (!responseURL) { throw new Error('Edit response URL is undefined'); } // Open the spreadsheet and select the sheet let ws = SpreadsheetApp.openById(Sheet_ID); let dataSS = ws.getSheetByName(Data_SS_Name); if (!dataSS) { throw new Error(`Sheet with name "${Data_SS_Name}" not found`); } // Get the last row and set the edit response URL in the specified column if empty let lastRow = dataSS.getLastRow(); let emptyTest = dataSS.getRange(lastRow, Col_Edit_Link).getValue(); if (emptyTest === "") { dataSS.getRange(lastRow, Col_Edit_Link).setValue(responseURL); } } catch (error) { Logger.log(`Error: ${error.message}`); } }

[@practicalsheets]

Don't seem such a novice. Very nice code! Thank you for sharing!

[@ahlannurhamzah1631]

Hi, thank you for your tutorial. I already follow exact like you did, but I have an error, always like thank. the error note is TypeError: Cannot read properties of undefined (reading 'response') at getEditorLink(Code:6:21), could you help for it?

[@practicalsheets]

Remember you should not execute the code from the editor. It will only work sending the form Kind Regards

[@MuratKekec-ok5hr]

How to edit specific url from the google form instead of google sheet. is there any way to find the google url for that person response?

[@practicalsheets]

Hmmm Not that I know of Kind Regards

[@girishyanamala9361]

Thanks🎉

[@practicalsheets]

Thank you for commenting!

[@GhaziFarouk]

thank you sir

[@practicalsheets]

Thank you!

[@lauyell]

Hello sir, Thanks for the video. I have Question! it looks like we can only edit it for a new response. What about the hundreds of responses that have been submitted before this script was used? I want to edit the past responses, because if I create a new one and input it manually again it takes a lot of time, because my responses have exceeded 100. is there a solution for that? so that the graphs and answers on the G-Form are no longer error or defective because they do not match the sheet?

[@practicalsheets]

Hello! Theoretically, it will only work for new responses. I don't think there is a way to acces old responses, but I will research Kind Regards

[@vinaysavla]

@@practicalsheets Same question.. Could you get a way to generate EDIT URL for the previously submitted Google sheets data OR data that is added to the google sheet as a bulk??

[@isnanfajarmuaddin2689]

I’ve just created a script that generates Edit Response URLs for all responses, including the older ones.

[@ianyates4097]

@@isnanfajarmuaddin2689 Is it possible that you can share the code please?

[@agenjova7162]

9:20 mine says "ReferenceError: e is not defined at [unknown function] (Code:2:19) " And the status is error, how to fix it?

[@practicalsheets]

Remember you can not execute this code from the Editor. It will only work when you trigger it by sending the form Kind Regards

[@josefernandocarreromartin5349]

Can I create edit links for answers sent prior to the process described on the video?

[@practicalsheets]

Hmmmm I'm not sure I think not, but maybe there is a way to "access" past responses in Forms I'll look into it Regards!

[@big1ne]

@@practicalsheets Hi, Did you manage to get the solution to this question? I also want to edit the existing responses before I did this script

[@practicalsheets]

@@big1ne Not yet. In the queue Kind Regards

[@jeanswart8508]

How do I add a specific sheet in a spreadsheet to the script

[@practicalsheets]

You put it here const DATA_SS_NAME="Students" Kind Regards

[@BradWilson-gf3ny]

I dont know if im doing something wrong but the code works and adds the url but if your click the hyperlink and there is rows under that hyperlink with other data its changes the last row edit link to the same as the one i edited

[@practicalsheets]

You're absolutely right. This is the fix Replace this let lastRow=dataSS.getLastRow() dataSS.getRange(lastRow,COL_EDIT_LINK).setValue(responseURL) with this let lastRow=dataSS.getLastRow() let url=dataSS.getRange(lastRow,COL_EDIT_LINK).getValue() if(url=="") dataSS.getRange(lastRow,COL_EDIT_LINK).setValue(responseURL) Kind Regards

[@BradWilson-gf3ny]

@@practicalsheets thank you works perfectly now. Is there a way to show the hyperlink in the sheet as just something like "Click to Edit" or does it have to be the full hyperlink

[@practicalsheets]

@@BradWilson-gf3ny Glad it worked You could include it in a HYPERLINK function Just use .setFormula('=HYPERLINK("Click to Edit","'+url+'"') Something like that Kind Regards

[@practicalsheets]

@@BradWilson-gf3ny You could add a HYPERLINK formula, such as this =setFormula('HYPERLINK("Click to Edit",'+url+'"') Regards!

[@khannansilvasamy7478]

Sir if i want this link to appear in a sheet 2 .how to do it

[@practicalsheets]

Hello! Do you mean in a different list? Kind Regards

[@MuratKekec-ok5hr]

How to edit the form if you close the page and you have only access to google sheet but dont see " edit my response anymore?

[@practicalsheets]

The only way I know is storing the url somewhere as I do in the video Kind Regards

[@MuratKekec-ok5hr]

@@practicalsheets Thanks.Do youknow how to add more questions after submission?

[@practicalsheets]

@@MuratKekec-ok5hr Hmmm I shouldn't be too hard Using the trigger on Form Sent in Sheets, you could follow this tutorial kzbin.info/www/bejne/oWG1hGelhd-sbpI Kind Regards