LinkedIn
Twitter
This tip is just to share a working example of "receiving" or getting data by using "GET" verb from an API using AppSheet bot and "return values' feature. The API in this tip example is such that JSON response is of simple structure without any nested field values.
Background
Show More
Round about 8 months ago the following announcement regarding processing "return values" from webhooks or APIs was made in release notes section.
June 13, 2023 - Google Cloud Community
The documentation related to "return values" is also neat and gives nice examples of which JSON response can be processed and which cannot by Appsheet return values settings.
Use return values from webhooks - AppSheet Help
Use return values from Apps Script tasks - AppSheet Help
However , just thought of sharing the testing done by using a NASA API for those who may be looking for an active working example as at least I did not come across an example discussing an API and processing return values in the BOT itself. without using external tools like GAS to process an API call.
Free or Freemium APIs for Testing
Show More
There are many free or freemium public APIs available for developers to test and there are many articles on the internet about these APIs. I chose to use NASA's simple API of APOD( Astronomy Picture Of the DaY) API. It is simple enough to understand and does not have complex , nested JSAON responses. This API basically returns a different astronomy picture every day with a title and its explanation. We supply a date as a query parameter to the API so that the details of the picture for that day are returned.
The details of the NASA APOD API are available at NASA Open APIs
The BOT details
A) The Process to get the desired response from the API
Show More
The bot process settings of the bot that triggers on a record add are as follows.
1. The URL setting looks something like below
https://api.nasa.gov/planetary/apod?api_key=[API_KEY]&date=<<[APOD_DATE_V]>>
Note that we can use template variables in the URL. So we are passing the date of the picture to the API URL as a query through the column [APOD_DATE_V] .
You can register yourself with the API program and get the API key for yourself from the NASA API site.
2. We make the settings to "GET" or receive the following data values as return values from the API call.
We will receive or get ( through the GET verb) the explanation , date , the hdurl and title of the astronomy picture of day (APOD) API. You can find the values that the API will return by running an dummy call or referring the API documentation. It is important to note that we need to assign proper type values as per AppSheet table record for the JSON response values that we will receive through API call.
B) The step to populate the AppSheet table's record columns with the values received from the API call in step A described above .
Show More
The settings of the step look like follows. We run a data action to set the values in teh AppSheet record with the values received through API call in the previous step.
1. The syntax of the expression to populate the AppSheet record column by using the values from the previous BOT step are
[Previous_Step_Name].[Field_Name]
So as an example we got the [title] value from the API response and we will populate the AppSheet table record's [Title] field by using an expression as below
We write similar expressions to populate other three fields in the AppSheet record.
The BOT running in actual app looks like below.
Here we add a record with a random date in the [APOD_Date] field to the AppSheet table. The API call bot runs on record add form save event and populates the fields in the record with title, image, returned date and explanation as we can see.
Hope this helps.
There are more observations during testing on the complexities of nested JSAON response values that can be handled. These are already well documented in the help documentation. However I may add my observations with some real life examples in another post.
