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.
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?
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
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
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?
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.
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); }
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?
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 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??
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
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
@@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
@@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
@@MuratKekec-ok5hr Hmmm I shouldn't be too hard Using the trigger on Form Sent in Sheets, you could follow this tutorial czcams.com/video/k0RS4oPzy80/video.html Kind Regards
![[2023] Google Workspace Beginners Guide | Tips on Getting Started (from an Expert)!](http://i.ytimg.com/vi/FwT6_JFAk5Y/mqdefault.jpg)
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.
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?
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
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
dataSS variable wins for me this time. xD
Thanks for the tutorial! Will try to add this code to the others in one script.
Let me know how it goes!
Kind Regards
Sir you are legend
Thx for the kind words!
Great, worked amazingly
Thank you!
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?
Even if you had already filled them? you should obtain all previous responses
Kind Regards
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.
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.
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}`);
}
}
Don't seem such a novice. Very nice code!
Thank you for sharing!
Thanks🎉
Thank you for commenting!
thank you sir
Thank you!
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?
Remember you should not execute the code from the editor. It will only work sending the form
Kind Regards
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?
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
@@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??
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?
Hmmm
Not that I know of
Kind Regards
9:20 mine says "ReferenceError: e is not defined at [unknown function] (Code:2:19) " And the status is error, how to fix it?
Remember you can not execute this code from the Editor. It will only work when you trigger it by sending the form
Kind Regards
Can I create edit links for answers sent prior to the process described on the video?
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!
@@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
@@big1ne Not yet. In the queue
Kind Regards
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
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
@@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
@@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
@@BradWilson-gf3ny You could add a HYPERLINK formula, such as this
=setFormula('HYPERLINK("Click to Edit",'+url+'"')
Regards!
How do I add a specific sheet in a spreadsheet to the script
You put it here
const DATA_SS_NAME="Students"
Kind Regards
Sir if i want this link to appear in a sheet 2 .how to do it
Hello!
Do you mean in a different list?
Kind Regards
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?
The only way I know is storing the url somewhere as I do in the video
Kind Regards
@@practicalsheets Thanks.Do youknow how to add more questions after submission?
@@MuratKekec-ok5hr Hmmm
I shouldn't be too hard
Using the trigger on Form Sent in Sheets, you could follow this tutorial
czcams.com/video/k0RS4oPzy80/video.html
Kind Regards