In this tutorial, we are going to create a Quote of the Day skill.  This skill is simple.  Each day, the skill will respond with a new quote.  If you launch the skill multiple times on the same day, it will return the same quote.

Step 1:  Create A Google Sheets API Key

The first thing we need to get is an API Key so we can call the Google Sheets API and get our data out of our spreadsheet.

Navigate to the Google Developers console and login with your Google account if you are not already logged in:

https://console.developers.google.com

Click on the "Create Project" button to the right and then click "Create".

Give your project a name.  For this walkthrough I named it "API Access".

Click the "Create" button to create your project.

Now, click the "+Enable APIs and Services" button at the top of the screen.

Type "Google Sheets" in the search box and then click on the "Google Sheets" API box.

Click the "Enable" button to activate this API.

Click on the "Credentials" menu item in the right side menu.

Click the "Create Credentials" dropdown and choose "API Key"

Copy the new API key and save it somewhere.  We'll use it in a minute.

Step 2:  Create Your Google Sheet

Now we need to create our spreadsheet and populate it with data.  We are going to create a single column spreadsheet with 365 rows and populate it with one quote per row.  This is one row for each day.  The built-in row numbers will represent the days of the year, 1 to 365.

Navigate to Google Sheets and login with your Google account if you are not already logged in:  https://google.com/sheets

Click the big green plus sign to Start a New Spreadsheet.

Give your spreadsheet a name and enter your responses for each day.  To cover the entire year, you would have 365 rows.

Click the "Share" button at the top right.

Click on the "Advanced" link at the bottom right.

Where it says "Private - Only You Can Access", click on "Change".

Choose "On - Anyone with the Link".

Click "Save" and then click "Done".

In the URL of the page, copy the Google Sheet ID and paste it somewhere to use later (an empty google sheet cell, a google doc, or a text editor of your choice).  The Google Sheet ID is highlighted in the example below:

Step 3:  Call the Google Sheets API from your Skill

Now that we have our API Key and have created our spreadsheet, the final step in this process is to actually call the Google Sheets API and get data out of the spreadsheet.

To do this, add an API Function to your skill.  On the Data tab of the intent, click the first icon in the toolbar at the bottom of the page.

In the API Url block, enter the following URL for your Google Sheet.  You'll need the API Key and Sheet ID that you captured above:

https://sheets.googleapis.com/v4/spreadsheets/YOUR_SHEET_ID/values/Sheet1!A{{dayOfYear}}:A{{dayOfYear}}?key=YOUR_API_KEY  

Replace the "YOUR_SHEET_ID" and "YOUR_API_KEY" strings with the Sheet ID and API Key you captured above.

In the "And Assign these values..." section, enter "todaysQuote" for the variableName and "response.values[0][0]" for the value and hit enter.

Your API Function should look like this:

Now, on the Response tab, enter "{{todaysQuote}}" in your response to have Alexa say todays quote to the user.

That's it!  You can view this sample project in Voice Apps.  Look for the "Quote Of The Day - Google Sheets" project.

As always...if you have any problems, questions, or comments,  please let us know!  We're here to help!

Did this answer your question?