Update your app's spreadsheet in a regular regular interval using our API.
With Open as App it is easy to build apps with data exported from an arbitrary backend system. Almost every system in the world offers an Excel export. With Open as App you can build an app based on this export and create an appealing interactive visualization of your data. You can even allow your users to edit this data!
When working with data exports, the main issue always is how to keep the data up to date. The data needs to be exported in regular intervals and uploaded into the target system. With Open as App's API, this can easily be automated in as little as a single line of code! The users can even be allowed to edit this data since you can easily download all changes with the API and import it into your system again.
This tutorial illustrates how to use Open as App's API to update the data of your app with automated scripts, and optionally, if your app supports editing, how to download the changes your users made to the data. The usage of the API is illustrated using simple PowerShell scripts, although you could use any programming language of your choice since the API is a simple HTTP based REST API.
API-Token
Before we can use the API, we need to create an API-Token. Go to /user/apiTokens, provide a description for the token and use the Try It button to generate a token you can use in your scripts to access the API on your behalf.
As described in the /user/apiTokens section, the API-Token needs to be passed as Authorization: Token <ApiToken> header in every request so the scripts can interact with the API on your behalf.
App ID
The API identifies your apps by their id. Before you can use the API you need to know the id of your app. In order to get the id of your app, go to the Open as App Portal, select your app and then go to Design & Info > Name and description and copy the id from the Additional Info section.
Download the spreadsheet
If your app is a read-write app and supports editing the data (as described in following article:
Edit lists in the app | mobile data collection), you might want to download the current spreadsheet including any changes your users made before you update the app with the most recent data from your backend. The downloaded spreadsheet can then be processed by you or imported into the original backend system again.
The following PowerShell script uses the GET /apps/{id}/spreadsheet endpoint to download your app's current spreadsheet including any changes your users made. Of course, you can also use any other programming language of your choice as the API is a simple HTTP based REST API.
Invoke-WebRequest https://api-staging.openasapp.net/v1/api/apps/{appId}/spreadsheet -Method Get -OutFile file.xlsx -H @{"Authorization" = "Token {apiToken}"}
In order to download the spreadsheet of your app using this code, simply open PowerShell in Windows, copy&paste the code snippet above, replace the variable {appId} with the id of your app and {apiToken} with the API-Token you generated in the beginning of this tutorial (make sure to remove the curly braces {}) and hit enter. The spreadsheet is now downloaded as file.xlsx to your current working directory.
Upload a new spreadsheet
Open as App's API can also be used to replace the spreadsheet of your app and upload a new one. E.g. this is useful to replace the data of your app with the most recent export from your backend system.
The following PowerShell script uses the POST /apps/{id}/spreadsheet endpoint to replace your app's current spreadsheet with a newly uploaded one. Of course, you can also use any other programming language of your choice as the API is a simple HTTP based REST API.
Invoke-WebRequest https://api-staging.openasapp.net/v1/api/apps/{appId}/spreadsheet -Method Post -ContentType "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" -InFile file.xlsx -H @{"Authorization" = "Token {ApiToken}"}
In order to update the spreadsheet of your app using this code, simply open Powershell in Windows, copy&paste the code snippet above, replace the variable {appId} with the id of your app and {apiToken} with the API-Token you generated in the beginning of this tutorial (make sure to remove the curly braces {}) and hit enter. The file file.xlsx from your current working directory is uploaded and replaces the data of your app.
Summary
That's it! That is how easy it is to use Open as App's API to update and download the spreadsheet of your app, e.g. to create apps that are updated with Excel exports from a backend system in a regular interval.