Helping Talent Agency with Google Apps Script

The Challenge of Modern Job Applications

In today's fast-paced job market, keeping track of multiple job applications can quickly become overwhelming. As someone who has been through this process, I created a Google Apps Script solution that transforms how we handle job applications using Google Sheets.

Automation Workflow

The Power of Automation

This Google Apps Script project combines URL parsing and AI-powered job analysis to create a comprehensive job application tracking system. Let's dive into its key features and how it can revolutionize your job search process.

Data Processing

Automation

Analysis

Key Features

1. Automated URL Parsing

The script automatically extracts crucial information from job posting URLs:

Company name
Role/Position
Location
All formatted in proper case for consistency

2. AI-Powered Job Analysis

The system includes an AI integration that analyzes job descriptions and provides:

Match score for your profile
Skills analysis (matching and gaps)
Required and preferred languages
Automated formatting for spreadsheet compatibility

AI Analysis

Data Processing

Results

Technical Implementation

Custom Menu Integration

function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Job Processing')
.addItem('Parse Job Data', 'parseJobData')
.addToUi();
}

Smart URL Parsing

function extractJobData(jobURL) {
const company = jobURL.match(/companies\/([^\/]+)/i)?.[1] || "";
const role = jobURL.match(/jobs\/([^?]+)/i)?.[1]?.replace(/-m-f.$/i, "").replace(/-/g, " ") || "";
const city = jobURL.match(/[^]+(?=\?)/)?.[0]?.replace(//g, " ") || "";
return [company, role, city];
}

Code

Integration

Analysis

Results

Benefits and Impact

Efficiency Gains

Reduces manual data entry time by 90%
Automates formatting and standardization
Provides instant job compatibility analysis
Streamlines application tracking

Enhanced Analysis

AI-powered skill matching
Automated qualification assessment
Language requirement analysis
Comprehensive match scoring

Impact Analysis

Future Enhancements

The script is designed for easy expansion, with planned features including:

Integration with more job platforms
Enhanced AI analysis capabilities
Automated application status tracking
Interview preparation recommendations

Getting Started

To implement this solution:

Copy the script to your Google Apps Script editor
Set up your spreadsheet with the required columns
Configure the AI endpoint (if using the analysis feature)
Run the script through the custom menu

Conclusion

This Google Apps Script solution transforms the job application process from a manual, time-consuming task into an efficient, automated system. Whether you're actively job hunting or managing recruitment processes, this tool can significantly streamline your workflow.

Future of Work

Complete Script

/**
 * Google Apps Script File: jobProcessor.gs
 */

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Job Processing')
    .addItem('Parse Job Data', 'parseJobData')
    .addToUi();
}

// Function to split the Job URL and parse Company, Role, and City
function parseJobData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("W2J_Apply");
  const data = sheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    const jobURL = data[i][0];
    if (jobURL) {
      const [company, role, city] = extractJobData(jobURL);
      
      sheet.getRange(i + 1, 7).setValue(properCase(company));
      sheet.getRange(i + 1, 8).setValue(properCase(role));
      sheet.getRange(i + 1, 9).setValue(properCase(city));
    }
  }
}

// Custom formula to parse job URL and return Company, Role, and City
function PARSEJOBDATA(jobURL) {
  if (!jobURL) return ["", "", ""];
  
  const [company, role, city] = extractJobData(jobURL);
  
  return [properCase(company), properCase(role), properCase(city)];
}

// Helper function to extract company, role, and city from job URL
function extractJobData(jobURL) {
  const company = jobURL.match(/companies\/([^\/]+)/i)?.[1] || "";
  const role = jobURL.match(/jobs\/([^_?]+)/i)?.[1]?.replace(/-m-f.*$/i, "").replace(/-/g, " ") || "";
  const city = jobURL.match(/_[^_]+(?=\?)/)?.[0]?.replace(/_/g, " ") || "";
  
  return [company, role, city];
}

// Convert text to Proper Case
function properCase(text) {
  return text.toLowerCase().replace(/\b\w/g, char => char.toUpperCase());
}


/**
 * Function to analyze a job based on description.
 * @param {string} jobDescription - The job description text.
 * @returns {Array} - 2D array with analysis results.
 */
function ANALYZE_JOB(jobDescription) {
  if (!jobDescription) {
    return [["Error: Missing job description"]];
  }

  const payload = {
    description: jobDescription
  };

  const params = {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
    timeout: 20000  // Increased timeout for potentially longer processing times
  };

  try {
    const startTime = new Date().getTime();
    const response = UrlFetchApp.fetch("youraiendpoint", params);
    const endTime = new Date().getTime();

    console.log("API call duration:", (endTime - startTime) + "ms");
    console.log("API Response Status:", response.getResponseCode());

    const rawContent = response.getContentText().trim(); // Trim any extraneous whitespace or newlines
    console.log("API Response Content:", rawContent);

    // First, check if rawContent is valid JSON
    let result;
    try {
      result = JSON.parse(rawContent);
      console.log("Parsed Result:", JSON.stringify(result));
    } catch (parseError) {
      console.error("Error parsing top-level JSON response:", parseError.message);
      console.log("Raw Response:", rawContent);
      return [["Error: Invalid top-level JSON response"]];
    }

    // Check if the response contains an error
    if (result.error) {
      console.error("API Error:", result.error);
      
      // Attempt to parse the raw_response.response if available
      if (result.raw_response && result.raw_response.response) {
        try {
          const detailedResponse = JSON.parse(result.raw_response.response);
          console.log("Detailed AI Response:", JSON.stringify(detailedResponse));
          
          // Extract and prepare values for the output
          const aiResponse = detailedResponse;
          
          const values = [
            aiResponse["Match Score"] || "N/A",
            Array.isArray(aiResponse["Skills I Have"]) && aiResponse["Skills I Have"].length > 0
              ? aiResponse["Skills I Have"].join(", ")
              : "None",
            Array.isArray(aiResponse["Skills I Don’t Have"]) && aiResponse["Skills I Don’t Have"].length > 0
              ? aiResponse["Skills I Don’t Have"].join(", ")
              : "None",
            Array.isArray(aiResponse["Languages Needed"]) && aiResponse["Languages Needed"].length > 0
              ? aiResponse["Languages Needed"].join(", ")
              : "None",
            Array.isArray(aiResponse["Languages Good to Have"]) && aiResponse["Languages Good to Have"].length > 0
              ? aiResponse["Languages Good to Have"].join(", ")
              : "None",
          ];

          console.log("Extracted Values from Detailed Response:", values);

          // Return the values as a 2D array for horizontal placement
          return [values];
        } catch (detailedParseError) {
          console.error("Error parsing detailed AI response:", detailedParseError.message);
          return [["Error: AI response is not valid JSON"]];
        }
      } else {
        return [["Error: " + result.error]];
      }
    }

    // Ensure the response has the expected structure
    if (!result || typeof result !== 'object' || !result.response) {
      console.error("Unexpected JSON structure:", JSON.stringify(result));
      return [["Error: Unexpected JSON structure"]];
    }

    // Parse the AI response
    const aiResponse = result.response;

    // Extract and prepare values for the output
    const values = [
      aiResponse["Match Score"] || "N/A",
      Array.isArray(aiResponse["Skills I Have"]) && aiResponse["Skills I Have"].length > 0
        ? aiResponse["Skills I Have"].join(", ")
        : "None",
      Array.isArray(aiResponse["Skills I Don’t Have"]) && aiResponse["Skills I Don’t Have"].length > 0
        ? aiResponse["Skills I Don’t Have"].join(", ")
        : "None",
      Array.isArray(aiResponse["Languages Needed"]) && aiResponse["Languages Needed"].length > 0
        ? aiResponse["Languages Needed"].join(", ")
        : "None",
      Array.isArray(aiResponse["Languages Good to Have"]) && aiResponse["Languages Good to Have"].length > 0
        ? aiResponse["Languages Good to Have"].join(", ")
        : "None",
    ];

    console.log("Extracted Values:", values);

    // Return the values as a 2D array for horizontal placement
    return [values];

  } catch (error) {
    console.error("Error in ANALYZE_JOB:", error.message);

    const errorValues = [
      "Error",
      "Error",
      "Error",
      "Error",
      "Error: " + error.message
    ];

    console.log("Returning Error Values:", errorValues);

    // Return the error messages as a 2D array
    return [errorValues];
  }
}

AI Tools That Changed My Workflow

A deep dive into the AI-powered tools that revolutionized my productivity and development process

Automating Cover Letters with Apple Shortcuts

How I streamlined job applications using Apple Shortcuts, ChatGPT, and automation