How can I integrate multi-field questions with Zapier?

  • EUSdata
    Asked on March 28, 2018 at 8:20 PM

    I currently would like to integrate Zapier with google form; this has worked fine in the past, however there's one problem I'm running into. The results of input tables are concattenating into one response, e.g. a multitable with 6 questions will yield one bulk response as 

    Slighly Disagree|Slighly Disagree|Neutral|Slighly Disagree|Slighly Disagree|Slighly Disagree

    This is crucial for constant integration, and I can't seem to find an easy workaround :/. I do not want to add the "Form Calculation" widget for every answer, that'd be too verbose (we're talking about a 86 question survey). Any tips??

    Jotform Thread 1429164 Screenshot
  • EUSdata
    Replied on March 29, 2018 at 12:34 AM

    Alright in the short I created a regular expression equation to catch the different answers. 

    All of the answers are coming into one spreadsheet, OG, and being copied into Master by reference (e.g. Master!K2=OG!K2). Then, on the master spreadsheet, I created the regular expression to refer to the one cell that contains all the information for each input table.

    Let's say All of input table 1 is stored in OG!L3, which has values for the three columns in Master!L3:N3.

    OG!L3="Slightly Disagree|Strongly Agree|Neutral"

    Master!L3=regexextract(OG!$L$3,"([[:alnum:],\s]+)\|")
    Master!M3=regexextract(OG!$L$3,"[[:alnum:],\s]+\|([[:alnum:],\s]+)\|")
    Master!N3=regexextract(OG!$L$3,"[[:alnum:],\s]+\|[[:alnum:],\s]+\|([[:alnum:],\s]+)")

    Regexextract arguments:
    regexextract(Cell/reference,"Regular Expression")

    Regular expression:
    [[:alnum:],\s]+\|[[:alnum:],\s]+\|[[:alnum:],\s]+
    This expression covers all the potential results the OG!L3 could contain without having to create each possible option. It's broken down so that you can "capture" any individual response, using the pipe character ( "|" ) as a buffer between answers.
    To select which answer you want to pull, just put parentheses around the section you're looking for.

    Unfortunately because Google Sheets only supports RE2, and it will return the first item in parentheses, it could not be shortened any further.

    I wish I had a more straightforward answer, but this worked best for me.

    Best of luck to you all out there!

    @jotform team, if your spreadsheet exports can parse input tables into multiple columns, so should zapier's api! Get on that team, I love your stuff!

  • Adrian
    Replied on March 29, 2018 at 7:58 AM

    I am glad you have managed to find a workaround.

    @jotform team, if your spreadsheet exports can parse input tables into multiple columns, so should zapier's api! Get on that team, I love your stuff!

    Yes, it is true that the Input Table rows are displayed in separate columns when you export the submission data to Excel from the submissions page but I am not sure why Zapier cannot make that distinction. The pipe character between the answers seems to have helped you, however, extracting the data from the spreadsheet.