Using Google Sheet as CMS for your App - Part 1

Reading time: 4 minutes

Imagine you had an easy to understand CMS without too much overhead, just to add translations and a bit of content for your (Xamarin) app. A CMS with an “excel-like” interface, an interface most of your customers would understand without even a training? Say “Hi” to Google Sheets & Azure Functions!

Any time I start a new app project the same question arises: how do we maintain content in a (Xamarin) app:

Just recently I had a customer who literaly said: “I don’t want a CMS. It is slow, hard to maintain, difficult to quickly add new content. Can you use Google Docs?”. Just a few minutes of research and I had it up and running!

In this first part I will explain how to use a Google Sheet as a CMS for your Xamarin App by connecting your app straight to the Google Sheets API. In another post I will create a more reliable and professional API by adding Azure Functions as a proxy so we can add caching.

The sample

For this post I will create a simple app with a list of jobs and a detail page. I will use Xamarin Forms to keep it simple and clean.

List Details
Job list Job details

Enable the Google Sheets API

What needs to be done is this: in the Google Developers Console the Google Sheets Api needs to be enabled:

Google Console

The next step is to generate a secret json file:

Secret 1

Secret 2

Keep the secret.json file, we will use this later.

Create a new Google Sheet

The sheet I use is simple:

The sheet

To enable access for the Sheets API you have to add the API user’s email to the share permissions.

Permissions 1

Permissions 2

The App

I created a Xamarin Forms app and added the following nuget packages to the NetStandard core project

Adding packages

Next I added the secret.json file to the iOS and Android project:

Added json

For Android set the Build Action to AndroidAsset:

Build Action

The JobService

The JobService is responsible for getting the content from the sheet and map it to the model. Set the required parameters and map the colums to the model:

var sheetData = await provider.GetAsync(filePath,
                                                    "Job offers", // sheet tab name
                                                    "SHEET ID", // The sheet id (from the url)
                                                    "A:G") // the range

            var sheetMapper = new SheetMapper().AddConfigFor<JobOffer>(cfg => cfg
                                                                       .MapColumn(column => column.WithHeader("Title").MapTo(m => m.Title))
                                                                       .MapColumn(column => column.WithHeader("Introduction").MapTo(m => m.Introduction))
                                                                       .MapColumn(column => column.WithHeader("Text").MapTo(m => m.Text))
                                                                       .MapColumn(column => column.WithHeader("Tags").MapTo(m => m.Tags))
                                                                       .MapColumn(column => column.WithHeader("Color").MapTo(m => m.Color))
                                                                       .MapColumn(column => column.WithHeader("Date").MapTo(m => m.Date))
                                                                       .MapColumn(column => column.WithHeader("Published")
                                                                                  .MapTo(m => m.Published)));

If you look at the source you can see I use the DependencyService to get the file location of the secret file. The problem is that, AFAIK, it is not possible to access a file in Android by path since it is ‘zipped’ into the APK. I created a SecretService on Android and iOS to fix this issue.

What’s next

I didn’t show a lot of code. The code is simple anyway. It took me some time so sort a few things out:

I am going to write a post about adding Azure Functions between the app and the sheet as a proxy with caching. Next I will try to add a post about using the HTTP ETag to prevent downloading unnecessary data.

Let me know if you have questions or ideas about using Sheets as a CMS!


App source: GitHub

SheetToObjects library, created by Jos de Weger: SheetToObjects


comments powered by Disqus