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.
- Using the Jovo Google Sheets CMS Integration
- Next Steps
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:
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.
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
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
We will later use this credentials file in our Jovo config.
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:
Find a general introduction here: Tutorial: Use Google Sheets as CMS for your Voice App.
config.js file, you need to make a few changes so that it works with the private spreadsheet:
private (which is the default setting) and reference the
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.
No need to change here! You can still access the content of your spreadsheet with the
If you want to pass additional parameters, you can add them as an object:
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: