Roster Mechanics

A Russ Lyon Sotheby's International Realty Google Apps Script project to manage employees and maintain a company roster.

Table of Contents

• Roster Mechanics
  • Table of Contents
  • About
  • Prerequisites
  • Sheets
  • Forms
    • Hire Update Form
    • Suspend Form
    • Delete Form
  • Custom Schema
  • Install
  • Build
  • Push
  • Build And Push In a Single Step
  • Testing

This repo is a Google Apps Script project that produces a company roster in a Google Spreadsheet where all the data is stored safely in Google Admin and made editable by links to Google Form responses within the Spreadsheet itself. All the data in the Spreadsheet is stored in Google Admin via the default data structures provided (name, organization, phone, etc.), as well as defined custom schema fields.

The repo is written entirely in Typescript and uses webpack to transpile it into something that is understable by the Google Apps Script V8 runtime. While the project doesn't have any runtime dependencies (which increases the run speed and makes life in Google Apps Script land less complicated), you can add runtime dependencies that will get wrapped into the bundle on build and run just fine. Though I will say, if you add a runtime dependency, you should make sure that the Google Apps Script V8 runtime provides the necessary environment to run the package nontheless. There are on the other hand, many compile-time (development) dependencies that help to lint, format, build, and deploy the code.

Tests for the project are all completely locally run via Jest. You may wonder how it is that a Google Apps Script project can be tested locally? I accomplished this by writing mock implementations for MANY of the Google Apps Script's runtime classes (for example: UrlFetchApp, SpreadsheetApp, FormApp, DriveApp, GmailApp, and MANY MANY more). While this isn't a perfect way of testing, it does do the job for the most part. Being that it isn't perfect, I set the project up to run in two different environments: a development/testing environemnt, and a production environment. The instructions for setting up both are below. That way, you can deploy to a testing environment first to work out any bugs that local testing might not have caught before deploying to the production environment.

About

This project was born out of a need. We had a company roster stored in a Goolge Sheet, but that Google Sheet had problems. For starters, anyone with access to the spreadsheet could edit it. This was a problem for many reasons. For example, people could and would accidently delete things. Secondly, the data in the Spreadsheet was not backup up, ever. Another problem with the sheet were the values themselves. They weren't formatted the same. Maybe for the value "No" some people would write "no" or "NO." Another glaring problem is that simply entering a new employee in the Google Sheet didn't actually create a new Google User account, or create their user signature, or all the other actions invovled in setting up a new User for Google Workspace. We needed a way for administrative office staff to be able to create, update, suspend, and delete employees on the roster, without them actually having admin access to Google Admin / Google Workspace. To fix these problems and more, I created the project "Roster Mechanics." Here is the workflow:

1. The "Hire Update" Google form is filled out for a new employee
2. The script associated with the "Hire Update" form runs on the event "Form Submit"
3. The script does a ton of things, such as creating a new Google User account and sets it up with all the bells and whisltes. All the data filled into the form is stored into the Google User Account's custom schema fields. The last thing the script does is tell the Google Sheet script to run.
4. Next, the Google Sheet associated with the Roster runs it's associated script on the event "Spreadsheet Open", on a regular interval, as well as being triggered at the end of all the Google Form submissions.
5. The Google Sheet script grabs all users from Google Admin / Google Workspace, as well as their custom schema fields containing the information from the "Hire Update" form, and prints them to their associated sheet in the Workbook
6. At this point, the company roster is neatly printed in a Google Sheet and all the data within backed up in Google Admin / Google Workspace
7. When an employee needs to be edited, you find them in the sheet, specifically the column "Edit Response Url"
8. You follow the link to the orignal form response, edit the response, submit it again, and the process begins all over
9. When an Employee needs to be Suspended, you locate the "Suspend" Google Form, fill it out, and submit it.
10. The script associated with the "Suspend" Google Form runs. It suspends the user, as well as updating information on the Google User account such as the reason, time, and date of the suspension.
11. The Google Sheet script is again triggered to run. It updates the Google Sheet to reflect the newly suspended user.
12. When a user needs to be deleted, you located the "Delete" Google Form, fill it out, and submit it.
13. The script associated witht the "Delete" Google form runs. It deletes the user entirely.
14. Once again, the Google Sheet script is triggered to run. It updates the Google Sheet by removing the deleted user.

The problems this fixes are many. Firstly, all the data in the spreadsheet is stored and retireved from Google Admin. You could essentially delete the entire spreadsheet and it wouldn't be a problem. The next time the Google Sheet script runs, the spreadsheet will be restored to new. Secondly, all the data entry is proxied through Google Forms. In the Google Form, you can validate the data being entered. You can also make sure if responses need to be certain values or certain formats, that they are. Thirdly, the Google Form and the Google Sheet are subject to Authentication and Authorizaton. You can have viewers of the Spreadsheet, who can't edit the data because they don't have access to the Google Form. This helps limit who can see, create, update, suspend, and delete users.

Prerequisites

In order to run this repo...

1. Make sure you're running at least Node.js >= v18.16.0 and npm >= v9.6.5.
2. You'll need to enable the Google Apps Script API. You can do that by visiting script.google.com/home/usersettings
3. google clasp installed
4. A .env file with all the values filled out from the .env.example file
5. You will need a company config in the directory src/config/company named russ-lyon.ts based on the sample config found at src/config/company/sample-company.ts
6. You will need a company config in the directory src/config/company named russ-lyon.ts based on the sample config found at src/config/company/sample-company.ts
   • OR you will need to replace all imports such as import RussLyonConfig from 'src/config/company/russ-lyon.ts' with your own company config
7. 2 Google Sheets. One is for development environment, the other is for the production environment
   • See the Sheets Section For more information
8. You'll need 6 Google Forms Total. 3 will be for the Development Environment, and the 3 other are for the Production Envionrment.
   • See the Form Section for more information
9. You need custom schema values defined in Google admin
   • See the Custom Schema Section for more information

Sheets

You will need 2 Google Sheets.

One Google Sheet is for the development environment, the other is for the testing environment.

Both Google Sheets will need four named sheets within:

1. One named "AdminRoster"
2. One named "GeneralRoster"
3. One named "PseudoRoster"
4. One named "SuspensionLog"

See Screenshot:

Forms

There are 6 Google Forms needed.

Technically, there is only 3 unique forms.

The 3 forms from the development environment are the same as the 3 forms in the production environment.

The Forms are as follows:

• Testing - Hire Update
  • For creating and updating users in the testing/development environment
• Testing - Suspened
  • For suspending users in the testing/development environment
• Testing - Delete
  • For deleting users in the testing/development environment
• Production - Hire Update
  • For creating and updating users in the production environment
• Production - Suspend
  • For suspending users in the production environment
• Production - Delete
  • For deleting users in the production environment

Hire Update Form

See Screenshot:

You will need these questions in the form, with these EXACT titles, question types, and answers:

• Hire Date
  • Date
• First Name
  • Short Answer
• Preferred Name
  • Short Answer
• Last Name
  • Short Answer
• Email
  • Short Answer
• Role
  • Checkboxes
    • Sales Associate
    • Branch Manager
    • Relocation
    • Relo Agent
    • Accounting
    • Training
    • Office Admin
    • Marketing
    • Technology
    • Quality Control
    • Listing/Contract Coordinator
    • Licensed Assistant
    • Unlicensed Assistant
    • Executive Assistant
    • Designated Broker
• Office
  • Multiple Choice
    • Carefree
    • Desert Mountain
    • Flagstaff
    • Market Street
    • Pinnacle Peak
    • Prescott
    • Sedona
    • Southeast Valley
    • Tubac
    • Tucson
    • Camelback Tower
    • West Valley
    • Fountain Hills
    • Relocation
    • Corporate
    • Humphreys
    • Lyon's Den - PP
    • Lyon's Den - CF
    • Lyon's Den - PR
    • Lyon's Den - CT
    • Lyon's Den - TUC
    • Lyon's Den - TUB
    • Lyon's Den - FLG
    • Lyon's Den - DM
    • Lyon's Den - SEV
    • Lyon's Den - FH
    • Lyon's Den - SED
    • Lyon's Den - WV
• Critical New Hire Form
  • File Upload
• Phone
  • Short Answer
• License Number
  • Short Answer
• ADRE Link
  • Short Answer
• Date Licensed
  • Date
• License Expiration Date
  • Date
• Board
  • Dropdown
    • CAAR
    • GVSAR
    • LHAR
    • NAAR
    • Phoenix Realtors
    • PAAR
    • SAAR
    • SVVAR
    • TAR
    • WeSERV
    • WMAR
    • To Be Determined
• MLS ID
  • Short Answer
• Board 2
  • Dropdown
    • CAAR
    • GVSAR
    • LHAR
    • NAAR
    • Phoenix Realtors
    • PAAR
    • SAAR
    • SVVAR
    • TAR
    • WeSERV
    • WMAR
    • To Be Determined
• MLS ID 2
  • Short Answer
• Board 3
  • Dropdown
    • CAAR
    • GVSAR
    • LHAR
    • NAAR
    • Phoenix Realtors
    • PAAR
    • SAAR
    • SVVAR
    • TAR
    • WeSERV
    • WMAR
    • To Be Determined
• MLS ID 3
  • Short Answer
• Ninja
  • Short Answer
• Ninja Audit
  • Date
• Lone Wolf Number
  • Short Answer
• PC or PLLC
  • Multiple Choice
    • Yes
    • No
• Re-Hire
  • Multiple Choice
    • Yes
    • No
• Recruited From
  • Short Answer
• Status Type
  • Multiple Choice
    • Husband/Wife
    • Licensed Assistant
    • RLSIR Staff
• Charge $395
  • Multiple Choice
    • Yes
    • No
• Monthly Fees
  • Multiple Choice
    • Full Month
    • Half Month
• Date Fees to Start
  • Date
• Notes
  • Paragraph
• Show on Roster
  • Multiple Choice
    • Yes
    • No

Suspend Form

See Screenshot:

You will need these questions in the form, with these EXACT titles, question types, and answers:

• Email
  • Short Answer
• Suspend
  • Multiple Choice
    • Suspend
    • Unsuspend
• Sever Date
  • Date
• Inactive Reason
  • Short Answer
• New Brokerage
  • Short Answer

Delete Form

See Screenshot:

You will need these questions in the form, with these EXACT titles, question types, and answers:

• Email
  • Short Answer
• Delete
  • Multiple Choice
    • Delete
    • Undelete

Custom Schema

You'll need many custom defined schema within Google Admin

See Screenshot:

The custom schema should have a cateogry or field mask of Roster

You will need these custom schema values spelled exactly as follows with the same data types:

• Created At
  • Text
• Edit Response URL
  • Text
• Hire Date
  • Date
• Preferred Name
  • Text
• Office
  • Text
• Role
  • Text
• Date Licensed
  • Date
• License Number
  • Text
• License Expiration Date
  • Date
• Ninja
  • Text
• Ninja Audit
  • Date
• MLS ID
  • Text
• Re-Hire
  • Yes or No
• Recruited From
  • Text
• Sever Date
  • Date
• Inactive Reason
  • Text
• Phone
  • Phone
• Show on Roster
  • Yes or No
• Lone Wolf Number
  • Text
• New Brokerage
  • Text
• Board
  • Text
• Board 2
  • Text
• Board 3
  • Text
• Updated At
  • Text
• PC or PLLC
  • Yes or No
• Exists
  • Yes or No
• MLS ID 2
  • Text
• MLS ID 3
  • Text
• ADRE Link
  • Text
• Special Status
  • Yes or No
• Status Type
  • Text
• Hire Fee
  • Text
• Charge 395
  • Yes or No
• Monthly Fees
  • Text
• Date Fees to Start
  • Date
• Notes
  • Text

Install

To install the dependencies:

npm install

Build

To build the project for development:

npm run build:dev

To build the project for production:

npm run build:prod

Push

To push the project to google apps script for development purposes:

npm run clasp:push:dev

To push the project to google apps script for production purposes:

npm run clasp:push:prod

Build And Push In a Single Step

To build and then push the project in a single command for development:

npm run dev

To build and then push the project in a single command for production:

npm run prod

Testing

As mentioned already, all the testing in this repo is carried out locally. I have accomplished this by mocking all the necessary Google Apps Script Classes (for example: UrlFetchApp, SpreadsheetApp, FormApp, DriveApp, GmailApp, and MANY MANY more).

To run all tests:

npm run test