What's the easiest way to grab all specific JSON data from a Smartsheet JSON?

DougSmartsheetDev · 02/23/23

I have a sheet that I have imported. I have a column that I'm trying to grab all the values from and add them to a list in Python what would be the easiest way to go about this from the development end?

Jeff Reisman · 02/23/23

Hello Jeff & Shark,<\/p>

However, when I try to do,\n https:\/\/api.smartsheet.com\/2.0\/sheets\/{sheetId}\/columns\n<\/pre>I get this:<\/p>
{<\/p>
    "errorCode": 1006,<\/p>
    "message": "Not Found",<\/p>
    "refId": "6liwku"<\/p>
}<\/p>
I can get the sheet but not the columns?<\/p>","bodyRaw":"[{\"insert\":\"Hello Jeff & Shark,\\nHowever, when I try to do,\"},{\"attributes\":{\"code-block\":true},\"insert\":\"\\n\"},{\"insert\":\" https:\\\/\\\/api.smartsheet.com\\\/2.0\\\/sheets\\\/{sheetId}\\\/columns\"},{\"attributes\":{\"code-block\":true},\"insert\":\"\\n\"},{\"insert\":\"I get this:\\n{\\n    \\\"errorCode\\\": 1006,\\n    \\\"message\\\": \\\"Not Found\\\",\\n    \\\"refId\\\": \\\"6liwku\\\"\\n}\\n\\nI can get the sheet but not the columns?\\n\"}]","format":"rich","dateInserted":"2023-02-23T16:16:18+00:00","insertUser":{"userID":158634,"name":"DougSmartsheetDev","url":"https:\/\/community.smartsheet.com\/profile\/DougSmartsheetDev","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-28T14:04:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363938#Comment_363938","embedType":"quote"}">
                     https://community.smartsheet.com/discussion/comment/363938#Comment_363938

Are you putting the sheetId value in place of{sheetId}?

When I run that as a GET in Postman, I get an array of each column ID with the name and type:

sharkasits · 02/23/23

@DougSmartsheetDevI don't use Python for this, so I can't give you specifics on that, bur from the API side you'll use a GET athttps://api.smartsheet.com/2.0/sheets/<> to pull the data.

If you don't know the column index you can look for the column title where the JSON element is

columns.<>.title

Once you know the column index, all the values for that column will be in the JSON element

rows.<>.cells.<>.value

Sorry I can't help with the specific Python, but I hope that helps.

Jeff Reisman · 02/23/23

@DougSmartsheetDev

Start with theSmartsheet API 2.0 / SDK guide.

First, get your API Auth Bearer Token from the admin app.

Then you need to isolate your sheet id:

GET /sheets https://api.smartsheet.com/2.0/sheets response = smartsheet_client.Sheets.list_sheets(include_all=True) sheets = response.data Response sample: { "pageNumber": 1, "pageSize": 50, "totalPages": 25, "totalCount": 136, "data": [ { "id": 0, "accessLevel": "ADMIN", "createdAt": "2019-08-24T14:15:22Z", "modifiedAt": "2019-08-24T14:15:22Z", "name": "string", "permalink": "string", "version": 0, "source": { "id": 0, "type": "string" } } ] }

Then you need to GET the list of columns and find the column Id you're looking for:

https://api.smartsheet.com/2.0/sheets/{sheetId}/columns response = smartsheet_client.Sheets.get_columns( 9283173393803140, # sheet_id include_all=True) columns = response.data Sample Response: { "pageNumber": 1, "pageSize": 50, "totalPages": 25, "totalCount": 136, "data": [ { "id": 0, "index": 0, "symbol": "string", "title": "string", "type": "ABSTRACT_DATETIME", "validation": true } ] }

Then you need to GET the row data from that column:

That's detailed here in the API/SDK guide:https://smartsheet.redoc.ly/tag/rows#operation/row-get

You could also use theSmartsheet Live Data Connectorto query the sheet you want and use some other app to parse the column data to json.

DougSmartsheetDev · 02/23/23

Really appreciate all the help. Super awesome of everybody.

Gratefully,

Doug

DougSmartsheetDev · 02/23/23

Hello Jeff & Shark,

However, when I try to do, https://api.smartsheet.com/2.0/sheets/{sheetId}/columns

I get this:

{

"errorCode": 1006,

"message": "Not Found",

"refId": "6liwku"

}

I can get the sheet but not the columns?

Jeff Reisman · 02/23/23

Hello Jeff & Shark,<\/p>

However, when I try to do,\n https:\/\/api.smartsheet.com\/2.0\/sheets\/{sheetId}\/columns\n<\/pre>I get this:<\/p>
{<\/p>
    "errorCode": 1006,<\/p>
    "message": "Not Found",<\/p>
    "refId": "6liwku"<\/p>
}<\/p>
I can get the sheet but not the columns?<\/p>","bodyRaw":"[{\"insert\":\"Hello Jeff & Shark,\\nHowever, when I try to do,\"},{\"attributes\":{\"code-block\":true},\"insert\":\"\\n\"},{\"insert\":\" https:\\\/\\\/api.smartsheet.com\\\/2.0\\\/sheets\\\/{sheetId}\\\/columns\"},{\"attributes\":{\"code-block\":true},\"insert\":\"\\n\"},{\"insert\":\"I get this:\\n{\\n    \\\"errorCode\\\": 1006,\\n    \\\"message\\\": \\\"Not Found\\\",\\n    \\\"refId\\\": \\\"6liwku\\\"\\n}\\n\\nI can get the sheet but not the columns?\\n\"}]","format":"rich","dateInserted":"2023-02-23T16:16:18+00:00","insertUser":{"userID":158634,"name":"DougSmartsheetDev","url":"https:\/\/community.smartsheet.com\/profile\/DougSmartsheetDev","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-28T14:04:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/363938#Comment_363938","embedType":"quote"}">
                    https://community.smartsheet.com/discussion/comment/363938#Comment_363938

Are you putting the sheetId value in place of{sheetId}?

When I run that as a GET in Postman, I get an array of each column ID with the name and type:

DougSmartsheetDev · 02/23/23

Jeff,

Appreciate the response!

Yes I am putting the sheetId in place there. I'm able to access the sheet it's when I try to access anything further that I have trouble.

Thankfully,

Doug

Jeff Reisman · 02/23/23

@DougSmartsheetDev

Do you have at least editor permissions on the sheet you're trying to pull from? I don't believe your API token can access anything that your account cannot.

Double check that the sheet ID is correct.

I'm assuming you're not using { } curly braces in the URL.

Before the sheet ID, "sheets" should be all lowercase, as should "columns" after the Sheet ID.

Any of the above being wrong will throw an error code 1006 "Not Found".

DougSmartsheetDev · 02/23/23

Jeff,

No curly Braces in URL
sheets is lowercase, get call works for sheet, just stops at the /columns
sheetId is correct
Permission level is admin

indebtedly,

Doug

Edit: Embarrassed to say that I had my sheet ID & Linked Values Swapped. The answers provided above worked like a charm. My bad all and thank you.

What's the easiest way to grab all specific JSON data from a Smartsheet JSON?

Best Answer

Answers

Categories