How to edit Google Form responses directly from GOOGLE SHEETS

แชร์
ฝัง
  • เผยแพร่เมื่อ 29 ต.ค. 2024

ความคิดเห็น • 58

  • @practicalsheets
    @practicalsheets  4 หลายเดือนก่อน +5

    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
      @aumkarshah1055 3 หลายเดือนก่อน

      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?

    • @heinzmarccarlos4123
      @heinzmarccarlos4123 14 วันที่ผ่านมา

      my code is not working
      const SHEETS_ID="1UZ9nalwVD_A1G0ORrIQo0tKuHUwq1ySBiZfN_B0dlhY"
      const DATA_SS_NAME="Verification"
      const COL_EDIT_LINK=6
      function getEditLink(e) {
      let responseURL = e.response.getEditResponseUrl()
      //logger.log(responseURL)
      let ws = SpreadsheetApp.openByID(SHEETS_ID)
      let dataSS = ws.getSheetbyName(DATA_SS_NAME)
      let lastRow = dataSS.getLastRow()
      let url = dataSS.getRange(lastRow,COL_EDIT_LINK).getValue()
      if(url=="") dataSS.getRange(lastRow,COL_EDIT_LINK).setValue(responseURL)
      }

    • @vinaysavla
      @vinaysavla 11 วันที่ผ่านมา

      I am facing an error here.. A blank row gets added below all the rows for which there is data that is entered and then the EDIT URL for all the new cells gets updated in that cell against the blank row and not against which the edit is made..

  • @yoav-vv
    @yoav-vv 6 วันที่ผ่านมา

    super helpful! Thank you😁

  • @krpdi3m
    @krpdi3m 4 หลายเดือนก่อน +1

    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
      @practicalsheets  4 หลายเดือนก่อน

      Let me know how it goes!
      Kind Regards

  • @Raju-q2u
    @Raju-q2u 2 หลายเดือนก่อน +1

    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
      @practicalsheets  2 หลายเดือนก่อน +1

      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

  • @ahlannurhamzah1631
    @ahlannurhamzah1631 3 หลายเดือนก่อน +1

    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
      @practicalsheets  3 หลายเดือนก่อน

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

  • @agenjova7162
    @agenjova7162 8 หลายเดือนก่อน +2

    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
      @practicalsheets  6 หลายเดือนก่อน

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

  • @GiuseppeCristino
    @GiuseppeCristino 6 หลายเดือนก่อน +1

    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
      @practicalsheets  6 หลายเดือนก่อน

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

  • @big1ne
    @big1ne 6 หลายเดือนก่อน +1

    Great, worked amazingly

  • @lauyell
    @lauyell 3 หลายเดือนก่อน +1

    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
      @practicalsheets  3 หลายเดือนก่อน

      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
      @vinaysavla 2 หลายเดือนก่อน +1

      @@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
      @isnanfajarmuaddin2689 หลายเดือนก่อน

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

    • @ianyates4097
      @ianyates4097 หลายเดือนก่อน

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

    • @ameliashofi7040
      @ameliashofi7040 21 วันที่ผ่านมา

      @@isnanfajarmuaddin2689 can you teach me how please?

  • @sandeepkumarkamboj
    @sandeepkumarkamboj หลายเดือนก่อน +1

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

    • @practicalsheets
      @practicalsheets  28 วันที่ผ่านมา

      Does it throw an error?
      Kind Regards

  • @khannansilvasamy7478
    @khannansilvasamy7478 5 หลายเดือนก่อน +1

    Sir you are legend

  • @SondraDally
    @SondraDally 5 หลายเดือนก่อน

    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
      @SondraDally 5 หลายเดือนก่อน +1

      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
      @SondraDally 5 หลายเดือนก่อน

      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
      @practicalsheets  4 หลายเดือนก่อน

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

    • @vinaysavla
      @vinaysavla 8 วันที่ผ่านมา

      @@SondraDally I am facing same error.. how to correct it? Actually, my sheet is being used by multiple people.. so if someone edits using their edit URL say in Row1 or Row5 still the last row say for eg row250 gets updated.. also new URLs dont get updated and remain blank.. not sure why..

  • @MuratKekec-ok5hr
    @MuratKekec-ok5hr 5 หลายเดือนก่อน +1

    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
      @practicalsheets  5 หลายเดือนก่อน

      Hmmm
      Not that I know of
      Kind Regards

  • @BradWilson-gf3ny
    @BradWilson-gf3ny 5 หลายเดือนก่อน

    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
      @practicalsheets  4 หลายเดือนก่อน

      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
      @BradWilson-gf3ny 4 หลายเดือนก่อน +1

      @@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
      @practicalsheets  4 หลายเดือนก่อน +2

      @@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
      @practicalsheets  4 หลายเดือนก่อน

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

  • @girishyanamala9361
    @girishyanamala9361 3 หลายเดือนก่อน +1

    Thanks🎉

    • @practicalsheets
      @practicalsheets  3 หลายเดือนก่อน

      Thank you for commenting!

  • @GhaziFarouk
    @GhaziFarouk 4 หลายเดือนก่อน +1

    thank you sir

  • @josefernandocarreromartin5349
    @josefernandocarreromartin5349 6 หลายเดือนก่อน +1

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

    • @practicalsheets
      @practicalsheets  6 หลายเดือนก่อน

      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
      @big1ne 6 หลายเดือนก่อน +1

      @@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
      @practicalsheets  5 หลายเดือนก่อน +1

      @@big1ne Not yet. In the queue
      Kind Regards

  • @khannansilvasamy7478
    @khannansilvasamy7478 5 หลายเดือนก่อน +1

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

    • @practicalsheets
      @practicalsheets  5 หลายเดือนก่อน

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

  • @MuratKekec-ok5hr
    @MuratKekec-ok5hr 5 หลายเดือนก่อน +1

    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
      @practicalsheets  5 หลายเดือนก่อน +1

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

    • @MuratKekec-ok5hr
      @MuratKekec-ok5hr 5 หลายเดือนก่อน +1

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

    • @practicalsheets
      @practicalsheets  5 หลายเดือนก่อน

      @@MuratKekec-ok5hr Hmmm
      I shouldn't be too hard
      Using the trigger on Form Sent in Sheets, you could follow this tutorial
      th-cam.com/video/k0RS4oPzy80/w-d-xo.html
      Kind Regards

  • @jeanswart8508
    @jeanswart8508 6 หลายเดือนก่อน +1

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

    • @practicalsheets
      @practicalsheets  6 หลายเดือนก่อน

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