Google spreadsheets as a data source for mobile apps.
Building a pro-typical application that takes real data as a source of information is really troublesome. We have to think about the time, cost, and scope of the project.
We can think of solutions such as building an application to stored data or use CMS. If you are not used to off these systems then it will take time to learn them. Learning this platform is itself a challenging part cause of the learning curve and could be daunting to figure out how to use it.
On the contrary, the spreadsheet is something that people are familiar with and has been using for quite a long period of time.
The story of Using Excel sheet as Data
I have to build a mobile application for an organization( Voluntary ) that shows the information about the users and its employee schedule. It’s structured data. The catch was these data had to be frequently edited by a non-technical person.
I consider several solutions, including using a full backend as a service and also the CMS too but
People are less likely to do the migration. They were using the excel sheet as a source of information to the users The biggest challenge was using excel sheet as a source of information and they don’t want to do migration cause excel has lots of features and they are comfortable with it.
So I choose the non-orthodox solution i.e. to use a google spreadsheet as a source of information.
When is a spreadsheet-based database a solution?
Using Google spreadsheets as a database for mobile applications is not a mainstream solution, and it may or may not be suitable for your next project.
To help you decide whether this is a good option, I’ve put together the following list of considerations.
Note: we’re talking about a spreadsheet, which works great for structured, tabular data. But doesn’t work well for a document/object store.
Other than that, here are some pros and cons to consider:
Pros
- It’s free
- Very easy to set up — no need for API keys or complicated SDKs
- Zero maintenance
- You get a data editing GUI for free
- You get write access management for free
- Can include internal calculations using spreadsheet functions
- The app that uses the data can be easily upgraded in a later phase to use a real database, as data is exposed as standard JSON
- Some level of automation can be achieved by using apps-scripts in combination with time-driven triggers
- It can be combined with Google Forms for data collection
Cons
- No server-side filtering logic to talk about
- All the data you want to access has to be publicly accessible
- The entire database is manually-editable, so a human error may break the application. For example, if someone accidentally changes the label of some field, it won’t be available for the application.
This can be partially remedied by protecting the critical cells - You can have up to 2 million cells in a spreadsheet
How I implemented this
My implementation is based on the fact that once a spreadsheet is published to the web, it’s also available as a standard RSS feed, which can be fetched and parsed.
To publish your Google Sheet and go to File > Publish to the web
You will get this dialog menu.
To keep your changes update automatically on the site we can check Automatically republish when changes are made.
If you open the URL here. You can see the excel sheet data published on the web.
When you are viewing any Google Sheet in your browser you are viewing it using a URL like:
https://docs.google.com/spreadsheets/d/[sheet_id]/edit
Of course, [sheet_id] is replaced with the actual id for your sheet, but the URL demonstrates what you will see. Once you publish your Google sheet to the web you are given a slight variation on that URL:
https://docs.google.com/spreadsheets/d/[sheet_id]/pubhtml
This is the URL you will share with the public, allowing them to view the data you have in your spreadsheet in their browsers. In order to get at a JSON representation of the data you just need to learn the right way to craft the URL using the same sheet id:
https://spreadsheets.google.com/feeds/list/[sheet_id]/default/public/values?alt=json
Now let’s get the same data in our console.
Here’s a live app you can play with.
It’s worth considering using Google Spreadsheets as a data source for a mobile application, especially if you’re just building a quick prototype or informative app. It has some unique advantages, and implementation is easy too.
