Podtest

There are a few steps to get things started. I don’t need access to any of your accounts except for Voiceflow.

Alexa developer account

Assuming you’re registered with Amazon for shopping, you can sign in with the same account details at https://developer.amazon.com/en-US/alexa. Ideally this account will be the same one you’ve got an Alexa device configured with, as you’ll be able to test on that device. Don’t worry if you don’t have an Alexa device, it’s not required.

That should set up an account for working with Alexa. You shouldn’t need to do too much in there other than submit the skill when we’re happy.

Google Sheets

A Google Sheet will serve as the data source for the skill.

You’ll need to set up a Google Sheet with the first row containing lowercase header values of:

• title
• description
• url

The sheet with the podcast data should be named MasterData. There can be other sheets in the spreadsheet – they’ll be ignored.

The sheet needs to be accessible online.

Go to File and click Publish to the web.

The two main points here are to publish as a Web page and ensure the Automatically republish when changes are made checkbox is active.

My published sheet is available at https://docs.google.com/spreadsheets/d/e/2PACX-1vQjlZgPAaC0adC9SZEbXikCqaZbKqFQGDy5t7qQHJI7q_GelQUCLFDIhQvRXLYg4XT1cw3tf9mwf_l5/pubhtml, though I don’t think you’ll need it for reference.

When you add a new podcast episode, it should be added at the bottom of the sheet. Also there shouldn’t be any empty rows between rows of data.

Before finishing with the sheet, you will need the ID of the sheet. That’s the long string of letters and numbers in the URL, not including any forward slashes. Make a note of that as it will be needed in the next step.

Google App Scripts

It is possible to connect Voiceflow directly to a Google Sheet, however there appears to be maximum amount of data that can be imported by this method. If the sheet grows beyond that threshold, the skill stops working.

To avoid this, we need to set up a Google App Script that will get just the required data from the sheet and return it to Voiceflow.

Go to https://www.google.com/script/start/ and click one of the Start Scripting buttons. Click New project.

Enter a project name. You can leave the file name unchanged.

Highlight and copy all the code in the box below and then paste it into the App Script editor. Then replace the ENTER_YOUR_SHEET_ID_HERE text on the second line with the ID of your sheet. Take care not to delete either of the single quotes.

function doGet(request) {
  var sheetKey  = '__ENTER_YOUR_SHEET_ID_HERE__';
  var sheetName = request.parameters.sheet;
  var headerRow = request.parameters.header;
  var startRow  = request.parameters.startRow;
  var latestRow  = request.parameters.latest;
  var searchTerm  = request.parameters.searchTerm;
  var random  = request.parameters.random;
    
  var spreadsheet = SpreadsheetApp.openById(sheetKey);
  var keys = getHeaderRowKeys_(spreadsheet, sheetName, headerRow);
  var data;
  var sheet = spreadsheet.getSheetByName(sheetName);
  if ( latestRow != undefined ) {
    data = readData_(spreadsheet, sheetName, keys, sheet.getLastRow(), 1);
  } else {
    data = readData_(spreadsheet, sheetName, keys, startRow);
  }

  if ( searchTerm != undefined ) {
    var maxResults = 50;
    searchTerm = Array.isArray( searchTerm ) ? searchTerm[ 0 ].toLowerCase() : searchTerm.toLowerCase();
    var newData = [];
    for ( var c = 0; c < data.length; c++ ) {
      if ( data[ c ].title.toLowerCase().indexOf( searchTerm ) > -1 || data[ c ].description.toLowerCase().indexOf( searchTerm ) > -1 ) {
        newData.push( data[ c ] );
        maxResults -= 1;
        if ( maxResults < 1 ) {
          break;
        }
      }
    }

    data = newData;
  } else if ( random != undefined ) {
    var max = data.length - 1;
    var row = Math.floor( Math.random() * Math.floor( max ) );
    data = [ data[ row ] ];
  }

  var response = JSON.stringify({ records: data });
  var output = ContentService.createTextOutput();
  
  output.setContent(response).setMimeType(ContentService.MimeType.JSON);

  return output;
}

function readData_(spreadsheet, sheetName, properties, startRowNum, latestRow) {
  if (typeof properties == "undefined") {
    properties = getHeaderRowKeys_(spreadsheet, sheetName);
  }
  
  var rows = getDataRows_(spreadsheet, sheetName, startRowNum, latestRow);
  var data = [];
  for (var r = 0, l = rows.length; r < l; r++) {
    var row = rows[r];
    var record = {};
    for (var p in properties) {
      record[properties[p]] = row[p];
    }
    data.push(record);
  }
  return data;
}

function getDataRows_(spreadsheet, sheetName, startRowNum, latestRow) {
  if (typeof startRowNum == "undefined") startRowNum = 2;
  
  var sheet = spreadsheet.getSheetByName(sheetName);
  var rangeEnd = latestRow != undefined ? 1 : sheet.getLastRow() - ( startRowNum - 1 );
  return sheet.getRange(startRowNum, 1, rangeEnd, sheet.getLastColumn()).getValues();
}

function getHeaderRowKeys_(spreadsheet, sheetName, rowNum) {
  if (typeof rowNum == "undefined") rowNum = 1;
  return getHeaderRow_(spreadsheet, sheetName, rowNum).map(function(value) { 
    return value.replace(/\s+/g, '_');
  });
}

function getHeaderRow_(spreadsheet, sheetName, rowNum) {
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
}

Go to File and click Manage versions…

Enter something like Initial version into the changes field and click the Save new version button. I may add one or more features to this script, so you may have to add new code later and save another version. You’ll just follow these and the following steps if that happens.

Click on Publish and Deploy as web app…

Set the Project version to the latest available. When starting you should only have 1 to select from. Who has access to the app should be set to Anyone, even anonymous then click Deploy or Update.

Because your script will be able to access sheets in your Drive account, you need to give permission to the script. Click the Review Permissions button.

Click on your Google account that owns the spreadsheet.

You’ll see a warning that the app isn’t verified. You don’t need to verify it as you’re the only person using the app. Click the Advanced link and then the Go to… link.

Click the Allow button to grant the script access to the Google sheet.

Copy the Current web app URL as we’ll need this to connect Voiceflow to the sheet and then click the OK button.

Voiceflow

You can register for a free account at https://www.voiceflow.com/.

I will need your login details to this account to set up the skill for you, but once we’re happy with it, you can update your account password.

That’s my prototype skill below as it stands currently. If all goes well, then I’ll look into creating a training course for podcast owners and I’ll let you have access to that to help you understand what’s going on.

While Voiceflow is largely a drag and drop tool, this skill does contain several code blocks, particularly for search functionality. However you can create skills completely with no code, so you may be interested to check the docs at bottom left.

Once set up, you can test the skill online by clicking the Prototype tab and Start Test button. You can either type responses or hold the space bar and speak commands. The latter hasn’t worked for me, but my input settings may be scrambled.