New Project: AI Spreadsheet Cleaner + Fixer Issue #17

by

🚀 Apps Script + Gemini Mastery — Issue #17

AI Spreadsheet Cleaner + Fixer

Automatically clean, standardize, and fix messy spreadsheet data using Google Apps Script + Gemini.

⭐ What You Will Build

In this issue, you’ll build an AI Data Cleaner that can:

🧹 Fix inconsistent formatting
🔤 Standardize capitalization
📅 Normalize dates
📊 Clean categories and labels
🔁 Detect and correct duplicates
📝 Apply rules described in plain English

🧠 Why This Project Matters

Dirty data is one of the biggest problems in:

• reporting
• automation
• analytics
• dashboards

This tool lets users simply say:

“Clean this dataset and standardize all region names”

Instead of manually editing rows, the AI suggests or applies fixes.

🧩 Architecture

Spreadsheet data
↓
Apps Script reads values
↓
Gemini analyzes inconsistencies
↓
Apps Script applies cleaned output

🧱 Step 1 — Menu

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("AI Data Cleaner", "showCleanerSidebar")
    .addToUi();
}function showCleanerSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Data Cleaner")
  );
}

🧱 Step 2 — Sidebar UI

Sidebar.html

<div style="font-family:Arial;padding:14px;"><h2>AI Data Cleaner</h2><label><b>Describe how to clean the data</b></label><textarea id="prompt" style="width:100%;height:90px;">
Example: Standardize region names and fix capitalization
</textarea><button onclick="clean()">Clean Data</button><pre id="output" style="white-space:pre-wrap;margin-top:12px;"></pre><script>function clean(){document.getElementById("output").textContent="Cleaning data..."google.script.run
.withSuccessHandler(res=>{
document.getElementById("output").textContent=res
})
.cleanData(
document.getElementById("prompt").value
)}</script></div>

🧱 Step 3 — Read Sheet Data

SheetReader.gs

function getSheetValues_(){const sheet = SpreadsheetApp.getActiveSheet()return sheet.getDataRange().getValues()}

🧱 Step 4 — Send Data to Gemini

Cleaner.gs

function cleanData(userPrompt){if(!userPrompt) return "Enter cleaning instructions."const values = getSheetValues_()const headers = values[0]
const rows = values.slice(1)const prompt = `
You are a data cleaning assistant.Headers:
${headers.join(", ")}Rows:
${JSON.stringify(rows.slice(0,200))}Instructions:
${userPrompt}Return cleaned rows in JSON array format.
Do not explain.
`let resulttry{result = callGemini(prompt,"")}catch(e){return "Gemini error: "+e}result = result.replace(/```json/g,"").replace(/```/g,"")let cleanedtry{
cleaned = JSON.parse(result)
}catch(e){
return "Error parsing cleaned data."
}return applyCleanedData_(headers, cleaned)}

🧱 Step 5 — Apply Cleaned Data

ApplyCleaner.gs

function applyCleanedData_(headers, cleanedRows){const sheet = SpreadsheetApp.getActiveSpreadsheet()
.insertSheet("Cleaned Data")sheet.getRange(1,1,1,headers.length)
.setValues([headers])sheet.getRange(2,1,cleanedRows.length,headers.length)
.setValues(cleanedRows)return "Cleaned data written to new sheet."}

🧱 Step 6 — Gemini Helper

Reuse from previous issues:

const GEMINI_API_KEY = "YOUR_API_KEY"
const GEMINI_MODEL = "gemini-2.5-flash"

🧪 Example Prompts

Users can request:

Standardize region names and fix capitalization
Convert all dates to YYYY-MM-DD format
Fix inconsistent product categories
Remove duplicate rows

🔥 Advanced Exercises

Readers can extend this by adding:

✅ preview changes before applying
✅ column-specific cleaning rules
✅ audit log of changes
✅ validation rules
✅ smart duplicate detection

🔜 Next Issue (#18)

AI Google Docs Content Refiner

Improve, rewrite, summarize, or transform documents automatically.

Example:

“Rewrite this document to be more professional”