List Twilio Flex Users with Functions and Google Sheets

Time to read: 6 minutes

July 29, 2024
Written by
Bry Schinina
Twilion
Reviewed by
Jose Tirado
Twilion
Paul Kamp
Twilion

List Twilio Flex Users with Functions and Google Sheets

In this post, you’ll learn how to customize a Google Sheet to pull your Twilio Flex user details into a spreadsheet with a single button click. No extra steps, no complicated menus—just straightforward access to the latest details right when you need them.

And the best part? It takes less than fifteen minutes to get up and running, even if you're new to coding.

Demoing populating a Google sheet with Twilio Flex users.

Ready to get started?

For this tutorial, you will need:

Twilio Serverless Function

A Twilio Serverless Function will be used to securely pass Flex user data to the online spreadsheet.

We’ll be using the Twilio Console’s Function Editor to create the Serverless Function, Node.js code hosted by Twilio.

Create a Service

Functions are contained within Services. To create a Service:

Create Environment Variables

Add your TaskRouter Workspace SID as an Environment Variable in your Service.

To find your Workspace SID, navigate to the TaskRouter Console > Workspaces and copy the identifier displayed under Flex Task Assignment.

Finding a Workspace SID

To add the Environment Variable to your new Service, look for the Settings & More area and select Environment Variables.

Set a Functions environment variable with your Workspace SID

Enter WORKSPACE_SID as the Key and your identifier (WSxxxxx) as the Value. Click Add to complete this step.

Add a Function

Next, we’ll create a Function that uses the twilio helper library to fetch a list of Flex users from the TaskRouter Worker API.

Add a new Function called workers:

Add a new Function at /workers

Replace the sample code in the editor with the following: 

exports.handler = async function (context, event, callback) {
    //configure headers
    const response = new Twilio.Response()
    response.appendHeader('Access-Control-Allow-Origin', '*')
    response.appendHeader('Access-Control-Allow-Methods', 'OPTIONS POST GET')
    response.appendHeader('Access-Control-Allow-Headers', 'Content-Type')
    response.appendHeader('Content-Type', 'application/json')
//log request user-agent 
const request_user_agent = event.request.headers['user-agent'] 
console.log(request_user_agent)
    try {
        const client = context.getTwilioClient() //helper library
        const workspaceSid = context.WORKSPACE_SID //environment variable
        //get workers from taskrouter/flex
        const workers = await client.taskrouter.v1.workspaces(workspaceSid).workers.list()
        //format data for table - comment out any unwanted columns
        const data = workers.map(worker => {
            let attributes = JSON.parse(worker.attributes)
            return {
                Email: attributes.email,
                Sid: worker.sid,
                //FriendlyName: worker.friendlyName,
                FullName: attributes.full_name,
                //Attributes: attributes,
                DateStatusChanged: worker.dateStatusChanged,
                //DateUpdated: worker.dateUpdated,
                //DateCreated: worker.dateCreated,
                Activity: worker.activityName,
            }
        })
        //set worker data in response body
        response.setBody(data)
    }
    catch (e) {
        //set error response
        response.setBody(e).setStatusCode(500)
        console.error(e)
    }
    //return response
    return callback(null, response)
}

Here, we are fetching TaskRouter Workers and returning the Worker data to be included in the table.

In the above code, we return a subset of the data available from Twilio.


Included

Excluded

  • Email
  • SID
  • FullName
  • ActivityName
  • DateStatusChanged
  • Roles
  • DateCreated
  • DateUpdated
  • Attributes

This can be tailored to your needs. By adding in values you want (uncomment the line) or removing the values you do not want (comment out the line), you can control what gets populated into the spreadsheet.

For example, to include all the data available, uncomment every value in the object returned from this function, like shown below:

return {
  Email: attributes.email,
  Sid: worker.sid,
  FriendlyName: worker.friendlyName,
  FullName: attributes.full_name,
  Attributes: attributes,
  DateStatusChanged: worker.dateStatusChanged,
  DateUpdated: worker.dateUpdated,
  DateCreated: worker.dateCreated,
  Activity: worker.activityName,
}

Once you’ve curated your desired Worker data, click the Save button to save your new Function.

Function Visibility

Then, change the Function’s visibility to public:

Make a Function public
Concerned about making your Function public? Good catch! We'll circle back to tighten security in a later step.

Deploy the Function

Finally, click on the blue Deploy All button.

Deploy a Twilio Function

Once the deploy has finished, click the Copy URL button to copy the Function’s URL to your clipboard. Keep this handy, as we will use it in a moment within Google Sheets.

Example:

https://get-flex-workers-1234.twil.io/workers

Google Sheet

Create a new Sheet

Open a new browser tab and create a new blank spreadsheet.

When using the Chrome browser, you can quickly spin up a new Google Sheet by entering sheet.new into the address bar.

Change the name of the default tab from Sheet1 to Worker List.

Google App Script

Google App Scripts is a powerful tool included with Google Sheets, allowing you to implement advanced custom logic in your spreadsheet. In this step, you’ll use Google App Scripts to style your Sheet, fetch data from your Twilio Function, and populate the data into a table.

Open the Editor

From the menu bar of the Google Sheet, select Extensions > Apps Script.

Edit or add a new Apps Script in the Google Sheets Extension menu

This opens a code editor, where you can start adding custom logic.

Configure Variables

First, let’s set up some global variables.

Paste the below code into the Google App Script editor: 

//configurable global variables
const getFlexWorkersUrl = "https://YOUR_FUNCTION_DOMAIN.twil.io/workers" 
const sheetName = 'Worker List' //name of your target sheet
const title = 'Twilio Flex Workers' //table title

Change the getFlexWorkersUrl to your deployed Function URL you set aside in an earlier step.

Optionally, you can also update title (which will populate in the A1 cell of the sheet) and sheetName which controls which Google Sheet tab will be modified.

Code

Next, add the remaining code.

In the editor, right underneath the variables you just configured, paste the following code: 

//target cell locations
const startCell = { row: 4, column: 1 }  //starting cell for worker list
const cellLocations = {
    title: "A1", //cell for title
    dateUpdated: "B1", //cell for date updated
    totalCount: "A2", //cell for total count of workers
    headerRow: startCell.row - 1 //header - one row above worker data
}
//get target sheet
const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName)
/* BUTTON FUNCTION */
const updateSheet = async () => {
    try {
        setupSheet()                        //1. add title, update style
        clearSheet()                        //2. reset data in table
        const workers = await getWorkers()  //3. get twilio data
        populateSheet(workers)              //4. update sheet
        addMetaData(workers.length)         //5. add timestamp and total user count
    } catch (e) {
        console.error(e)
    }
}
/* HELPER FUNCTIONS */
//style sheet
const setupSheet = () => {
    //set and style title
    sheet.getRange(cellLocations.title)
        .setValue(title)
        .setFontSize(14)
    //freeze top rows
    sheet.setFrozenRows(cellLocations.headerRow)
    //adjust height
    sheet.setRowHeights(1, cellLocations.headerRow, 25)
    //bold table header row
    sheet.getRange(cellLocations.headerRow, 1, startCell.column, 8)
        .setFontWeight('bold')
    //adjust column size
    sheet.autoResizeColumn(1)
}
//network request
const twilioRequest = (method, path) => {
    let options = {
        method: method,
        contentType: 'application/json',
        headers: {
            Accept: 'application/json',
        }
    }
    let response = UrlFetchApp.fetch(path, options) //external network request
    let data = response.getContentText()            //extract response data
    return JSON.parse(data)                         //parse and return JSON
}
//fetch worker data from Twilio Function
const getWorkers = async () => {
    console.log("** getting all workers from Twilio **")
    return await twilioRequest("GET", getFlexWorkersUrl)
}
//reset worker data in table
const clearSheet = () => {
    console.log("** clearing sheet **")
    let dataRange = sheet.getRange(startCell.row, startCell.column).getDataRegion() //range for all data
    let workerRange = sheet.getRange(cellLocations.headerRow, startCell.column, dataRange.getLastRow(), dataRange.getLastColumn()) //range for worker data
    workerRange.clearContent() //clear only worker data
}
//add table column headers 
const addTableHeaders = (workerItem) => {
    let tableHeaders = Object.keys(workerItem) //get column headers from worker data
    //add column headers to table
    sheet.getRange(cellLocations.headerRow, startCell.column, 1, tableHeaders.length)
        .setValues([tableHeaders])
    return tableHeaders //return table column headers 
}
//populate sheet with new data
const populateSheet = (workers) => {
    console.log("** populating sheet **")
    let workerData = workers.map(worker => Object.values(worker))  //format worker data for table
    let th = addTableHeaders(workers[0]) //add headers to table
    //update worker data
    sheet.getRange(startCell.row, startCell.column, workers.length, th.length)
        .setValues(workerData)
}
//add date and count metadata to table
const addMetaData = (count) => {
    console.log("** adding metadata **")
    //add timestamp for last time data was fetched
    let timezone = Session.getScriptTimeZone()
    let date = Utilities.formatDate(new Date(), timezone, "E, MMM d' at 'KK:mma")
    sheet.getRange(cellLocations.dateUpdated)
        .setValue(`Date updated: ${date}`)
    //add total count of workers
    sheet.getRange(cellLocations.totalCount)
        .setValue(`Total workers: ${count}`)
        .setVerticalAlignment("top")
        .setHorizontalAlignment("right") //optional style
    //resize columns
    sheet.autoResizeColumns(1, 10)
}

There’s a lot happening in the above code. If you're curious to understand how it works, the detailed comments within the code will help you grasp what each part is doing.

But if you’re short on time, you can skip that exploration. Let’s take a closer look at one function: updateSheet.

updateSheet function

This function is important because you'll link it to a button in Google Sheets in the following step. The comments explain the logic of each step.

const updateSheet = async () => {
  try {
    setupSheet()				//1. add title, update formatting/style
    clearSheet()				//2. reset data in table
    const workers = await getWorkers()	//3. get twilio data
    populateSheet(workers)			//4. update sheet
    addMetaData(workers.length)		//5. add timestamp and total user count
  } catch (e) {
    console.error(e)
  }
}

Save Script

Save by clicking on the save icon in the menu above the editor.

Save a Apps Script

Add a Button

Navigate back to the Google Sheet UI to create a custom button that will invoke the Google App Script code you just saved.

Insert a Drawing

From the top menu bar, select Insert > Drawing:

Insert a drawing in a Google Sheet

Create and style the button however you prefer. When you are ready, click Save and close.

Drawing a button in Google Sheets.

Edit Drawing

Right-click on the Drawing in the Google Sheet to get the three-dot edit option to appear. Click to edit and select Assign script from the drop-down menu.

In the pop-up prompt, enter the name of the function to connect–updateSheet–and click OK.

Assign a script to a Google Sheet button

Run a Test

Because your script is performing programmatic actions on your behalf, you will be prompted to enable certain Google permissions the first time you run this script.

Click your custom UPDATE button, and then select Allow to any of the Google authorization prompts that appear.

Once you’ve granted permission, your Google Sheet should be populated with data.

Quickly listing Twilio Flex users in a Google Sheet.

Voilà! Your workforce information, at the click of a button.

Securing your Twilio Function

Nice work so far, but there's a crucial step remaining. Remember earlier when we set the Function visibility to “public”? Let's tighten security now by only processing requests triggered by your specific Google Sheet.

Determine Google user-agent Header

HTTP requests originating from a Google App Script will have a specific user-agent header with a unique identifier for your Script.

In your deployed Function code, a console.log() is configured to reveal your Google App Script request user-agent in the Function logs. Let’s trigger a request to the Function again, and this time, monitor your “Live logs” in the Function console:

  • Enable Live Logs: Ensure "Live logs" feature is activated within the Functions editor.
  • Trigger the Function: From the Google Sheet, click the custom button.
  • Review Logs: Return to the Functions editor tab and observe the logs (example screenshot below)
Determine the user-agent header

The value from the execution log contains a unique string. In the example shown, the value is: 

Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: UAEmdDd-TGNFIDTwP_WX8W4naEXkFu6S-JA)

This identifier is exclusive to your Google App Script project. Next, you will use it to ensure the Function returns data only upon receiving this specific identifier.

Add Environment Variable

Let’s save the user-agent value from the previous step as a new Environment Variable in your Service.


Key

Value

GAS_USER_AGENT

Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: YOUR_UNIQUE_IDENTIFIER)


From the Function console, select Environment Variables again, and add your key/value pair, as shown below:

Set the user-agent variable in a Functions Environment Variable

Add Function Validation

Finally, let’s build validation into the Function with this user-agent variable.

Update your current workers Function code to the following: 

exports.handler = async function (context, event, callback) {
    //setup response    
    const response = new Twilio.Response()
    //validate requester 
    const request_user_agent = event.request.headers['user-agent'] //request user-agent
    const valid_user_agent = context.GAS_USER_AGENT //valid user-agent
    if (request_user_agent != valid_user_agent) {
        //error response
        response.setBody('Unauthorized')
 	.setStatusCode(401)
    } else {
        // configure proper headers
        response.appendHeader('Access-Control-Allow-Origin', '*');
        response.appendHeader('Access-Control-Allow-Methods', 'GET');
        response.appendHeader('Access-Control-Allow-Headers', 'Content-Type');
        response.appendHeader('Content-Type', 'application/json');
        // get workers from taskrouter
        try {
            const client = context.getTwilioClient()
            const workspaceSid = context.WORKSPACE_SID
            const workers = await client.taskrouter.v1.workspaces(workspaceSid).workers.list()
            //format data for table - comment out any unwanted columns
            const data = workers.map(worker => {
                let attributes = JSON.parse(worker.attributes)
                return {
                    Email: attributes.email,
                    Sid: worker.sid,
                    //FriendlyName: worker.friendlyName,
                    FullName: attributes.full_name,
                    //Attributes: attributes,
                    DateStatusChanged: worker.dateStatusChanged,
                    //DateUpdated: worker.dateUpdated,
                    //DateCreated: worker.dateCreated,
                    Activity: worker.activityName,
                }
            })
            response.setBody(data)
        }
        catch (e) {
            response.setBody(e)
            console.error(e)
        }
    }
    return callback(null, response)
}

With this change, if the user-agent header does not match what you have stored in the Environment Variable, the Function will return a 401 Unauthorized error, restricting access only to your Google Sheet.

Click Save and Deploy All to re-deploy the updated Function.

Run a Final Test

Finally, return to your Google Sheet and press the button once more.

It should still run, just like before.

However, attempting to invoke the Function externally, such as from a web browser, will result in an Unauthorized error, as shown below:

Protecting a Google App Function from external triggering

Success. Sheet enabled, Function protected.

Conclusion

In this guide, you learned how to seamlessly populate Twilio Flex user data into a spreadsheet with a single button click. Whether it's for review, planning, or analysis, having this data readily available empowers you and your team to make informed decisions and maintain a pulse on your contact center's human resources.

To learn more about how you can manage your workforce with Flex, explore the features offered in the Flex UI Teams View or review how Workforce Optimization can be achieved with the out-of-the-box reporting and analytics tool, Flex Insights.

Bry is a developer and educator passionate about creating intuitive tools that simplify interactions with software. She works as a Personalized Support Lead at Twilio, managing complex technical issues and helping organizations thrive with their digital strategies. She can be reached at bschinina [at] twilio.com.

Related Posts

Related Resources

A newspaper article

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
An Open book

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.
User group reactions

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.