If you are managing HR for a small or medium business, you know the drill. A new hire signs the offer, and immediately, the administrative chaos begins. You copy-paste their name into a contract, then into an email, then into a calendar invite, and finally into an IT request form.

It’s repetitive, boring, and prone to “copy-paste errors.”

Most companies solve this by buying expensive HR software (HRIS). But if you are running your business on Google Workspace, you already have everything you need to build a powerful, automated onboarding engine for free.

Welcome to Part 1 of our series on building an Automated Onboarding System. Today, we are building the “Brain” of the operation: The Master Onboarding Database.

The Concept: Single Source of Truth

The biggest mistake in manual onboarding is having data scattered everywhere (emails, sticky notes, Slack messages).

To automate, we need a Single Source of Truth. We will use a Google Sheet to act as our database. When you add a new hire to this sheet, our future scripts will automatically:

1. Generate their contract.
2. Create their folder structure in Google Drive.
3. Send them welcome emails.
4. Schedule their induction.

But first, we have to build the sheet.

Step 1: Setting up the Spreadsheet

We need a structured format so our code knows exactly where to look for information.

1. Create a new Google Sheet (sheets.new).
2. Rename the file to “Master Onboarding Database”.
3. Rename the bottom tab (worksheet) to “Data”.
4. Create the following headers in Row 1:

Step 2: Adding Data Validation

To prevent errors (like typing a date wrong), let’s add some protection.

1. For the Start Date (Column E): Highlight the whole column $\rightarrow$ Click Data $\rightarrow$ Data Validation $\rightarrow$ Set criteria to “Is valid date”.
2. For Status (Column G): We want a dropdown menu to control the automation.
   • Highlight Column G.
   • Go to Data $\rightarrow$ Data Validation.
   • Criteria: Dropdown.
   • Options: Pending, Offer Sent, Onboarding, Complete.

Now, fill in Row 2 with dummy data (use your own personal email so you can test the system later).

• First Name: Suraj
• Last Name: Kumar
• Personal Email: [Your Personal Email]
• Role: Sales Associate
• Start Date: [Next Monday’s Date]
• Manager Email: [Your Work Email]
• Status: Pending

Step 3: Connecting the Script

Now, let’s open the engine room.

1. In your Google Sheet, click on Extensions in the top menu.
2. Select Apps Script.
3. A new tab will open. This is the code editor.
4. Rename the project (top left) to “Onboarding Automation”.

You will see a function called myFunction(). Delete it. We are going to write a custom script that creates a specific menu inside your spreadsheet so you can run automations with a click.

/**
 * PART 1: THE SETUP
 * This script creates a custom menu in your Google Sheet.
 */

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🚀 Onboarding Menu')
      .addItem('Test Connection', 'testSystem')
      .addToUi();
}

/**
 * A simple function to prove our script can read the data.
 */
function testSystem() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  const activeRow = sheet.getActiveCell().getRow();
  
  // Check if the user is clicking on a valid row (not the header)
  if (activeRow <= 1) {
    SpreadsheetApp.getUi().alert("Please select a row with employee data.");
    return;
  }

  // Get data from the active row
  // Note: We get the range (Row, Column 1, 1 Row, 7 Columns)
  const data = sheet.getRange(activeRow, 1, 1, 7).getValues()[0];
  
  const firstName = data[0]; // Column A
  const role = data[3];      // Column D
  
  // Show a pop-up to confirm it works
  SpreadsheetApp.getUi().alert("Success! The system sees that " + firstName + " is being hired as a " + role + ".");
}

Here is the first article for your blog series.

I have structured it to be beginner-friendly, starting with the “Why” and moving into a step-by-step tutorial. I have also included the specific code snippets your readers will need to copy and paste.

Build a Free Employee Onboarding System: Part 1 – The Master Database

Category: Google Apps Script / HR Automation

Difficulty: Beginner

Time: 15 Minutes

If you are managing HR for a small or medium business, you know the drill. A new hire signs the offer, and immediately, the administrative chaos begins. You copy-paste their name into a contract, then into an email, then into a calendar invite, and finally into an IT request form.

It’s repetitive, boring, and prone to “copy-paste errors.”

Most companies solve this by buying expensive HR software (HRIS). But if you are running your business on Google Workspace, you already have everything you need to build a powerful, automated onboarding engine for free.

Welcome to Part 1 of our series on building an Automated Onboarding System. Today, we are building the “Brain” of the operation: The Master Onboarding Database.

The Concept: Single Source of Truth

The biggest mistake in manual onboarding is having data scattered everywhere (emails, sticky notes, Slack messages).

To automate, we need a Single Source of Truth. We will use a Google Sheet to act as our database. When you add a new hire to this sheet, our future scripts will automatically:

1. Generate their contract.
2. Create their folder structure in Google Drive.
3. Send them welcome emails.
4. Schedule their induction.

But first, we have to build the sheet.

Step 1: Setting up the Spreadsheet

We need a structured format so our code knows exactly where to look for information.

1. Create a new Google Sheet (sheets.new).
2. Rename the file to “Master Onboarding Database”.
3. Rename the bottom tab (worksheet) to “Data”.
4. Create the following headers in Row 1:

Pro Tip: Do not use spaces or special characters in your headers if you can avoid it. It makes coding easier later.

Step 2: Adding Data Validation

To prevent errors (like typing a date wrong), let’s add some protection.

1. For the Start Date (Column E): Highlight the whole column $\rightarrow$ Click Data $\rightarrow$ Data Validation $\rightarrow$ Set criteria to “Is valid date”.
2. For Status (Column G): We want a dropdown menu to control the automation.
   • Highlight Column G.
   • Go to Data $\rightarrow$ Data Validation.
   • Criteria: Dropdown.
   • Options: Pending, Offer Sent, Onboarding, Complete.

Now, fill in Row 2 with dummy data (use your own personal email so you can test the system later).

• First Name: Suraj
• Last Name: Kumar
• Personal Email: [Your Personal Email]
• Role: Sales Associate
• Start Date: [Next Monday’s Date]
• Manager Email: [Your Work Email]
• Status: Pending

Step 3: Connecting the Script

Now, let’s open the engine room.

1. In your Google Sheet, click on Extensions in the top menu.
2. Select Apps Script.
3. A new tab will open. This is the code editor.
4. Rename the project (top left) to “Onboarding Automation”.

You will see a function called myFunction(). Delete it. We are going to write a custom script that creates a specific menu inside your spreadsheet so you can run automations with a click.

Copy and paste the following code:

JavaScript

/**
 * PART 1: THE SETUP
 * This script creates a custom menu in your Google Sheet.
 */

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🚀 Onboarding Menu')
      .addItem('Test Connection', 'testSystem')
      .addToUi();
}

/**
 * A simple function to prove our script can read the data.
 */
function testSystem() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  const activeRow = sheet.getActiveCell().getRow();
  
  // Check if the user is clicking on a valid row (not the header)
  if (activeRow <= 1) {
    SpreadsheetApp.getUi().alert("Please select a row with employee data.");
    return;
  }

  // Get data from the active row
  // Note: We get the range (Row, Column 1, 1 Row, 7 Columns)
  const data = sheet.getRange(activeRow, 1, 1, 7).getValues()[0];
  
  const firstName = data[0]; // Column A
  const role = data[3];      // Column D
  
  // Show a pop-up to confirm it works
  SpreadsheetApp.getUi().alert("Success! The system sees that " + firstName + " is being hired as a " + role + ".");
}

Step 4: Running Your First Test

1. Click the Save icon (floppy disk) in the script editor.
2. Go back to your Google Sheet and Refresh the page.
3. Look at the top menu bar (next to Help). You should see a new custom menu called Onboarding Menu.
4. Click on the row where you entered “Suraj Kumar” (Row 2).
5. Click Onboarding Menu $\rightarrow$ Test Connection.

The Authorization Step:

Since this is the first time you are running it, Google will ask for permission.

1. Click Continue.
2. Select your account.
3. You may see a screen saying “Google hasn’t verified this app” (because you just wrote it!). Click Advanced $\rightarrow$ Go to Onboarding Automation (unsafe).
4. Click Allow.

The Result:

If everything is set up correctly, you will see a pop-up box that says:

“Success! The system sees that Suraj is being hired as a Sales Associate.”