API call for RowID

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.

Geoff Parkins

Parkins Financial, LLC

Answers

  • Lee Joramo
    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()
    
    
    
    
    


  • 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.

    Geoff Parkins

    Parkins Financial, LLC

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭

    Glad that was helpful.