There are some good use cases for using a Google Spreadsheet as a back-end for a mobile application. Here are few:
- Quick prototyping: It is easy to fireup a spreadsheet and populate data than setting up a back-end server with database.
- Departmental data: Sales, Marketing and other departments might already have lot of data in spreadsheets that they would like to leverage
- Small businesses: Spreadsheets are easier to create and manage than database infrastructure
In any case, let us see how we can create a mobile app on OmnyPlay with Google Spreadsheet as a the back-end. Initially we thought of providing a native connector to google spreadsheet, but never got it up on the priority list.
I recently came up with two proxy services which provide a very easy way to use spreadsheet as back-end.
- www.sheetsu.com (You can get a discount by following the link or click
We will use Sheetsu and walk thru creating an app. We will try to build a shopping app.
- Go to your google docs and create a spreadsheet with two sheets, one to hold Product catalog and other to store the order information. You can copy the sample spreadsheet from https://docs.google.com/spreadsheets/d/1akRi3QZD1Q7n8HQRyOJO-585jVD7fniQArHYlCPJlsY/edit?usp=sharing
2. Go to www.sheetsu.com. Login with your google account (OAuth). They provide a free plan to try it. Once you are convinced, you can subscribe to one of their paid plans. (Follow this link to get a discount
3. Once logged in, select JSON API from the dashboard
4. Cut and paste the URL to your google spreadsheet that you would like to use
5. Go to settings and make sure you have all the CRUD permissions for the API. For simplicity sake we leave the Authentication turned off. You can turn it on and configure basic authentication.
6. Login to hub.omnyplay.com and create a new app. While creating the deck, use the Sheetsu API URL
7. Create your deck using the designer. Here I have created four cards, Products (a collection of Products), Product (product details), Orders (List of orders), Order (order details).
8. Sheetsu exposes CRUD operations to the google spreadsheet as REST APIs. Every sheet has an API end point. For example to access the products
To access orders sheet
9. To Create an order, we do a post request to the API with needed data.
10. When creating the order, we might want the Order ID to be auto generated. To accomplish the same, we can use scripting functionality of Google Spreadsheets. You can follow this tutorial to create Auto-increment functionality
Auto-incrementing ID Columns in Google Sheets
One fun aspect of answering Cloudstitch support tickets is seeing what kinds of crazy cool spreadsheet-powered web apps…
11. We can publish our Micro-App and test it in the emulator
Enjoy building apps from google spreadsheets now.