List Twilio Flex Users with Functions and Google Sheets
Time to read: 6 minutes
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.
Ready to get started?
For this tutorial, you will need:
- A Twilio Flex Account with Flex users
- A free Google Account
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:
- Log in to the Twilio Console.
- Navigate to the Functions tab.
- Select Create Service button. Name your service something like
get-flex-workers
.
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 Finding a Workspace SID](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media2/_jcr_content/renditions/compressed-original.webp)
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 Set a Functions environment variable with your Workspace SID](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media3/_jcr_content/renditions/compressed-original.webp)
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 Add a new Function at /workers](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media4/_jcr_content/renditions/compressed-original.webp)
Replace the sample code in the editor with the following:
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 |
|
|
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:
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 Make a Function public](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media5/_jcr_content/renditions/compressed-original.webp)
Deploy the Function
Finally, click on the blue Deploy All button.
![Deploy a Twilio Function Deploy a Twilio Function](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media6/_jcr_content/renditions/compressed-original.webp)
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.
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 Edit or add a new Apps Script in the Google Sheets Extension menu](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media7/_jcr_content/renditions/compressed-original.webp)
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:
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:
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.
Save Script
Save by clicking on the save icon in the menu above the editor.
![Save a Apps Script Save a Apps Script](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media8/_jcr_content/renditions/compressed-original.webp)
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 Insert a drawing in a Google Sheet](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media9/_jcr_content/renditions/compressed-original.webp)
Create and style the button however you prefer. When you are ready, click Save and close.
![Drawing a button in Google Sheets. Drawing a button in Google Sheets.](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media10/_jcr_content/renditions/compressed-original.webp)
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 Assign a script to a Google Sheet button](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media11/_jcr_content/renditions/compressed-original.webp)
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.
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 Determine the user-agent header](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media13/_jcr_content/renditions/compressed-original.webp)
The value from the execution log contains a unique string. In the example shown, the value is:
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 |
|
|
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 Set the user-agent variable in a Functions Environment Variable](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media14/_jcr_content/renditions/compressed-original.webp)
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:
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 Protecting a Google App Function from external triggering](/content/dam/twilio-com/global/en/blog/importer-images/h-l/list-flex-users-google-sheet/media15/_jcr_content/renditions/compressed-original.webp)
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
Twilio Docs
From APIs to SDKs to sample apps
API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.
Resource Center
The latest ebooks, industry reports, and webinars
Learn from customer engagement experts to improve your own communication.
Ahoy
Twilio's developer community hub
Best practices, code samples, and inspiration to build communications and digital engagement experiences.