Get SmartSheet Row by Cell Value
As discussed on this forum page (https://community.nintex.com/t5/Nintex-Workflow-Cloud-Forum/NWC-and-Smartsheet/m-p/215571) I'd really like to be able to get a Smartsheet row based on a cell value.
If not that, I'd like to at least call by row number (rather than row ID) so that I can engage the looping mechanism discussed on the forum.
I have a similar use case where I would like to be able to find a particular row on Smartsheet base on the value of a cell. Functionality like the SharePoint Online - Query a list action item is exactly what I'd be looking for.
For further explanation my use case is nearly identical to the situation discussed on this forum page: https://community.nintex.com/t5/Nintex-Workflow-Cloud-Forum/NWC-and-Smartsheet/m-p/215571
If that kind of a filter was at least available for the "get a row" action, that would be very helpful. I say "get a row" because I'm not always needing to update something in Smartsheet, but instead just checking if a row is present that meets the criteria. In my original use case, it depends what information I need. If I need to get the entire row or the row id, usually it's looping through each row until I find the one that has the value in a specific column(s) that indicate it's the one I need. That involves knowing the ID of the column you're looking for.
If I need to get a specific cell from a specific row, I still need to do the above steps, but once I have the row I also need to find the particular cell in that row with the desired column id, and get the value (or Display Value) of that cell.
I did realize this can be made much easier with the "Query JSON" action, when using the results from a Get Sheet web request action. (Unfortunately, I can't use my custom Xtension "Get Sheet" action because Nintex always seems to throw slashes before quotes in the results, making it an invalid JSON format....)
$.rows.[?(@.cells..value=='SEARCH VARIABLE' && @.cells..columnId== SEARCH COLUMN ID)].cells.[?(@.columnId== DESIRED COLUMN ID)].value
The above returns the value of a specific column from each row in the sheet that has a specific value in a specific column. This kind of functionality would be awesome if it was built into the existing actions.
Recently, the Smartsheet connector added the "Add a row" and "Update a row" actions, which is awesome! It would be great if we also had the "Get Sheet" action. Right now, there's a "Get Sheets" action, which I believe will return a list of sheets, but not the sheet's contents and rows.
If I wanted to find and apply an update to a row in a sheet, normally I'd follow this set of steps:
1. If not known, determine desired Sheet ID by using "Get Sheets" action and looping through sheets until desired sheet is found, then store the ID
2. Use the Sheet ID to get the contents of the sheet. There is no "Get Sheet" action, so this must be a Web Request or custom Xtension.
3. Loop through rows of sheet return in step 2 until desired row is found, then store the ID of that row.
4. Update the row using the "Update a Row" action.
Step 2 is the only part that actually interacts with Smartsheet and doesn't have a built in action to support it.