Use Private Google Spreadsheets as a CMS

Learn how to get the credentials to use a private Google Spreadsheet in your Alexa Skills and Google Actions.

Get started with public spreadsheets here: Tutorial: Use Google Sheets as CMS for your Voice App.

• Introduction
• Credentials
  • Google API Console
  • Spreadsheet Permissions
• Using the Jovo Google Sheets CMS Integration
  • Configuration
  • Accessing the Content
• Next Steps

Introduction

In a previous tutorial (Use Google Sheets as CMS for your Voice App), we used the Jovo Google Sheets Integration to create a public spreadsheet that can be used to store and update content for your Alexa Skills and Google Actions.

The public spreadsheet looked like this:

You can find the Hello World Spreadsheet here.

Very often, public spreadsheets are a great starting point. However, in many cases you might not want to allow anyone to access your spreadsheet (even though public means that people still need to have the full URL to be able to access it).

Luckily, the Jovo Google Sheets Integration works the same way for both public and private spreadsheets.

Credentials

To convert the integration from a public spreadsheet to a private spreadsheet, you need to do two things:

• Create a service account and credentials in the Google API Console
• Invite the service account to the spreadsheet

Google API Console

First, you need to enable the Google Sheets API in the Google API Console:

Click the "Enable APIs and Services" button and search for "Sheets" to find the Google Sheets API:

The Google Sheets API overview has a blue "Enable" button that you need to click:

The next step is to create the credentials for this API. You can do this by clicking on the "Create Credentials" button:

In the "Add credentials to your project" menu, select "Google Sheets API", "Web server", and "Application data" as shown below:

Next, you need to create a service account. The "Service account ID" element also shows an email address that we're going to use in a later step:

Saving this service account will save a JSON file to your computer.

Use this file and save it anywhere in your src folder of your Jovo project. You can also rename it. For example, we could name it google-sheets.json and save it in a credentials folder:

We will later use this credentials file in our Jovo config.

Spreadsheet Permissions

As a next step, we need to make the spreadsheet accessible through the service account. Create a spreadsheet or make a copy of the spreadsheet mentioned above (here's the link). Click File > Make a copy... and save it to your own Google Drive. By default, spreadsheets are set to private.

To add your spreadsheet to the service account, you need to use the above mentioned email address and invite it:

You can find the email in your credentials JSON file:

Using the Jovo Google Sheets CMS Integration

Find a general introduction here: Tutorial: Use Google Sheets as CMS for your Voice App.

• Configuration
• Accessing the Content

Configuration

In your config.js file, you need to make a few changes so that it works with the private spreadsheet:

Set access to private (which is the default setting) and reference the credentialsFile.

As a reminder, the spreadsheetId can be found in the URL of your spreadsheet:

You can find the full documentation here: Jovo Google Sheets CMS Integration.

Accessing the Content

No need to change here! You can still access the content of your spreadsheet with the t() notation:

If you want to pass additional parameters, you can add them as an object:

Learn more about i18n here.

Next Steps

That's it! If you now test it, you should be able to get the responses you defined in your Google Spreadsheet.

You can test it locally by using the following command:

Any questions? Please let us know in the comments below 👇. You can also reach us on Twitter or Slack.