🚀 Apps Script + Gemini Mastery — Issue #28

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

AI Apps Script Dashboard Builder

Describe the dashboard you want in plain English, then use Google Apps Script + Gemini to generate the structure, KPIs, charts, filters, and starter automation logic.

This issue takes the series from individual AI helpers into a more complete business application builder.

⭐ What You Will Build

You’ll build an AI Dashboard Builder that can:

analyze a Google Sheet
understand available columns
generate KPI recommendations
choose useful chart types
suggest dashboard sections
create a new dashboard sheet
generate chart configuration
produce a Google Docs implementation plan

Example request:

“Create a sales dashboard with total revenue, average order value, sales by region, monthly trend, and product performance.”

Gemini turns that request into a structured dashboard specification that Apps Script can build.

🧠 What Readers Will Learn

This lesson teaches several useful Apps Script patterns at once:

reading spreadsheet structure
sending schema/context to Gemini
working with structured JSON responses
dynamically building dashboards
creating charts programmatically
separating AI planning from Apps Script execution
designing safer AI-generated automation

The important idea is that Gemini plans the dashboard, while Apps Script performs the actual changes.

🧩 Architecture

User request
     ↓
Read spreadsheet headers + sample data
     ↓
Gemini designs dashboard
     ↓
Structured dashboard JSON
     ↓
Apps Script creates:
  • Dashboard sheet
  • KPI cards
  • Charts
  • Titles
  • Layout
     ↓
User reviews dashboard

🧱 Step 1 — Add the Menu

`Code.gs`

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("AI Tools")
    .addItem("AI Dashboard Builder", "showDashboardSidebar")
    .addToUi();
}

function showDashboardSidebar() {
  const html = HtmlService
    .createHtmlOutputFromFile("Sidebar")
    .setTitle("AI Dashboard Builder");

  SpreadsheetApp.getUi().showSidebar(html);
}

🧱 Step 2 — Create the Sidebar

`Sidebar.html`

<!DOCTYPE html>
<html>
<head>
  <base target="_top">

  <style>
    body {
      font-family: Arial, sans-serif;
      padding: 14px;
    }

    textarea {
      width: 100%;
      height: 120px;
      box-sizing: border-box;
    }

    button {
      margin-top: 10px;
      padding: 8px 14px;
      cursor: pointer;
    }

    #output {
      margin-top: 12px;
      white-space: pre-wrap;
    }
  </style>
</head>

<body>

<h2>AI Dashboard Builder</h2>

<label>
  <b>Describe the dashboard you want</b>
</label>

<textarea id="prompt">
Create a sales dashboard with total revenue, average order value, sales by region, monthly trend, and top products.
</textarea>

<button onclick="generateDashboard()">
  Generate Dashboard
</button>

<pre id="output"></pre>

<script>

function generateDashboard() {

  document.getElementById("output").textContent =
    "Analyzing spreadsheet and designing dashboard...";

  google.script.run
    .withSuccessHandler(function(result) {

      document.getElementById("output").textContent =
        result;

    })
    .withFailureHandler(function(error) {

      document.getElementById("output").textContent =
        "Error: " + error.message;

    })
    .generateDashboard(
      document.getElementById("prompt").value
    );
}

</script>

</body>
</html>

🧱 Step 3 — Read the Spreadsheet

Gemini needs some understanding of the available data.

`SheetReader.gs`

function getSheetContext_() {

  const sheet = SpreadsheetApp.getActiveSheet();

  const range = sheet.getDataRange();

  const values = range.getValues();

  if (!values.length) {
    throw new Error("The active sheet contains no data.");
  }

  const headers = values[0];

  const rows = values.slice(1, 21);

  return {
    sheetName: sheet.getName(),
    headers: headers,
    sampleRows: rows,
    totalRows: values.length - 1
  };
}

Notice that we’re only sending a sample of the data.

That’s usually enough for Gemini to understand the schema without sending an entire large spreadsheet.

🧱 Step 4 — Ask Gemini to Design the Dashboard

`DashboardPlanner.gs`

function createDashboardPlan_(userRequest) {

  const context = getSheetContext_();

  const prompt = `
You are an expert Google Sheets dashboard designer.

Design a dashboard using the spreadsheet information below.

Sheet:
${context.sheetName}

Columns:
${context.headers.join(", ")}

Sample rows:
${JSON.stringify(context.sampleRows)}

Total data rows:
${context.totalRows}

User request:
${userRequest}

Return JSON ONLY.

Use this structure:

{
  "title": "Dashboard title",

  "kpis": [
    {
      "label": "Total Revenue",
      "column": "Revenue",
      "aggregation": "SUM"
    }
  ],

  "charts": [
    {
      "title": "Revenue by Region",
      "type": "BAR",
      "categoryColumn": "Region",
      "valueColumn": "Revenue",
      "aggregation": "SUM"
    }
  ],

  "recommendations": [
    "Optional dashboard recommendation"
  ]
}

Supported chart types:

BAR
COLUMN
LINE
PIE

Supported aggregations:

SUM
AVERAGE
COUNT
MAX
MIN

Only reference columns that exist in the provided spreadsheet.

Return valid JSON only.
`;

  let result = callGemini(prompt, "");

  result = result
    .replace(/```json/gi, "")
    .replace(/```/g, "")
    .trim();

  return JSON.parse(result);
}

🧱 Step 5 — Main Dashboard Function

`DashboardBuilder.gs`

function generateDashboard(userRequest) {

  if (!userRequest) {
    return "Enter a dashboard request.";
  }

  let plan;

  try {

    plan = createDashboardPlan_(userRequest);

  } catch (error) {

    Logger.log(error);

    return "Could not generate dashboard plan: " +
      error.message;
  }

  try {

    buildDashboard_(plan);

  } catch (error) {

    Logger.log(error);

    return "Dashboard build error: " +
      error.message;
  }

  return "Dashboard created successfully.";
}

🧱 Step 6 — Create the Dashboard Sheet

function buildDashboard_(plan) {

  const ss =
    SpreadsheetApp.getActiveSpreadsheet();

  let dashboard =
    ss.getSheetByName("AI Dashboard");

  if (dashboard) {

    dashboard.clear();

    dashboard
      .getCharts()
      .forEach(function(chart) {

        dashboard.removeChart(chart);

      });

  } else {

    dashboard =
      ss.insertSheet("AI Dashboard");
  }

  dashboard
    .getRange("A1")
    .setValue(
      plan.title || "AI Dashboard"
    );

  dashboard
    .getRange("A1")
    .setFontSize(20)
    .setFontWeight("bold");

  createKpis_(dashboard, plan.kpis || []);

  createCharts_(
    dashboard,
    plan.charts || []
  );
}

🧱 Step 7 — Generate KPI Cards

`KpiBuilder.gs`

function createKpis_(dashboard, kpis) {

  const source =
    SpreadsheetApp.getActiveSheet();

  const headers =
    source
      .getRange(
        1,
        1,
        1,
        source.getLastColumn()
      )
      .getValues()[0];

  let column = 1;

  kpis.forEach(function(kpi) {

    const index =
      headers.indexOf(kpi.column);

    if (index === -1) {
      return;
    }

    const columnLetter =
      columnToLetter_(index + 1);

    let formula = "";

    switch (
      String(kpi.aggregation)
        .toUpperCase()
    ) {

      case "AVERAGE":

        formula =
          `=AVERAGE('${source.getName()}'!${columnLetter}2:${columnLetter})`;

        break;

      case "COUNT":

        formula =
          `=COUNTA('${source.getName()}'!${columnLetter}2:${columnLetter})`;

        break;

      case "MAX":

        formula =
          `=MAX('${source.getName()}'!${columnLetter}2:${columnLetter})`;

        break;

      case "MIN":

        formula =
          `=MIN('${source.getName()}'!${columnLetter}2:${columnLetter})`;

        break;

      default:

        formula =
          `=SUM('${source.getName()}'!${columnLetter}2:${columnLetter})`;
    }

    dashboard
      .getRange(3, column)
      .setValue(kpi.label)
      .setFontWeight("bold");

    dashboard
      .getRange(4, column)
      .setFormula(formula)
      .setFontSize(16);

    column += 2;
  });
}

🧱 Step 8 — Column Letter Helper

function columnToLetter_(column) {

  let letter = "";

  while (column > 0) {

    const temp =
      (column - 1) % 26;

    letter =
      String.fromCharCode(
        temp + 65
      ) + letter;

    column =
      (column - temp - 1) / 26;
  }

  return letter;
}

🧱 Step 9 — Create Chart Data Tables

Google Sheets charts work best when Apps Script creates a small summarized data range first.

We’ll create these on a hidden helper sheet.

`ChartDataBuilder.gs`

function getDashboardDataSheet_() {

  const ss =
    SpreadsheetApp.getActiveSpreadsheet();

  let sheet =
    ss.getSheetByName(
      "_AI_Dashboard_Data"
    );

  if (!sheet) {

    sheet =
      ss.insertSheet(
        "_AI_Dashboard_Data"
      );

  }

  sheet.clear();

  sheet.hideSheet();

  return sheet;
}

🧱 Step 10 — Build Chart Summary Data

function buildChartData_(
  config,
  startColumn
) {

  const source =
    SpreadsheetApp.getActiveSheet();

  const dataSheet =
    getDashboardDataSheet_();

  const data =
    source
      .getDataRange()
      .getValues();

  const headers =
    data[0];

  const categoryIndex =
    headers.indexOf(
      config.categoryColumn
    );

  const valueIndex =
    headers.indexOf(
      config.valueColumn
    );

  if (
    categoryIndex === -1 ||
    valueIndex === -1
  ) {

    throw new Error(
      "Dashboard chart references invalid columns."
    );
  }

  const groups = {};

  data
    .slice(1)
    .forEach(function(row) {

      const category =
        row[categoryIndex];

      const value =
        Number(row[valueIndex]) || 0;

      if (!groups[category]) {

        groups[category] = {
          values: [],
          total: 0
        };
      }

      groups[category]
        .values
        .push(value);

      groups[category]
        .total += value;
    });

  const output = [
    [
      config.categoryColumn,
      config.valueColumn
    ]
  ];

  Object.keys(groups)
    .forEach(function(category) {

      const group =
        groups[category];

      let value;

      switch (
        String(config.aggregation)
          .toUpperCase()
      ) {

        case "AVERAGE":

          value =
            group.total /
            group.values.length;

          break;

        case "COUNT":

          value =
            group.values.length;

          break;

        case "MAX":

          value =
            Math.max.apply(
              null,
              group.values
            );

          break;

        case "MIN":

          value =
            Math.min.apply(
              null,
              group.values
            );

          break;

        default:

          value =
            group.total;
      }

      output.push([
        category,
        value
      ]);
    });

  const range =
    dataSheet.getRange(
      1,
      startColumn,
      output.length,
      2
    );

  range.setValues(output);

  return range;
}

🧱 Step 11 — Generate Charts

`ChartBuilder.gs`

function createCharts_(
  dashboard,
  charts
) {

  let chartRow = 7;

  let helperColumn = 1;

  charts.forEach(function(config) {

    const range =
      buildChartData_(
        config,
        helperColumn
      );

    const builder =
      dashboard
        .newChart()
        .addRange(range)
        .setOption(
          "title",
          config.title || ""
        )
        .setPosition(
          chartRow,
          1,
          0,
          0
        );

    switch (
      String(config.type)
        .toUpperCase()
    ) {

      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
        );
    }

    dashboard.insertChart(
      builder.build()
    );

    helperColumn += 3;

    chartRow += 18;
  });
}

🧱 Step 12 — Gemini Helper

Use the same helper we’ve been using throughout the series.

`GeminiHelpers.gs`

const GEMINI_API_KEY =
  "YOUR_API_KEY_HERE";

const GEMINI_MODEL =
  "gemini-2.5-flash";

function callGemini(
  prompt,
  text
) {

  if (
    !GEMINI_API_KEY ||
    GEMINI_API_KEY ===
      "YOUR_API_KEY_HERE"
  ) {

    throw new Error(
      "Set your Gemini API key."
    );
  }

  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 response =
    UrlFetchApp.fetch(
      url,
      {

        method: "post",

        contentType:
          "application/json",

        payload:
          JSON.stringify(
            payload
          ),

        muteHttpExceptions:
          true

      }
    );

  const json =
    JSON.parse(
      response.getContentText()
    );

  if (json.error) {

    throw new Error(
      json.error.message
    );
  }

  return (
    json
      .candidates[0]
      .content.parts[0]
      .text
  );
}

🧪 Sample Spreadsheet

Suppose your source sheet contains:

Date	Region	Product	Revenue	Orders
Jan 3	East	Widget A	4200	20
Jan 6	West	Widget B	5700	28
Feb 2	East	Widget B	6800	31
Feb 8	North	Widget A	3900	18
Mar 1	West	Widget C	8100	36

🧪 Example Prompt

Create a sales dashboard.

Show:

total revenue

total orders

average revenue

revenue by region

revenue by product

monthly revenue trend

Gemini could return:

{
  "title": "Sales Performance Dashboard",

  "kpis": [
    {
      "label": "Total Revenue",
      "column": "Revenue",
      "aggregation": "SUM"
    },
    {
      "label": "Total Orders",
      "column": "Orders",
      "aggregation": "SUM"
    },
    {
      "label": "Average Revenue",
      "column": "Revenue",
      "aggregation": "AVERAGE"
    }
  ],

  "charts": [
    {
      "title": "Revenue by Region",
      "type": "BAR",
      "categoryColumn": "Region",
      "valueColumn": "Revenue",
      "aggregation": "SUM"
    },
    {
      "title": "Revenue by Product",
      "type": "COLUMN",
      "categoryColumn": "Product",
      "valueColumn": "Revenue",
      "aggregation": "SUM"
    },
    {
      "title": "Monthly Revenue",
      "type": "LINE",
      "categoryColumn": "Date",
      "valueColumn": "Revenue",
      "aggregation": "SUM"
    }
  ]
}

Apps Script then converts that plan into a real Google Sheets dashboard.

🔥 Exercise: Add a Preview Mode

A very useful improvement is separating:

Generate Plan

from:

Build Dashboard

Instead of immediately changing the spreadsheet, first show Gemini’s proposed dashboard.

Users can review:

KPIs
chart choices
columns
calculations

Then click:

Build Dashboard

This is a much safer design for AI-powered automation.

🔥 Advanced Challenges

Extend the project with:

✅ dashboard preview mode
✅ dynamic date filters
✅ dropdown filters
✅ slicers
✅ year-over-year comparisons
✅ automatic formatting
✅ conditional KPI indicators
✅ dashboard refresh button
✅ export dashboard summary to Google Docs
✅ Gemini-generated narrative insights

One particularly useful extension is:

Explain this dashboard

Gemini reads the underlying data and generates a written executive summary underneath the charts.

🧠 Key Lesson: AI Plans, Apps Script Executes

This is an important design pattern for integrating AI into Google Workspace.

Instead of giving AI direct control of the spreadsheet:

Gemini returns structured instructions.

Apps Script validates those instructions and performs the actual operations.

That gives you much more control over:

data integrity
permissions
predictable execution
error handling
user review

It’s a pattern you can reuse across almost every AI + Apps Script application.

🔜 Issue #29

AI Apps Script Debugging Assistant

Paste an error message such as:

TypeError: Cannot read properties of undefined

along with your Apps Script code.

The assistant will analyze:

🐞 probable cause
📍 likely problem location
🔧 suggested fix
🧪 tests to verify it
⚠️ related edge cases
✨ corrected Apps Script code

It will essentially become an AI debugging console specifically for Google Apps Script.

Apps Script and Gemini – AI Dashboard Builder

🚀 Apps Script + Gemini Mastery — Issue #28

AI Apps Script Dashboard Builder

⭐ What You Will Build

🧠 What Readers Will Learn

🧩 Architecture

🧱 Step 1 — Add the Menu

`Code.gs`

🧱 Step 2 — Create the Sidebar

`Sidebar.html`

🧱 Step 3 — Read the Spreadsheet

`SheetReader.gs`

🧱 Step 4 — Ask Gemini to Design the Dashboard

`DashboardPlanner.gs`

🧱 Step 5 — Main Dashboard Function

`DashboardBuilder.gs`

🧱 Step 6 — Create the Dashboard Sheet

🧱 Step 7 — Generate KPI Cards

`KpiBuilder.gs`

🧱 Step 8 — Column Letter Helper

🧱 Step 9 — Create Chart Data Tables

`ChartDataBuilder.gs`

🧱 Step 10 — Build Chart Summary Data

🧱 Step 11 — Generate Charts

`ChartBuilder.gs`

🧱 Step 12 — Gemini Helper

`GeminiHelpers.gs`

🧪 Sample Spreadsheet

🧪 Example Prompt

🔥 Exercise: Add a Preview Mode

🔥 Advanced Challenges

🧠 Key Lesson: AI Plans, Apps Script Executes

🔜 Issue #29

AI Apps Script Debugging Assistant

Related

🚀 Apps Script + Gemini Mastery — Issue #28

AI Apps Script Dashboard Builder

⭐ What You Will Build

🧠 What Readers Will Learn

🧩 Architecture

🧱 Step 1 — Add the Menu

Code.gs

🧱 Step 2 — Create the Sidebar

Sidebar.html

🧱 Step 3 — Read the Spreadsheet

SheetReader.gs

🧱 Step 4 — Ask Gemini to Design the Dashboard

DashboardPlanner.gs

🧱 Step 5 — Main Dashboard Function

DashboardBuilder.gs

🧱 Step 6 — Create the Dashboard Sheet

🧱 Step 7 — Generate KPI Cards

KpiBuilder.gs

🧱 Step 8 — Column Letter Helper

🧱 Step 9 — Create Chart Data Tables

ChartDataBuilder.gs

🧱 Step 10 — Build Chart Summary Data

🧱 Step 11 — Generate Charts

ChartBuilder.gs

🧱 Step 12 — Gemini Helper

GeminiHelpers.gs

🧪 Sample Spreadsheet

🧪 Example Prompt

🔥 Exercise: Add a Preview Mode

🔥 Advanced Challenges

🧠 Key Lesson: AI Plans, Apps Script Executes

🔜 Issue #29

AI Apps Script Debugging Assistant

Share this:

Related

`Code.gs`

`Sidebar.html`

`SheetReader.gs`

`DashboardPlanner.gs`

`DashboardBuilder.gs`

`KpiBuilder.gs`

`ChartDataBuilder.gs`

`ChartBuilder.gs`

`GeminiHelpers.gs`