Archivi categoria: Twitter Posts

How to create an IVR that reads from Google Sheet with Amazon Connect and AWS Lambda

In this quick tutorial, I will explain how to create a basic IVR that can access a Google Sheet and communicate a 3 digit code via voice.

This tutorial assume that you have a basic knowledge of AWS Console, what is AWS Lambda (the demo function is written in Javascript) and how to create, upload and debug a function via the console.

Need: we have this basic “manual” and “human” flow. We have a safe with a 3 digit code. This safe contains the key of a specific room in the office that must be protected but sometimes opened by guests when no staff is in the office, and we want to give them access only after a phone authorization. When someone has access, the code is changed (usually the day after).

So a guest now do this:
– in front of the safe there is a phone number.
– the guest calls the phone number
– me or my colleague answer and give him the code (The code is stored in a Google Sheet to permit us to find and remember it easily after a change)
– the guest opens the safe and take the key of the room
– the day after, the member of the staff who received the call changes the safe code and update the Google Sheet

What we want now, is to change this flow to avoid that someone of the staff answer to the phone in the night only for giving a stupid 3 digit code 🙂

Start with Amazon Connect.

1) Claim a new Phone Number

1 - claim phone number

2) create a contact flow

2 - create contact flow

3 - create contact flow

3) edit the contact flow like this

4 - contact flow details

4) look at the configuration of “Invoke AWS Lambda Function”

Specifically, in the “Invoke AWS Lambda Function”,  you must insert your function ARN.
By now you haven’t created the Lambda function yet, so leave this field empty, but after creating the function you will insert the ARN which is a code like this:


4) change the “Play prompt” configuration

The upper “play prompt” must be configured like in this image

5 - play prompt details


the lower one is simply an audio that plays “There was an error”

That’s all by now, simply save and publish.

100 - publish

If you call the phone number now, you will hear “There was an error”


Now, let’s create a Lambda function. You have to edit it in your local editor, the lambda function must be uploaded via .zip file (or s3) with the whole node_modules directory and the Google credentials.

Disclaimer: This function is not well formatted and organized, I copied and pasted it adding some spaghetti code, so rewrite it as you prefer 🙂 however it works.

var fs = require('fs');
var readline = require('readline');
var google = require('googleapis');
var googleAuth = require('google-auth-library');
// If modifying these scopes, delete your previously saved credentials
// at ~/.credentials/
var SCOPES = [''];
//var TOKEN_DIR = (process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE) + '/.credentials/';
var TOKEN_DIR = './.credentials/';
var final_callback = null;
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
function authorize(credentials, callback) {
    var clientSecret = credentials.installed.client_secret;
var clientId = credentials.installed.client_id;
var redirectUrl = credentials.installed.redirect_uris[0];
var auth = new googleAuth();
var oauth2Client = new auth.OAuth2(clientId, clientSecret, redirectUrl);
// Check if we have previously stored a token.
fs.readFile(TOKEN_PATH, function(err, token) {
        if (err) {
            getNewToken(oauth2Client, callback);
} else {
            oauth2Client.credentials = JSON.parse(token);

 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 * @param {google.auth.OAuth2} oauth2Client The OAuth2 client to get token for.
 * @param {getEventsCallback} callback The callback to call with the authorized
 *     client.
function getNewToken(oauth2Client, callback) {
    var authUrl = oauth2Client.generateAuthUrl({
        access_type: 'offline',
scope: SCOPES
console.log('Authorize this app by visiting this url: ', authUrl);
var rl = readline.createInterface({
        input: process.stdin,
output: process.stdout
rl.question('Enter the code from that page here: ', function(code) {
oauth2Client.getToken(code, function(err, token) {
            if (err) {
                console.log('Error while trying to retrieve access token', err);
            oauth2Client.credentials = token;

 * Store token to disk be used in later program executions.
 * @param {Object} token The token to store to disk.
function storeToken(token) {
    try {
} catch (err) {
        if (err.code != 'EEXIST') {
            throw err;
    fs.writeFile(TOKEN_PATH, JSON.stringify(token));
console.log('Token stored to ' + TOKEN_PATH);

function readCode(auth) {
    var sheets = google.sheets('v4');
        auth: auth,
spreadsheetId: 'XXXXXX', // id of the spreadsheet
range: 'SHEET_NAME!A1:A1', // cell name for the code
}, function(err, response) {
        if (err) {
            console.log('The API returned an error: ' + err);
        var rows = response.values;
if (rows.length == 0) {
            console.log('No data found.');
} else {
            if (final_callback) {
                final_callback(null, {code: rows[0][0]});

exports.handler = (event, context, callback) => {
    // Load client secrets from a local file.
fs.readFile('client_secret.json', function processClientSecrets(err, content) {
        if (err) {
            console.log('Error loading client secret file: ' + err);
        // Authorize a client with the loaded credentials, then call the
        // Google Sheets API.
authorize(JSON.parse(content), readCode);
final_callback = callback;
exports.handler('1','2', (a, b) => console.log(b.code));

in the readCode function you have these 2 parameters to fill:

spreadsheetId: 'XXXXXX', // id of the spreadsheet
range: 'SHEET_NAME!A1:A1', // cell name for the code

Some things to remember:

– You will need a Google client_secret.json file
– You have to execute this function at least one time from your PC. The function will ask you to visit a Google link to obtain a token and paste it back. The token is then saved in a .credentials folder inside your project directory (you have to create it!!). Then, the credentials file must be uploaded with the function to Lambda.
– You have to include the whole node_modules directory to the uploaded zip file.
– create the Lambda function in the same region of Connect

After doing this, the last things to do are:

– Connect the Amazon Connect “Invoke AWS Lambda Function” node to the right function ARN, you can find it in the AWS Lambda section in the upper right corner.
– Add the correct permissions to Connect for accessing the Lambda function, this must be done via AWS CLI with this command

aws lambda add-permission –function-name function:lambda-function-name –statement-id 1 –principal  –action lambda:InvokeFunction –source-account 123456789012 –source-arn arn:aws:connect:eu-central-1:123456789012:instance/faf03769-52ce-4577-bedf-dd82c9a933c8 –region eu-central-1

Parameters are:

–function-name is always “function:xxxx” where xxx is the lambda function name
–source-account is your Amazon account ID, you find it in the upper-right “My Account” section of AWS Console
–source-arn is the Amazon Connect ARN
–region  is the Connect and Lambda region

That’s all, if you have done all correctly, you will hear a prompt with the code present in your Google Sheet file.

Next step, this code should be improved to add some controls, for example to save the CLI for the caller and to block anonymous calls, so our staff can know when and who called.

If something is not clear feel free to write me an e-mail.


Fabio Lombardo