Question

Transform a big data sample --- Webook --> JSON --> Google Sheet


Hello there!


I have 2 questions:

  • How can I get nested JSON data from a GET zapier webhook?
  • How can I transform this nested JSON data into a google sheet


Background Information

I am trying to find a way get data through a GET webhook, this can't be a “Catch hook” trigger because I can only use API Webhooks. I managed to do this but now I want to put this big data sample in google sheet in rows and columns. I see a lot of tools online to convert this with Nested JSON output, but I cant find a way to do it in Zapier, who can help me?

If you know a better way to get the job done that's also fine, als long as I can import the data in a google sheet through a webhook.


16 replies

I partly fixed it. I now was how I could add rows as in line items in google sheets. This is working pretty well, apart from the custom fields I cannot filter on. This shouldnt be a problem, but because there are multiple custom fields, instead of them being seperated bij a “,” they are also seperated bij a “[“”]”. The problem is though, if I create a row (with or without line item support), it doesnt work and i just get the entire text, without the null (which is I also would like to have there as empty columns) and the “[“”]” is gone so I have no idea which belonges to which line.

Can someone help me to seperate this or insert this entire string so I can separate it in google sheets?

Userlevel 5
Badge +5

Hi @Frisse Blikken, thanks for reaching out!

Tagging in @ikbelkirasan who is a coding guru and may be able to assist here. @ikbelkirasan any ideas? Thank you!

Userlevel 7
Badge +6

@Frisse Blikken do I understand correctly that you want to split the Custom Fields Value into 12 values so you can map the 12 values to 12 different columns? 

 

In splitting it up you’d want to preserve order and the null values, correct?

Is there a corresponding field called Data Custom Fields Name or Data Custom Fields Key that contains the names of the custom fields?

 

If so this is less about nested JSON or transforming JSON to a Google Sheet and instead how to deal with custom fields where the field names and field values come in as two separate arrays. ← this is a common problem and there are workarounds for this, but I just want to clarify if I have the problem correct?

 

thanks

Hi @PaulKortman ,

 

Thanks for helping out! Hereby my answers

 

do I understand correctly that you want to split the Custom Fields Value into 12 values so you can map the 12 values to 12 different columns?  Yes, correct

In splitting it up you’d want to preserve order and the null values, correct? Also correct

Is there a corresponding field called Data Custom Fields Name or Data Custom Fields Key that contains the names of the custom fields? The output looks like this if thats what you mean: 

Turning it into a Google sheet, I have to make the headers myself and in the webhook they stick together as a group of “custom values” (as you can see in the screenshot in my second post)

If so this is less about nested JSON or transforming JSON to a Google Sheet and instead how to deal with custom fields where the field names and field values come in as two separate arrays. ← this is a common problem and there are workarounds for this, but I just want to clarify if I have the problem correct? Well pretty much ye, at first I had no clue about any of this, but I slowly improved and now this is the last thing I need to figure out and then its perfect.

Userlevel 7
Badge +6

@Frisse Blikken It’s a bit cumbersome, but here’s a solution using two zaps and two spreadsheets. 

 

The theory is if you created new rows in a temporary sheet with just the “Data Custom Fields Name” and the “Data Custom Fields Value” as new rows in a spreadsheet. That should line up the names of the custom fields and the values (even the nulls) in a verticle listing in that spreadsheet? To be clear, map the Name to column 1, and the value to column 2. 

 

If that works there’s a possible workaround. The sheet above I will refer to as the temp-sheet. We would add a third column into that zap step for the temp-sheet with the project number.  this way we know that the 12 (or how many ever) rows are connected to that project number. 

here’s s fake example of the temp-sheet

 

What we can do is have a second zap that triggers off of a new row in this temp-sheet and uses Stroage by Zapier to store those values into a variable using the key of “Project number”-Name (in the example above it would result in personxyz stored in 91-Owner, and person123 stored in 91-Manager)

this would create 12 keys, one for each of the custom fields in a project.

 

So in your main zap as you have it right now, insert a step above where you are adding the row to google sheets and have it add the rows to the temp-sheet.

Then have a delay step. To be safe I would delay for 2 minutes. This allows for the second zap to fire on all 12 of the new rows created in the temp-sheet and store those values in storage. 

Add a storage step after the delay that Gets Multiple Values and code in the Projectnumber-Custom Field name

the results of this step will be the 12 values lined up with the 12 names

You can then use the output of that storage step for the columns in the original Spreadsheet you are writing to. 

 

Extra Credit: 

At the end of the main zap you should have another step of Storage by Zapier that Sets Multiple Values, and use the same Key names as above (“Project number”-Owner etc) but leave the values blank. This has the effect of unregistering those keys from storage. This will prevent your storage account from filling up (I think the limit is 500 keys) 

Hey @PaulKortman ,

Unfortunatelly I'm already stuck with the 1st thing I have to do, below are screenshots, what am I doing wrong? Or can it just not be done?

Hope you can help, I really appriciate all the effort you have put in to this so far!

Userlevel 7
Badge +6

@Frisse Blikken okay that didn’t work, take out the project number (leave that column empty in step 5) and test that step again… see if it breaks out the custom field name and values into rows as it should. 

 

Backstory:

I wasn’t expecting to see two values in the project number field, we need a single value there which will repeat for each row, if we give it a line-item list (which is what it is right now) then it will not function the way we want it to.

@PaulKortman Cutting out the project number doesnt break up the values unfortunatelly..

Userlevel 7
Badge +6

@Frisse Blikken bummer, 

 

okay then there are a couple of things we can test/try out. If you just use the names for now (take out the values from step 5 as well) 

Add a formatter > text > replace step infront of step 5 and take the names (output of step 2) as the input and [ as the character to replace but leave the replacement field with nothing. 

 

Then add another formatter > text > replace and use the output of the previous formatter step and replace the ] with nothing. 

 

then use the output of that formatter step in the spreadsheet and see if it breaks it into rows. 

 

(this is just a test, I’m trying to discover how Zapier is seeing this data)

Userlevel 7
Badge +6

if that doesn’t work the next thing to try is to add a step between the last formatter step and the spreadsheet step that converts a text into line item (Formatter > Utilities > text to line item) and use the output of the second formatter and then use the output of this step in the Google sheet.  (again this is just a test)

@PaulKortman Both don't work unfortunatelly.

 

The first idea: It gets processed without the [, so they cant be removed according to zapier

The second idea: I get the same as I get with the other attempts:

 

Userlevel 7
Badge +6

@Frisse Blikken I’m sorry I keep getting this wrong, but that last screenshot helped me remember what the problem is here. 

 

Okay, lets try this: 

add a formatter > Utilities> line Item to text and use just the names as in input. 

The output of that will give Item one… you can use just item_one in the spreadsheet step and it should split it out. 

 

The reason for this is in the screenshot you sent I see sub-line-items. Theres a 0: with 7 line items, and a 1: with 7 line items. (etc) we just need to grab the line items from the first item and then it will split them out as you need. (In theory)

Hey @PaulKortman ,

 

I get the idea and I think were close, but it doesn't seem to work out just yet:

 

 

Userlevel 7
Badge +6

I know it might seem counterintuitive but after you’ve split it from line item to text you now need to convert the text to line items. 
 

So the idea is to have a step in front of the google sheets step that is formatter > utilities > text to line item. And use the item_1 as the input. 
 

Then use the output of that in the google sheet. 

@PaulKortman n This kinda works for the names, but not for the values, because the null-values are filtered out by the convertion from line-items to text

@ikbelkirasan Hey, I saw you were tagged earlier, maybe you can help? Were close, but we cant quite get the job done.. You can read in more detail what I need, but there might be some random information, so here is what I want in scope:

This is the output I get from my webhook, this is for 2 projects now, but with the real sample it will be 200+

What I want is to put this data in a google sheet like this:

Now manually put therem which I do not intent on doing for 200+ projects

 

Maybe @PaulKortman can help with this additional information as well..

 

Thanks guys!

Reply