Solved

Grab/format an array/objects from Jotform widget to create multiple Google Spreadsheet rows

  • 18 October 2020
  • 2 replies
  • 47 views

Userlevel 7
Badge +7
  • Community Manager
  • 246 replies

I am utilizing the “Configurable list” widget in Jotform and list items are coming across to my Zap in a single field like so:

 

[{"Name":"Name One","Address":"123 some st.","Tenant Type":"financially responsible"},{"Name":"Name Two","Address":"453 some st. ","Tenant Type":"regular"}]

 

However, I need to reformat them to fit into this format:

 

Any ideas on how I can make this happen?

icon

Best answer by squibler 18 October 2020, 05:28

Hi @jesse,

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;

 

View original

2 replies

Userlevel 3

Hi @jesse,

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;

 

Userlevel 7
Badge +7

@squibler thank you so much! This worked perfectly on the first try. I really appreciate the help - you’re a lifesaver!

Reply