More and more, we are seeing our customers implementing zero touch IT and bursting at the seams with new ideas on how to automate and create a hands-off process for their IT teams to be able to concentrate on more strategic and sensitive projects. A recent post in our BetterIT community got us thinking here at BetterCloud: How do we provide our users with a way to schedule onboarding without the use of Okta or Zapier; sticking only with Google and BetterCloud?
We set out to make this workaround happen, and today we’re providing instructions on how you can set up onboarding using a Google Sheet, an Apps Script, and BetterCloud. This allows IT admins to set a specific date as an employee’s onboard day, detailing down to the hour when onboarding should occur. It ensures that the user is set up for success on day 1.
Please note: This Apps Script is designed to only move users into their onboarding OU. This should not be used to schedule any other automated processes. We do not recommend modifying this Apps Script in any way, and any changes/modifications will not be supported.
It is best to be a Super Admin in your Google Workspace environment with the ability to permit an Apps Script to edit your user directory. While no programming knowledge is required to implement this solution, any customizations should be done by someone who has familiarity with Apps Scripts or object-oriented programming languages.
Google Workspace knows who your users are. BetterCloud knows who your users are in Google Workspace. While adding a user to an onboarding OU can kick off a workflow in BetterCloud, scheduling onboarding may be useful as well. Fortunately, by using a Sheet and an Apps Script, you are able to set the date you’d like your user(s) to move into your onboarding OU and then have the action take place automatically.
Step 1: Set up the Sheet
Title the Sheet as you’d like. Column headings should appear as follows. Those in RED are required fields. Please do not alter the format of any fields unless specified below.
A. Date of Entry
B. User’s Email
C. Confirm User’s Email
D. Manager’s Email (not required, but will update manager’s email immediately if filled in)
F. Date/Time for Onboarding (ensure this is date/time format)
G. Days Until Onboarding
H. User Onboarded?
I. Event Log
At the bottom of the sheet, change the Sheet1 text to read Onboarding.
Default Sheet name is Sheet1; this should be changed to Onboarding
Step 2: Create an Apps Script
In the Sheet menu, click on Extensions, and then Apps Script. This is where you will create an Apps Script that links the Sheet to your admin directory. Don’t worry, the code will be provided with plenty of comments, linked in the paragraph below this image!
Extension > Apps Script is where the code will be placed
With the script editor open, name the Apps Script Onboarding (top left). Now clear all text in the Apps Scripts body so you’re left with a blank page. Paste in the code found here so /* appears on line 1. It will look like this:
Please ensure your code looks like this
Once you’ve pasted the code onto the page, you’ll be modifying two lines: 17 and 20.
This is where you will make two changes according to your tenant.
For line 17, navigate back to your Sheet. In your browser’s address bar, find the alphanumeric ID for your Sheet. It will look similar to the below:
Address bar URL provides your Sheet’s ID
Replace ENTER YOUR SHEET ID HERE with the alphanumeric string so it appears like this:
This will let the script find the Sheet with users in it
Notice the 1Ui… that appears in the address bar is now placed into line 17 after var sheetID = within quotes.
For line 20, navigate to your Google Workspace instance, copy the name of your onboarding Organizational Unit, and paste it over the text ENTER YOUR ONBOARDING OU HERE. Ensure the forward slash stays in front of the OU. This will ensure the Apps Script finds the OU in your directory.
Your onboarding OU is entered on line 20
Important note for those with sub-OUs: If your organizational unit is nested within another OU, please ensure you enter the entire path. In the image below, /Joiners is the correct path. However, if your onboarding OU is “Executives,” the path you would enter into line 20 would be /New York/Executives.
Nested OUs need correct paths
Congratulations! Your Apps Script is written.
It’s just point-and-click from here on out.
Step 3: Allow access to your Admin SDK API
This step is why you are required to be a Super Admin within your org; you will be granting the Apps Script access to your directory. On the Apps Script menu, click on the Services plus sign (+), then select Admin SDK API:
Adding Admin SDK gives access to your Directory
Within the window that pops up, click Add. After it has been added, it will be listed under Services as AdminDirectory.
This allows the Sheet and the script to modify the OU
Step 4: Fill the Sheet and test the script
Spreadsheet: Head back to the spreadsheet and enter a test user you’d like to onboard. Remember, columns B, C, and F are the only things required. For this initial test, set the date of onboarding to be today’s date, and a time close to (or prior to) when you will be running the script. Once this information is filled in, switch back to your Apps Script.
Apps Script: On the Apps Script page, click the function drop-down, then click on dailyUserOnboardWork if not already selected.
Select dailyUserOnboardWork so the script can be tested
Once this is selected, click the Run button. You will be prompted to authorize the Apps Script. Click on Review Permissions.
Review Permissions will open a new window
In the new window, select the account you’re using to run this script, then on the next screen, click the Allow button.
You must allow the script to take action
Spreadsheet: Switch back to your sheet. Once the date/time for the user’s onboarding has passed, you will see that Days Until Onboarding is listed as 0 (or less than 0). User Onboarded is listed as YES, and the Event Log column states the user was moved to your onboarding OU. If the user is not properly moved to the onboarding OU, the reason why will be written to the log column to help you understand what went wrong.
What if this fails and the user isn’t moved into the onboarding OU?
Check to ensure lines 17, 19, and 20 are correctly entered, that the Admin SDK API (AdminDirectory) has been added to Services, and that the Sheet is properly filled in (the user’s email has been entered in columns B and C, and the onboard date is listed in column F). If you continue to have issues, comment below.
Step 5: Automating the action
Once you’ve gotten the script to run and the test user ends up in the right onboarding OU, you can now set the script to trigger on its own. Navigate to https://script.google.com/home/my and locate the Onboarding project. Place your cursor over the Onboarding project in the table so you can see three dots at the far right of the table. Click on those three dots, then select Triggers.
A new page will load, where you will be able to select create a new trigger.
This will make your onboarding work automatically
Select the following:
Choose which function to run > dailyOnboardUserWork
Choose which deployment should run > Head
Select event source > Time-driven
Select type of time based trigger > This is up to you. If you would like this to run daily, select Day timer.
Select time of day > This is up to you. Select the time when you would like the user to be moved to an OU to kick off your onboarding.
Failure notification settings > If you would like to be notified of failure, select your notification intervals.
Additional security: Further protect the Sheet
You should lock down the sheet you are working on beyond typical sharing. Within the Onboarding sheet, click on Data in the menu, then select Protect sheets and ranges from the drop-down. Choose Sheet and make sure the Onboarding sheet is selected. Provide a description for the protection, then click Set permissions. If you want to be the lone editor for this, leave Only you selected in the drop-down. If you’d like others to be able to edit this, click the drop-down and add users or groups (preferably groups) to this file. On the next screen, select Edit from the drop-down.
Once this is set, click on the green Share button, select Settings icon (gear in the upper right of the pop-up), and uncheck the checkbox next to Editors can change permissions and share. This will ensure only the people you want to edit this Sheet are able to.
You are now set up to run onboarding on any users added to your Sheet. If you would like this to be customized, please contact your customer success manager here at BetterCloud and they will be able to work with you to understand your needs. Please keep in mind this is a custom solution, so requested changes may not be possible, but they will certainly be looked into and tested.
Thanks for reading, and let us know what else we can showcase!