AppSheet API Limit Records in the response

Swamp-Dev
Participant I

Dear Friends,

We would like to know how to limit records in the response. The documentation explains the TOP command but it always uses the Filter or OrderBy. I don’t need any filter or sorting. Just top N records.

Can someone help you to do this in the API.

What i have tried.

{
“Action”: “Find”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”,
“Selector”:“Top(10)”,
“UserSettings”: {
“Option 1”: “value1”,
“Option 2”: “value2”
}
},
“Rows”:[

]

}

0 28 1,719
28 REPLIES 28

LeventK
Participant V
{
   "Action": "Find",
   "Properties": {
      "Locale": "en-US",
      "Location": "47.623098, -122.330184",
      "Timezone": "Pacific Standard Time"
   },
  "Rows": [
    <<Start: TOP(SELECT(TableName[KeyColumn],TRUE),N)>> //N represents a number
    {
      "ColumnA": "<<[ColumnA]>>",
      "ColumnB": "<<[ColumnB]>>",
      "ColumnC": "<<[ColumnC]>>"
    },
    <<End>>
  ]
}

I am getting this {“Message”:“An error has occurred.”}

What am i missing ?

curl -X POST \
  https://api.appsheet.com/api/v2/apps/62f78833-5066-40c8-a3a9-9b06ece80aa0/tables/DataFeed/Action \
  -H 'Content-Type: application/json' \
  -H 'applicationAccessKey: ---------' \
  -H 'cache-control: no-cache' \
  -d '{
  "Action": "Find",
  "Properties": {
   "Locale": "en-US",
   "Location": "47.623098, -122.330184",
   "Timezone": "Pacific Standard Time",
 
   "UserSettings": {
      "Option 1": "value1",
      "Option 2": "value2"
   }
  },
  
  "Rows":[
  	 <<Start: TOP(SELECT(DataFeed[_ComputeKey],TRUE),10)>>
    {

      "Reference Txn ID": "<<[Reference Txn ID]>>"
    },
    <<End>>
  	
  	]
}'

What does that Find action in your app? And provided you are not using at all, you can remove the UserSettings parameter from the payload.

I couldn’t understand “What does that Find action in your app”.

I am trying the get the data from a Table called “DataFeed”. I used this in the API Url and also inside the Rows.

I removed the UserSettings and still the same error.

Would you mind telling me what’s your purpose of using AppSheet API?

  • We have a Table Named DataFeed which gets populated by some external source.
  • We have created a Slice (Virtual Table) named “Confirm Matches” using the underlying DataFeed Table.
  • The ConfirmMatches has some action behaviours (Inline Action). Currently its cumbersome to manually click and run the action. We want to automate this by calling the Action API. We don’t want to overload the server and we want to update batch by batch and thats the reason we want to fetch using TOP N records.

Please suggest.

What are these inline actions? What they do?

3X_b_1_b1e98982bd76a264e3dc0f28cd399682cf0f5a62.png

The Inline Action checks for some matching column values based on query and if Matches, runs sequence of actions.

How the action checks the matching column values? Provided you are mentioning about the Only if this condition is true conditional rule, than the action will stop the execution for the very first time it evals to FALSE.

How do you trigger this action? By bulk selecting the records and then choosing this action from the menu?

Hi LeventK,

Thanks for helping out to resolve.

The actual challenge i am facing is how to fetch ‘N’ records from DataFeed. Once i figure out then i will go on next to actually invoke the Action which is the final objective.

The conditional checks are done already and its working well from the UI. I am not sure how this done (by the previous developer).

@Swamp-Dev
You can invoke the inline action directly with the AppSheet API and apply that action on a set of rows defined within the <<Start>><<End>> expression. The SELECT() or FILTER() expression, will populate the rows as per defined criteria and then apply the behavior action one by one on these rows. You can run this with creating a Scheduled Report with webhook workflow which will call the preset AppSheet API. Your payload shall be like this:

{
   "Action": "Your_Inline_Action_Name_Here",
   "Properties": {
      "Locale": "en-US",
      "Location": "47.623098, -122.330184",
      "Timezone": "Pacific Standard Time"
   },
  "Rows": [
    <<Start: TOP(SELECT(TableName[KeyColumn],Your_Conditional_Criteria_Here),N)>> //N represents a number to select Top N records
    {
      "KeyColumn": "<<[KeyColumn]>>"
    },
    <<End>>
  ]
}

You may also want to read this:

Same Error : “Message”: “An error has occurred.”
API : …/tables/Confirm Matches/Action

{
   "Action": "Auto Match Inline",
   "Properties": {
      "Locale": "en-US",
      "Location": "47.623098, -122.330184",
      "Timezone": "Pacific Standard Time"
   },
  "Rows": [
    <<Start: TOP(SELECT(DataFeed[Computed Sheet Key],TRUE),10)>> 
    {
      "Computed Sheet Key": "<<[Computed Sheet Key]>>"
    },
    <<End>>
  ]
}

What your audit log says about the error? Can you give the JSON details?

Action Details
Email Details
REST API:
{
  "Action": "'Action' is missing.",
  "Properties": {},
  "Rows": []
}

Properties:
 {
  "RestAPIVersion": 2,
  "TableName": "DataFeed",
  "AppTemplateVersion": "1.000128",
  "AppTemplateName": "62f78833-5066-40c8-a3a9-9b06ece80aa0",
  "Operation": "REST API invoke",
  "RecordType": "Start"
}```

LeventK
Participant V

Is the table that your action belongs to same as the table that you’re trying to invoke the API?

Swamp-Dev
Participant I

3X_7_1_71167b2e3203c2fb70599ce1b36be6ab3186c88b.png

Your error indicates that the action parameter’s value is missing. Haven’t you specified the action name? Auto Match Inline is a sequence of 3 seperate actions: Auto Match Hidden, Record Matches Current Account, and Record Matches Main. What are those actions doing? Does anyone of them have a conditional rule for the execution?

Swamp-Dev
Participant I

I think we are deviated to different things.

My original query to know how to filter N records from a Table or a Slice. It will be great if someone helps Me to resolve this error in the same thread AppSheet API Limit Records in the response - #3 by Swamp-Dev

Thanks again

@Swamp-Dev
We are all in the same page, but you’re insistingly looking to a way different direction.


[A] Problem: How to filter records from a Table or a Slice


This is quite easy and robust because there are 2 main expressions to do that. These are:
1.) SELECT()
2.) FILTER()


Variations:
[A1] How to filter top N records from a Table or a Slice


To provide this, you need to use 2 main expressions:
1.a.) SORT() - Used to sort ordinary lists in asc. or desc. order only
1.b.) ORDERBY() - Used to sort key column values as per criteria in asc. or desc. order
2.) TOP()


[B] Workflows, Scheduled Reports and AppSheet API


There are 3 distinct but similar ways to manipulate AppSheet Data as the header explains. As you want to manipulate bulk data all at once you can use 2 options:

  • Scheduled Report w/DataChange for EachRow in Table or for Entire Table
  • Scheduled Report w/Webhook+Preset AppSheet API for Entire Table

[C] Your App Setup



[C1] Behavior Action


In your DataFeed table, you have setup an action:

1.) For this action you had defined an Only if this condition is true property. As you are intending to execute this action either from a DataChange or Webhook Scheduled Report, you don’t need to set this expression. This expression have to be set up either in the workflow/scheduled report’s running condition OR as the Y/N conditional parameter of the data selection.
2.) This action has been set up as a Grouped action consisting of 3 individual actions. There’s neither an explanation or description of what these actions are doing nor you have answered my question provided there 3 individual actions have an Only if this condition is true expression or not.


[C2] AppSheet API Payload


I’m considering that you should have been enabled AppSheet API via Manage > Integrations > IN and you have the basic knowledge about the JSON payload and the key-value pairs for proper setup. However, I would like to sweetly remind:

An AppSheet API call, has 3 integrat parts:

  • Endpoint URL (provided you haven’t appended the applicationAccessKey parameter to the endpoint URL, then you have to specify it within the HTTP Header)
  • HTTP Request Body
  • HTTP Header (where you define your applicationAccessKey parameter)

HTTP Request Body has 3 integral key params:

  • Action: where the API action has to be specified: add, edit, delete, behavior action name
  • Properties: where API properties need to be specified i.e. Locale, Location, Timezone, UserSettings etc.
  • Rows: *where you need to specify each [Column, Value] pairs that you want to manipulate. With each one of the used Actions, this array parameter shall explicitly contain the key column

With the workflows and scheduled reports, it’s possible to loop thru data. To achieve this, you need to define a <<Start>><<End>> expression within the Rows array. This expression is the expression that you select/filter the table records as per the basic guidelines clarifed under Section [A].


[D] Debugging or Tracing Errors


To debug or trace any errors, you need to examine your Audit History Log carefully to see what was wrong and why the error is generated. I have asked your audit log and what was the error message and you have posted this in post#15:

which has clearly implicated that the Action is missing.

@LeventK Thanks for the detailed response. We have enabled the AppSheet API and tried using them. Appreciated for your support so far.

Either we have not understood the API syntax or something wrong on the API Platform. From the Audit history “‘Action’ is missing.” is the only log i see for any API request (even for Find operation.)

My honest feedback as a developer to the API Team is to provide a working demo api explorer for developers to play with (including the START parameters using API request). It’s been a tough time just to pull the N records and still struggling to resolve.

@Swamp-Dev
Provided it’s possible, can I look into your app build? If you can add levent@able3ventures.com as a co-author to your app, I can in detail look at the audits and try to understand the issue better. I would also like to chime in @Phil as he is the AppSheet expert regarding the AppSheet API. Thanks.

@LeventK
Thanks for the assistance so far.
I have now added your email as co-author of the app.

Hope we can get to the bottom it, so we can run this actions using API rather than having to use the App UI and clicking them manually.

Thanks again!
Tom (App owner/maker, business manager, not the API developer)

@LeventK, Please confirm whether you were able to access our App

LeventK
Participant V

@Tom_Graham
@Swamp-Dev
I haven’t received an email regarding the co-authorship and I’m unable to see an app under my Co-Authored Apps pane. What’s your app name?

@LeventK
Definitely appears that your email is added as co-author - just added again but did not change anything with the user settings displayed at the bottom. And also just sent a “reminder” as it advises user has not responded.

App name is “PaypalBankAccount-80261”

Attached is screenshot to show details.

@Tom_Graham
I have found your app under my Co-Authored Apps pane. Thnx.

Hi @LevenK, Were you able to see the table structure and relationship. Does that helped?

Tom_Graham
Participant V

For now, I have moved the developer to work on a solution using Browser Automation to click the Action buttons, rather than using the API. (we need some solution ASAP)

Would be great if we could solve the issue in the near future and use the API to run actions.

Top Labels in this Space