AI Chart Builder for Google Sheets — Issue #14

by

AI Chart Builder for Google Sheets

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

Automatically generate charts from natural language using Google Apps Script + Gemini.

Instead of manually configuring charts, users can simply describe the visualization they want.

Example:

“Create a bar chart of revenue by region”

Apps Script will analyze the sheet and build the chart automatically.

⭐ What You Will Build

In this issue, you’ll build an AI Chart Generator that can:

📊 Analyze spreadsheet data
🧠 Use Gemini to determine the correct chart type
⚙️ Automatically create charts such as:

  • Bar charts
  • Column charts
  • Pie charts
  • Line charts
  • Area charts

⚡ Insert the chart directly into the sheet.

🧠 Why This Project Matters

Many users struggle with:

  • Choosing the correct chart type
  • Configuring chart settings
  • Understanding which columns to visualize

This tool allows users to simply ask:

“Create a pie chart showing sales by product category”

And the chart is generated automatically.

🧩 Architecture

User request
↓
Apps Script reads sheet structure
↓
Gemini determines visualization
↓
Apps Script generates the chart

🧱 Step 1 — Add the Menu

Code.gs

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("AI Chart Builder", "showChartSidebar")
    .addToUi();
}function showChartSidebar() {
  SpreadsheetApp.getUi().showSidebar(
    HtmlService.createHtmlOutputFromFile("Sidebar")
      .setTitle("AI Chart Builder")
  );
}

🧱 Step 2 — Sidebar UI

Sidebar.html

<div style="font-family:Arial;padding:14px;"><h2>AI Chart Builder</h2><label><b>Describe the chart you want</b></label><textarea id="prompt"
style="width:100%;height:80px;">
Example: Create a bar chart showing revenue by region
</textarea><button onclick="generate()">Create Chart</button><pre id="output"
style="white-space:pre-wrap;margin-top:12px;"></pre><script>function generate(){document.getElementById("output").textContent="Creating chart..."google.script.run
.withSuccessHandler(res=>{
document.getElementById("output").textContent=res
})
.generateChart(
document.getElementById("prompt").value
)}</script></div>

🧱 Step 3 — Read Sheet Structure

SheetContext.gs

function getSheetData_(){const sheet = SpreadsheetApp.getActiveSheet()const data = sheet.getDataRange().getValues()const headers = data[0]return {
sheetName: sheet.getName(),
headers: headers
}}

🧱 Step 4 — Ask Gemini Which Chart to Create

ChartPlanner.gs

function planChart(request){const context = getSheetData_()const prompt = `
You are an expert data visualization assistant.Headers available:
${context.headers.join(", ")}User request:
${request}Return JSON in this format:{
"type":"bar|column|pie|line",
"xColumn":"column name",
"yColumn":"column name"
}
`let result = callGemini(prompt,"")result = result.replace(/```json/g,"").replace(/```/g,"")return JSON.parse(result)}

🧱 Step 5 — Build the Chart

ChartGenerator.gs

function generateChart(request){if(!request) return "Enter a chart request."let plantry{plan = planChart(request)}catch(e){return "Gemini error: "+e}const sheet = SpreadsheetApp.getActiveSheet()const dataRange = sheet.getDataRange()const builder = sheet.newChart()builder.addRange(dataRange)switch(plan.type){case "pie":
builder.setChartType(Charts.ChartType.PIE)
breakcase "line":
builder.setChartType(Charts.ChartType.LINE)
breakcase "bar":
builder.setChartType(Charts.ChartType.BAR)
breakdefault:
builder.setChartType(Charts.ChartType.COLUMN)}builder.setPosition(5,5,0,0)sheet.insertChart(builder.build())return "Chart created."}

🧱 Step 6 — Gemini Helper

Reuse the same GeminiHelpers.gs from previous issues.

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

🧪 Example Prompts

Users can type:

Create a pie chart of sales by category
Create a line chart of revenue over time
Show a bar chart of sales by region
Create a column chart comparing monthly revenue

🔥 Advanced Exercises

Readers can extend the project by adding:

✅ Automatic column detection
✅ Chart title generation
✅ Multiple chart series
✅ Dashboard creation
✅ Automatic chart styling