My sheet integration formula not working, need help

  • Profile Image
    AgilityAssoc.Canada
    Asked on November 08, 2017 at 01:38 PM

    My form 52745887899988 is integrated with google sheets.

    https://docs.google.com/spreadsheets/d/1-4VBKaVZZGqDfAcroVyc4-Bne8GgjI8DfB0bt6vKzys/edit#gid=0

    I have column AG  to combine the CAT number with the Edit Submission link with this formula, 

    =ArrayFormula(HYPERLINK("https://jotformpro.com/edit/"&AE2:AE,"Edit-"&AF2:AF))

    The formula should grab the link and add text "Edit-" to the cat number and shows "Edit-1"

    But it is not working, I don't get the submission number.

    The idea is to have the Form CAT number as the edit submission link as well.

    I believe the problem in the formula is after the /edit/ ,it has no submission number to work with. 

    Any help appropriated...

    Thank you Robert

  • Profile Image
    aubreybourke
    Answered on November 08, 2017 at 03:19 PM

    You will need the form number and the submission number. Also you will need to specify the mode at the end. Try this in your spreadsheet:

    =HYPERLINK("http://www.jotform.com/form.php?formID=52745887899988&sid="&B2&"&mode=edit", "Edit-1")

    You should be able to figure out the rest yourself.




  • Profile Image
    AgilityAssoc.Canada
    Answered on November 08, 2017 at 04:14 PM

    Hi Aubrey,

    The first cell works fine but I require an array to fill the column. Dragging the formula is an option but not preferred. I tried a couple of options but none of them worked, the problem is trying to get the formID into the ARRAYFORMULA by using the column Edit Submission which works on its own but when used in a formula it brakes.. So my next thought was adding the CAT number to the Edit Submission, "Edit Submission-1".

    =HYPERLINK("https://jotformpro.com/edit/3762313831515311129","Edit Submission")&"-"&(AF2:AF)

    As soon as I added &"-"&(AF2:AF) the link brakes, so far no luck.

    Thanks for the option.

    regards, Robert

  • Profile Image
    Marvih
    Answered on November 08, 2017 at 05:49 PM

    Let me do further test on how to get this working, I will get back to you once I have a solution.

    Please reply to this ticket after some time so it will get updated.

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 09, 2017 at 09:55 AM

    Hi Marvih,

    Thank you, there is no rush on this...

    Here is another formula that failed:

    =ARRAYFORMULA(JOIN(AE2:AE," ",AF2:AF))

    AE = Edit Submission, AF = CAT No.

    I get a String of Text "Edit Submission 1Edit Submission 2" and so on.

    Best Regards, Robert

  • Profile Image
    aubreybourke
    Answered on November 09, 2017 at 10:08 AM

    I got it to work like this:
    =HYPERLINK("http://www.jotform.com/form.php?formID=52745887899988&sid="&B2&"&mode=edit", "Edit-"&D2)

    And then dragged the formula.

    For example:

    1510240086AAC Course Approvers(2017-11-0

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 09, 2017 at 11:41 AM

    Hi,

    OK I can use this for now but I really need an Array formula so it populates the column without my having to drag it down every time a new submission comes in. I suppose I could drag it down a long way and leave it at that. perhaps your colleague Marvih,may find a solution also. 

    FYI I had to change the columns in the formula.

    =HYPERLINK("http://www.jotform.com/form.php?formID=52745887899988&sid="&AF2&"&mode=edit","Edit-"&AF2)

      It works fine.

    see column AG

    https://docs.google.com/spreadsheets/d/1-4VBKaVZZGqDfAcroVyc4-Bne8GgjI8DfB0bt6vKzys/edit#gid=0

    and column A where =IMPORTRANGE is used.

    https://docs.google.com/spreadsheets/d/1tRDd0VhKK8PuusshSAccYfyYpdeD7A5gBe_7lU5I-f0/edit#gid=0

    Thank you, Robert

  • Profile Image
    Nik_C
    Answered on November 09, 2017 at 12:41 PM

    I'm afraid that I didn't use "ArrayFormula" until now, so I will have to work on this and get back to you Robert.

    Thank you for your patience.


  • Profile Image
    AgilityAssoc.Canada
    Answered on November 10, 2017 at 06:38 AM

    Hi, 

    After I checked further the formula Aubrey offered did not work. It gives the same submission number for every row. 

    =HYPERLINK("https://jotformpro.com/edit/3762313831515311129="&P2&"&mode=edit", "Edit-"&P2)

    I'm working on another option with a new sheet;

    CA Report 

    Column B has the HYPERLINK 
    Column P has the number,
    Column Q has what should be the result. 
    Q2 cell has the formula above and is for that row only;

    Thanks, Robert

  • Profile Image
    Kevin_G
    Answered on November 10, 2017 at 08:54 AM

    I have checked the last sheet you shared and it seems like you already got it working: 

    15103217701.png

    I was also testing on my end and got this working: 

    =ArrayFormula(HYPERLINK("https://jotformpro.com/edit/"&AD2:AD, "Edit-"&B2:B))

    Here's my cloned sheet from yours (the very first one you shared): https://docs.google.com/spreadsheets/d/1tmNr3-O4J0IhSvvrsmfePCJ8SrcPY0d12qTaCxyNitE/edit?usp=sharing 

    Find the formula on the "AG" column.

    Also, please note that it's not recommended to alter the integrated sheet with any formula or do any change on it since that could break the integration and your spreadsheet may stop updating, I would recommend you to create a copy and use the "IMPORTRANGE" function to get the data synced and apply any change on this sheet instead. 

    I hope this helps. 

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 10, 2017 at 11:16 AM

    Hi,

    I will give your formula a try, I think you have the answer, the formula I have there is not working, it gives the same submission for each row.

    Many thanks, Robert

    PS Yes I have learned the hard way about messing with the integrated sheet...

    RBC

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 10, 2017 at 11:38 AM

    RESOLVED

    Excellent Work As Usual

    Thank You
    Robert