Smartsheet API
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- API
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Wow! This did it. I completely missed using the text to columns and splitting the headers and values out like you did. Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
