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

[hapham]

i am trying to update rows with identifier filtered from "selector:" with HTTP request body as below. However it always returns 400 code. Pls show me how to update rows based on filter
{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time",
"Selector": "FILTER(DiemXetTuyen, [ma_ho_so]=<<[_THISROW].[ma_ho_so]>>)"
},
"Rows": [
<<Start:[_THISROW].[ma_to_hop].[to_hop_xet]>>
{
"loai_diem":"<<[ma_mon]>>"
},
<<End>>
]
}

[Marc_Dillon]

The selector is only for Find calls, not for Edits. Your start expression should be using that filter() instead.

[hapham]

Please recommend me the body request in this case. I edited my body http request as below:
{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [
<<Start: FILTER("DiemXetTuyenSinhVien", [ma_ho_so] = [_THISROW].[ma_ho_so])>>
{
"loai_diem": "<<INDEX([_THISROW].[ma_to_hop].[to_hop_xet],1)>>"
}, <<End>>
]
}
Note: to_hop_mon in the above example is a list of ma_mons. Here I take the first value of the list to check. However the code returned is 400.

[hungdeptrai2791]

you should use `` for query selector example: 

`SELECT(account[email],TRUE)`

[hapham]

The SELECTORS key only functioning within the FIND action in the body of the HTTP request.
Your collection rows by FILTER or SELECT formular inside ROWS key.