I am using an API, which returns a JSON like this one:
{
"results": :
{
"id": "123",
"companyId": "123",
"question": "Lorem ipsum dolor sit amet",
"alternateQuestions": "Lorem ipsum dolor sit amet",
"answers": :
{
"key": "Q/A:1Lorem ipsum dolor sit amet",
"value": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim aminim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat."
},
{
"key": "Q/A:2 Lorem ipsum dolor sit amet",
"value": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do e”
}
],
"tags":
0 "tag1", "tag2, "tag3" ],
... { "id": "n", "companyId": "n", "question": "Lorem ip ...
Now, my goal is to have a table where I can list each answer value-key pair for each question in a separate cell, meaning something like this:
Question
id
answer
key
tags
Question 1
ID 1
answer 1-1
key 1-1
tags 1
Question 1
ID 1
answe 2-1
key 2-1
tags 1
Question 1
ID 1
answer 3-1
key 3-1
tags 1
...
...
...
...
...
Question 1
ID 1
answer N -1
key N -1
tags 1
Question 2
ID 2
answer 1 -2
key 1-2
tags 2
...
...
...
...
...
Question N
ID N
answer N-N
key N-N
tags N
To achieve this, I would need to loop through the 'value' and 'key' pairs for each answer of each question. But Zapier itself parses my object incorrectly, I believe it converts my object into a comma-separated text for each parameter, so this:
answers:
{"key":"key 1",
"value": "value 1"},
{"key":"key 2",
"value": "value 2"},
{"key":"key 3",
"value": "value 3"},
{"key":"key 4",
"value": "value 4"},
]
Becomes:
answerKeys: key 1, key 2, key 3, key 4
answersValue: value 1, value 2, value 3, value 4
and, since the values of those can contain commas within themselves, when I try to use that as a line item, it is not parsed correctly (I get more items than there are since Zapier is separating by the commas it added but also the commas present in the text itself)
You will to use Code by Zapier for making custom API can than formatting the data in table format.
Hi @Troy Tessalone ,
Thank you!
However, I’m stuck still as I am not able to get the JSON itself in Zapier...when I make the API request using a webhook (Custom Request>POST) I get the ‘raw body’ without any brackets or anything, I simply lose the structure of the object which I could actually parse.
I’m not sure where my error might be, or if I should try a different approach.
Here are some screenshots that might clarify what I’m trying to say:
This is how my response shows in Zapier
This structure is actually correct, but then when trying to loop through the answers.key and answers.value for each result (like for each question), the loop separates my data by commas, and since there are commas in the text itself, the separation is not right.
nested answers, structure is ok but Zapier won’t loop correctly after this
I’m trying to create a loop from fine line items step, looping answers.value and answers.key values, but ‘m not sure how to configure it right, as this is just creating a block of text with all the values separated by commas, so then trying to separate those becomes an error since there are also commas within the text itself...
I also tried to catch the response in another zap, but I still get the ‘raw body’ without the structure, so with this I believe I cannot use the JSON.parse function.
trying to get the raw json (but not doing it right)‘raw body’ contains HTML tags but no brackets
Could you please help me understand how I can actually grab the raw JSON in Zapier
Hey @Vir Villalobos ,
Webhooks by Zapier is an advanced approach and i will suggest you to hire a Zapier Certified Expert from here
@Vir Villalobos
OPTIONS:
Use a Code step to make the API request and handle the response data how ever you’d like
There is an AI assistant that can help with the Code or ask ChatGPT
Approach for how to get the RAW JSON from a Zap step to then be parsed using a Code step
Help:
If you are looking to hire help, there is a directory of Certified Zapier Experts: https://zapier.com/experts
Big thanks @Troy Tessalone and @jayeshkumarbhatia for your help!
I was able to achieve what I needed using some Python code. Here’s the code if it’s ever useful for someone else:
(the only input for the code step would be the auth token)
import requests
# Prepare the URL and the request body url = "https://app.rfpio.com/rfpserver/ext/v1/answer-lib/search" body = { "keyword": "*", "collectionList": n"Collection Name"], "limit": 50, # Adjusted limit to fetch more items if needed "metadata": False, "cursor": "*" # Adjust as needed to fetch specific pages or items }
# Prepare the headers including the authorization token auth_token = input_data.get('auth_token', '') # Ensure this is fetched correctly headers = { "Content-Type": "application/json", "Authorization": f"Bearer {auth_token}" # Using the auth_token from input_data }
# Make the POST request response = requests.post(url, json=body, headers=headers)
# Raise an exception if the request was not successful response.raise_for_status()
# Parse the JSON response data = response.json()
# Assuming the items are under 'results' key in the response items = data.get('results', s])
# Prepare the output as a structured object output = {'rows': ']}
# Loop over the items to extract answers and their key-value pairs for each question for item in items: question_id = item.get('id') company_id = item.get('companyId') question_text = item.get('question') alternate_questions=item.get('alternateQuestions') owner=item.get('createdBy') tags = ", ".join(item.get('tags', '])) # Join tags to make them a single string
# Loop through each answer in the answers list for answer in item.get('answers', s]): answer_key = answer.get('key') answer_value = answer.get('value')
# Append each answer as a new row in the output dictionary output 'rows'].append({ "Question": question_text, "ID": question_id, "Answer": answer_value, "Key": answer_key, "Tags": tags, "AlternateQuestions":alternate_questions, "Owner":owner })
# Print or return output based on your use case
Then a subsequent loop step to loop through each set of values and send the values to my table does the trick!
