What are special characters supported on the smartsheet api search

Question

Right now form my understanding the smartsheet search is looking for anything alike the term searched.

Let's say i am looking for 'test' :

https://api.smartsheet.com/2.0/search?query=test

It can return :

• my testimonial
• test
• i detest potatoes
• yet a regular test

If there any special characters to perform a more advanced search (like a SQL like query) ?

• exact match ?
• begins with ?
• ends with ?

Answer 1

I'm not sure that the /search endpoint supports special characters in the same way that SQL query would. However, both of the following scenarios are supported:

• Scenario #1: find places where the specified string exists as a separate word (case-insensitive)

• Scenario #2: find places where the specified string exists either as a separate word OR as a part of another word (case-insensitive)

The two examples below demonstrate each of these scenarios, by searching a sheet that looks like this:

Scenario #1: find places where the specified string exists as a separate word (case-insensitive)

To find places where the specified string exists as a separate word, enclose that string (i.e., the value of the query parameter) in double quotes.

API request: GET https://api.smartsheet.com/2.0/search/sheets/5831916227192708?query="new"

API response: the response contains entries for only row #2 and row #3 -- because the word "new" (as a separate word) exists only in those two rows.

{
    "results": [
        {
            "text": "New York, NY",
            "objectType": "row",
            "objectId": 5895212085602180,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 2: Meeting #2"
            ]
        },
        {
            "text": "Scranton, new jersey",
            "objectType": "row",
            "objectId": 3643412271916932,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 3: Meeting #3"
            ]
        }
    ],
    "totalCount": 2
}

Scenario #2: find places where the specified string exists either as a separate word OR as a part of another word (case-insensitive)

To find places where the specified string exists either as a separate word OR as a part of another word DO NOT enclose that string (i.e., the value of the query parameter) in quotes.

API request: https://api.smartsheet.com/2.0/search/sheets/5831916227192708?query=new

API response: the response contains entries for all 5 rows -- because the string "new" exists in all of those rows.

{
    "results": [
        {
            "text": "New York, NY",
            "objectType": "row",
            "objectId": 5895212085602180,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 2: Meeting #2"
            ]
        },
        {
            "text": "Avonew, MS",
            "objectType": "row",
            "objectId": 6198495731836804,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 4: Meeting #4"
            ]
        },
        {
            "text": "Newtown, PA",
            "objectType": "row",
            "objectId": 1391612458231684,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 1: Meeting #1"
            ]
        },
        {
            "text": "Anewston, MI",
            "objectType": "row",
            "objectId": 3946695918151556,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 5: Meeting #5"
            ]
        },
        {
            "text": "Scranton, new jersey",
            "objectType": "row",
            "objectId": 3643412271916932,
            "parentObjectType": "sheet",
            "parentObjectId": 5831916227192708,
            "parentObjectName": "SOTest",
            "contextData": [
                "Row 3: Meeting #3"
            ]
        }
    ],
    "totalCount": 5
}

Answer 2

Unfortunately, there is not. You will need to filter the results locally after you get the search results back. If there are too many results, try limiting the scope of your search, such as one workspace at a time, or only searching for reports.