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:

• A CMS can be used but brings additional costs and might be overkill. It also might need a data connection to retrieve all content;
• Static translations or resource files is an option but makes it harder to change content.

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.

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:

<img src="./google_console.png" alt="Google Console">

The next step is to generate a secret json file:

<img src="./secret_1.png" alt="Secret 1">

<img src="./secret_2.png" alt="Secret 2">

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

Create a new Google Sheet

The sheet I use is simple:

<img src="./google_sheet.png" alt="The sheet">

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

• Copy the email address from the secret.json file
• click on Share (the blue button at the right top of the sheet) and add the email address:

<img src="./permissions_1.png" alt="Permissions 1">

<img src="./permissions_2.png" alt="Permissions 2">

The App

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

• SheetToObjects.Lib
• SheetToObjects.Core
• SheetToObjects.Adapters.GoogleSheets

<img src="./adding_packages.png" alt="Adding packages">

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

• for iOS, just add it to the root
• for Android, add it to the Assets folder

<img src="./added_json.png" alt="Added json">

For Android set the Build Action to AndroidAsset:

<img src="./set_build_action.png" alt="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
                                          .ConfigureAwait(false);

            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")
                                                                                  .WithDefaultValue<bool>(true)
                                                                                  .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:

• How to allow access from the API to the sheet (adding the email address from the json file!)
• How to read a json file in Android (with a work around to copy the contents from the asset file to a temp file)
• I added a implementation for protected sheets to the SheetToObjects]( https://github.com/josdeweger/SheetToObjects ) nuget package

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!

Links

App source: GitHub

SheetToObjects library, created by Jos de Weger: SheetToObjects