Best answer

How do I separate a JSON array data into Google Sheets columns?

  • 1 February 2023
  • 4 replies
  • 610 views

Userlevel 1

### Description

I can create a new Google Sheets row with a simple curl command.

curl -X POST -H "Content-Type: application/json" -d '{"Value1": "Example1", "Value2": "Example2", "Value3": "Example3"}' https://hooks.zapier.com/hooks/catch/your_zapier_webhook_id/your_zapier_webhook_token/

 

### Current Behaviour

It creates a new row without the data parsed separately into individual columns.

 

### Expected Behaviour

I would like the create 4 columns of data: timestamp, Example1, Example2, Example3. How can I do so?

Thanks for your help!

 

icon

Best answer by Troy Tessalone 1 February 2023, 03:14

View original

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

4 replies

Userlevel 7
Badge +14

Hi @sayanee 

Good question.

Try changing the Zap trigger to Catch Hook, instead of Catch Raw Hook.

 

GSheets supports line items if you use this Zap action:

 

Otherwise, the Looping app can be used to handle line items: https://zapier.com/apps/looping/help

 

Userlevel 1

Thanks for your clues, @Troy Tessalone!

I have not been able to do it successfully. I have 3 questions.

  1. How do I setup the field “Pick off a Child Key” in the Trigger “Catch Hook” with my incoming JSON structure as `{"Value1": "Example1", "Value2": "Example2", "Value3": "Example3"}`? The Test Trigger step gives an error that the list is malformed. I am willing to re-structure the JSON that I am sending via the CURL command. Maybe it should be `

    {"data":[

    {"Example1":"Value1"},

    {"Example2":"Value2"},

    {"Example3":"Value3"}

    ]}`.

  2. In the “Create Multiple Spreadsheet Rows in Google Sheets” what should I put in the text fields “Value1”, “Value2” and “Value3”? 
  3. And finally, I am guessing I need to put the loop after the trigger Catch Hook, right? So there will be 3 steps: 1. Catch Hook, 2. Loop, 3. Create Multiple rows in Sheets.

 

 

Eventually I want the spreadsheet to be filled with the data into separate columns like in Row 9 below: 

 

Many thanks for your clarifications once again!

Userlevel 7
Badge +14

@sayanee 

This field is optional, try leaving it blank, then test.

 

Userlevel 1

Thanks @Troy Tessalone. My curl command is now creating split columns from the JSON data 😃 And I didn’t need to use loop at all.