https://github.com/lsvekis/Apps-Script-Code-Snippets

🚀 Apps Script + Gemini Mastery — Issue #13

AI Spreadsheet Formula Generator for Google Sheets

Generate complex spreadsheet formulas from plain English using Apps Script + Gemini.

---

⭐ What You Will Build

In this issue you will build an AI Formula Assistant for Google Sheets that can:

🧠 Convert plain language requests into spreadsheet formulas
📊 Understand the selected sheet structure
📌 Suggest formulas using functions like:

• FILTER
• QUERY
• ARRAYFORMULA
• VLOOKUP
• INDEX MATCH
• SUMIFS
• COUNTIFS

⚡ Insert the formula directly into the selected cell.

---

🧠 Why This Project Matters

Many people know what they want a spreadsheet to do, but they don’t know how to write the formula.

Instead of searching Google for:

“how to sum column B if column A equals something”

You can just ask:

“Sum revenue in column B where status in column C equals Closed”

And Gemini generates the correct formula.

---

🧩 Architecture

User request
↓
Apps Script reads sheet structure
↓
Gemini generates formula
↓
Apps Script inserts formula

---

🧱 Step 1 — Create the Menu

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("Formula Generator", "showFormulaSidebar")
    .addToUi();
}function showFormulaSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Formula Generator")
  );
}

---

🧱 Step 2 — Sidebar Interface

Sidebar.html

<div style="font-family:Arial;padding:14px;">
  <h2>AI Formula Generator</h2>  <label><b>Describe the formula you want</b></label>  <textarea id="prompt"
    style="width:100%;height:80px;">
Example: Sum column B where column C = Closed
</textarea>  <button onclick="generate()">Generate Formula</button>  <pre id="output"
   style="white-space:pre-wrap;margin-top:12px;"></pre><script>function generate() {document.getElementById("output").textContent = "Generating formula..."google.script.run
.withSuccessHandler(res=>{
document.getElementById("output").textContent=res
})
.generateFormula(
document.getElementById("prompt").value
)}</script>
</div>

---

🧱 Step 3 — Read Sheet Structure

SheetReader.gs

function getSheetContext_() {const sheet = SpreadsheetApp.getActiveSheet()const headers = sheet
.getRange(1,1,1,sheet.getLastColumn())
.getValues()[0]return {
sheetName: sheet.getName(),
headers: headers
}}

---

🧱 Step 4 — Generate Formula with Gemini

FormulaGenerator.gs

function generateFormula(userRequest) {if(!userRequest) return "Enter a request."const context = getSheetContext_()const prompt = `
You are an expert Google Sheets formula generator.Sheet name:
${context.sheetName}Headers:
${context.headers.join(", ")}User request:
${userRequest}Return ONLY the formula.
No explanation.
Use correct Google Sheets syntax.
`let resulttry{result = callGemini(prompt,"")}catch(e){return "Gemini error: "+e}return result.trim()}

---

🧱 Step 5 — Insert Formula Into Cell

Add this improvement:

function insertFormula(formula){const cell = SpreadsheetApp.getActiveCell()if(!formula.startsWith("="))
formula = "=" + formulacell.setFormula(formula)return "Formula inserted."
}

You can update the sidebar to allow inserting the result.

---

🧱 Step 6 — Gemini Helper

GeminiHelpers.gs

const GEMINI_API_KEY = "YOUR_API_KEY_HERE"
const GEMINI_MODEL = "gemini-2.5-flash"function callGemini(prompt,text){const url = `https://generativelanguage.googleapis.com/v1/models/${GEMINI_MODEL}:generateContent?key=${GEMINI_API_KEY}`const payload = {
contents:[{
parts:[{
text: prompt + (text ? "\n\n"+text : "")
}]
}]
}const res = UrlFetchApp.fetch(url,{
method:"post",
contentType:"application/json",
payload:JSON.stringify(payload)
})const json = JSON.parse(res.getContentText())return json.candidates[0].content.parts[0].text}

---

🧪 Example Prompts

Users can ask:

Sum column B where column C = Closed

Count rows where status column = Active

Return rows where region = Canada

Calculate average of revenue column by salesperson

Gemini generates formulas like:

=SUMIF(C:C,"Closed",B:B)

or

=FILTER(A:D,C:C="Canada")

---

🔥 Exercise Upgrades

Advanced readers can extend this by adding:

✅ Query builder
✅ Pivot table generator
✅ Data validation assistant
✅ Conditional formatting generator
✅ Chart generator

---

🔜 Next Issue (#14)

AI Spreadsheet Chart Builder

Describe the chart you want → Apps Script generates it automatically.

Example:

“Create a bar chart of revenue by region”