🚀 Apps Script + Gemini Mastery — Issue #15
https://github.com/lsvekis/Apps-Script-Code-Snippets
AI Spreadsheet Dashboard Generator
Automatically generate a complete analytics dashboard from spreadsheet data using Google Apps Script + Gemini.
Instead of manually building charts and layout, users can simply describe the dashboard they want.
Example request:
“Create a sales dashboard with revenue by region and a monthly trend chart.”
Apps Script will analyze the spreadsheet and automatically build the charts.
⭐ What You Will Build
In this issue, you’ll create an AI Dashboard Builder that can:
📊 Analyze spreadsheet headers and data
🧠 Use Gemini to design a dashboard layout
⚙️ Automatically generate charts
📈 Insert charts into a new Dashboard sheet
🧠 Why This Project Matters
Dashboards normally take time to build.
You must:
• choose chart types
• configure ranges
• arrange layout
• adjust formatting
This project lets users simply ask:
“Create a revenue dashboard”
and Apps Script builds it.
🧩 Architecture
User request
↓
Apps Script reads sheet structure
↓
Gemini designs dashboard plan
↓
Apps Script generates charts and layout
🧱 Step 1 — Add the Menu
Code.gs
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("AI Tools")
.addItem("AI Dashboard Generator", "showDashboardSidebar")
.addToUi()
}
function showDashboardSidebar(){
SpreadsheetApp.getUi().showSidebar(
HtmlService.createHtmlOutputFromFile("Sidebar")
.setTitle("AI Dashboard Builder")
)
}
🧱 Step 2 — Sidebar Interface
Sidebar.html
<div style="font-family:Arial;padding:14px;">
<h2>AI Dashboard Generator</h2>
<label><b>Describe the dashboard you want</b></label>
<textarea id="prompt"
style="width:100%;height:80px;">
Example: Create a dashboard with revenue by region and monthly sales trend
</textarea>
<button onclick="generate()">Generate Dashboard</button>
<pre id="output"
style="white-space:pre-wrap;margin-top:12px;"></pre>
<script>
function generate(){
document.getElementById("output").textContent="Building dashboard..."
google.script.run
.withSuccessHandler(res=>{
document.getElementById("output").textContent=res
})
.generateDashboard(
document.getElementById("prompt").value
)
}
</script>
</div>
🧱 Step 3 — Read Spreadsheet Context
SheetContext.gs
function getSheetContext_(){
const sheet = SpreadsheetApp.getActiveSheet()
const data = sheet.getDataRange().getValues()
return {
sheetName: sheet.getName(),
headers: data[0]
}
}
🧱 Step 4 — Ask Gemini to Design the Dashboard
DashboardPlanner.gs
function planDashboard(request){
const context = getSheetContext_()
const prompt = `
You are a data visualization expert.
Headers available:
${context.headers.join(", ")}
User request:
${request}
Return JSON like:
{
"charts":[
{"type":"bar","x":"column name","y":"column name"},
{"type":"line","x":"column name","y":"column name"}
]
}
`
let result = callGemini(prompt,"")
result = result.replace(/```json/g,"").replace(/```/g,"")
return JSON.parse(result)
}
🧱 Step 5 — Generate Dashboard Sheet
DashboardGenerator.gs
function generateDashboard(request){
if(!request) return "Enter a dashboard request."
let plan
try{
plan = planDashboard(request)
}catch(e){
return "Gemini error: "+e
}
const ss = SpreadsheetApp.getActiveSpreadsheet()
const dashboard = ss.insertSheet("AI Dashboard")
const source = ss.getActiveSheet()
const dataRange = source.getDataRange()
let row = 1
plan.charts.forEach(chart => {
const builder = dashboard.newChart()
builder.addRange(dataRange)
switch(chart.type){
case "line":
builder.setChartType(Charts.ChartType.LINE)
break
case "pie":
builder.setChartType(Charts.ChartType.PIE)
break
case "bar":
builder.setChartType(Charts.ChartType.BAR)
break
default:
builder.setChartType(Charts.ChartType.COLUMN)
}
builder.setPosition(row,1,0,0)
dashboard.insertChart(builder.build())
row += 15
})
return "Dashboard created."
}
🧱 Step 6 — Gemini Helper
Reuse the same file from previous issues:
GeminiHelpers.gs
const GEMINI_API_KEY = "YOUR_API_KEY"
const GEMINI_MODEL = "gemini-2.5-flash"
🧪 Example Prompts
Users can ask:
Create a dashboard showing revenue by region and monthly sales trend
Create a sales dashboard with product category breakdown
Create a dashboard with revenue trend and region comparison
🔥 Advanced Exercises
Readers can extend this project by adding:
✅ Chart titles
✅ Dashboard layout optimization
✅ Automatic chart sizing
✅ KPI metrics cards
✅ Dashboard refresh button