Automate HR Salary Benchmarking with Google Sheets and Gemini

May 06, 2026

Relying on manual spreadsheets for salary benchmarking is a critical liability in today’s dynamic market, sowing the seeds of attrition and strategic failure.

The Challenge of Manual Salary Benchmarking

Compensation is more than a line item on a budget; it’s the foundational contract between an organization and its talent. Get it right, and you build a culture of fairness that attracts and retains top performers. Get it wrong, and you sow the seeds of attrition, disengagement, and strategic failure. Yet, for many organizations, the critical process of salary benchmarking remains trapped in a cycle of manual drudgery, outdated tools, and reactive adjustments. Before we dive into building a solution, we must first dissect the fundamental flaws of the traditional approach.

Why Static Salary Spreadsheets Fail in a Dynamic Market

The trusty spreadsheet has been the workhorse of HR for decades, but in the context of modern compensation strategy, it has become a liability. A static salaries.xlsx file is an artifact of a bygone era, fundamentally unequipped to handle the velocity and complexity of today’s talent market.

Here’s where the model breaks down:

Data is a Snapshot, Not a Stream: The moment you finish compiling your benchmarking spreadsheet—pulling data from three different survey sites, cross-referencing with job board postings, and averaging it all out—it’s already obsolete. Tech salaries can shift in a single quarter, remote work policies redefine geographic pay differentials, and new, in-demand skills can emerge overnight. Your spreadsheet is a digital fossil, representing a market that no longer exists.

Prone to Human Error and Tedium: The process is a recipe for mistakes. It involves endless hours of copy-pasting, manually standardizing job titles that vary wildly between sources (“Lead Developer” vs. “Senior Software Engineer II”), and wrestling with complex VLOOKUP or INDEX/MATCH formulas. A single misplaced decimal or a broken formula can cascade into costly errors, leading to misinformed salary offers and budget overruns.
Lacks Context and Nuance: A cell containing $150,000 is deceptively simple. It fails to capture the multi-dimensional reality of compensation. Is that for a candidate in San Francisco or Omaha? Does it account for equity, a signing bonus, or specific, high-value skills like “large language model optimization”? Spreadsheets flatten this rich, contextual data into a single, often misleading, number. They can’t effectively weigh the premium for a candidate with 8 years of experience versus one with 6, or parse the qualitative difference between two companies’ benefits packages.
Impossible to Scale: What might be manageable for a 30-person startup becomes an unmitigated disaster for a 300-person scale-up. The spreadsheet bloats into a labyrinth of tabs, versions, and conflicting data sources. It cannot serve as a single source of truth and actively hinders collaborative, data-driven decision-making across departments.

The Strategic Cost of Compensation Misalignment

The consequences of relying on a broken, manual process extend far beyond administrative headaches. Misaligned compensation is a silent killer of growth, creating significant strategic costs that ripple through the entire organization.

Losing the War for Talent: In a competitive market, speed and accuracy are paramount. If your compensation data is stale, your offers will be off-market. Too low, and you lose top candidates to competitors who have their finger on the pulse. Too high, and you burn through cash inefficiently. This misalignment directly impacts your ability to acquire the talent needed to execute your business strategy.
Fueling Employee Attrition: Nothing erodes trust faster than the perception of unfair pay. When existing employees discover that new hires are being brought in at significantly higher rates, or that their salary has fallen behind the market rate, disengagement is inevitable. This leads to increased churn, forcing you to bear the immense cost of recruiting, hiring, and onboarding replacements—a cost that far exceeds that of proactive, fair market adjustments.
Eroding Budgets and Profitability: Paying people fairly doesn’t mean overpaying everyone. Without accurate, granular data, you’re flying blind. You might overspend on certain roles while under-investing in others, leading to an inefficient allocation of your most significant expense: payroll. This directly impacts your operational margin and limits your ability to invest in other growth areas.
Creating Compliance and Pay Equity Risks: A manual, inconsistent process is a compliance nightmare. It makes it incredibly difficult to conduct pay equity audits and ensure that compensation decisions are free from unconscious bias. This lack of a systematic, data-backed approach exposes the organization to significant legal and reputational risk.

Our Goal: An Automated, AI-Powered Intelligence System

To overcome these challenges, we need to fundamentally rethink our approach. Our goal is not to build a better spreadsheet. Our goal is to create a living, automated compensation intelligence system. This is where the synergy of a familiar platform like [Automated Web Scraping with [Multilingual Text-to-Speech Tool with SocialSheet Streamline Your Social Media Posting 123](https://votuduc.com/Multilingual-Text-to-Speech-Tool-with-Google-Workspace-p809282)](https://votuduc.com/Automated-Web-Scraping-with-Google-Sheets-p292968) and a powerful Large Language Model like Gemini comes into play.

We aim to build a system that transforms our process from:

Static to Dynamic: Instead of a quarterly report, imagine a dashboard that can be refreshed on-demand, pulling in the latest market data with the click of a button.
Manual to Automated: Eliminate the error-prone copy-paste cycle. We will build a workflow that programmatically fetches, cleans, and structures data from multiple sources.
**Flat Data to Rich Intelligence: This is the most critical leap. We will leverage Gemini not just to aggregate numbers, but to interpret them. We’ll ask it to analyze job descriptions to extract key skills, adjust salary bands based on nuanced location data, and even summarize qualitative trends from market reports.

The ultimate objective is to empower HR, finance, and hiring managers with the real-time, contextual, and accurate intelligence they need to make fast, fair, and strategic compensation decisions that drive the business forward.

Architecting Your Smart Salary Dashboard

Before we write a single line of code, let’s draw the blueprint. A robust system isn’t just about clever code; it’s about a clear architecture where each component has a distinct and vital role. Understanding this structure will not only make the building process smoother but will also empower you to customize and expand upon it later. We’re essentially creating a data assembly line, transforming raw market noise into a clear, actionable signal right inside your Google Sheet.

Core Components: Google Sheets, Apps Script, and the Gemini API

Our automated dashboard is a three-legged stool, with each leg providing essential support. If one is weak, the whole system falters. Let’s break down the role of each technology.

Google Sheets: The Command Center. This is far more than just our final destination for data. Google Sheets will serve as our User Interface (UI), our simple database, and our visual analytics layer. We’ll structure our workbook with distinct tabs:
Roles_Input: A clean interface where you or your HR team can input the job titles, levels, and locations you need to benchmark.
Benchmark_Results: The destination for the structured data returned by our system. This is where the magic becomes visible, with columns for the 25th, 50th, and 75th percentile salaries, plus qualitative commentary.
Dashboard: An optional but highly recommended tab with charts and tables that visualize the data from Benchmark_Results, allowing for at-a-glance comparisons.
[AI Powered Cover Letter [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606) Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automated Quote Generation and Delivery System for Jobber-Engine-p111092): The Engine. This is the invisible workhorse connecting everything. Apps Script is a cloud-based scripting platform based on JavaScript that lets you extend and automate Automatically create new folders in Google Drive, generate templates in new folders, fill out text automatically in new files, and save info in Google Sheets. In our project, it’s the central nervous system that will:
Read the job role inputs from our Roles_Input sheet.
Construct a precise, context-rich prompt to send to the AI.
Make a secure, authenticated API call to the Gemini API.
Receive the AI’s response and parse the structured data (JSON).
Write the extracted salary benchmarks and insights back into the Benchmark_Results sheet.
Create a custom menu item in Google Sheets (e.g., “Analyze Salaries”) to trigger the entire process with a single click.
The Gemini API: The Brains. This is where the “smart” in our smart dashboard comes from. We’re not just running a simple calculation; we’re leveraging a powerful Large Language Model (LLM) to perform sophisticated analysis. Gemini’s role is to act as a tireless, expert-level compensation analyst. We will instruct it to:
Understand the nuances of a job title, level, and geographic market.
Synthesize information from its vast training data to determine competitive salary bands.
Provide not just a single number, but a statistical range (25th, 50th, 75th percentiles) to reflect market variance.
Return this complex analysis in a clean, machine-readable JSON format that our Apps Script can easily process.

The End-to-End Data Flow: From Market to Insight

Understanding the journey of your data is key. When you click that “run” button, here is the precise sequence of events that unfolds in a matter of seconds:

Input & Trigger: You enter a job role like “Senior Product Manager” with a level and location into the Roles_Input sheet. You then click a custom menu button we’ll create, like Benchmark > Run Analysis for Selected Row.
Script Execution: The click triggers a specific function in your bound Apps Script project. The script reads the data from the active row.
[Prompt Engineering for Reliable Autonomous Workspace Agents for Reliable Autonomous Workspace Agents](https://votuduc.com/prompt-engineering-for-reliable-autonomous-workspace-agents-p-20260319404106): The script takes the raw inputs (title, level, location) and embeds them into a carefully crafted prompt. This prompt instructs the Gemini model to act as an HR compensation analyst and return a structured JSON object containing specific data points.
API Call: The Apps Script packages the prompt and sends it via a secure HTTPS request to the Gemini API endpoint, authenticating the request with your API key.
AI Synthesis: The Gemini model processes your request. It synthesizes its knowledge of compensation data, job markets, and role responsibilities to generate the requested salary percentiles and qualitative commentary.
JSON Response: Gemini sends the data back to your Apps Script, not as plain text, but as a neatly structured JSON string. It looks something like this:


{

"p25": 145000,

"p50_median": 165000,

"p75": 190000,

"commentary": "The market for this role in this location is highly competitive, with top-tier tech companies driving up the 75th percentile."

}

Parsing & Writing: Your Apps Script receives this JSON, parses it to extract each value (p25, p50_median, etc.), and then writes these values into the corresponding cells in the Benchmark_Results sheet for the row you initiated.
Insight: Instantly, the new data populates your sheet. Any charts or conditional formatting you’ve set up on your Dashboard tab update automatically, transforming the raw output into an immediate, actionable insight.

Prerequisites and Initial AC2F Streamline Your Google Drive Workflow Setup

Before we dive into the code, let’s get our environment set up. This is a one-time process that lays the groundwork for our project.

A Google Account: You’ll need a standard Google account (@gmail.com) or a Automated Client Onboarding with Google Forms and Google Drive. account.
Create a Google Cloud Platform (GCP) Project: The Gemini API is a Google Cloud service. You need a project to enable the API and manage credentials.

Navigate to the Google Cloud Console.
In the top project selector dropdown, click “New Project”.
Give it a memorable name (e.g., “HR Salary Benchmarker”) and create it.

Enable the [Building Self Correcting Agentic Workflows with Building Self-Correcting Agentic Workflows with Vertex AI](https://votuduc.com/building-self-correcting-agentic-workflows-with-vertex-ai-p-20260321542526) API: This is the API that gives us access to the Gemini models.

In your new GCP project, use the search bar at the top to find “Vertex AI API”.
Click on the result and then click the “Enable” button. This may take a minute.

Generate an API Key: For this project, an API key is the simplest way to authenticate our requests from Apps Script.

In the GCP Console, navigate to APIs & Services > Credentials.
Click + Create Credentials at the top and select API key.
Copy the generated key immediately and save it somewhere secure (like a password manager). Important: Treat this key like a password. We will add security restrictions to it later.

Link Your Apps Script to the GCP Project: This is a crucial step that tells Google Sheets which GCP project’s APIs it’s allowed to use.

Create a new Google Sheet for this project.
Go to Extensions > Apps Script to open the script editor.
In the left-hand menu of the Apps Script editor, click the Project Settings (gear) icon.
Scroll down to the “Google Cloud Platform (GCP) Project” section.
Click Change project and paste in your GCP Project Number (you can find this on the main dashboard of your GCP project). Click Set project.

With these prerequisites in place, your environment is now configured. Your Google Sheet is linked to a Cloud project with the Gemini API enabled, and you have a secure key ready to authorize your requests. You’re ready to start building.

Step 1: Automating Market Data Ingestion with Apps Script

Before Gemini can work its magic, we need to feed it the right data. Manually copy-pasting salary information from a dozen websites is a soul-crushing, error-prone task that defeats the entire purpose of Automated Work Order Processing for UPS. This is where Genesis Engine AI Powered Content to Video Production Pipeline becomes our workhorse. It’s a cloud-based JavaScript platform that lets us extend Automated Discount Code Management System applications—in our case, programmatically pulling data from the web directly into our Google Sheet. Think of it as the central nervous system for our project, connecting our spreadsheet to the vast ocean of market data.

Identifying and Connecting to Reliable Salary Data Sources

The quality of your benchmarking is only as good as the quality of your data. Sourcing this data is the first, and arguably most critical, hurdle. You have two primary paths, each with its own trade-offs.

1. The Gold Standard: APIs

Application Programming Interfaces (APIs) are the cleanest, most reliable way to get structured data. Companies like Payscale, Salary.com, and Radford offer commercial APIs that provide detailed, filterable salary data.

Pros: Highly reliable, structured (usually in JSON format), and often comes with support and documentation.
Cons: Almost always comes at a cost. You’ll need to sign up for a plan, get an API key, and manage authentication.
Best For: Businesses that need a robust, scalable, and legally sound solution for ongoing benchmarking.

2. The Pragmatic Path: Web Scraping

Web scraping involves writing a script to automatically extract information directly from the HTML of a public website. This is how you can tap into data aggregators like Levels.fyi (for tech) or public sources like the Bureau of Labor Statistics.

Pros: Can be free (if the data is public).
Cons: It’s a fragile process. A simple website redesign can break your script overnight. More importantly, you must be diligent about the legal and ethical implications. Always check a site’s robots.txt file and Terms of Service before scraping. Scraping is a tool, and like any tool, it can be misused.
Best For: One-off projects, pulling from explicitly public data sources, or when an API isn’t available.

For this guide, we’ll focus on the mechanics of using an API, as it represents a more stable, long-term methodology. The principles, however, apply equally to scraping.

Using UrlFetchApp to Scrape or Call APIs

[Architecting Multi Tenant AI Workflows in Building Modular Agentic Apps Script with Gemini Function Calling](https://votuduc.com/architecting-multi-tenant-ai-workflows-in-google-apps-script-p-20260321290501) provides a built-in service called UrlFetchApp that allows our script to make HTTP requests to any external endpoint. This is our bridge to the outside world.

Let’s write a function to call a hypothetical salary API. Imagine we have an API endpoint that returns salary data for a specific role and location.


// A function to fetch market data from a hypothetical API.

function fetchMarketData(jobTitle, location) {

// Always encode URL parameters to handle spaces and special characters.

const encodedJobTitle = encodeURIComponent(jobTitle);

const encodedLocation = encodeURIComponent(location);

// This is a fictional API endpoint for demonstration purposes.

// Replace this with your actual API endpoint.

const url = `https://api.hypothetical-salary-data.com/v1/salary?title=${encodedJobTitle}&location=${encodedLocation}`;

// Most APIs require an API key for authentication, passed in the headers.

// It's a best practice to store secrets like API keys in PropertiesService, not in your code.

const API_KEY = PropertiesService.getScriptProperties().getProperty('SALARY_API_KEY');

if (!API_KEY) {

throw new Error('API Key not found. Please set it in Script Properties.');

}

const options = {

'method': 'GET',

'contentType': 'application/json',

// The 'Authorization' header is a common way to send an API key.

// The format 'Bearer YOUR_KEY' is common, but check your API's documentation.

'headers': {

'Authorization': 'Bearer ' + API_KEY

},

'muteHttpExceptions': true // Prevents script from stopping on HTTP errors (e.g., 404)

};

try {

const response = UrlFetchApp.fetch(url, options);

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode === 200) {

// Success! Return the raw JSON data.

return responseBody;

} else {

// Log the error for debugging.

console.error(`API request failed with code ${responseCode}: ${responseBody}`);

return null;

}

} catch (e) {

console.error(`Failed to fetch data due to an error: ${e.message}`);

return null;

}

}

Key Takeaways from this script:

UrlFetchApp.fetch(url, options): This is the core command. It takes the target URL and an options object.
Authentication: We’re passing our API key in the headers object. This is a standard and secure practice.
Error Handling: By setting muteHttpExceptions to true and checking response.getResponseCode(), we can handle API errors (like “Not Found” or “Unauthorized”) gracefully instead of letting our script crash.
Security: We are using PropertiesService to store our API key. Never hardcode secrets directly in your script. Go to Project Settings > Script Properties to add your key securely.

Parsing and Staging Data in a Dedicated Google Sheet

Getting the raw data is only half the battle. The API returns a string of JSON (JavaScript Object Notation), which we need to parse into a usable format and place neatly into our spreadsheet.

First, create a new sheet (tab) in your Google Sheet and name it Raw Market Data. This keeps our raw, unprocessed data separate from our final analysis, which is a crucial best practice for data integrity. Let’s assume our API returns JSON that looks like this:


{

"request": {

"title": "Senior Product Manager",

"location": "New York, NY"

},

"data": {

"source": "Hypothetical Data Inc.",

"currency": "USD",

"percentiles": {

"p25": 145000,

"p50_median": 165000,

"p75": 190000

},

"lastUpdated": "2023-10-27T10:00:00Z"

}

}

Now, let’s write a function that calls our fetchMarketData function, parses the result, and writes it to our Raw Market Data sheet.


// A function to parse the JSON and write it to our staging sheet.

function stageMarketData(jobTitle, location) {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const stagingSheet = ss.getSheetByName('Raw Market Data');

if (!stagingSheet) {

throw new Error('Sheet "Raw Market Data" not found. Please create it.');

}

// 1. Fetch the raw data string from the API.

const rawJsonData = fetchMarketData(jobTitle, location);

if (rawJsonData) {

// 2. Parse the JSON string into a JavaScript object.

const parsedData = JSON.parse(rawJsonData);

const salaryData = parsedData.data;

// 3. Prepare the data row for insertion into the sheet.

// Adding a timestamp is vital for knowing how fresh your data is.

const dataRow = [

jobTitle,                      // Column A: Job Title

location,                      // Column B: Location

salaryData.percentiles.p25,    // Column C: 25th Percentile

salaryData.percentiles.p50_median, // Column D: Median

salaryData.percentiles.p75,    // Column E: 75th Percentile

salaryData.source,             // Column F: Data Source

new Date()                     // Column G: Timestamp

];

// 4. Append the new row to the staging sheet.

stagingSheet.appendRow(dataRow);

console.log(`Successfully staged data for ${jobTitle} in ${location}.`);

} else {

console.log(`Could not stage data for ${jobTitle} in ${location}. See logs for details.`);

}

}

// Example of how to run it for a specific role.

function testStaging() {

stageMarketData('Senior Product Manager', 'New York, NY');

}

With this script, you can now programmatically fetch, parse, and store salary benchmarks. We’ve successfully built the data pipeline. The next step will be to orchestrate these functions to run automatically for all the roles we need to benchmark, creating a rich dataset for Gemini to analyze.

Step 2: Building the Central Benchmark Hub

With our data sources identified and Gemini ready to assist, it’s time to build the foundation of our entire system: the central benchmark hub. Think of this as the “single source of truth” for all compensation data within your organization. Its purpose is to consolidate, standardize, and serve up reliable market data to any other planning document you use. Centralizing this data prevents stale, conflicting information scattered across a dozen different spreadsheets and ensures everyone is working from the same numbers.

Designing the Master ‘Benchmark’ Sheet Schema

Before we write a single line of code, we need to design the structure—the schema—of our master data sheet. A well-designed schema is the difference between a robust, scalable system and a brittle one that breaks every other week.

In your Google Sheet, create a new tab and name it Benchmark. This sheet should be locked down, with edit access granted only to a few key administrators. Everyone else will get View Only access.

Here is a recommended schema with the essential columns. Create these as the headers in the first row of your Benchmark sheet:

| Column Header | Data Type | Purpose & Best Practices |

| :--- | :--- | :--- |

| BenchmarkID | Text | Primary Key. A unique identifier for each specific role, level, and location combination. This is crucial for reliable lookups. Use a formula in your source sheets to generate this, like =CONCATENATE(B2, "-", C2, "-", D2). |

| RoleTitle | Text | The standardized job title (e.g., “Product Manager”, not “PM”). Use Data Validation to create a dropdown list of approved titles to enforce consistency. |

| Level | Text | The internal job level (e.g., “L4”, “Senior”, “IC5”). This must map directly to your company’s leveling framework. Also a great candidate for a dropdown list. |

| Location | Text | The geographic market (e.g., “New York, NY”, “Remote - Tier 1”). Standardize your location tiers to make analysis easier. |

| DataSource | Text | The origin of the data (e.g., “Pave”, “Levels.fyi”, “Gemini Analysis”). This is vital for auditing and understanding the context of each data point. |

| 25th_Percentile | Currency | The 25th percentile of the base salary market data for this benchmark. |

| 50th_Percentile | Currency | The 50th percentile (median) of the base salary market data. This is typically your primary reference point. |

| 75th_Percentile | Currency | The 75th percentile of the base salary market data. |

| LastUpdated | Date/Time | A timestamp indicating when this row was last inserted or updated by our script. This helps you track data freshness at a glance. |

| Notes | Text | An optional field for any relevant context, such as “Thin data for this market” or “Includes equity estimate.” |

Your clean Benchmark sheet should look something like this:

| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |

| SWE-L4-SF | Software Engineer | L4 | San Francisco, CA | Pave | $155,000 | $175,000 | $195,000 | 10/26/2023 9:15 AM | |

| … | | | | | | | | | |

With this structure in place, the next challenge is populating it efficiently and accurately. Manually copying and pasting data from multiple sources is not only tedious but also prone to error. This is where automation becomes our superpower.

Automating Data Ingestion with Google Apps Script & Gemini

Google Apps Script is a JavaScript-based platform that lets you create custom functions and automations within the Automated Email Journey with Google Sheets and Google Analytics ecosystem. We will write a script that acts as the engine for our benchmark hub. It will automatically fetch data from our various source sheets, process it, and populate our master Benchmark sheet.

The script will perform several key functions:

Read Source Data: It will programmatically access the raw data you’ve exported into separate tabs (e.g., a Pave_Export tab, a Levels_Export tab).
Standardize and Clean: It will transform the raw data to match our master schema. For example, it might convert “Software Dev” from a source to our standardized “Software Engineer” RoleTitle.
Generate Unique IDs: It will create the BenchmarkID for each row, ensuring there are no duplicates.
Call the Gemini API (Optional but Powerful): For roles where you lack structured data, you can use the script to send a prompt to the Gemini API. For instance, you could ask: “Based on data from Levels.fyi and Radford for a Senior Data Scientist in Austin, TX, what are the estimated 25th, 50th, and 75th percentile base salaries?” The script can then parse Gemini’s response and add it as a new entry with DataSource set to “Gemini Analysis”.
Write to the Master Sheet: Finally, the script will append the clean, standardized data into the Benchmark sheet, creating a comprehensive and up-to-date repository.

Here’s a simplified conceptual outline of what the Apps Script code might look like:


// This is a conceptual outline, not fully functional code.

function updateBenchmarkHub() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sourceSheet = ss.getSheetByName('Pave_Export');

const benchmarkSheet = ss.getSheetByName('Benchmark');

// 1. Get all data from the source sheet

const sourceData = sourceSheet.getDataRange().getValues();

// Remove header row

sourceData.shift();

const processedData = [];

// 2. Loop through each row of raw data

for (const row of sourceData) {

const role = standardizeRole(row[0]); // e.g., "Software Dev" -> "Software Engineer"

const level = standardizeLevel(row[1]); // e.g., "IV" -> "L4"

const location = standardizeLocation(row[2]);

// 3. Generate the unique BenchmarkID

const benchmarkId = `${role}-${level}-${location}`;

// 4. Assemble the clean data row for our master sheet

const newRow = [

benchmarkId,

role,

level,

location,

'Pave', // DataSource

row[3], // 25th_Percentile

row[4], // 50th_Percentile

row[5], // 75th_Percentile

new Date(), // LastUpdated

'' // Notes

];

processedData.push(newRow);

}

// 5. Write all the processed data to the Benchmark sheet in one go

// This is more efficient than writing row by row.

benchmarkSheet.getRange(benchmarkSheet.getLastRow() + 1, 1, processedData.length, processedData[0].length).setValues(processedData);

}

// You would also have helper functions like standardizeRole(), standardizeLevel(), etc.

// and another function to call the Gemini API for specific queries.

You can set this script to run automatically on a trigger—for example, every night at 1 AM or every time a change is made to one of the source sheets. This ensures your central benchmark hub remains a living, breathing repository of the most current data available, without any manual intervention.

Step 3: Activating AI for Proactive Outlier Analysis

With our data structured and our benchmarks in place, it’s time for the main event. This is where we transcend the limitations of traditional spreadsheet formulas and empower our sheet with the advanced reasoning capabilities of a Large Language Model. We’ll use Google Apps Script to build a bridge between our data and the Gemini API, turning our static sheet into a dynamic, intelligent analysis tool.

Connecting Google Sheets to the Gemini API via Apps Script

Google Apps Script is the JavaScript-based cloud scripting platform that lets you extend and automate Automated Google Slides Generation with Text Replacement applications. Think of it as the nervous system connecting your Google Sheet’s brain to the powerful Gemini model.

First, let’s open the script editor. From your Google Sheet, navigate to Extensions > Apps Script. This will open a new tab with a code editor.

To communicate with the Gemini API, you’ll need an API key. You can generate one for free from Google AI Studio. Treat this key like a password—never share it publicly or commit it to a public code repository.

Instead of hardcoding the key directly into our script (a major security risk), we’ll use Apps Script’s built-in PropertiesService to store it securely.

In the Apps Script editor, click the Project Settings (gear icon) on the left.
Scroll down to Script Properties and click Add script property.
Enter GEMINI_API_KEY as the Property and paste your actual API key as the Value. Click Save script properties.

Now, let’s write the function that will handle the communication. Replace the default code in Code.gs with the following:


// Define a custom menu to run our analysis easily from the sheet UI

function onOpen() {

SpreadsheetApp.getUi()

.createMenu('AI Salary Analysis')

.addItem('Analyze Department Salaries', 'analyzeDepartmentData')

.addToUi();

}

/**

* Calls the Gemini API with a given prompt.

* @param {string} prompt The complete prompt to send to the Gemini API.

* @returns {string} The text response from the Gemini model.

*/

function callGeminiAPI(prompt) {

// Retrieve the API key securely from Script Properties

const API_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

if (!API_KEY) {

throw new Error('GEMINI_API_KEY not found in Script Properties. Please set it in Project Settings.');

}

const API_URL = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent?key=' + API_KEY;

const requestBody = {

"contents": [{

"parts": [{

"text": prompt

}]

}],

"generationConfig": {

"temperature": 0.2,

"topK": 1,

"topP": 1,

"maxOutputTokens": 2048,

},

};

const options = {

'method': 'post',

'contentType': 'application/json',

'payload': JSON.stringify(requestBody),

'muteHttpExceptions': true // Prevents script from stopping on HTTP errors

};

try {

const response = UrlFetchApp.fetch(API_URL, options);

const responseCode = response.getResponseCode();

const responseBody = response.getContentText();

if (responseCode === 200) {

const parsedResponse = JSON.parse(responseBody);

// Navigate through the nested JSON to get the actual text content

return parsedResponse.candidates[0].content.parts[0].text;

} else {

Logger.log(`API Error: ${responseCode} - ${responseBody}`);

return `Error: Could not connect to Gemini API. Status: ${responseCode}.`;

}

} catch (e) {

Logger.log(`Exception during API call: ${e.message}`);

return `Error: An exception occurred while calling the API.`;

}

}

This script does three key things:

onOpen(): Creates a custom menu in your Google Sheet UI, making it easy for anyone to trigger the analysis without needing to open the script editor.
Securely retrieves the API key: It fetches the key from Script Properties, keeping it out of the code itself.
callGeminiAPI(prompt): This is our core communication function. It formats the request, sends it to the Gemini API endpoint using UrlFetchApp, and handles the response, including basic error logging.

Crafting Effective Prompts to Analyze Departmental Data

The quality of Gemini’s analysis is directly proportional to the quality of your prompt. A vague request will yield a vague answer. We need to be specific, providing context, data, and a clear definition of the desired output format. This practice is often called “prompt engineering.”

Here is a powerful, structured prompt template designed for our salary analysis task. We’ll build a function in Apps Script to dynamically generate this prompt using data from our sheet.

The Prompt Template:

You are an expert HR Compensation Analyst AI. Your task is to analyze a dataset of employee salaries for a single department and identify potential outliers based on job level, location, and tenure.

Context:

The company aims for compensation to be within a 15% range of the departmental average for each job level. Significant deviations, especially when not justified by location or tenure, should be flagged.

Data:

Here is the data for the department in CSV format:

EmployeeID,JobTitle,JobLevel,Location,TenureInYears,CurrentSalary


{department_data_csv}

Instructions:

Analyze the provided data to identify employees whose salaries are potential outliers (both significantly high and low) compared to their peers at the same job level.
Consider location and tenure as potential justifying factors. For example, a higher salary in a high-cost-of-living area like ‘San Francisco’ is more acceptable than in ‘Boise’. Similarly, longer tenure might justify a salary at the higher end of the range.
Return your findings ONLY as a valid JSON object. Do not include any other text, greetings, or explanations outside of the JSON structure.

Required JSON Format:


{

  "analysisSummary": "A one-sentence qualitative summary of the department's overall salary health.",

  "outliers": [

    {

      "employeeID": "The ID of the outlier employee",

      "reasoning": "A concise explanation for why this salary is flagged as an outlier, considering job level, peers, and other factors.",

      "severity": "high | medium | low"

    }

  ]

}

This prompt is effective because it:

Assigns a Role: “You are an expert HR Compensation Analyst AI.”
Provides Context: Explains the company’s compensation philosophy (within 15% of the average).
Structures the Data: Uses a placeholder {department_data_csv} that we will populate with actual data from our sheet.
Gives Clear Instructions: Tells the AI exactly what to look for.
Enforces an Output Format: Demands a specific JSON structure, which is critical for programmatic parsing and automation in the next step.

Automatically Flagging and Annotating Salary Outliers

Now we’ll write the main function that ties everything together. This function will read the data from our sheet, build the prompt, send it to Gemini using our callGeminiAPI function, parse the JSON response, and then update the sheet with the AI’s insights.

Add the following function to your Code.gs file:


/**

* Main function to analyze departmental data, call Gemini, and update the sheet.

* Triggered from the custom menu.

*/

function analyzeDepartmentData() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName('Employee Data'); // Change 'Employee Data' to your sheet's name

const ui = SpreadsheetApp.getUi();

// Get all data from the sheet, assuming headers are in row 1

const dataRange = sheet.getDataRange();

const allData = dataRange.getValues();

const headers = allData.shift(); // Remove headers and store them

// Find column indices to make the script robust to column reordering

const idCol = headers.indexOf('EmployeeID');

const salaryCol = headers.indexOf('CurrentSalary');

if (idCol === -1 || salaryCol === -1) {

ui.alert('Error', 'Could not find required columns: "EmployeeID" and "CurrentSalary".', ui.ButtonSet.OK);

return;

}

// Convert the 2D array of data into a single CSV string

const dataAsCsv = allData.map(row => row.join(',')).join('\n');

// --- 1. PROMPT ENGINEERING ---

const prompt = `

You are an expert HR Compensation Analyst AI. Your task is to analyze a dataset of employee salaries for a single department and identify potential outliers based on job level, location, and tenure.

**Context:**

The company aims for compensation to be within a 15% range of the departmental average for each job level. Significant deviations, especially when not justified by location or tenure, should be flagged.

**Data:**

Here is the data for the department in CSV format:

${headers.join(',')}

${dataAsCsv}

**Instructions:**

1. Analyze the provided data to identify employees whose salaries are potential outliers (both significantly high and low) compared to their peers at the same job level.

2. Consider location and tenure as potential justifying factors. For example, a higher salary in a high-cost-of-living area like 'San Francisco' is more acceptable than in 'Boise'. Similarly, longer tenure might justify a salary at the higher end of the range.

3. Return your findings ONLY as a valid JSON object. Do not include any other text, greetings, or explanations outside of the JSON structure.

**Required JSON Format:**

\`\`\`json

{

"analysisSummary": "A one-sentence qualitative summary of the department's overall salary health.",

"outliers": [

{

"employeeID": "The ID of the outlier employee",

"reasoning": "A concise explanation for why this salary is flagged as an outlier, considering job level, peers, and other factors.",

"severity": "high | medium | low"

}

]

}

\`\`\`

`;

ui.showSidebar(HtmlService.createHtmlOutput('<h3><i class="fas fa-spinner fa-spin"></i> Analyzing... Please wait.</h3><p>Contacting Gemini AI. This may take a moment.</p>').setTitle('AI Analysis in Progress'));

// --- 2. API CALL ---

const rawResponse = callGeminiAPI(prompt);

// Clean up the response to ensure it's valid JSON

const jsonString = rawResponse.replace(/```json\n/g, "").replace(/\n```/g, "").trim();

// --- 3. PARSE AND UPDATE SHEET ---

try {

const analysisResult = JSON.parse(jsonString);

// Clear previous formatting and notes to start fresh

dataRange.clearNote().setBackground(null);

// Create a map of employee IDs to their row number for quick lookups

const employeeIdToRowMap = new Map();

allData.forEach((row, index) => {

// +2 because allData is 0-indexed and we shifted headers (so +1), and sheets are 1-indexed (so +1 more)

employeeIdToRowMap.set(row[idCol], index + 2);

});

// Process each outlier found by the AI

analysisResult.outliers.forEach(outlier => {

const rowNum = employeeIdToRowMap.get(outlier.employeeID);

if (rowNum) {

const salaryCell = sheet.getRange(rowNum, salaryCol + 1);

// Flag the cell with a color based on severity

let flagColor;

switch (outlier.severity.toLowerCase()) {

case 'high':

flagColor = '#f4cccc'; // Light red

break;

case 'medium':

flagColor = '#fff2cc'; // Light yellow

break;

default:

flagColor = '#d9ead3'; // Light green

break;

}

salaryCell.setBackground(flagColor);

// Add the AI's reasoning as a note to the cell

const note = `AI Analysis (${outlier.severity.toUpperCase()}):\n${outlier.reasoning}`;

salaryCell.setNote(note);

}

});

// Display the summary

ui.alert('Analysis Complete', analysisResult.analysisSummary, ui.ButtonSet.OK);

} catch (e) {

Logger.log(`JSON Parsing Error: ${e.message}`);

Logger.log(`Raw Response was: ${rawResponse}`);

ui.alert('Error', 'Failed to parse the AI response. Check the logs for details.', ui.ButtonSet.OK);

}

}

After pasting this code, save your project. Reload your Google Sheet, and you should see a new menu item: AI Salary Analysis. When you click Analyze Department Salaries, the script will:

Read all your employee data.
Construct the detailed prompt.
Display a “working” sidebar so you know it’s running.
Call the Gemini API.
Parse the structured JSON response.
Loop through the identified outliers. For each one, it finds the correct employee row, highlights their salary cell with a color corresponding to the severity, and adds the AI’s detailed reasoning as a cell note.
Finally, it presents the overall summary in an alert box.

You have now successfully automated the most complex part of the analysis. Instead of manually scanning rows and making subjective judgments, you have an AI assistant that flags potential issues and provides the context you need to investigate further, directly within your spreadsheet.

From Implementation to Strategic Advantage

Building an automated script is the first major milestone. The true value, however, emerges when you integrate this tool into your strategic HR functions. Moving from a reactive, manual process to a proactive, data-driven one transforms salary benchmarking from a periodic chore into a continuous competitive advantage. This section covers how to operationalize your new system, interpret its outputs for financial planning, and scale it effectively as your organization grows.

Setting Up Triggers for Regular Automated Updates

A script that you have to run manually is only a partial automation. To make this system truly “set it and forget it,” you need to use Google Apps Script’s built-in triggers. This will ensure your salary data is refreshed on a consistent schedule without any intervention.

How to Create a Time-Driven Trigger:

Open your Google Sheet and navigate to Extensions > Apps Script.
In the Apps Script editor, click on the Triggers icon (it looks like a clock) in the left-hand sidebar.
Click the + Add Trigger button in the bottom-right corner.
Configure the trigger settings as follows:

Choose which function to run: Select the main function you created to fetch and update the benchmarks (e.g., updateAllSalaryBenchmarks).
Choose which deployment should run: Leave this as Head.
Select event source: Change this from From spreadsheet to Time-driven.
Select type of time-based trigger: For salary data, a Month timer is generally sufficient and cost-effective. A Week timer might be excessive and lead to unnecessary API calls. Choose a frequency that aligns with your review cycle.
Select day of month: Choose a day, such as 1st to 5th.
Select time of day: It’s best practice to run automations during off-peak hours, like 1am - 2am, to minimize potential performance impact.

Configure Failure Notifications: Under Failure notification settings, it’s wise to select Notify me immediately. This will send you an email if the script fails for any reason (e.g., an API outage, a change in the sheet structure), allowing you to address the issue promptly.
Click Save.

You will be asked to authorize the script permissions one more time. Once saved, your script will now automatically execute every month, populating your sheet with fresh market data and ensuring your compensation strategy is always based on current information.

Interpreting AI-Generated Insights for Budget Planning

The automated data in your sheet is more than just a list of numbers; it’s a powerful tool for strategic financial planning. By analyzing the AI-generated benchmarks, you can move from reactive salary adjustments to proactive budget modeling.

Look Beyond the Median (50th Percentile):

Your Gemini-powered prompt should be configured to return multiple data points, typically the 25th, 50th, and 75th percentiles. Here’s how to use them:

25th Percentile: This represents the lower end of the market range. It’s a useful baseline for defining the floor of your salary bands, evaluating compensation for junior or entry-level talent, or for roles in lower cost-of-living areas.
50th Percentile (Median): This is the standard market rate and the most common anchor for compensation strategies. Aligning your salaries to the median ensures you are competitive with the majority of the market.
75th Percentile: This is an aggressive, “market-leading” rate. Targeting this percentile is a strategic choice for critical, hard-to-fill roles or for organizations that want to attract and retain the absolute top tier of talent in their industry.

From Data to Decisions:

With this multi-layered data, you can now model concrete budget scenarios. Use spreadsheet formulas to compare each employee’s current salary against these new benchmarks.

Identify Compensation Gaps: Use conditional formatting to immediately highlight employees who are paid below the 25th percentile or significantly below the 50th percentile. These are your highest flight risks and may represent internal pay equity issues that need immediate attention.
Model Budget Scenarios: Answer critical questions before your budget cycle even begins. Create new columns in your sheet to calculate the cost of different strategic initiatives:
“What is the total cost to bring our entire engineering department to the market median?”
“What is the budget impact of shifting our compensation philosophy to target the 60th percentile for all senior-level roles?”
“How much investment is needed to close all identified pay gaps below the 25th percentile?”
Justify Budget Requests: When you present your budget to leadership, you are no longer relying on anecdotal evidence. You can present a clear, data-backed case: “Based on Q3 market data, our Product Management team is compensated 11% below the market median. To remain competitive and mitigate turnover risk, we are requesting a budget adjustment of $X to bring the team to market rate.”

Scaling the Solution Across Your Organization

A solution for 50 employees is different from one for 500 or 5,000. As you grow, you must evolve your simple sheet into a more robust and secure system.

1. Standardize and Centralize Your Data:

The biggest challenge in scaling is data consistency. The “Garbage In, Garbage Out” principle applies directly to AI prompts.

Canonical Job Titles: Create a “master” list of standardized job titles and levels. “Senior Software Engineer” and “Software Engineer, Sr.” must be resolved to a single, canonical title before being sent to the Gemini API. Use data validation drop-downs in your HRIS or source sheets to enforce this standard.
Master Data Sheet: Maintain a single, protected Google Sheet as the “source of truth” for all employee role data. Use IMPORTRANGE or QUERY functions to pull relevant, filtered data into departmental or managerial view-only sheets. This prevents data fragmentation and ensures your script always runs on clean, centralized information.

2. Manage API Costs and Performance with Caching:

Running API calls for thousands of roles every month can become slow and costly. Caching is the solution. The principle is simple: don’t ask for the same information twice.

Implement a Cache Sheet: Create a new tab in your spreadsheet named APICache. Structure it with columns like CacheKey (e.g., “SeniorSoftwareEngineer-NewYork-L4”), Result_P25, Result_P50, Result_P75, and LastFetchedTimestamp.
Modify Your Apps Script: Before calling the Gemini API, have your script first check the APICache sheet.
If a result exists for the given role and is recent (e.g., fetched within the last 90 days), use the cached data.
Else (if no result exists or the data is stale), call the API, write the new result to your main sheet, and also write it to the APICache sheet with a new timestamp.

This dramatically reduces the number of API calls, saving money and speeding up execution time, especially on subsequent runs.

3. Ensure Security and Access Control:

Salary data is among the most sensitive information in any organization. Use Automated Order Processing Wordpress to Gmail to Google Sheets to Jobber’s built-in security features to protect it.

Protect Ranges: The master data sheet containing all salaries and benchmarks should be heavily restricted. Use Google Sheets’ Data > Protect sheets and ranges feature to lock it down so only key HR administrators can edit it.
Principle of Least Privilege: Create separate, view-only spreadsheets for department heads or managers. Use a QUERY formula to pull in only the data relevant to their specific team. This allows them to see the market benchmarks for their direct reports without giving them access to the entire company’s compensation data. This granular control is crucial for maintaining confidentiality as you scale.

Conclusion: Your Next Step in Data-Driven HR

You’ve just bridged a significant gap between manual process and intelligent automation. By connecting the familiar interface of Google Sheets with the analytical power of Gemini, you’ve laid the foundation for a more strategic, efficient, and equitable compensation strategy. This isn’t just a technical exercise; it’s a fundamental shift in how your organization approaches one of its most critical functions. But where do you go from here?

Recap: The Power of an Automated Compensation Engine

Let’s take a moment to appreciate what’s been built. You’ve transformed a static spreadsheet from a simple record-keeping tool into a dynamic, intelligent engine.

From Tedium to Immediacy: Gone are the days of manually cross-referencing multiple salary survey tabs and websites. With a single click, you now have access to real-time, context-aware market data directly within your workflow.
From Guesswork to Governance: This system introduces a layer of objectivity and consistency to your compensation decisions. It ensures that every offer and salary review is grounded in data, helping to mitigate bias and build a defensible, transparent pay structure.
From Bottleneck to Enabler: By automating the data-gathering phase, you’ve freed up valuable HR and People Ops time. This newfound bandwidth can be reinvested into more strategic initiatives, such as career pathing, performance management, and improving the candidate experience.

The solution you’ve implemented is more than the sum of its parts. It’s a testament to the power of leveraging accessible tools to solve complex business problems, democratizing data for hiring managers and empowering your team to make faster, smarter decisions.

Ready to Scale? Your Architecture’s Next Evolution

The Google Sheets and Apps Script solution is a powerful and highly effective Minimum Viable Product (MVP). It solves the core problem with minimal overhead. However, as your organization grows and your data needs become more complex, you’ll want to consider a more robust and scalable architecture. Think of your current setup as the blueprint for a much larger structure.

Here are the logical next steps to evolve your compensation engine into an enterprise-grade internal tool:

Centralize Your Data Store: While Google Sheets is excellent for prototyping and collaboration, a dedicated database is the cornerstone of a scalable system. Migrating your compensation data to a platform like Google BigQuery or a Cloud SQL instance (e.g., PostgreSQL) will provide superior querying capabilities, data integrity, security, and the ability to handle massive datasets with ease.
Decouple the Logic with Cloud Functions: Your Apps Script code is currently tied to the spreadsheet. To scale, move this logic into a Google Cloud Function. This serverless function can be triggered via an HTTP request, allowing you to call your Gemini-powered benchmarking logic from anywhere—not just a Google Sheet. This makes your system more modular, easier to test, and capable of integrating with other applications.
Build a Dedicated User Interface (UI): As usage grows, you may want a more controlled and user-friendly experience than a raw spreadsheet. You could use a no-code/low-code platform like Google AI-Powered Invoice Processor to build a simple mobile or web app on top of your data. For more custom needs, a simple web front-end built with a framework like React or Vue can provide a polished interface for HR business partners and hiring managers.
Integrate and Visualize: With your data in BigQuery and logic in a Cloud Function, the possibilities expand. You can now:

Connect to your HRIS: Pull employee data directly from systems like Workday or BambooHR to analyze internal pay equity.
Build Dashboards: Use Looker Studio to create interactive dashboards that visualize compensation trends, budget adherence, and pay gaps across the organization, providing invaluable insights for leadership.

The path you’ve started on is an iterative one. The key is to begin with a real-world problem, solve it with accessible tools, and then thoughtfully scale your solution as your needs evolve. You now have the foundational knowledge and a working prototype to lead your organization toward a truly data-driven future.

Vo Tu Duc

A Google Developer Expert, Google Cloud Innovator

Stop Doing Manual Work. Scale with AI.

Hi, I'm Vo Tu Duc (Danny), a recognised Google Developer Expert (GDE). I architect custom AI agents and Google Workspace solutions that help businesses eliminate chaos and save thousands of hours.

Want to turn these blog concepts into production-ready reality for your team?