Automating Shift Scheduling with Twilio SMS and Google Sheets

Time to read: 7 minutes

February 28, 2025

Written by

Samuel Busayo

Contributor

Reviewed by

Dhruv Patel

Twilion

Automating Shift Scheduling with Twilio SMS and Google Sheets

Managing shift swaps can be a hassle, especially if it's last minute. Many businesses handle shift swaps manually by using group chats or notes. This can quickly become complicated and confusing for large teams. In this tutorial, you will learn how to build a solution to automate the process of swapping shifts using Google Sheets, Twilio Programmable Messaging, and a Node.js backend.

You will build an automated system where an employee can request a shift swap via SMS. The second employee whose shift is potentially being changed will receive an SMS asking to either approve or decline the swap request. Once confirmed, the shift will be updated on Google Sheets.

Prerequisites

Before you proceed with this tutorial, make sure you have:

A Twilio account. If you don't have an account, you can create one here.
A Google Cloud account with Google Sheets enabled.
An ngrok account to test your application locally. Learn how to get started here.
Node.js installed on your system.
A basic understanding of Google Sheets and JavaScript.

Setting up Google Sheets and Google Cloud

In your Google Cloud account, search for “Google Sheets API" using the search box at the top of the page. From the search results, select Google Sheets API. On the API page, click Enable to activate it for your project.

Once you enable the Google Sheets API, you will be redirected to the service details page.

To create a service account, click Create Credentials, then select Service Account from the dropdown menu. You will be redirected to a form to set up the service account. If prompted to select What data will you be accessing?, choose Application Data.

Enter a name for your service account, then click Create and Continue.

To access Google Sheets from your application, you will need to download a JSON key file containing an authentication token. To generate this file, go to the Credentials section in the sidebar. Under Service Accounts, click the email address link.

Screenshot of the credentials page on Google Cloud

Go to the Keys tab, click Add Key, then select Create new key.

Screenshot of credentials page on Google cloud

A modal will appear. Under Key type, ensure JSON is selected, then click Create. The JSON key file will be downloaded to your computer.

Finally, you need a spreadsheet to follow along with this project. I’ve created a template that you can copy and modify for your use case. Ensure that the mobile numbers entered in the sheet are valid and verified on Twilio. Your phone numbers should be formatted with an apostrophe at the beginning, like +10000000, to ensure Google Sheets correctly interprets it as text.

Open the JSON file you downloaded earlier and copy the GOOGLE_SERVICE_ACCOUNT_EMAIL value. Go to your Google Sheets, click Share, and paste the copied email into the Add people field. This grants your service account read and write access to the sheet.

Do not change the Date column format from YYYY-MM-DD.

Setting Up Your Project

In your terminal, create a new directory for your project and navigate to it.

mkdir shift-manager
cd shift-manager

Initialize your Node.js project by running:

npm init -y

Next, install the packages needed to run this project:

npm install express dotenv twilio google-spreadsheet google-auth-library moment body-parser nodemon

In the package.json file, add the following line under the scripts section to enable the server to automatically restart when you make changes to any file.

"start":"nodemon app.js"

Creating Environment Variables

In your project, create a new file called .env. This file will contain all the private values you do not want to leave exposed in your project. Copy the code below into the file and replace the values appropriately.

TWILIO_ACCOUNT_SID=your_account_sid
TWILIO_AUTH_TOKEN=your_twilio_auth_token
TWILIO_PHONE_NUMBER=your_twilio_number
GOOGLE_SHEETS_ID=your_google_sheet_id
GOOGLE_PRIVATE_KEY=your_google_private_key
GOOGLE_SERVICE_ACCOUNT_EMAIL=your_service_account_email

Your Twilio account information can be found on the Twilio console under the Account Info section at the bottom left of the page.

To get your GOOGLE_SHEETS_ID, open your Google Sheets in your browser, the URL will be in this format https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxx/edit

. The part after /d/ and before /edit is your GOOGLE_SHEETS_ID.

You can find your GOOGLE_PRIVATE_KEY and GOOGLE_SERVICE_ACCOUNT_EMAIL in the JSON file you downloaded from Google Cloud in the previous section.

Integrating Google Sheets to Your Project

In your project’s directory, create a sheets.js file. Import the necessary packages into the file.

const { GoogleSpreadsheet } = require('google-spreadsheet');
const { JWT } = require('google-auth-library');
const moment = require('moment');
require('dotenv').config();

Create a class called SheetManager and define a constructor to initialize authentication for accessing Google Sheets using a service account.

class SheetManager {
  constructor() {
    this.serviceAccountAuth = new JWT({
      email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
      key: process.env.GOOGLE_PRIVATE_KEY.replace(/\\n/g, '\n'),
      scopes: ['https://www.googleapis.com/auth/spreadsheets']
    });
    this.doc;
    this.initialized = false;
  }
}
module.exports = new SheetManager();

Throughout the project, this.serviceAccountAuth will be used to authenticate access to the Google Sheets API. this.doc will store the reference to the Google Sheets document once it's loaded. this.initialized checks whether Google Sheets has been successfully loaded.

After the constructor, add an init method that will load the Google Sheets document and set initialized to true.

async init() {
    if (!this.initialized) {
      this.doc = new GoogleSpreadsheet(
        process.env.GOOGLE_SHEETS_ID,
        this.serviceAccountAuth
      );
      await this.doc.loadInfo();
      this.scheduleSheet = this.doc.sheetsByTitle['Schedule'];
      this.requestsSheet = this.doc.sheetsByTitle['Requests'];
      this.employeesSheet = this.doc.sheetsByTitle['Employees'];
      this.initialized = true;
    }
  }

Proceed to add a method called getEmployeeByPhone. As the name implies, a phone number is passed in as an argument and is used to find a specific row in the sheet with the phone number.

async getEmployeeByPhone(phoneNumber) {
    await this.init();
    const rows = await this.employeesSheet.getRows();
    return rows.find((row) => row.get('Phone') == phoneNumber);
  }

Add another method called createShiftRequest. This method will scan the scheduleSheet for a row that matches the specified shift and date. If a suitable entry is found, it generates a shift request, logs it in the requestsSheet, and returns the requestId along with the target employee's details.

async createShiftRequest(requestingEmployee, date, shift) {
    await this.init();
    // Find target employee
    const scheduleRows = await this.scheduleSheet.getRows();
    const targetRow = scheduleRows.find((row) => {
      return (
        row.get('Date') === date &&
        row.get('Shift') === shift &&
        row.get('Name') !== requestingEmployee.get('Name')
      );
    });
    if (!targetRow) return null;
    const requestId = `REQ${moment().format('YYYYMMDDHHmmss')}`;
    const newRequest = {
      'Request ID': requestId,
      Date: date,
      'Requesting Employee': requestingEmployee.get('Name'),
      'Their Shift': shift,
      'Target Employee': targetRow.get('Name'),
      Status: 'Pending',
      Timestamp: moment().format('YYYY-MM-DD HH:mm:ss')
    };
    await this.requestsSheet.addRow(newRequest);
    return {
      requestId,
      targetEmployee: {
        name: targetRow.get('Name'),
        phone: targetRow.get('Phone')
      }
    };
  }

Next, add the processRequest method. This method is triggered when the target employee sends an SMS in response to a shift swap request. It will search the requestsSheet for a valid request matching the shift swap. If found, the requestsSheet will be updated based on the employee’s response. The scheduleSheet will only be updated if the employee approves the shift swap.

async processResponse(employee, responseText) {
    await this.init();
    const rows = await this.requestsSheet.getRows();
    const requestRow = rows.find(
      (row) =>
        row.get('Requesting Employee') === employee.get('Name') &&
        row.get('Status') === 'Pending'
    );
    if (!requestRow) return null;
    const status =
      responseText.toLowerCase() === 'yes' ? 'Approved' : 'Rejected';
    requestRow.set('Status', status);
    await requestRow.save();
    if (status === 'Approved') {
      await this.updateSchedule(
        requestRow.get('Date'),
        requestRow.get('Their Shift'),
        requestRow.get('Requesting Employee'),
        requestRow.get('Target Employee')
      );
    }
    return {
      requestId: requestRow.get('Request ID'),
      status
    };
  }

Finally, add a method to update the scheduleSheet with the details of the new employee.

async updateSchedule(date, shift, emp1, emp2) {
    const scheduleRows = await this.scheduleSheet.getRows();
    const rowsToUpdate = scheduleRows.filter(
      (row) =>
        row.get('Date') === date &&
        row.get('Shift') === shift &&
        [emp1, emp2].includes(row.get('Name'))
    );
    for (const row of rowsToUpdate) {
      row.set('Name', row.get('Name' === emp1 ? emp2 : emp1));
      await row.save();
    }
  }

Setting up the SMS endpoint

Create an app.js file in your project and import the necessary files and packages.

const express = require('express');
const bodyParser = require('body-parser');
const twilio = require('twilio');
const sheetManager = require('./sheets');
require('dotenv').config();

Initialize express for handling requests and twilio for messaging.

const app = express();
app.use(bodyParser.urlencoded({ extended: false }));
const twilioClient = twilio(
  process.env.TWILIO_ACCOUNT_SID,
  process.env.TWILIO_AUTH_TOKEN
);

Then create a post endpoint for the /sms path.

app.post('/sms', async (req, res) => {
// code goes here
});

Copy the following code inside the callback function of the endpoint:

const twiml = new twilio.twiml.MessagingResponse();
  const incomingMsg = req.body.Body.trim();
  const phoneNumber = req.body.From;
  // Check if this is a shift exchange request
  const exchangeMatch = incomingMsg
    .toLowerCase()
    .match(/exchange (\d{4}-\d{2}-\d{2}) (day|night)/);

This code initializes Twilio’s MessagingResponse object to handle SMS replies. It also extracts the incoming message and the sender’s phone number from the body of the request. Next, it checks if the message follows a specific pattern for a shift exchange request to detect messages formatted as "exchange YYYY-MM-DD day" or "exchange YYYY-MM-DD night".

Inside the callback function of the endpoint, add a try-catch block. Leave the try block empty for now, and in the catch block, log any errors detected.

try {
} catch (error) {
    console.error('Error processing request:', error);
    twiml.message('An error occurred. Please try again later.');
    return res.type('text/xml').send(twiml.toString());
  }

Add the following code inside the try block. This code will verify if the sender is an employee in the system. A shift request is created if the number and SMS format are valid. The createShiftRequest method will ensure the requested shift exists. If it does, the shift request will proceed, and the target employee will receive an SMS notifying them of the shift swap request. They can reply with “Yes” or “No.” If no matching shift is found, an SMS will be sent informing them that no shift matches their request.

const employee = await sheetManager.getEmployeeByPhone(phoneNumber);
    if (!employee) {
      twiml.message("Sorry, you're not registered in our system.");
      return res.type('text/xml').send(twiml.toString());
    }
    if (exchangeMatch) {
      const [, date, shift] = exchangeMatch;
      const request = await sheetManager.createShiftRequest(
        employee,
        date,
        shift.charAt(0).toUpperCase() + shift.slice(1)
      );
      if (request) {
        // Send SMS to target employee
        await twilioClient.messages.create({
          body: `${employee.get(
            'Name'
          )} wants to exchange their ${shift} shift on ${date} with you. Reply YES to approve or NO to reject.`,
          from: process.env.TWILIO_PHONE_NUMBER,
          to: request.targetEmployee.phone
        });
        twiml.message(
          `Exchange request sent to ${request.targetEmployee.name}. Request ID: ${request.requestId}`
        );
      } else {
        twiml.message('No matching shift found for exchange.');
      }
      return res.type('text/xml').send(twiml.toString());
    }
// Check if this is a response to an exchange request

Add the following code after the last comment that checks if the message is a response to an exchange request. This ensures that the employee’s response includes “Yes” or “No” before it is processed.

if (['yes', 'no'].includes(incomingMsg.toLowerCase())) {
      const result = await sheetManager.processResponse(employee, incomingMsg);
      if (result) {
        twiml.message(
          `Exchange request ${
            result.requestId
          } has been ${result.status.toLowerCase()}.`
        );
      } else {
        twiml.message('No pending exchange requests found.');
      }
      return res.type('text/xml').send(twiml.toString());
    }
   // Invalid message format
    twiml.message(
      'Invalid format. To request a shift exchange, send: exchange YYYY-MM-DD shift\nExample: exchange 2025-01-30 day'
    );
    return res.type('text/xml').send(twiml.toString());

Finally, add this code outside the /sms endpoint. This will start the express server and listen for any incoming requests on the specified PORT number.

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}`);
});

Creating a Forwarding URL Using Ngrok

Currently, your application is running on http://localhost:3000 on your computer. This is not publicly accessible and cannot be added to Twilio’s webhook configuration, because Twilio needs a publicly accessible URL to send your API requests. To make your local server accessible from the internet, you can use a tool like ngrok to create a secure tunnel from a public URL to your local machine

To get started, ensure you have installed and connected your ngrok account to your system.

Navigate to your project’s directory and start your project using:

npm start

In another terminal, run:

npx ngrok http 3000

This command will allow ngrok to start a tunnel that exposes your local server running on port 3000 to the internet. It will generate a temporarily accessible URL that you can use to test your endpoint. In your command line, copy the forwarding URL generated by ngrok, which has the format: https://xxxx-xxx-xx-xx-xx.ngrok-free.app.

Go to the Twilio Console. In the sidebar, click Phone Numbers, then select Manage, and then click on Active Numbers. On the Active Numbers page, click on your number, then scroll down to the Messaging section. Under Messaging, set the webhook URL to your ngrok URL followed by /sms e.g. https://xxxx-xxx-xx-xx-xx.ngrok-free.app/sms . Ensure the HTTP method is set to POST.

Testing your application

To request a shift exchange:

Send an SMS in the format: exchange YYYY-MM-DD shift. For example: exchange 2025-01-30 day. Make sure the date and shift values exist in your Google Sheets.

To respond to an exchange request: Simply reply with YES to approve or NO to reject.

Conclusion

In this tutorial, you learned how to build a simple shift swap application using Twilio Programmable SMS, Google Sheets, and Node.js. This application allows employees to swap shifts with each other without the need for an extra layer of approval from a manager or the need to download extra apps and perform authentication.

This system is ideal for small businesses looking for a low-maintenance way to manage schedules where things are kept simple with no steep learning curves. Although this project made use of Google Sheets for storing data, this application can be integrated with just about any form of database and it’s flexible enough to scale if the need ever arises.

Busayo Samuel is a full-stack MERN developer. She enjoys building web apps from scratch. In her free time, she can be found buried in a mystery novel or binging a sitcom.

Related Resources

Twilio Docs

From APIs to SDKs to sample apps

API reference documentation, SDKs, helper libraries, quickstarts, and tutorials for your language and platform.

Resource Center

The latest ebooks, industry reports, and webinars

Learn from customer engagement experts to improve your own communication.

Ahoy

Twilio's developer community hub

Best practices, code samples, and inspiration to build communications and digital engagement experiences.

Automating Shift Scheduling with Twilio SMS and Google Sheets

Related Posts

Related Resources

From APIs to SDKs to sample apps

The latest ebooks, industry reports, and webinars

Twilio's developer community hub