API call for RowID

Geoff Parkins

Hello,

I am working on an automation where I need to get a row ID number. I'm starting with the unique value in a Row ID column called "Row", so I think I need to search the target sheet for that value and return the row ID number. I would like some help structuring the API call.

The format of the unique number in the "Row" column is "PPRI - XXXX." I am using a Smartsheet workflow automation to email that value to mailparser.io, and using Make to build the overall automation.

Once I have the row ID number, I think the rest of the automation will fall together nicely.

Lee Joramo

The Smartsheet API search is rather limited. You can search to see if a string occurs in a row. You cannot limit the search to a specific column. If you can guarantee that your ID "PPRI - XXXX" is unique to a row, it is fairly straight forward.

Here is Node.js code:

const smartsheet = require('smartsheet')
const apiKey = YOUR_API_TOKEN
const smartClient = smartsheet.createClient({accessToken: apiKey})

const optionsSearch = {
    sheetId: YOUR_SHEET_ID,
    queryParameters: {
        query: "PPRI - XXXX"
  }
}

async function searchSheet(options) {
    const searchResults = await smartClient.search.searchSheet(options)
        .catch(function(error) {
            console.log(`-- Error searching sheet ----`);
            console.log(error);
        });
    // because we are guaranteeing that the query will return a single result
    // we can return the first result's objectId
    return searchResults.results[0].objectId
}

async function main() {
    const rowId = await searchSheet(optionsSearch)
    console.log(`rowId: ${rowId}`)
}
main()

Geoff Parkins

Thank you, Lee. That looks like it will do the trick. In my case, the autonumber fields are all unique throughout the client's implementation, so this should work given that I can used a fixed sheet ID number for any of the 8-10 automations I need to create for that sheet.

It's not as efficient as what you've laid out, but I also figured out how to accomplish the same thing using three separate modules in Make (get a sheet, search a sheet, and get a row). Over time, using 30 separate API calls per row ID fetch across 1,000 row fetches per month will get ridiculously expensive.

Lee Joramo

Glad that was helpful.

ThomasHalpin

Hi @Lee Joramo and @Geoff Parkins,

I am using a similar method to try and gather the RowID, and I am passing in a variable "EpicRef" which will be unique to a particular row.

I am using Python code to query and loop through until this reference is found, and then it stores the information about the row.

url = "https://api.smartsheet.com/2.0/sheets/[SheetID]" 

headers = { "Authorization": "Bearer [AccessToken]", "Content-Type": "application/json", } 

# Making a GET request to the Smartsheet API to retrieve the data from the specified 
sheet response = requests.request("GET", url, headers=headers) 

# Converting the response data to JSON format 
data = response.json() 

# Initializing an empty dictionary to store the grouped data 
grouped_data = {} 

# Looping through each row in the sheet 
for row in data["rows"]: 
 
    if row["cells"][1]["value"] != input_data["EpicRef"]: continue 

          # Looping through each cell in the row 
          for cell in row["cells"]: 

                  # Checking if the 'value' key is present in the cell dictionary 
                  if "value" in cell: 
   
                        if cell["columnId"] in grouped_data:
 
grouped_data[cell["columnId"]].append(cell["value"]) 
                 else: 
                  grouped_data[cell["columnId"]] = [cell["value"]] 

# Storing the grouped data in the output variable as a dictionary 
output = {"grouped_data": grouped_data}

The query is able to generate an array with the information from the row itself.

However I am unable to find the "actual" RowID, as its only provided within the properties box when right clicking on a row, and I am just trying to store it as well within my query.

Any tips?

Lee Joramo

You should be able to get the row id with row.id

# Looping through each row in the sheet 
for row in data["rows"]: 
    print(row.id)

ThomasHalpin

Thanks @Lee Joramo, I didn't realise row.id was an attribute in its own right.

Really appreciate the help!

Geoff Parkins

@ThomasHalpin Once you test with the attribute that @Lee Joramo suggested (thanks, Lee!), would you please repost the Python code?