AI Spreadsheet Formula Generator for Google Sheets Issue 13

by

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”