NWC and Smartsheet

R
Badge +3

Hi Nintex Community,

Using Nintex Workflow Cloud, I created a connection to Smartsheet.  I am trying to retrieve specific cell data.  When I use "Get a Row" and "Get a Column" I get row and column collection variables.  I am trying to get one data point (like dollar amount) for one cell.  In excel terms, the value in cell b3.  Can this get done with the current SmartSheet Actions? and/or with Get Collection Variable?

 

More specifically my data is like this:  (In the Start Event Form the user will enter the store# 2501 and I want Nintex to connect with Smartsheet and return Sales of $650 and put it in a short text variable in a form downstream.

 

Store#  Sales$ Profit$

1001     $500    $300

2501     $650    $450

3001     $890    $560

 

Thank you.

6 replies

L
Userlevel 5
Badge +13

@Russ_G Will you know which row you need, or will you have to grab each row until you find the correct row?

R
Badge +3

We should know what row we need.  In the initial start form, I will have the user input the store number in a short text field.  Then in the next several steps I want Nintex to connect with Smartsheet, find the row that has the store number and then return an item in the row.


 


The same as a Vlookup in excel.  Find the row with the store number and then return, say the data point 3 columns to the right.


 


That is what I am trying to do.  I am thinking through your suggestion of using expressions.


 


Thank you.

L
Userlevel 5
Badge +13

@Russ_G Currently the smartsheet get a row action can only grab by row number not by value. So if the sheet has a lot of rows the below may not be a viable solution and you would need to look at another option potentially by working with the sheet as a CSV. It would look something like the below as  an example.



  • Start with a loop to grab each row one at time 

  • Set the cells to a text variable and check if it contains sales code

  • If yes then use get item from collection to grab the column and regex to get sales value.



If you have any feedback on improvements for the connectors, I would also suggest raising it on the uservoice(https://nintex.uservoice.com).

A

Hello @leighburke and @Russ_G ,


 


We at Stategroup, are also trying to perform similar steps, where we run through each row and locate the data required. 


 


We only have the option to Get a Row and not Rows, I am very happy to even have the option to get one row. But it kind of defeats the purpose if we cant loop through each row and locate the data we need. 


 


I have tried running the loop 10 times, to get the info, but I cannot use the Index number, and then add one to go to the next row. Have to use the Row ID or it keeps failing "Received an error response from the connector: "Not Found". [Error Code: IO.XtensionExecute.ErrorResponse]" . 


 


Here is an example of the loop I am running: 



Hope you can provide me with a bit more information to help run it efficently. 


 


Thank You

B
Userlevel 4
Badge +10

@aadhawan @Russ_G  @Tessa 


 


I think both of your scenarios could be solved by using the Web Request action to use the Smartsheet API's Get Sheet Endpoint and store the entire sheet into a NWC text variable. Then, you can use the Query JSON action to extract the information you're looking for, whether that be a different column for that row, or all the row IDs that were found. 


 


I have an example of what the Query Json action would look like when similar to a VLOOKUP here: Get Sheet action 


I think the same query could be pretty easily modified to return all the row IDs into a collection, which you could then loop through. 


 


It would be awesome if this functionality was built into the Get Row action, but I've found this is a pretty efficient solution. 

A

@bsikes


I am not very comfortable using JSON queries and have a hard time adding those for my rows. 


I tested what Brady commented here: https://nintex.uservoice.com/forums/430063-5-nintex-workflow-cloud/suggestions/44577309-add-get-sheet-action-to-smartsheet-connector


 


We would really prefer having this option of either getting rows or looping through rows without the row ID. 


 


Thank You


 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings