Workaround: Scheduled Onboarding


Userlevel 3

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.

 

Prerequisites

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.

 

Background information

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)

E. Location

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.

p1Y4QYJuIDX0YFKNT6rfDcgxxeztYt-7PVxcLM-xdkT7dvIMuu6y06gt-llsE7YolyNY8oMh6hncsAD9sm9lgr5HFcake2VPRcpFluJeFqTl-yjoQqF7BStJFUblEMz-ZqyX3-lW

 

6qsQSLRYeXm81ZSXzTglFyKg58rNCZtDevJTEHYdIyun5vPueFjq0dlJ6IdZd5gaIE_rgtqoTHNuZcU3rmyhBjHRgoG9u0-Uj-vwzb-XbGrxVSSu1lawctTWhcmOZ3pT5Wedihje

 

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!

 

jv77DpH9-oNcTo2puinjOJ6wP0Flo05j7A3kyoDBqALH2D3CTLIEhhIopo5R4mzj-Tt12y7p57R0LlOZVsrULFmcNBFCVFc8T3CjyilQCPjawduJNu1LZzipL9HvXo9rj-gdPrSi

 

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:

lvnFlbXKFwo7RWmIWIyhnTTgFWo085c0khpUGAX_oISz14hEMHXbQ3vP1orAvwkNkRLT8nAIYvnSWy02RFeLXq7_ufyjHcF5ebWEP5PtBnXxN7UXsCJoZ0DEqFNsjxOrAxYwYU27

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.

hvspzWRvklaEZNcwNqoLP8fpCp6dT7SfBoWAEbHQDX2RRT_Y3NhrVZ9q2juOUA6F-e6Ki7mSmibPkVGlICNji63hG08eH2rvuZ3X-3Jm0Kk5aIpRCn47d8PGoVaYxYlXVWY5vfq1

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:

_wgt4gMgOHN2ArHBsETvF0fDDMYDvK8Nr8pElaKJEq4CEakNnVq9J8BWXMfQptCxvjn_zbaA2jW0MqqUFzIeIUvO-kNzo7R14Q0-tREBegyxHp6o6QQ1SbVGZI97t1a_VlJHKpxn

Address bar URL provides your Sheet’s ID

 

Replace ENTER YOUR SHEET ID HERE with the alphanumeric string so it appears like this:

OcaIB6Ww2jkgnZCk58JTQPYL-D8z5eZMgrsePzwKJQ6x6Gt_WTIIEVxZwpmDSUCEVLgz-3vh8-7yDedU3fFQG702nQIWtfMREEb5NG5a0PUmcbbBSOgam-sfDlOgMZuUsenKoVDt

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.

XOw0PCTVn1X2iyqiFiOEJyAhAv1S3MffTJ1c1AmfLDvc5NCgZHPqV6By2i1Rp373gm7IKvxPZwjxAK6063FFnmblb23PuhyAq8h7mRtm83xB9qZudMLEf3fxcF7qbEcPrS2g7YH8

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.

BVRMeBhH7ZmYDBP6R7ZW0GOZWtKOaYSnvYicg7SPY44zP1YQjxRymd7mE6OUZXLpG789RsOn-dXz3Bmtj5YyuFdUivwC4eufwgHXYJdCFkCwr0SmeCKOrM64Xi1zYuNzgLVbv_hd

 

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:

 

vXdKRcxNDm3xWDAsFGeX79QFXPm0L-oboUv29UmNubkUdybENxb4LwQejnILO0-ZfANbsae9s8MT5UWfOrUJz91Rnlq8x71DH5GLWcxK7PZZQ2HQodEW4h5ypschfgkjKqu1AOS3

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.

SQ9Osvkr25EnukSJY7iyCb7Th3gFSpE2_XEYPR0nQmbTRUVD2En1T8hVjcA-Siuf1qbpyP5drtu42e8jkyfR5XJM76LRQg3TKq6NW98endGy13ScIpN9UQSpnJde08jIlXbsXVq7

 

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.

 

bna2KBAepkeX5dOo33kZJNCQpMACIXIp8CZmeNoox5r6ztWXmlYCR4VLMtZqoovdCys6W7FZWP6frC8NQcKtse0EhCGASX907m2cfuEAceo_lb35Hka_tR86_gLUuSzakx9uUvDw

 

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.

 

i28DpBJcFXDjXQPOo125-WVjpnnA4BpqZac7_nUtVyWjSBASG4rqIn7M25km9Cmef1CkfvpyUlGiKh7jSBVMetNNFcvL9wQiL58JysG5h-M5BRsOa7MHA5Jxr1m-4qNHMWTIgyr5

 

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.

UBuYTdKh701HRV3SAda2pp-tct4NfRc_lgkIgXQ-Ba3ILPvMoUbEa-mVb5M4ITb0brWulRhTM0ZlkctwHThRtvTAYOPdUBRj5tIKDVnITpZa4IdMfwnIKZkp_PGizD8G00HkxX6N

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.

 

wA2QWIeMiACM_NvdDVTyAO1f2JjkKucX77FfZ24IDOclexeMl_yy2K-TygzSRLxsE09QdxE_h6PXTJaOdxAcXVpMWBZAg3QBypjUP13v8jbos9BzqQ7xCzLoSSmjASVspwPRb7M8

 

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.

 

Closing

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!


0 replies

Be the first to reply!

Reply