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

  • As an optional step, 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.
  • Check the Pre-Requisites : API Proxy Requirements 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 :

  • 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.

OpenAPI Spec (read/write)

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

  • Apigee API Key: Provide the Key generated earlier in Apigee.
  • OpenAPI Spec URL: Provide a URL pointing to the OpenAPI 3.0 Spec describing the Apigee Proxy.
  • 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.

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 Based Apigee Connector

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"

}

]

}

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.

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.

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.

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.

Limitations & Known issues

Pagination, Row Limits and Filter params are not currently supported.

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:
    - GET /tableName
    - POST /tableName - requestbody contains id field.
    - PUT /tableName/{id} - requestbody contains id field.
    - GET /tableName/{id}
    - DELETE /tableName/{id}
  • The OpenAPI Spec is not stored within Appsheet, so each operation in Appsheet will involve a call made to the OpenAPI endpoint.

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.
Did this answer your question?