The Endless Possibilities of Freeing Your Data from Workday and Putting it into Google Sheets: a How-to
Update: I have now launched an app that saves your Finance & HR teams
thousands of hours and dollars by enabling this integration in just a few clicks! The app has a free trial and the dev team is willing to customize features to YOUR use as a pilot client. Please try it out at this link and email max.brawer@gmail.com if you’d like me to help ensure your success with the tool and make you the most famous person in your admin org:
https://sheetswizard.gumroad.com/l/workday-to-sheets
Today, we are going to learn how to integrate Google Sheets and Workday, the HR Info System. Even if these aren’t your exact tools, the knowledge of how to use the Workday API should generalize well.
Why are we doing this? In starting People Analytics at BuzzFeed, my entire role was made possible by a good database like Workday. However, that leaves you with two analysis options: use built-in, stock reporting tools, or hit “download to Excel” 1,000,000 times per week and juggle all those exports for a living. No more! We are going to help the data escape so that it is auto-refreshing and good-to-go in Google Sheets.
First, here is the magic box to check in Workday:
I don’t know Workday makes this so hard to learn / buries this knowledge in the community center, but that’s why I am here for you :). You can ignore all the other complexities once you have hit this checkbox.
Second, grab the URL:
You will be met with a number of data types: for today, right click on CSV and then copy URL. It should look like this: https://services1.myworkday.com/ccx/service/customreport2/[company name]/[owner’s email]/[Report Name]?format=csv. This is the proverbial link THEY DON’T WANT YOU TO SEE! It has all your data ready for you.
Third, steal this JavaScript:
Below is the function setup I often use and reuse to get data from Workday, but really this will apply to most open API data sources. It shows you how the URL and Password should look:
function pullDataFromWorkday() {
var url = 'https://services1.myworkday.com/ccx/service/customreport2/[company name]/[owner's email]/[Report Name]?format=csv';
var b64 = 'asdfghjklkjhgfdfghj==';
...}
…with one exception. That b64 variable is a reference to my Workday username and password (I just smashed the keys above), encoded into what’s called base64. To get yours, go to base64encode.org and enter your username and password with a colon in between them like this:
Paste yours in and you’re good to go. DISCLAIMER: please remember to keep your credentials safe as they are sensitive!
The above function shows how to pull the data from Workday. If you want to paste it right into Sheets, here is some basic code for that step:
function pullDataFromWorkday() {
var url = 'https://services1.myworkday.com/ccx/service/customreport2/[company name]/[owner's email]/[Report Name]?format=csv';
var b64 = 'asdfghjklkjhgfdfghj==';* * * * U P D A T E F O R 2 0 2 3 * * * * * * * * * *
I have now finally launched a reusable plugin to run your workday x sheets ecosystem. A full on integration:https://sheetswizard.gumroad.com/l/workday-to-sheets?Check out the app and let me know what you think. If you can't get it by security, let's talk! inquiries@maxbrawer.com* * * * * * * * * * * * * * * * * ** * * * * * * * ** * * * * * * *
}
If you put this into a Google Apps Script project attached to a Spreadsheet (open the new sheet, go to tools > script editor), this will paste the data nicely into the first tab of the Sheet.
Lastly, set up a time-based trigger to have fresh data for life:
With your function pasted in Apps Script, click the stopwatch icon and add a daily timer like this:
This implies that after midnight, your robot will wake up and pull the Workday report for you so that it’s fresh each day.
A practical example of why this can profoundly impact your team’s workload: let’s say you have/are an analyst who has to go into Workday every daily/weekly/every single time they get a data request, run a report and export it to Excel, maintain 100s of versions, re-upload them to Sheets, and do reporting. Using the above, you can just make a sheet that stands in for your report, name it ‘my daily report’, and open it any time you want to see live data and build an analysis off of it right there in Sheets.
I know I go through these quickly and without deeper explanation of the JavaScript, but that’s the idea here: to get you up and running and to learn by doing.
UPDATE: As Okta and other password/sign on options grow more popular, this method may stop working. If you don’t have a direct-to-Workday password and username, you have two options: 1) your workday admin can give you ‘backdoor’ access to login anyway, 2) your admin can create an ISU user, which is like a service account with its own credentials and access levels that can access reports.
Max is the founder & maker behind sheetswizard.com, where you can find educational materials and apps to help you be a great co-worker, better analyst, and excel at Google Sheets. Visit our newest apps like:
Custom Sheet Themes: launch every Sheet you make with custom colors & fonts,
Calendar x Sheets Integration: the easy way to grab and visualize cal data,
9-Block Maker in Sheets: make a 3x3 matrix in one click for your performance reviews,
Org Chart Slideshows (coming this week!): automatically make beautiful slide-sized org charts from your people & org data,
Workday to Sheets: the missing integration to beam HR data right into your sheets automatically.Try them out or reach out to inquiries@maxbrawer.com if your security policy blocks add-ons and you need custom or on-premises work.
