Google Sheets CMS Integration

Learn how to use Google Sheets as CMS for your Alexa Skills and Google Actions.

Introduction

With this Jovo CMS integration, you can manage all the content of your Alexa Skills and Google Actions in a Google Spreadsheet. This makes collaboration easier and enables you update and add content faster.

Here is what a sample spreadsheet could look like: Google Sheets CMS for Alexa and Google Assistant

You can use this Spreadsheet as a starter template.

Configuration

To get started, install the following package:

Add it to your app.js file and register it with the use command:

Next, add configurations like the spreadsheetId to your config.js file:

Each sheet can be added as an object that includes both a name and a type. Learn more about Sheet Types below.

Additional configuration might differ depending if you want to use a publicly accessible or private spreadsheet:

Public Spreadsheets

Tutorial: Use Google Sheets as CMS for your Voice App

Public spreadsheets allow you to get started quickly whithout having to care about credentials. We recommend setting up a public spreadsheet first and then turning to private spreadsheets later.

For public spreadsheets, you need to add the following to your config.js file:

The additional information you need to add for public spreadsheets is the position of the sheet. It is the position of the tab the sheet is located in.

Private Spreadsheets

Tutorial: Use Private Google Spreadsheets as a CMS

With private spreadsheets, you can control who has access to your content. This comes with the price of a few more extra steps to set it up.

For private spreadsheets, you need to add the following to your config.js file:

To make private spreadsheets work, you need to create a service account and security credentials. These can be downloaded as a JSON file and then referenced in the credentialsFile element (default is ./credentials.json).

Default Sheet Types

Google Sheets offers flexible ways to structure data. This is why the Jovo CMS integration supports several sheet types that are already built in:

Default

If you don't define a sheet type in the config.js, you receive an array of arrays that can be accessed like this:

Responses

If you define the sheet type as Responses, the integration expects a spreadsheet of at least two columns:

  • a key
  • a locale, e.g. en, en-US, or de-DE

For this locale, you can then access the responses like this:

You can add as many locales as you want by adding additional columns for each key.

KeyValue

If you define the sheet type as KeyValue, the integration expects a spreadsheet of at least two columns:

  • a key
  • a value

For every key, this will return the value as a string:

ObjectArray

If you define the sheet type as ObjectArray, you will receive an array of objects where each row is converted to an object with the first row of the spreadsheet specifying the keys

Here's an example sheet:

Name Location Date
Voice Summit Newark, New Jersey, USA 7/22/2019
SuperBot San Francisco, California, USA 4/2/2019

And here's the array of objects you will receive:

Access the array using:

Defining your own Sheet Type

You can extend the Default sheet and pass it to the SpreadsheetCMS object like this:

You can then reference the sheet type by its name in the config.js file:

Comments and Questions

Any specific questions? Just drop them below or join the Jovo Community Forum.

Join Our Newsletter

Be the first to get our free tutorials, courses, and other resources for voice app developers.