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":
[ "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)
Best answer by Troy Tessalone
@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
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
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": ["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', [])
# Prepare the output as a structured object
output = {'rows': []}
# Loop over the items to extract answers and their key-value pairs for each questionfor 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 listfor answer in item.get('answers', []):
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!
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. This information might be about you, your preferences or your device and is mostly used to make the site work as you expect it to. The information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies. Click on the different category headings to find out more and change our default settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
More about cookies
Customize which cookies to allow
Marketing cookies
These cookies may be set through our site by our advertising partners. They may be used by those companies to build a profile of your interests and show you relevant adverts on other sites. They do not store directly personal information, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less targeted advertising.
Social media cookies
label
These cookies are set by a range of social media services that we have added to the site to enable you to share our content with your friends and networks. They are capable of tracking your browser across other sites and building up a profile of your interests. This may impact the content and messages you see on other websites you visit. If you do not allow these cookies you may not be able to use or see these sharing tools.
Analytics/performance cookies
These cookies enable the website to provide enhanced functionality and personalization, and allow us to count visits and traffic sources so we can measure and improve the performance of our site. They help us to know which pages are the most and least popular and see how visitors move around the site. These cookies may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies then some or all of these services may not function properly and we will not know how you are using our site.
Functional cookies
label
These cookies enable the website to provide enhanced functionality and personalisation. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies then some or all of these services may not function properly.
Essential cookies
Always active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work. These cookies do not store any personally identifiable information.