Google Spreadsheet Formula for generating edit link

  • studentuniontest
    Asked on August 29, 2014 at 12:27 PM
    sorry not really sure I understand that - is there an example I can see on a  spreadsheet?
  • Kiran Support Team Lead
    Replied on August 29, 2014 at 3:21 PM

    You can achieve this by using the below formula.

    ="http://www.jotform.com/form.php?formID=FORMID&sid="&Submission ID Cell No&"&mode=edit"

    Please see the screenshot (Click on it to enlarge) below :

    Google Spreadsheet Formula for generating edit link Image 1 Screenshot 30

    Here the submission ID is in the Column I. So, the submission ID shall be I2, I3 and so on for each row. You can get the respective form ID from the form edit mode :

     

    Google Spreadsheet Formula for generating edit link Image 2 Screenshot 41 

    Hope this information helps. Please get back to us if you need further assistance. We are happy to assist you.

  • UoNSU
    Replied on September 2, 2014 at 9:44 AM

    Hi 

     

    many thanks - I've done this, and made it into an array formula so that it auto-copies to every row

    However when i click the link it says formula not valid

     

    this is the formula i have put 

     

    =ARRAYFORMULA("http://www.jotform.com/form.php?formID=30711732068954&sid="&BH2:BH&"&mode=edit")

     

    any thoughts on where i might be going wrong?

  • KadeJM
    Replied on September 2, 2014 at 10:28 AM

    Please allow me some additional time to look into this problem for you and I will get back to you about it briefly.

  • KadeJM
    Replied on September 2, 2014 at 11:47 AM

    Sorry about the delay. The original formula plus shifting to your array confused me slightly at first but I think I may have figured it out more now.

    From what I found I believe it's not valid because the array is causing the reference link to fail since your #ref cell was getting parsed incorrectly.

     

    You had:

    BH2:BH

    I'm not exactly sure what your BH is but it needs to reference your column cells to pull the appropriate sequential data. This means that it should actually be something like:

    =ARRAYFORMULA("http://www.jotform.us/form.php?formID=42443973435156&sid="&G2:G4&"&mode=edit")

     

    Here's a Screenshot:

    - Note that I have referenced the submission id column to pull and populate that data into the form's edit link which is now also hyperlinked together.

    Google Spreadsheet Formula for generating edit link Image 1 Screenshot 20