API Query

PraveenS

Hi Team,

I am using smartsheet api and few for loops to get data out of smartsheet. I have few queries with smartsheet api which i need help with. Below is the scenario which i have in smartsheet.

Consider having 20 rows in a smartsheet. Search a specific unique "string" called (ABC123) in Column A of a sheet, if that string is found(say 7th Row), then go to that row where the string(ABC123) is present and get the value of another column(say Column D) in that 7th row with value (HELLO)

To perform this i am currently doing like below.

1. To find if string exists in sheet : I am using smartsheetapi -> /search/sheets/sheetid/?query=string.

Here it just displays output as below.

{

"results": [

{

"text": "ABC123",

"objectType": "row",

"objectId": 3594751570798468,

"parentObjectType": "sheet",

"parentObjectId": 7845968654690180,

"parentObjectName": "CAT-NEW",

"contextData": [

"SGDM"

]

}

],

"totalCount": 1

}

2. Then second step i am using for loop to navigate the row, once this string is found in specific row, then navigate with 1 more for loop to search the column D in that row and get the value from that column.

Currently this is working but have few concerns.

Queries:

1. I need row id and column id of the result string when i use search api. Is there a way in smartsheet to get rowid and columnid from search api ? as i don't see this in the output.

2. Is there a way to navigate with smartsheet api to get a value of a string using row/column id ? or any other criteria. This is to avoid the for loop. Because if i consider 10 strings to search in the sheet and get values for each of these 10 strings, then i have to run these 2 for loop for every single string search.

Appreciate any help or guidance on this.

Thank you

Genevieve P.

Hi @PraveenS

1) The "objectId" returned by the search endpoint is the row ID you're looking for:

2) For the second question, two thoughts:

First, if you're looking for multiple values on a given sheet and you know which column you intend to search in, it would be more efficient to fetch the sheet and do the searching in one single for loop rather than calling the search endpoint multiple times with different values.

Second, executing multiple for loops won't be an issue. The loops you're describing will execute in fractions of fractions of a second; I wouldn't worry at all about looping over the same data set multiple times.

I hope this helps!

Cheers,

Genevieve

PraveenS

Thank you for your response. This helps.

I had one more query. Is there a way to list all the values under a column? I see Smartsheet api for "Get column description & list all columns" . However i don't see the option to list values/cells under a column.

Genevieve P.

Hi @PraveenS

Get Column(s) will list the type of column and the column names. If you're looking for the cell data in a sheet you can Get Sheet or Get a specific Row and then identify the Column ID.

Cheers,

Genevieve