Cannot filter the row return from appshet API

[gaixixon]

I have a function below running with google script to retrieve appsheet data via API call.

Everything works, however, I am unable to filter the data. What ever I do with selector, the appsheet API still return all the row, even when I intentionally makes some mistake in Filter query.

Can anybody help me out please.

function appSheetAPI(){

param={'appKey':'my-app-key',

               'appId':'my-app-Id',

              'table':'TripNames',

              'selector':' Filter(TripNames, [Currency Unit] = "KRW"), true)'

}

  var url = "https://api.appsheet.com/api/v2/apps/"+param.appId+

            "/tables/" + param.table+"/Action";

  var data = {

      "Action": "Find",

      "Properties": {},

      "Selector" : param.selector

}

resp = UrlFetchApp.fetch(url,{'method' : 'post','contentType': 'application/json',

  'headers':{'ApplicationAccessKey': param.appKey},

  'payload' : JSON.stringify(data)})

console.log(resp.toString());

 }

[Marc_Dillon · Accepted solution]

Your syntax isn't correct. Look at the example more closely:
https://support.google.com/appsheet/answer/10105770?sjid=13710529162891050262-NA

[gaixixon]

Thank you for your help!

The selector should be INSIDE Properties bracket!

BTW, I need to escape the \" charactor if I need to compare text string. The sample code does not suggest that.

{
"Action": "Find",
"Properties": {    
"Selector":"Filter(
TripNames, [Trip Name]=\"trip number 1\")",
"Locale": "en-US",
"Timezone": "Pacific Standard Time"
}
}

[Marc_Dillon]

---

@gaixixon wrote:

BTW, I need to escape the \" charactor if I need to compare text string.

---

That's not quite right. You're already inside of a double-quote around the entire selector value, so your double quotes around the column value are messing that up. If you just use single quotes for one of the pairs, you'd be good.

[gaixixon]

Thank you Marc.

By the way, in the table "TripNames", I have some columns let say Colum_A, which is referenced to another table (let's say Table_A).

The API call to "TripNames" table return the value of Column_A, which is the ID value of a row in Table_A.

Is there anyway via API call to "TripNames" table, we can get the value of Column_A, the Label column, instead of ID column in Table_A?

[Marc_Dillon]

The key value is the actual value in the column. If you need a different value from the referenced table, you can create a virtual column to pull that value via dereference, which should be available from the API.

[gaixixon]

I see. Unfortunately, Column_A is enum_list type, which is referenced to Table_A, so a single virtual column is not enough.

[Marc_Dillon]

https://help.appsheet.com/en/articles/4575708-list-dereference