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 “s“”]” 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?

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!

@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.

@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. 

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)

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

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!

@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..

@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 a 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)

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 e, so they cant be removed according to zapier

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

@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:

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!

Sorry for the late reply

The conversion to line items will kill orders and mess with null values, but typically only really badly if the first item is null. 

So in the past, I’ve used some funky logic to mess with/replace the first value if it’s null so as to keep the order of the values in a line item like what you are dealing with. 

Can you share a screenshot of what you get as the output of Text to Line Items for the Names and for the Values? From one of your screenshots it looks like the first value is person:####, followed by nulls. Is that true and what does the output of the Formatter step do with that?

Hey @PaulKortman , no worries! Im already forever in your debt for helping me out this much.

First the Names

So first I put in the custom field names (Utilities > Line-items to text):

This generates this output:

Then I convert it back (Text to Line-items):

Which gives me this output:

If I Create spreadsheet rows (with line item support) I get it like this:

Which is not ideal, because I'd rather have them as Collums, but that's something we can work around, shouldn't be too hard. 

Now the Values

I put in the custom field values (Utilities > Line-items to text):

The first “null” should appear after 4 values, however, this is the output:

Typed out it looks like this:
item_1
person:3d051a44af24989766550e5701120079,employee:f5587cd5cfc81bae6d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,person:042f83ab3ee8f2ad66550e5701120079,person:ea9dfdf71515254e66550e5701120079,person:ea9dfdf71515254e66550e5701120079
item_2
person:3d051a44af24989766550e5701120079,employee:f5587cd5cfc81bae6d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,person:042f83ab3ee8f2ad66550e5701120079,person:ea9dfdf71515254e66550e5701120079,person:ea9dfdf71515254e66550e5701120079
item_last
person:3d051a44af24989766550e5701120079,employee:f5587cd5cfc81bae6d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,person:042f83ab3ee8f2ad66550e5701120079,person:ea9dfdf71515254e66550e5701120079,person:ea9dfdf71515254e66550e5701120079
text
person:3d051a44af24989766550e5701120079,employee:f5587cd5cfc81bae6d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,person:042f83ab3ee8f2ad66550e5701120079,person:ea9dfdf71515254e66550e5701120079,person:ea9dfdf71515254e66550e5701120079,person:3d051a44af24989766550e5701120079,employee:f5587cd5cfc81bae6d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,employee:39f8e0ed809ce3196d44e34a3f0f8c3d,person:042f83ab3ee8f2ad66550e5701120079,person:ea9dfdf71515254e66550e5701120079,person:ea9dfdf71515254e66550e5701120079
 

Now converting it back won't work because we already lost the null's:

And the output is this:

Giving this result, making spreadsheet rows (with line-item support) look like this:

So were still missing the “null” values. You need anything more?

Please let me know!

@Frisse Blikken So I’m struggling to process how the null values were stripped in the first go round. I know that the following suggestion won’t work, but it’s an attempt on my part to see what’s going on. 

Add a code step (javascript) and set it up like so:

The code block (you can copy/paste this in. 

let keys = Object.keys(inputData)
let data = (];

// loop through each Input Data field
for (let key of keys) {
  // split the contents of each Input Data field on the commas into an array
  let li = inputDataakey].split(",");
  for (let i=0; i<li.length; i++) {
    if (typeof data)i] === "undefined") dataai] = {};
    data i]tkey] = li;i];
    // add a record number (in case we want to break the fork/loop with a Filter)
    datawi].recordNumber = i+1;
  }
}


output = {"data":data};

And then I want you to try two things:

Attempt #1: Put the names and the values strings in there right after they went through the very first line item to text… use item_1 for each.  -- I suspect this will fail as it will still be missing the null values. Please post a screenshot of the output 

Attempt #2: Put the names and values strings in there before they go through the line item to text. -- I suspect this will fail because my code block isn’t taking sub-line items into account. But I want to see what the output of this attempt is. Please post a screenshot of the output. 

Okay @PaulKortman , So I added the code:

Attempt #1: They sort of stick together, but with the wrong names, just leaving out the null-values and attach to the first name in line:

Attempt #2: This seems to do the exact same thing..:

Is this everything you need? Please let me know!

@PaulKortman You have any new angle I can try out?