Using Smartsheet API to update Cell value without SDK

Question

I have been using the Smarthseets API trying to create a script that can read a sheet and update its cells. I have been able to read the sheet using the following script. Inside the IF statement it checks for a certain column title and reads off the values in the cells,

import requests
import json

Test = "SHEET_ID"

url = "https://api.smartsheet.com/2.0/sheets/"+Test
Header = {
    'Authorization': "Bearer AUTH_TOKEN",
    'Content-Type': "application/json"
    }
response = requests.request("GET",url,headers=Header)
if response.status_code == 200:
    SheetInfo = json.loads(response.text)
    for column in SheetInfo["columns"]:
        if column["title"] == "Tracking #":
            TrackingNumberColumnID = column["id"]
    for row in SheetInfo["rows"]:
        for cell in row["cells"]:
            if cell["columnId"] == TrackingNumberColumnID:
                numberlist.append(cell["displayValue"])
for number in numberlist:
    print(number)

Seeing this working let me know that using the SmartSheets API without the SDK was possible, i then tried to very simply use the Get Rows method from the SmartSheets API Documentation

The code for that looks like the following

import requests
Test = "SHEET_ID"
row = "ROW_ID"
url = "https://api.smartsheet.com/2.0/sheets/"+Test+"/rows/"+row
Header = {
    'Authorization': "Bearer AUTH_TOKEN"
    }
response = requests.request("GET",url,headers=Header)
print(response.text)

When this script is ran however the response that is generated gives a status code of 404 and the following in its JSON

{
  "errorCode" : 1006,
  "message" : "Not Found",
  "refId" : "o7wsk0qea9ct"
}

Any help as to how to format the get rows request properly would be much appreciated. The end goal of using the get rows request is to use the update rows request to make changes to the sheet

Answer 1

A 404 response code in the scenario you've described indicates that the URL you're using is not valid. I'd suspect that either the value you're specifying for the sheet Id (i.e., contents of the Test variable in your code) is invalid or the value you're specifying for the row Id (i.e., contents of the row variable in your code) is in valid -- or perhaps both of those values are invalid.

To troubleshoot, try adding a print statement to print out the URL -- then verify that the structure of that URL looks correct and that values it specifies for sheet Id and row Id are valid (i.e., exactly match what you got back in the previous Get Sheet response).

Here's some sample code that contains the print statement I've described:

sheetId = '3932034054809476'
rowId = '5225480965908356'
url = 'https://api.smartsheet.com/2.0/sheets/' + sheetId + '/rows/' + rowId
print('Request URL: ' + url)
header = {
    'Authorization': 'Bearer ' + os.environ['SMARTSHEET_ACCESS_TOKEN']
    }
response = requests.request('GET', url, headers=header)
print(response.text)

The URL should look something like this -- only with your sheet's values for the sheet Id and row Id:

https://api.smartsheet.com/2.0/sheets/3932034054809476/rows/5225480965908356

UPDATE - how to identify Sheet ID

Adding this additional info in response to your comment below about Sheet ID. When using the Smartsheet API, you should never use the ID values that appear in URLs within the Smartsheet app. That ID from the URL in-app will technically work for a GET Sheet request -- but it won't work for anything else via API. Instead, when using the API, always use the unique numeric ID value (e.g., a value similar to 5225480965908356) that's assigned to each object in Smartsheet. You can find a Sheet's ID by opening the sheet in the Smartsheet app, then choosing Properties from the File menu.

The screenshot below shows the sheet properties dialog window that contains the Sheet ID. Once you have this ID, you can use it to issue the GET Sheet API request, and get any additional IDs you need from that response.