How I made "Check-In/Out" for my society's gym digital using Google Apps Script and QR

What was happening till now

For few years now, our gym relied on the traditional pen-and-paper method for check-ins and check-outs. Members would manually sign their names and record the time of their visit on a physical logbook. While this method was simple, it often led to occasional errors in tracking attendance. Managing these paper logs also became increasingly cumbersome, making it clear that a more efficient solution was needed to streamline our operations and enhance the member experience. Additionally, the pen-and-paper system posed privacy concerns, as anyone could easily read the personal details of other members, such as phone numbers and addresses, which were sometimes logged in the same book.

What did I do to change this

Recognizing the need for a more efficient and secure system, I helped transition from the traditional pen-and-paper method to a digital solution using Google Forms and Sheets. Now, members check in and out by filling out a simple Google Form by scanning a QR at the entry. This form instantly logs their information into a Google Sheet, streamlining the process and eliminating long queues. 

This new system not only ensures accurate tracking of attendance but also significantly enhances privacy. Members' personal details are securely stored in the digital system, accessible only by authorized staff, thus protecting their information from being viewed by others. This change has modernized our operations, making check-ins and check-outs quicker and more secure for everyone.

Features: How did this help us track easily

1. Automated Unique ID Generation and Registration:
   When a member registers, a Google Form captures their details and automatically generates a unique 3-digit ID. This ID is sent to their email along with a welcome message. The unique ID ensures that each member can be easily identified without any confusion.
   
   
2. Real-time Check-in and Check-out Logging:
   Members check in and out by filling out a Google Form with their unique ID. The system verifies the ID against the registration data and logs the action in the appropriate sheet. If there are any issues, such as attempting to check in without having checked out, the system sends an email notification.
   
3. Automated Reminders and Error Handling:
   The system also sends automatic reminders to members who have not checked out after a certain period and also notifies admins of any invalid check-in or check-out attempts using wrong/made-up IDs.

Code for both the forms

So there are two forms which come in picture which talk to each other. Details below:

This form will generate a 3 digit unique ID for your residents and sends them an email 

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // Name of your registration sheet
  var lastRow = sheet.getLastRow();
  
  // Generate a unique 3-digit ID
  var uniqueID = generateUniqueID();
  
  // Assign unique ID in the G column (7th column)
  var idColumn = 7;
  sheet.getRange(lastRow, idColumn).setValue(uniqueID);
  
  // Send email to the user with their unique ID
  var emailAddress = e.values[1]; // Adjust index based on form field positions
  var userName = e.values[2]; // Adjust index based on form field positions
  var subject = "YOUR_SOCIETY_NAME: Gym ID";
  var message = "Hello " + userName + ",\n\nYour Gym ID is: " + uniqueID + "\n\nThank you for registering.\n\nBest Regards,\nYOUR_SOCIETY_NAME";
  
  // Log details for debugging
  Logger.log("Email Address: " + emailAddress);
  Logger.log("User Name: " + userName);
  Logger.log("Subject: " + subject);
  Logger.log("Message: " + message);
  
  // Send email
  MailApp.sendEmail(emailAddress, subject, message);
}

function generateUniqueID() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // Name of your registration sheet
  var dataRange = sheet.getRange('G:G').getValues(); // Column G for existing IDs
  var existingIDs = dataRange.flat().filter(String); // Flatten and filter out empty values

  var uniqueID;
  var isUnique = false;

  while (!isUnique) {
    uniqueID = Math.floor(Math.random() * 900) + 100; // Generates a random 3-digit number
    isUnique = !existingIDs.includes(uniqueID.toString());
  }

  return uniqueID;
}
e

This form will record the check-in/out of the residents and send them emails about the same, block duplicate check-in/out, and notify the admin when a check-in/out is attempted using a wrong/made-up User ID

function onFormSubmit(e) {
  try {
    var formData = e.values;
    Logger.log('Form Data: ' + JSON.stringify(formData)); // Log all form data for debugging

    var checkStatus = formData[2]; // Third column: Check-In or Check-Out
    Logger.log('Check Status: ' + checkStatus);
    if (!checkStatus) {
      throw new Error('Check-In or Check-Out status is not defined in the form data.');
    }

    var uniqueID = formData[1]; // Second column: Unique ID
    Logger.log('Unique ID: ' + uniqueID);

    var registrationSpreadsheet = SpreadsheetApp.openById('YOUR_FIRST_SPREADSHEET_ID_HERE');
    var registrationSheet = registrationSpreadsheet.getSheetByName('Form Responses 1');
    var dataRange = registrationSheet.getDataRange().getValues();
    Logger.log('Registration Data: ' + JSON.stringify(dataRange));

    var valid = false;
    var userDetails = [];
    var emailAddress = '';
    var userName = '';

    // Loop through registration data to find the matching unique ID
    for (var i = 1; i < dataRange.length; i++) {
      if (dataRange[i][6] == uniqueID) { // 7th column (0-based index 6): Unique ID
        valid = true;
        userDetails = dataRange[i];
        emailAddress = userDetails[1]; // 2nd column: Email address
        userName = userDetails[2]; // 3rd column: User's name
        break;
      }
    }

      if (valid) {
      // Determine the correct sheet based on check-in/check-out status
      var checkInSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Check-In Responses');
      var checkOutSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Check-Out Responses');

      if (!checkInSheet || !checkOutSheet) {
        throw new Error('Check-In or Check-Out sheet not found in the active spreadsheet.');
      }

      var lastAction = getLastAction(uniqueID, checkInSheet, checkOutSheet);
      Logger.log('Last Action: ' + lastAction); // Log last action

      if (checkStatus.toLowerCase() === 'check-in' && lastAction === 'check-in') {
        // Notify user to check out first
        if (emailAddress) {
          var subject = "YOUR_SOCIETY_NAME: Check-In Attempt Denied";
          var message = "Hello " + userName + ",\n\nOur records show that you have already checked in. Please check out before attempting to check in again.\n\nThank you;
          MailApp.sendEmail(emailAddress, subject, message);
          Logger.log('Check-In Attempt Denied email sent to ' + emailAddress);
        } else {
          Logger.log('Error: Email address not found for user ID ' + uniqueID);
        }
      } else if (checkStatus.toLowerCase() === 'check-out' && lastAction === 'check-out') {
        // Notify user to check in first
        if (emailAddress) {
          var subject = "YOUR_SOCIETY_NAME: Check-Out Attempt Denied";
          var message = "Hello " + userName + ",\n\nOur records show that you have not checked in today or you might have already checked out. Please check in before attempting to check out.\n\nThank you";
          MailApp.sendEmail(emailAddress, subject, message);
          Logger.log('Check-Out Attempt Denied email sent to ' + emailAddress);
        } else {
          Logger.log('Error: Email address not found for user ID ' + uniqueID);
        }
      } else {
        var sheetName = checkStatus.toLowerCase() === 'check-in' ? 'Check-In Responses' : 'Check-Out Responses';
        Logger.log('Sheet Name: ' + sheetName);
        var actionSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
        if (!actionSheet) {
          throw new Error('Sheet "' + sheetName + '" not found in the active spreadsheet.');
        }

        // Log the action (check-in or check-out) with the user's details
        actionSheet.appendRow([new Date(), uniqueID, userName, emailAddress]);

        // Send email confirmation
        if (emailAddress) {
          var subject = "YOUR_SOCIETY_NAME: Gym " + checkStatus;
          var message = "Hello " + userName + ",\n\nYour " + checkStatus.toLowerCase() + " has been successfully recorded.\n\nThank you";
          MailApp.sendEmail(emailAddress, subject, message);
          Logger.log(checkStatus + ' email sent to ' + emailAddress);
        } else {
          Logger.log('Error: Email address not found for user ID ' + uniqueID);
        }
      }
    } else {
      // Notify admin about the invalid check-in/check-out attempt
      var adminEmail = 'ADMIN-EMAIL@DOMAIN.COM'; // Replace with your admin email
      var subject = checkStatus + " Denied: Invalid User ID Attempt";
      var message = "A user attempted to " + checkStatus.toLowerCase() + " with an invalid Unique ID: " + uniqueID + ".\n\nForm Data:\n" + JSON.stringify(formData) + "\n\nPlease investigate this issue ASAP.";
      MailApp.sendEmail(adminEmail, subject, message);
      Logger.log('Error: Invalid ID entered: ' + uniqueID + '. Notification sent to ' + adminEmail);
    }
  } catch (error) {
    Logger.log('Error: ' + error.message);
  }
}

function getLastAction(uniqueID, checkInSheet, checkOutSheet) {
  var checkInData = checkInSheet.getDataRange().getValues();
  var checkOutData = checkOutSheet.getDataRange().getValues();
  var lastCheckIn = null;
  var lastCheckOut = null;

  for (var i = 1; i < checkInData.length; i++) {
    if (checkInData[i][1] == uniqueID) {
      var checkInTime = new Date(checkInData[i][0]);
      if (!lastCheckIn || checkInTime > lastCheckIn) {
        lastCheckIn = checkInTime;
      }
    }
  }

  for (var j = 1; j < checkOutData.length; j++) {
    if (checkOutData[j][1] == uniqueID) {
      var checkOutTime = new Date(checkOutData[j][0]);
      if (!lastCheckOut || checkOutTime > lastCheckOut) {
        lastCheckOut = checkOutTime;
      }
    }
  }

  Logger.log('Last Check-In: ' + lastCheckIn); // Log last check-in time
  Logger.log('Last Check-Out: ' + lastCheckOut); // Log last check-out time

  if (!lastCheckIn) return 'check-out';
  if (!lastCheckOut) return 'check-in';

  return lastCheckIn > lastCheckOut ? 'check-in' : 'check-out';
}

function sendCheckoutReminders() {
  try {
    var checkInSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Check-In Responses');
    if (!checkInSheet) {
      Logger.log('Error: Check-In Responses sheet not found.');
      return;
    }

    var checkInData = checkInSheet.getDataRange().getValues();
    var now = new Date();
    var reminderThreshold = 5 * 60 * 60 * 1000; // 5 hours in milliseconds

    for (var i = 1; i < checkInData.length; i++) {
      var checkInTime = new Date(checkInData[i][0]); // Check-In time
      var uniqueID = checkInData[i][1];
      var userName = checkInData[i][2];
      var emailAddress = checkInData[i][3];
      
      if (now - checkInTime > reminderThreshold && !hasCheckedOut(uniqueID)) {
        // Send reminder email
        if (emailAddress) {
          var subject = "Reminder: Check-Out Needed";
          var message = "Hello " + userName + ",\n\nIt looks like you haven't checked out yet. Please remember to check out to complete your visit.\n\nThank you";
          MailApp.sendEmail(emailAddress, subject, message);
          Logger.log('Reminder email sent to ' + emailAddress);
        } else {
          Logger.log('Error: Email address not found for user ID ' + uniqueID);
        }
        
        // Notify admin
        var adminEmail = 'admin@domain.com'; // Replace with your admin email

        var adminSubject = "User Check-Out Reminder Sent";
        var adminMessage = "A reminder email was sent to user ID " + uniqueID + " (" + emailAddress + ") who has not checked out yet.";
        MailApp.sendEmail(adminEmail, adminSubject, adminMessage);
        Logger.log('Admin notification sent regarding user ID ' + uniqueID);
      }
    }
  } catch (error) {
    Logger.log('Error in sendCheckoutReminders: ' + error.message);
  }
}

function hasCheckedOut(uniqueID) {
  try {
    var checkOutSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Check-Out Responses');
    if (!checkOutSheet) {
      Logger.log('Error: Check-Out Responses sheet not found.');
      return false;
    }
    
    var checkOutData = checkOutSheet.getDataRange().getValues();
    for (var i = 1; i < checkOutData.length; i++) {
      if (checkOutData[i][1] == uniqueID) { // Unique ID
        return true;
      }
    }
    return false;
  } catch (error) {
    Logger.log('Error in hasCheckedOut: ' + error.message);
    return false;
  }
}

// Set up a trigger to run the sendCheckoutReminders function every hour
function createTrigger() {
  ScriptApp.newTrigger('sendCheckoutReminders')
    .timeBased()
    .everyHours(1)
    .create();
}

Triggers for Google Apps Script

1. For Form 1: User ID Generation, you will need the following triggers. Screenshot is below with the details.
   
   
   
   
   
   
2. For Form 2: Check-In/Out, you will need the following triggers. Screenshot is below with the details.
   
   

What does forms collects?

Form 1 - User ID Generation: This form collects the following 5 data.

• Timestamp (Automatic, no code needed, it is by default in google forms)
• Email ID 
• Name
• Building/Wing
• Flat Number
• Acknowledgment to follow the rules of the gym.
• In the last column it automatically generates a 3 digit unique ID.
  
  

Form 2 - Check In/Out: This form collects the following 2 data. Try to keep this form as small as possible to make the user experience better and for faster check-ins/out.

• Unique ID (which the form one creates, and the user receives an email.)
• A drop down menu asking if the user wants to "Check-In" or "Check-Out"

Both the forms talk to each other so when the user uses the Form 2 on daily bases to "Check-In" or "Check-Out", the email is fetched from the Form 1, and an email is trigged to the user accordingly.