App Creators can build Apps in Appsheet using the data exposed by APIs managed by Apigee with the Apigee Data Source type. At this time, there are primarily 2 options to connect AppSheet apps to Apigee APIs:

  • Manual (read-only)

  • OpenAPI Spec (read/write)

See the demo video to get started.

This article covers the detailed steps and the pre-requisites for the API necessary to set up the connection from the AppSheet apps to Apigee managed APIs.


Setting up the Apigee Data Source for Appsheet

To authenticate with an Apigee data source, you can choose an API key, OAuth, or no authentication.

No Authentication : Select API Key and put any non empty value (e.g. "ABC") into the API Key field

API Key Authentication

  • Setup API Key Validation in Apigee for providing an authorized way to access your API.
    - Note: The API Key is passed back in the header of each request Appsheet makes with the Key as x-apikey.

OAuth Authentication

For all Authentication options, the next steps are:

  • Check the Pre-Requisites : Review the API Proxy Requirements section below to ensure your API can be consumed by AppSheet.

Create the Apigee Data Source in Appsheet to create a connection to Apigee

  • In the My Account page, open the Sources tab and click on the "+ Data Source" button.

  • Name your new Data Source and select "Apigee".

There are two options to connect to Apigee - Manual and OpenAPI Spec.


Manual (read-only)

Use this if you don't have an OpenAPI spec available for your API. To connect with Apigee using the Manual option, you have to provide the following information :

  • Authentication Type: Select OAuth or API Key

For the API Key Authentication Option

  • Authentication Type: Select API Key

  • Apigee API Key: Provide the Key generated earlier in Apigee.

  • Apigee API Base Path: The Base Path of your Apigee proxy.

  • API Resource Paths: Enter a list of comma separated API Proxy Endpoints. These paths correspond to Table names in Appsheet.

  • Note: This Manual method of configuration for the Data Source is READ-ONLY (GET operations only)

  • Once the connection information is filled in, you can test the connection to Apigee.

  • Clicking on the "Test Connection" button will make a GET call to all the {Apigee API Base Path}/{API Resource Path} paths to verify access.

  • Finally, click on the "Authorize Access" button to add the Data Source.

For the OAuth Authentication Option

  • Authentication Type: Select OAuth

  • Apigee Authorization Token Url: Provide the URL of your API proxy which handles the Access token generation.

  • Apigee API Base Path: The Base Path of your Apigee proxy.

  • Apigee Client Id: Provide your Client Id generated earlier in the Apigee.

  • Apigee Client Secret: Provide your Client Secret generated earlier in the Apigee.

  • Authorization Scopes(Optional): Provide scopes of your authorization if you define it in your policy.

  • API Resource Paths: Enter a list of comma separated API Proxy Endpoints. These paths correspond to Table names in Appsheet.

  • Note: This Manual method of configuration for the Data Source is READ-ONLY (GET operations only)

  • Clicking on the “Test Connection button” will make a POST call with Apigee Client Id and Apigee Client Secret to Apigee Authorization Token Url and get the Accesstoken, with the Access Token it will make a GET call to all the {Apigee API Base Path}/{API Resource Path} paths to verify access.

  • Finally, click on the "Authorize Access" button to add the Data Source.

The ends the Manual (Read Only) configuration option.


OpenAPI Spec (read/write)

You can also connect using an OpenAPI Spec by providing the following information.

  • Authentication Type: Select OAuth or API Key

For the API Key Authentication Option

  • Authentication Type: Select API Key

  • Apigee API Key: Provide the Key generated earlier in Apigee.

  • OpenAPI Spec URL: Provide a URL pointing to the OpenAPI 3.0 Spec. AppSheet will accept either JSON or YAML format. The URL must be publicly accessible as a REST endpoint. Appsheet will send a GET call to the URL to get the OpenAPI Spec.

  • Once the connection information is filled in, you can test the connection to Apigee.

  • Clicking on the "Validate OpenApi Spec" button will make a GET call to the OpenAPI Spec URL and parse the OpenAPI Spec. GET calls are made to each table resource once they are inferred from the OpenAPI Spec.

  • Finally, click on the "Authorize Access" button to add the Data Source.

For the OAuth Authentication Option

  • Authentication Type: Select OAuth

  • OpenAPI Spec URL: Provide a URL pointing to the OpenAPI 3.0 Spec. AppSheet will accept either JSON or YAML format. The URL must be publicly accessible as a REST endpoint. Appsheet will send a GET call to the URL to get the OpenAPI Spec.

  • Apigee Client Id: Provide your Client Id generated earlier in Apigee.

  • Apigee Client Secret: Provide your Client Secret generated earlier in Apigee.

  • Check the Open API 3.0 requirement to make sure your Open Api Spec meets the requirements.

  • Once the connection information is filled in, you can test the connection to Apigee.

  • Clicking on the "Validate OpenApi Spec" button will make a GET call to the OpenAPI Spec URL and parse the OpenAPI Spec. Appsheet will extract the Authorization Token Url from the OpenAPI Spec. After getting the Accesstoken from the URL, GET calls are made to each table resource once they are inferred from the OpenAPI Spec.

  • Finally, click on the "Authorize Access" button to add the Data Source.


Pre-Requisites : API Proxy Requirements

Depending on the type of Apigee Data Source setup (Manual/OpenAPI), there are some additional requirements for the underlying APIs to ensure compatibility with Appsheet.

Manual Apigee Connector

Get All Entries for the “tableName” API Path - GET /tableName

  • This API is expected to return a JSON Array with the key for this list as the table name.

  • Example :

{

"tableName": [

{

"ID": 1,

"TextValue": "Test1",

"DecimalValue": 1.10,

"DateTime": "1/1/2016 1:01:01 AM",

"PhoneValue": "111 111 1111",

"EmailValue": "email1@appsheet.com"

},

{

"ID": 2,

"TextValue": "Test2",

"DecimalValue": 1.11,

"DateTime": "2/1/2016 1:01:01 AM",

"PhoneValue": "111 112 1111",

"EmailValue": "email2@appsheet.com"

}

]

}

OpenAPI Spec 3.0 Requirements for the Apigee Data Source in AppSheet

  • Servers must have exactly one entry as a base path

  • For OAuth authentication type :

    • Must include components/securitySchemes section

    • When the components/securitySchemes type is oauth2, flows must be specified as clientCredentials

    • tokenUrl must be provided

    • scopes is optional

Example OpenAPI Spec components snippet:

components:
securitySchemes:
oAuth2ClientCredentials:
type: oauth2
Description: Experimental apigee APIs
flows:
clientCredentials:
tokenUrl: https://appsheet-test.apigee.net/oauth2/token/
scopes: #Can be empty {}
read: Grant read-only access

  • For each table, GET /tableName and GET /tableName/{id} are required.

  • If the user provides the schema of the response body of GET /tableName/{id}, appsheet will infer the column type from it, otherwise appsheet will directly try to infer the column type from real response.


Operations

(Required) Get all entries - GET /tableName

  • This API is expected to return a list of rows with the key for this list as the table name/proxy endpoint.

  • Example :

{

"tableName": [

{

"ID": 1,

"TextValue": "Test1",

"DecimalValue": 1.10,

"DateTime": "1/1/2016 1:01:01 AM",

"PhoneValue": "111 111 1111",

"EmailValue": "email1@appsheet.com"

},

{

"ID": 2,

"TextValue": "Test2",

"DecimalValue": 1.11,

"DateTime": "2/1/2016 1:01:01 AM",

"PhoneValue": "111 112 1111",

"EmailValue": "email2@appsheet.com"

}

]

}

(Required) Get entry by id - GET /tableName/{id}

  • This endpoint is expected to return one row from table tableName with the matching id.

  • The path param id should be unique for the API Path /tableName since the primary key for this table is inferred from this API Path.

  • The path param id should also map to a valid column in the /tableName GET response.

  • Example - The primary key for table tableName is inferred as id.

Note: Best practices for the id field:

The preferred solution is to have one id field generated by AppSheet using expressions like UNIQUEID().

If the generation of the id field occurs in the backend,

  • We recommend using two fields in your API Payload that can act as the primary key for that entry. One generated and sent by AppSheet in the POST request body and the other one generated by the backend.

  • For the AppSheet generated id, you can use the expression “UNIQUEID()” (Documentation Link) as the initial value.

  • The backend generated id field will be left empty in the POST request body and will be populated on the next “Sync” after the creation of a new row.

The API should operate with the {id} parameter input with either the AppSheet id or the backend generated id. In other words, the id matching needs to work with either of the column values. AppSheet will always use the AppSheet generated id for its API calls.

If your API can only support one id field that is generated by the backend, then this will be the behavior you will see:

  • AppSheet will need to generate a temporary unique id in the API’s id field (use expression UNIQUEID() in the “Initial Value” setting for the API id column).

  • When the POST operation is called, the backend will generate its own key for the entry, which will overwrite the value that AppSheet generated and passed in the POST body.

  • On the next Sync after the Create (POST) operation is finished, AppSheet will retrieve the id generated by the backend, and the temporary id generated by AppSheet will be overwritten.

  • This can cause issues in cases where a row is created and updated (or deleted) before a sync operation occurs. Since the operations are queued, id used for the update operation will be the temporary id generated by Appsheet and this will not exist in the backend.

(Optional) Update an entry by id - PUT /tableName/{id}

  • The request body sent by Appsheet contains the entire row including all the updated values.

  • Appsheet makes a PUT /tableName/{id} request to update an existing entry, where id is the inferred primary key and the body contains the entire entry.

(Optional) Creating a new entry - POST /tableName/

  • The request body sent by Appsheet contains the entire row that was newly created.

  • Appsheet makes a POST /tableName/ request to create the entry.

(Optional) Delete an entry - DELETE /tableName/{id}

  • This endpoint is expected to delete one row from table tableName with the matching id.

  • The path param id should be the same primary key inferred from the GET Requests.

The OpenAPI Spec endpoint can be hosted within or outside Apigee, SwaggerHub example: Sample OpenAPI Specification Note: Replace the app.swaggerhub.com with api.swaggerhub.com to get the correct API based URL.

Note: The API Key provided will be passed in the header as `x-apikey` when making a request to fetch the OpenAPI Spec.


Pagination

To enable pagination for your API, the open API spec must specify a “x-pagination” extension for the GET ALL operation as an extension. The Apigee data source supports offset based and cursor based pagination directives.

General Parameters:

  • Type (required): offset | cursor

  • limitParam (required): The param key in parameters object. Example: limit

  • defaultLimit: Default value of limit, if not specified, it will be set as 50

offset based parameters:

  • offsetParam (required): The offset param key in parameters object

  • defaultOffset: Default value of offset, if not specified, it will be set as 0

cursor based parameters:

  • cursorParam (required): The cursor param key in parameters object

Offset based parameters example in OpenAPI Spec YAML:

/users:
get:
tags:
- person
parameters:
- name: limit
in: query
required: false
schema:
type: integer
format: int64
- name: offset
in: query
required: false
schema:
type: integer
format: int64
responses:
'200':
description: Array of all person in the system with filter and pagination
content:
application/json:
schema:
type: array
items:
$ref: '#/components/schemas/Person'
x-pagination:
type: offset
limitParam: limit
defaulLimit: 30
offsetParam: offset
defaultOffset: 0

URL

api/users?limit=30&offset=0

Cursor Example:

/users:
get:
tags:
- person
parameters:
- name: page_size
in: query
required: false
schema:
type: integer
format: int64
- name: page_token
in: query
required: false
schema:
type: integer
format: int64
responses:
'200':
description: Array of all person in the system with filter and pagination
content:
application/json:
schema:
type: array
items:
$ref: '#/components/schemas/Person'
x-pagination:
type: cursor
limitParam: page_size
defaulLimit: 30
cursorParam: page_token

URL

api/users?page_size=30&page_token=GIYDAOBNGEYS2MBWKQYDAO

Filter Params (Query Params / Security Filters)

To specify a filter for a table, the open API spec must specify a “x-filter” extension for GETALL operation as an extension. And must specify a type of filer, and now apigee data source only support RHS-Colon and LHS-Brackets.

RHS-Colon example:

/users:
get:
tags:
- publisher
summary: Get all person
responses:
'200':
description: Array of all person in the system with filter and pagination
content:
application/json:
schema:
type: array
items:
$ref: '#/components/schemas/Person'
'500':
description: Internal server error.
x-filter:
type: RHS-Colon

Make sure that you put a space in between the type: and the RHS-Colon or LHS-Brackets.

Won’t work:

type:RHS-Colon

Correct syntax with a space

type: RHS-Colon

The x-filter directive only supports following operators in the table below, and the following table shows the mapping between security filter resources and url request (using age in the users table as example).

AppSheet’s security filter for the Apigee Data Source will be passed down to the API via these query parameters using the scheme (RHS-Colon or LHS-Brackets) as specified in the OpenAPI Spec. The operators supported are shown in the following table.

AppSheet Security Filter Operator

AppSheet Security Filter example

Filter operator

RHS-Colon example

LHS-Brackets example

=

[age]=25

eq

api/users?age=eq:25

api/users?age[eq]=25

<>

[age]<>25

neq

api/users?age=neq:25

api/users?age[neq]=25

>

[age]>25

gt

api/users?age=gt:25

api/users?age[gt]=25

>=

[age]>=25

gte

api/users?age=gte:25

api/users?age[gte]=25

<

[age]<25

lt

api/users?age=lt:25

api/users?age[lt]=25

<=

[age]<=25

lte

api/users?age=lte:25

api/users?age[lte]=25


Limitations & Known issues

Row Limits are not currently supported. Please use Filter Params to limit the data returned.

Response types of all APIs are expected to be application/json.

Manual Apigee Connector - This connector type is read-only.

OpenAPI Spec based Apigee Connector

  • During create row operation, `id` values as a best practice should be generated inside Appsheet. Backends that generate the `id` field on insert are not expected to follow some workarounds mentioned in the ‘Best practices for the id field’ section .

  • The path params should always be at the end of the path string.

  • Only the following REST operations are supported (Both GET operations are required):
    - GET /tableName
    - POST /tableName - requestbody contains id field.
    - PUT /tableName/{id} - requestbody contains id field.
    - GET /tableName/{id}
    - DELETE /tableName/{id}

The fields in the responses should not be nested, the fields should be of simple types like int, string or list that can be converted into Appsheet Types.

Doesn’t strictly adhere to OAS

  • Doesn’t use resource definition in OAS, uses superset of responses from GET /resources to build columns.

  • AppSheet assigns emails and phone number data types as required during initial data source creation. The App Creator can adjust this after the table is created in the app.

  • Doesn’t recognize the security schemes for API Key. Appsheet sends the API Key as x-apikey header only.


Troubleshooting

Some exceptions will show in the test step or app creation step, here are some examples and possible solutions.

Your client credentials are invalid. Please ensure your Apigee client credentials and token url are correct.

Please ensure your credentials are correct, for the OAuth type you can verify it by an API call from a command line interface using the curl command (curl is available on Linux based systems...for MS DOS, you may need to install a third party curl program). If this is unfamiliar to you, you may need to have your Apigee administrator/developer or IT staff help you with this :

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" \

“tokenUrl” \

-d "client_id={key}&client_secret={secret}"

You should get a 200 response with access_token in the response body

Access Token Response:

{

...

"token_type" : "BearerToken",

"client_id" : "xNnREu1DNGfiwzQZ5HUN8IAUwZSW1GZW",

"access_token" : "GTPY9VUHCqKVMRB0cHxnmAp0RXc0",

"refresh_token_expires_in" : "30000", //--in seconds

...

}

Call the API with an access token:

curl https://{org-name}-test.apigee.net/tablename -H "Authorization: Bearer {access-token}"

Parsing of OpenAPI Spec failed. Error: The Apigee data source requires both the `GET /tableName` and 'GET /tableName/id'

Please make sure every table in the spec includes both “GET tableName” and “GET /tableName/id” operations. For more details, please refer to the re-Requisites : API Proxy Requirements.

If the Apigee datasource successfully passes the test step and is authorized, but shows error while creating the app : “ Error: Table 'table' is no longer connected to its schema/column structure. "

Please check the response format of the “GET tableName”, this API is expected to return a list of rows with the key for this list as the table name/proxy endpoint. For more details, please refer to the re-Requisites : API Proxy Requirements.

Did this answer your question?