Question

I need help getting info from Jotform into a manageable format for invoicing guests attending a party.


Thank you in advance for any help ya’ll can lend.

I need help getting info from Jotform into a manageable format for invoicing guests attending a party.

 

Setup: I receive a filled in form with a list of up to 32 different guests with the following info: Name, Email which is then sent over to an google spreadsheet.

 

Challenge :  I need to take , essentially, a bunch of disparate columns of info on one submission for all guests, and add each guest as a separate row on a spreadsheet.

 

So instead of 

Submission 1 Guest 1 Name Guest 1 Email Guest 2 Name Guest 2 Email

 

I’d like it to convert to : 

Guest 1 Name Guest 1 Email
Guest 2 Name Guest 2 Email

 

I’ve explored using formatter and the Line Itemizer, but can’t get them to appear on separate rows. I need to do this for up to 32 different guest on one form submission.


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

10 replies

Userlevel 7
Badge +14

Hi @R. Stone 

Good question.

Can you post screenshots with how the data is returned from the Zap trigger?

Sounds like you may have to use a Code step to handle and transform the data: https://zapier.com/apps/code/help

Hi @R. Stone 

Good question.

Can you post screenshots with how the data is returned from the Zap trigger?

Sounds like you may have to use a Code step to handle and transform the data: https://zapier.com/apps/code/help

Hey Troy, 

I could, but the results I have gotten, just end up working for one guest, and I can’t seem to figure how to get it to do each and every guest. 

I can’t get it to give me multiple rows of data using the methods above. There isn’t even an option on the google spreadsheets step to be able to put in multiple rows. In other attempts, I was able to get something like this (see below)

 

If I have 2 guests, it looks like this: 

 

Guest1Guest2 Guest1emailGuest2email
   

 

Userlevel 7
Badge +14

@R. Stone 

We’d need to be able to see screenshots with how the data is returned from your Zap trigger step in order to be able to evaluate and advise, thanks.

 

@R. Stone

We’d need to be able to see screenshots with how the data is returned from your Zap trigger step in order to be able to evaluate and advise, thanks.

No problem - 

 

Here is what it looks like coming through as just one submission. I am stuck on how to get it to do this for muliple guests and come through on separate rows.

 

Ahh, here is the screenshot form the Trigger Step.

 

Userlevel 7
Badge +14

@R. Stone 

Have you tried using this Zap trigger: Jotform - New Submission

 

 

Zap Steps

  1. Trigger: Jotform - New Submission
  2. Action: Code
  3. Action: GSheet - Create Row(s)

Hey Troy!

 

I haven’t tried that one - I just had it firing based on when a new row was formed (sent from Jotform on submission of the form) Is there an advantage to doing it with the jotform submission rather than on new row from Gsheet? 

The coding is the thing that worries me. I’m completely lost on that one 😕 I will try that out - I guess there aren’t any good no-code solutions on this one? Thanks again for your help!

Userlevel 7
Badge +14

@R. Stone 

Go with the Jotform - New Submission Zap trigger as it will provide the data structured differently than how it is sent to the GSheet.

Yes, the Code step is advanced: https://zapier.com/apps/code/help

Considering hiring a Zapier Expert: https://zapier.com/experts

Thanks Troy - I may have to get some assistance. Thanks!

Okay, so I have managed to find this solution by another member, but can’t quite seem to figure out how to get it to work for my needs. Could anyone lend a hand? Or point me in the right direction? (see below for copy paste of the thread) or go to this link here : 

 

Here is my current input:

 

And the code that may be a good starting point for what I need looks something like this: 

let RE = new RegExp(/\[\"(\w{2,3})\"\]/, 'g'),
COMPLETED_STR = inputData.COMPLETED.toString(),
COMPLETED,
KEYS = ["A", "B"],
RESULT = {};

let i = 0;
while ((COMPLETED = RE.exec(COMPLETED_STR)) !== null) {
RESULT[KEYS[i++]] = COMPLETED[1];
}

output = RESULT;



 

In your case, you should just be able to do a JSON.parse(); in a code step.

1. Convert to ASCII

 

Probably should mention for anyone looking at this answer - the Input value would normally be the JotForm field from the trigger - I used the example text directly in my example.

2. Parse JSON

 

 

3. Add rows as line items

 

 

All works ok :D
 

 

----

The other challenge with JOTFORMS is when the data comes back in this format  [“...”]|[“...”] 

Notice the | symbol separating the values.

I needed to use a Regular Expression to extract the values, and then assign them to the RESULT object as I needed them.
 

 

let RE = new RegExp(/\[\"(\w{2,3})\"\]/, 'g'),
COMPLETED_STR = inputData.COMPLETED.toString(),
COMPLETED,
KEYS = ["A", "B"],
RESULT = {};

let i = 0;
while ((COMPLETED = RE.exec(COMPLETED_STR)) !== null) {
RESULT[KEYS[i++]] = COMPLETED[1];
}

output = RESULT;