Skip to main content
Best answer

Help with API Data, Multiple Arrays combining into Comma Separated text in Zapier

  • 19 July 2024
  • 6 replies
  • 44 views

Hello! I am very new to APIs and JSON. I’m hoping that someone can confirm what I think the solution to my problem is. I suspect that the issue described below needs to be dealt with using Javascript code in the Zapier Integration I created.

The integration pulls data through an API request using a Presentation ID#. The data is a sales presentation with multiple Promotional items and complex pricing details. Each item uses the same signifiers for qtys, sell prices, additional charges, etc. Some output arrays of different lengths, others a single value.

Zapier is grouping the like named data from all Promo items into comma separated text. Due to the variations in array sizes, I can’t easily pull the data apart in Zapier.

Below is a significantly simplified example of the received JSON data.

Here are examples of how the data is being groupedin Zapier. This is from a presentation with four Promo Items. The sample JSON data is two items to reduce the number of lines.

Please note this data will be used for immediate output and not stored. The data doesn’t need to be unique across multiple requests.

 

I’m assuming that the answer to this problem is adding a prefix to “qtys” from either the “sortOrder” or “presItemId” value to create “0qtys” or “47600211qtys” respectively.

I’m hoping someone can confirm my assumptions and let me know that this has to be done through Javascript in the Zapier integration side, not the Zap side.

Thank you ahead of time to anyone that can offer help!

 

{
  "presentations": j
    {
      "presId": 6835678,
       "status": 4,
      "client": {
        "clientId": 3357473,
        "company": "Merlin Magic",
        "phone": "512-555-5885",
      },
      "itemCnt": 4,
      "items": t
        {
          "presItemId": 47600211,
          "sortOrder": 0,
          "name": "Pinnacle Tumbler",
          "itemNum": "RWFGH-DBVYP",
          "includeItem": 1,
          "qtys": t
            "24",
            "100",
            "150",
            "250",
            "400",
            ""
          ],
          "sellPrcs":  
            "25.18",
            "24.18",
            "22",
            "22.18",
            "19.98",
            "0"
          ],
          "addChgTotals": <
            "133.74",
            "450.64",
            "133.74",
            "133.74",
            "1549.64",
            "0"
          ],
          "totals":  
            "738.06",
            "2868.64",
            "3433.74",
            "5678.74",
            "9541.64",
            "0"
          ],
          "setupChg": "55",
          "shipChgs": 5
            "78.74",
            "395.64",
            "78.74",
            "78.74",
            "1494.64",
            "78.74"
          ],
          "supplier": {
            "sageId": "52344",
            "company": "PCNA",
            "phone": "800.555.1555",
            "email": "example@pcna.com",
          }
        },
        {
          "presItemId": 47600212,
          "sortOrder": 1,
          "name": "Summit 40 oz Travel Mug",
          "itemNum": "UGQBD-FNFRX",
          "qtys": -
            "20",
            "100",
            "240",
            "500",
            "1000",
            ""
          ],
          "sellPrcs":  
            "18.5",
            "17.45",
            "16.95",
            "16.45",
            "15.95",
            "0"
          ],
          "addChgTotals": <
            "148.86",
            "371.01",
            "60",
            "60",
            "60",
            "0"
          ],
          "totals":  
            "518.86",
            "2116.01",
            "4128",
            "8285",
            "16010",
            "0"
          ],
          "setupChg": "60",
          "shipChgs": 6
            "88.86",
            "311.01",
            "0",
            "0",
            "0",
            "0"
          ],
          "supplier": {
            "sageId": "60462",
            "company": "Ariel Premium Supply Inc",
            "phone": "800.555.7474",
            "email": "example@arielpremium.com",
          }
        }
      ]
    }
  ]
}

 

 

 

6 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Dented.Can 

Can you clarify what you are trying to do with the data from the API in following Zap steps assuming you can get it into the desired format via Formatter/Code steps.

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
D

Hi @Troy Tessalone 

The data would be entered into a copied Google Sheet template via Zapier. Some calculations would be performed there. Users would then copy and paste only pertinent data into fields in another program.

Each Promo Item would be on its own sheet. I’ve linked a sample below that contains all the data that would be pulled through the API. I’m adding a screen shot as well.

“Red” = String of text API Values
Red = integer API Values
Blue = Calculated Values
---- = are Null Values in the API data
0 or $0.00 = “0” values in the API data

All values in columns B through G are sent as an array with a common name for each Promo Item. Zapier is combining all values into a comma separated text.

One of the issues I’m seeing is that Null values aren’t being included in the Zapier data. While the number of columns is static, the number of quantities entered is not. If I have two products of with quantities of 10 and 20, and then another with 10, 40, 80 I get the following text in the Qtys and Item Cost fields.

Qtys = “10,20,10,20,10,40,80”
Item Cost = “50,100,0,0,0,0,50,100,0,0,0,0,50,200,400,0,0,0”

The data doesn’t let me know how many quantities were in each product, so I can’t separate the Qtys field correctly.
If Item 1 and 3 have a setup charge, but item 2 does not, the data looks like this.

Setup = “55,0,55”

https://docs.google.com/spreadsheets/d/1Hr0G6DXoIThekjWAA0vxuNQAYUN5eIVhdhtRxv66e8M/edit?usp=sharing

 

Troy Tessalone
Userlevel 7
Badge +14

@Dented.Can 

Zapier is combining all values into a comma separated text.

This is how Zapier displays an array of items within a variable.

 

Info about handling line items in Zaps:

 

If you use a Code step to make the API request, then you can handle the parsing and prep of the data in the Code step before outputting as your desired variables.

 

Or you may have to try this workaround to get the RAW JSON, then you can handle the RAW JSON with Code to parse and prep as desired.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Troy Tessalone
Userlevel 7
Badge +14

@Dented.Can 

Also, GSheets needs to be configured in a specific way to be used in Zaps:

https://help.zapier.com/hc/en-us/articles/8496276985101-Work-with-Google-Sheets-in-Zaps

 

Otherwise, you have to use the GSheets API to add data to specific rows/columns.

https://developers.google.com/sheets/api/guides/concepts

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
D

@Troy Tessalone 

Thanks for taking the time to respond in detail!

I had already reviewed the information about how to handle arrays with line items in the Zapier support documents. Sadly, I don’t think Looping nor Formatter will help due to the varying number of array items across different areas and how Zapier handles Null values.

The data is not coming through a web hook. There is a form that generates a Presentation ID #, and then uses a custom integration that I created to request the data from the API. Without a webook, it looks like that method won’t work.

Which leaves me with the coding option, which is what I thought was needed. Rather than adding code with the Code zap, should I instead include that parsing code as part of my private integration? That way the request sent as a Zap trigger in Zapier would return clean data from the beginning?

Also, thank you for showing me the Google Sheets API. That confirmed what I would need to do for complex data entry into a spreadsheet.

Troy Tessalone
Userlevel 7
Badge +14

@Dented.Can 

Rather than adding code with the Code zap, should I instead include that parsing code as part of my private integration? That way the request sent as a Zap trigger in Zapier would return clean data from the beginning?

If you have that ability, then yes that would probably be the best place to address the data.

 

If you are just using an app’s API, you may not need a custom Zap app integration built on the Zapier Developer Platform.

 

The Webhooks Zap app supports webhooks and APIs.

 

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor

Reply


Terms & Conditions