Smartsheet API

[justinirizarry]

Hi there,

I am connecting to Smartsheet's API to bring in data from a specific worksheet.

I am able to connect successfully but I am having trouble parsing data.

After using the JSON parse tool, I am using the Data Cleansing tool to remove all numbers and letters from the JSON-Name field. Then I am using a tile function to pivot the data on.

That's where I am getting the problem. More precisely, I have 13 seperate columns (which are correctly being scored with the tile tool), but the values that go in the rows under those columns are not being tiled correctly. See attached for example.

Does anyone have a recommendation?

[LordNeilLord]

Hi,

Can you explain what you are trying to achieve with the tile tool?

If you give a quick example of the output you need, that would be great

[justinirizarry]

Sure. I am trying to parse the data coming out of the API so I can see it in traditional column / row format.

There are 13 distinct columns in the data. So I would like to see the tile tool enumerate the rows 1 - 13, repeatedly so that I can use the cross-tab tool to pivot the data.

Does that make sense?

I am also open to any other way to accomplish this (taking the raw API data and manipulating it to a workable format).

[LordNeilLord]

Hi,

The traditional way to parse JSON is to use the text to columns tool to split JSON_Name thereby extracting the number and header...and then using crosstab.

Your data is a little bit more complex as it has the Column Data and then the row data

[LordNeilLord · Accepted solution]

Hey @justinirizarry

I had a bit of time to sit down and look at this and I think this is what you need:

Let me know if that's what you were looking for

[justinirizarry]

Wow! This did it. I completely missed using the text to columns and splitting the headers and values out like you did. Thank you!

[Calliecobbs]

Thank you so much for this workflow! Saved me a bunch of time this evening! The only tweak I had to make was to change the JSON_Name2 and JSON_Name4 fields to be INTs because I have more than 256 rows in my data. But otherwise, worked like a charm! Thanks much 🙂

[Iggypop]

Would you mind sharing how you connected to smartsheets API? I am going to be doing that as well, and trying to figure out how to do that.