How to Use Gemini in Google Sheets to Write Formulas, Clean Data, and Automate Rows

Fixing messy data exports and looking up nested formula syntax can eat hours out of your week. Using Gemini directly inside Google Sheets offloads the formula syntax and data cleanup to AI, leaving you to review the output. Connecting the Gemini side panel, cell-level AI functions, and Google Apps Script turns your spreadsheets into automated, self-cleaning data pipelines.

What You'll Need

• Plan level: Google Workspace account with Gemini add-on (Business, Enterprise, or Education) or Gemini Business/Enterprise license

• Sample file: A CSV export or Google Sheet with inconsistent formatting or text data

• Time required: 20 minutes

Step-by-Step Walkthrough

Step 1: Open the Gemini Side Panel and Give Context

Open your Google Sheet, locate the top toolbar or right sidebar, and click Ask Gemini (sparkle icon). Alternatively, press ⌘ + Ctrl + G on Mac or Ctrl + Alt + G on Windows.

Why it matters: Opening the side panel establishes conversational context with Gemini across active tabs in your spreadsheet.

Step 2: Write Formulas From Plain-English Requests

Type your request into the Gemini side panel using exact column headers or cell ranges (e.g., "Write a formula to sum column C when column B is 'Closed Won'"). Click Insert to place the generated formula directly into your selected cell.

Why it matters: Requesting formulas through plain text eliminates syntax errors in complex functions like INDEX/MATCH, SUMIFS, or ADVANCEFILTER.

• Example Prompt: "Write a formula that checks cell A2. If it contains 'US' or 'Canada', return 'North America', otherwise return 'International'."

• Generated Output: =IF(OR(ISNUMBER(SEARCH("US", A2)), ISNUMBER(SEARCH("Canada", A2))), "North America", "International")

Step 3: Clean Messy Data Across Columns

Ask Gemini in the side panel to standardize telephone formats, trim whitespace, or split combined text fields. For structural changes, Gemini generates an Action Preview Card showing conditional formatting or column modifications before applying them.

Why it matters: Previewing automated structural edits prevents accidental overwriting of raw data columns.

Step 4: Process Row-by-Row Data With =AI()

Type =AI() or =Gemini() directly into any spreadsheet cell to process row text, classify customer feedback, or extract key phrases.

Why it matters: Cell-level AI functions make qualitative text data quantitative without writing complex regex scripts.

• Syntax: =AI("your instruction prompt", reference_cell_or_range)

• Copyable Categorization Formula: =AI("Categorize this feedback into Pricing, Usability, or Feature Request. Return only the category name.", A2)

• Generation Batch Limits: You can select and generate up to 350 =AI() cells per batch pass.

Step 5: Automate Weekly Tasks With Gemini-Written Apps Script

Open Extensions > Apps Script in Google Sheets. Open the Gemini side panel inside the script editor (or ask Gemini in the main Sheet) to write a custom Apps Script function for recurring tasks.

Why it matters: Converting manual cleaning steps into an Apps Script allows one-click execution every time new data lands in your sheet.

Copyable Apps Script Prompt for Gemini:

"Write a Google Apps Script that scans column A for duplicate email addresses, removes the entire duplicate row, and converts all remaining text in column B to lower case. Include inline comments."

JavaScript

function cleanLeadData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var seenEmails = {};
  var rowsToKeep = [];
  
  // Keep header row
  rowsToKeep.push(data[0]);
  
  for (var i = 1; i < data.length; i++) {
    var email = data[i][0].toString().toLowerCase().trim();
    if (!seenEmails[email] && email !== "") {
      seenEmails[email] = true;
      // Convert Column B (index 1) to lowercase
      data[i][1] = data[i][1].toString().toLowerCase();
      rowsToKeep.push(data[i]);
    }
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
}

function cleanLeadData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var seenEmails = {};
  var rowsToKeep = [];
  
  // Keep header row
  rowsToKeep.push(data[0]);
  
  for (var i = 1; i < data.length; i++) {
    var email = data[i][0].toString().toLowerCase().trim();
    if (!seenEmails[email] && email !== "") {
      seenEmails[email] = true;
      // Convert Column B (index 1) to lowercase
      data[i][1] = data[i][1].toString().toLowerCase();
      rowsToKeep.push(data[i]);
    }
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
}

function cleanLeadData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var seenEmails = {};
  var rowsToKeep = [];
  
  // Keep header row
  rowsToKeep.push(data[0]);
  
  for (var i = 1; i < data.length; i++) {
    var email = data[i][0].toString().toLowerCase().trim();
    if (!seenEmails[email] && email !== "") {
      seenEmails[email] = true;
      // Convert Column B (index 1) to lowercase
      data[i][1] = data[i][1].toString().toLowerCase();
      rowsToKeep.push(data[i]);
    }
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
}

Step 6: Verify Results and Freeze Output

Audit generated formulas and AI cell outputs on five to ten sample rows before applying them across thousands of records. To stop =AI() functions from recalculating every time you open the sheet, select the generated range, copy it (Ctrl + C / ⌘ + C), and paste as values (Ctrl + Shift + V / ⌘ + Shift + V).

Why it matters: Pasting values freezes the generated text, saving API quotas and preventing accidental recalculation costs.

Real-World Example: Sales Ops Lead Export Cleanup

A sales operations analyst at The Alliance cleans a 2,000-row lead export containing messy company names, mixed phone formats, and raw notes.

1. Standardizing Company Names

• Cell Function Used: =AI("Clean and standardize this company name. Remove suffixes like Inc, LLC, or Corp. Capitalize properly.", A2)

• Result: Converts "acme corp incorporated" into "Acme".

2. Categorizing Industry

• Cell Function Used: =AI("Classify this company description into one of these industries: Healthcare, Finance, Tech, Manufacturing. If unclear, return 'Other'.", C2)

• Result: Tags 2,000 raw descriptions into four clean, chartable categories.

3. Error Caught During Spot-Check

During the spot-check on row 14, the analyst noticed that =AI() classified a "Healthcare Software" company as "Healthcare" instead of "Tech".

• The Fix: Updated prompt to include an explicit example: "Classify into Healthcare, Finance, Tech, Manufacturing. Example: 'Healthcare Software' = Tech."

4. Freezing and Automating

The analyst copied the AI output columns, pasted them as values, and asked Gemini to generate an Apps Script to automate the duplicate removal process every Monday.

Common Mistakes to Avoid

1. Accepting Formulas Without Sample Testing: Inserting generated complex array formulas across an entire sheet without verifying two or three rows by hand risks propagating math errors.

2. Exceeding Batch Limits on =AI() Functions: Running =AI() on more than 350 rows in a single selection exceeds single-pass limits, causing delayed or incomplete cell generations.

3. Cleaning Original Raw Data: Running bulk AI cleanup or scripts directly on your master data tab without duplicating the sheet first risks permanently overwriting source information.

Take It Further

Create a dedicated Data Standards Gem inside Gemini. Load your company's official naming conventions, country codes, and industry definitions into the Gem's knowledge base. Whenever you need a cleanup formula or Apps Script, ask your Gem to write instructions tailored specifically to your corporate data rules.

Combining Gemini side panel prompts, =AI() cell functions, and custom Apps Scripts transforms Google Sheets from a manual ledger into an automated data hub. Duplicate your current messy dataset and run your first cleanup formula today.