AppSheet API Limit Records in the response

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”:[

]

}

{
   "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>>
  ]
}
1 Like

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?

image

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?

1 Like

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:

3 Likes

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?

1 Like
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"
}```

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

image

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?

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.

1 Like