Track Job Applicants Using Gmail and Google Sheets

Hiring can be a challenging process for startup founders, especially when applications are scattered across multiple email threads. Using expensive Applicant Tracking Systems (ATS) may not always be an option, especially for early-stage companies. Fortunately, you can leverage existing tools like Gmail and Google Sheets to efficiently manage incoming resumes and applicant data.

The Problem

Startups often receive job applications via email, making it hard to track resumes and extract useful information manually. Sorting through attachments, gathering contact information, and saving files can quickly become overwhelming.

The Solution: Automating Resume Management

By combining Gmail and Google Sheets with Google Apps Script, you can automatically read resumes from your inbox, extract key information (like name, phone number, and LinkedIn profile), and save both the resume file and data into Google Drive and Sheets. This way, you can create a simple yet powerful tracking system.

How It Works

The script continuously scans your Gmail inbox for new emails with resume attachments. Once found, it:

  1. Extracts key details from the resume (name, phone number, email, LinkedIn profile).
  2. Saves the resume to a specified Google Drive folder.
  3. Records the sender's email, file name, resume URL, and extracted details in a Google Sheet.

Why It’s Effective

  • Cost-Efficient: Uses free Google Workspace tools.
  • Automated: Eliminates the need for manual resume sorting.
  • Centralized Data: Keeps all applicant information in one organized spreadsheet.
  • Scalable: Adapts as your hiring volume increases.

Setting Up the Script

  1. Open Google Sheets and go to Extensions > Apps Script.
  2. Copy and paste the provided script:
  3. 
    // Google Apps Script to automate resume management
    function saveResumesToSheet() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var acceptedFormats = ['pdf', 'docx', 'doc', 'rtf'];
      var row = sheet.getLastRow() + 1;
    
      if (row === 1) {
        sheet.getRange('A1:G1').setValues([['Sender Email', 'Resume File Name', 'Drive Path', 'Candidate Name', 'Phone Number', 'Email', 'LinkedIn Profile']]);
      }
    
      var processedIds = PropertiesService.getScriptProperties().getProperty('PROCESSED_IDS');
      processedIds = processedIds ? JSON.parse(processedIds) : {};
    
      var query = 'has:attachment';
      var threads = GmailApp.search(query);
    
      for (var i = 0; i < threads.length; i++) {
        var messages = threads[i].getMessages();
        for (var j = 0; j < messages.length; j++) {
          var messageId = messages[j].getId();
          if (processedIds[messageId]) continue;
    
          var attachments = messages[j].getAttachments();
          var senderEmail = messages[j].getFrom();
    
          for (var k = 0; k < attachments.length; k++) {
            var fileName = attachments[k].getName();
            var fileExtension = fileName.split('.').pop().toLowerCase();
    
            if (acceptedFormats.includes(fileExtension)) {
              var content = attachments[k].getDataAsString();
              var keywords = ['resume', 'curriculum vitae', 'experience', 'education', 'skills'];
              var isResume = keywords.some(keyword => content.toLowerCase().includes(keyword));
    
              if (isResume) {
                // Extract details using regex
                var nameMatch = content.match(/(?:name|full name|candidate name)[:\s]*([A-Za-z\s]+)/i);
                var name = nameMatch ? nameMatch[1].trim() : '';
    
                var phoneMatch = content.match(/(?:phone|mobile|contact)[:\s]*([+]?\d[\d\s\-()]{7,15})/i);
                var phone = phoneMatch ? phoneMatch[1].trim() : '';
    
                var emailMatch = content.match(/[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}/);
                var email = emailMatch ? emailMatch[0].trim() : '';
    
                var linkedInMatch = content.match(/https?:\/\/[a-zA-Z.]*linkedin.com\/[A-Za-z0-9_\-\/]+/i);
                var linkedIn = linkedInMatch ? linkedInMatch[0].trim() : '';
    
                // Save the resume to Google Drive
                var folder = DriveApp.getFolderById('YOUR_FOLDER_ID');
                var file = folder.createFile(attachments[k]);
                var fileUrl = 'https://drive.google.com/file/d/' + file.getId();
                Logger.log('Saved resume to Drive: ' + fileName);
    
                // Save data to the sheet
                sheet.getRange(row, 1).setValue(senderEmail);
                sheet.getRange(row, 2).setValue(fileName);
                sheet.getRange(row, 3).setValue(fileUrl);
                sheet.getRange(row, 4).setValue(name);
                sheet.getRange(row, 5).setValue(phone);
                sheet.getRange(row, 6).setValue(email);
                sheet.getRange(row, 7).setValue(linkedIn);
                row++;
              }
            }
          }
    
          // Mark the message as processed
          processedIds[messageId] = true;
        }
      }
    
      // Save the updated processed IDs
      PropertiesService.getScriptProperties().setProperty('PROCESSED_IDS', JSON.stringify(processedIds));
      Logger.log('Resume extraction completed.');
    }
    
    function setTrigger() {
      ScriptApp.newTrigger('saveResumesToSheet')
        .timeBased()
        .everyMinutes(30)
        .create();
      Logger.log('Trigger set to run every 30 minutes.');
    }
    
                        
  4. Update the YOUR_FOLDER_ID with the ID of your Google Drive folder.
  5. Run the setTrigger() function to schedule the script. Watch the video below for a step-by-step guide:

How to Get the YOUR_FOLDER_ID

To find the ID of the Google Drive folder where resumes will be saved:

  1. Open Google Drive and navigate to the folder.
  2. Click on the folder to open it.
  3. Look at the URL in your browser's address bar. It will look something like this:
https://drive.google.com/drive/folders/1g8n6yz9XLW4sf0OPLUXtwwzv5luBi83G

The long string after /folders/ is the YOUR_FOLDER_ID you need to use in the script.

Conclusion

This simple yet effective solution allows founders to streamline resume management without investing in costly ATS software. With Gmail and Google Sheets, you can maintain a well-organized and automated hiring pipeline.

Need help implementing this setup? Let me know!

Are You Ready for AI-Driven Hiring?

If you’re in HR or recruitment, now is the time to explore how FigLinks can transform your hiring strategy. The future of recruitment is intelligent automation, and the future is now!

Book your demo now or Sign Up to explore.

Ugendreshwar Kudupudi

First-generation entrepreneur with over 20 years of experience in the Semiconductor and AI sector. Extensive experience in engineering, sales, marketing, and customer success, providing a comprehensive perspective on customer behavior and cross-border team management.

0 Comments

Post Comment

Your email address will not be published. Required fields are marked *