bpwebs/#Create Google Apps Script Dashboard using AI

## #Create Google Apps Script Dashboard using AI
How to Create a Google Sheets Dashboard with AI
bpwebs.com

## Code.gs
function doGet() {
  return HtmlService.createTemplateFromFile('Index')
      .evaluate()
      .setTitle('Sales Analytics Dashboard')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

/**
 * Fetches data from the active sheet and normalizes it.
 * This function assumes the CSV has been imported into the active sheet.
 */
function getDashboardData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data.shift(); // Remove header row

  // Map column indexes based on your CSV structure
  // Transaction_ID(0), Date(1), Region(2), Category(3), SKU(4), Qty(5), Price(6), Discount(7), Rating(8), Member(9), Total(10)

  const normalizedData = data.map(row => {
    // Normalization Logic
    let dateObj = new Date(row[1]);
    let dateStr = isNaN(dateObj.getTime()) ? null : dateObj.toISOString(); // Handle invalid dates

    return {
      date: dateStr,
      region: String(row[2]),
      category: String(row[3]),
      isMember: String(row[9]).trim().toLowerCase() === 'yes', // Normalize boolean
      sales: Number(row[10]) || 0 // Normalize number, default to 0 if empty
    };
  });

  return JSON.stringify(normalizedData);
}

## css.html
<style>
  body {
    background-color: #f8f9fa;
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
  }

  .dashboard-container {
    max-width: 1400px;
    margin: 0 auto;
    padding-bottom: 50px;
  }

  .kpi-card {
    transition: transform 0.2s;
    box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
  }

  .kpi-card:hover {
    transform: translateY(-5px);
    box-shadow: 0 0.5rem 1rem rgba(0, 0, 0, 0.15);
  }

  .chart-container {
    width: 100%;
    min-height: 300px;
  }

  .card-header {
    border-bottom: 1px solid #eee;
    color: #555;
  }
</style>

## Index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <!-- Bootstrap 5 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <?!= include('css'); ?>
  </head>
  <body>

    <!-- Navbar -->
    <nav class="navbar navbar-dark bg-primary mb-4 shadow-sm">
      <div class="container-fluid">
        <span class="navbar-brand mb-0 h1">📊 Sales Analytics Dashboard</span>
        <button class="btn btn-light btn-sm" onclick="loadDashboardData()">Refresh Data</button>
      </div>
    </nav>

    <div class="container-fluid dashboard-container">

      <!-- Loading Spinner -->
      <div id="loader" class="text-center mt-5">
        <div class="spinner-border text-primary" role="status">
          <span class="visually-hidden">Loading...</span>
        </div>
        <p class="mt-2 text-muted">Processing datasets...</p>
      </div>

      <!-- Dashboard Content (Hidden initially) -->
      <div id="dashboard-content" style="display:none;">

        <!-- KPI Cards Row -->
        <div class="row g-3 mb-4">
          <div class="col-md-3">
            <div class="card kpi-card border-start border-4 border-primary">
              <div class="card-body">
                <h6 class="card-subtitle mb-2 text-muted">Total Revenue</h6>
                <h3 class="card-title" id="kpi-revenue">$0</h3>
              </div>
            </div>
          </div>
          <div class="col-md-3">
            <div class="card kpi-card border-start border-4 border-success">
              <div class="card-body">
                <h6 class="card-subtitle mb-2 text-muted">Total Transactions</h6>
                <h3 class="card-title" id="kpi-transactions">0</h3>
              </div>
            </div>
          </div>
          <div class="col-md-3">
            <div class="card kpi-card border-start border-4 border-info">
              <div class="card-body">
                <h6 class="card-subtitle mb-2 text-muted">Avg Order Value</h6>
                <h3 class="card-title" id="kpi-aov">$0</h3>
              </div>
            </div>
          </div>
          <div class="col-md-3">
            <div class="card kpi-card border-start border-4 border-warning">
              <div class="card-body">
                <h6 class="card-subtitle mb-2 text-muted">Member Sales %</h6>
                <h3 class="card-title" id="kpi-member-rate">0%</h3>
              </div>
            </div>
          </div>
        </div>

        <!-- Charts Row 1 -->
        <div class="row g-3 mb-4">
          <div class="col-md-6">
            <div class="card shadow-sm h-100">
              <div class="card-header bg-white fw-bold">Sales by Region</div>
              <div class="card-body">
                <div id="chart_region" class="chart-container"></div>
              </div>
            </div>
          </div>
          <div class="col-md-6">
            <div class="card shadow-sm h-100">
              <div class="card-header bg-white fw-bold">Sales by Category</div>
              <div class="card-body">
                <div id="chart_category" class="chart-container"></div>
              </div>
            </div>
          </div>
        </div>

        <!-- Charts Row 2 -->
        <div class="row g-3">
          <div class="col-md-8">
            <div class="card shadow-sm h-100">
              <div class="card-header bg-white fw-bold">Monthly Sales Trend</div>
              <div class="card-body">
                <div id="chart_trend" class="chart-container"></div>
              </div>
            </div>
          </div>
          <div class="col-md-4">
            <div class="card shadow-sm h-100">
              <div class="card-header bg-white fw-bold">Member vs Non-Member</div>
              <div class="card-body">
                <div id="chart_member" class="chart-container"></div>
              </div>
            </div>
          </div>
        </div>

      </div> <!-- End Dashboard Content -->
    </div>

    <!-- Google Charts Loader -->
    <script src="https://www.gstatic.com/charts/loader.js"></script>
    <?!= include('JavaScript'); ?>
  </body>
</html>

## JavaScript.html
<script>
  // Global variable to store raw data
  let rawData = [];

  // Load Google Charts
  google.charts.load('current', {'packages':['corechart']});
  google.charts.setOnLoadCallback(loadDashboardData);

  function loadDashboardData() {
    document.getElementById('loader').style.display = 'block';
    document.getElementById('dashboard-content').style.display = 'none';

    google.script.run
      .withSuccessHandler(processData)
      .withFailureHandler(error => alert("Error: " + error))
      .getDashboardData();
  }

  function processData(jsonString) {
    rawData = JSON.parse(jsonString);

    // Calculate KPIs
    calculateKPIs(rawData);

    // Draw Charts
    drawRegionChart(rawData);
    drawCategoryChart(rawData);
    drawTrendChart(rawData);
    drawMemberChart(rawData);

    // Show UI
    document.getElementById('loader').style.display = 'none';
    document.getElementById('dashboard-content').style.display = 'block';
  }

  // --- KPI Calculation ---
  function calculateKPIs(data) {
    const totalRevenue = data.reduce((sum, item) => sum + item.sales, 0);
    const totalTransactions = data.length;
    const avgOrderValue = totalTransactions ? totalRevenue / totalTransactions : 0;

    const memberSales = data.reduce((sum, item) => item.isMember ? sum + item.sales : sum, 0);
    const memberRate = totalRevenue ? (memberSales / totalRevenue) * 100 : 0;

    // Format Currency
    const fmt = new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' });

    document.getElementById('kpi-revenue').innerText = fmt.format(totalRevenue);
    document.getElementById('kpi-transactions').innerText = totalTransactions.toLocaleString();
    document.getElementById('kpi-aov').innerText = fmt.format(avgOrderValue);
    document.getElementById('kpi-member-rate').innerText = memberRate.toFixed(1) + "%";
  }

  // --- Helper: Group Data ---
  function groupData(data, keyFn, valueFn) {
    const groups = {};
    data.forEach(item => {
      const key = keyFn(item);
      const val = valueFn(item);
      groups[key] = (groups[key] || 0) + val;
    });
    return Object.entries(groups).sort((a,b) => b[1] - a[1]); // Sort Descending by value
  }

  // --- Chart 1: Sales by Region (Column) ---
  function drawRegionChart(data) {
    const grouped = groupData(data, item => item.region, item => item.sales);
    const chartData = [['Region', 'Sales', { role: 'style' }]];

    // Add colors dynamically
    const colors = ['#4285F4', '#DB4437', '#F4B400', '#0F9D58'];
    grouped.forEach((item, index) => {
      chartData.push([item[0], item[1], colors[index % colors.length]]);
    });

    const dataTable = google.visualization.arrayToDataTable(chartData);
    const options = {
      legend: { position: "none" },
      chartArea: { width: '80%', height: '70%' },
      vAxis: { format: 'currency' }
    };

    const chart = new google.visualization.ColumnChart(document.getElementById('chart_region'));
    chart.draw(dataTable, options);
  }

  // --- Chart 2: Sales by Category (Pie/Donut) ---
  function drawCategoryChart(data) {
    const grouped = groupData(data, item => item.category, item => item.sales);
    const chartData = [['Category', 'Sales']];
    grouped.forEach(item => chartData.push(item));

    const dataTable = google.visualization.arrayToDataTable(chartData);
    const options = {
      pieHole: 0.4,
      chartArea: { width: '90%', height: '80%' },
      legend: { position: 'right' }
    };

    const chart = new google.visualization.PieChart(document.getElementById('chart_category'));
    chart.draw(dataTable, options);
  }

  // --- Chart 3: Monthly Trend (Line) ---
  function drawTrendChart(data) {
    // Group by YYYY-MM
    const grouped = {};
    data.forEach(item => {
      if(!item.date) return;
      const d = new Date(item.date);
      const key = d.getFullYear() + "-" + String(d.getMonth() + 1).padStart(2, '0'); // YYYY-MM
      grouped[key] = (grouped[key] || 0) + item.sales;
    });

    // Sort chronologically
    const sortedKeys = Object.keys(grouped).sort();
    const chartData = [['Month', 'Revenue']];
    sortedKeys.forEach(key => {
      chartData.push([key, grouped[key]]);
    });

    const dataTable = google.visualization.arrayToDataTable(chartData);
    const options = {
      curveType: 'function',
      legend: { position: 'bottom' },
      chartArea: { width: '85%', height: '70%' },
      vAxis: { format: 'currency' },
      pointSize: 5
    };

    const chart = new google.visualization.LineChart(document.getElementById('chart_trend'));
    chart.draw(dataTable, options);
  }

  // --- Chart 4: Member vs Non-Member (Bar) ---
  function drawMemberChart(data) {
    const grouped = groupData(data, item => item.isMember ? "Member" : "Guest", item => item.sales);
    const chartData = [['Type', 'Sales', { role: 'style' }]];

    grouped.forEach(item => {
        const color = item[0] === "Member" ? "gold" : "silver";
        chartData.push([item[0], item[1], color]);
    });

    const dataTable = google.visualization.arrayToDataTable(chartData);
    const options = {
      legend: { position: "none" },
      chartArea: { width: '80%', height: '70%' },
      hAxis: { format: 'currency' }
    };

    const chart = new google.visualization.BarChart(document.getElementById('chart_member'));
    chart.draw(dataTable, options);
  }

  // Handle Resize
  window.onresize = function() {
      if(rawData.length > 0) processData(JSON.stringify(rawData));
  };
</script>
	function doGet() {
	return HtmlService.createTemplateFromFile('Index')
	.evaluate()
	.setTitle('Sales Analytics Dashboard')
	.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
	}

	function include(filename) {
	return HtmlService.createHtmlOutputFromFile(filename)
	.getContent();
	}

	/**
	* Fetches data from the active sheet and normalizes it.
	* This function assumes the CSV has been imported into the active sheet.
	*/
	function getDashboardData() {
	const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
	const data = sheet.getDataRange().getValues();
	const headers = data.shift(); // Remove header row

	// Map column indexes based on your CSV structure
	// Transaction_ID(0), Date(1), Region(2), Category(3), SKU(4), Qty(5), Price(6), Discount(7), Rating(8), Member(9), Total(10)

	const normalizedData = data.map(row => {
	// Normalization Logic
	let dateObj = new Date(row[1]);
	let dateStr = isNaN(dateObj.getTime()) ? null : dateObj.toISOString(); // Handle invalid dates

	return {
	date: dateStr,
	region: String(row[2]),
	category: String(row[3]),
	isMember: String(row[9]).trim().toLowerCase() === 'yes', // Normalize boolean
	sales: Number(row[10]) \|\| 0 // Normalize number, default to 0 if empty
	};
	});

	return JSON.stringify(normalizedData);
	}
	<style>
	body {
	background-color: #f8f9fa;
	font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
	}

	.dashboard-container {
	max-width: 1400px;
	margin: 0 auto;
	padding-bottom: 50px;
	}

	.kpi-card {
	transition: transform 0.2s;
	box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
	}

	.kpi-card:hover {
	transform: translateY(-5px);
	box-shadow: 0 0.5rem 1rem rgba(0, 0, 0, 0.15);
	}

	.chart-container {
	width: 100%;
	min-height: 300px;
	}

	.card-header {
	border-bottom: 1px solid #eee;
	color: #555;
	}
	</style>
	<!DOCTYPE html>
	<html>
	<head>
	<base target="_top">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<!-- Bootstrap 5 CSS -->
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
	<?!= include('css'); ?>
	</head>
	<body>

	<!-- Navbar -->
	<nav class="navbar navbar-dark bg-primary mb-4 shadow-sm">
	<div class="container-fluid">
	<span class="navbar-brand mb-0 h1">📊 Sales Analytics Dashboard</span>
	<button class="btn btn-light btn-sm" onclick="loadDashboardData()">Refresh Data</button>
	</div>
	</nav>

	<div class="container-fluid dashboard-container">

	<!-- Loading Spinner -->
	<div id="loader" class="text-center mt-5">
	<div class="spinner-border text-primary" role="status">
	<span class="visually-hidden">Loading...</span>
	</div>
	<p class="mt-2 text-muted">Processing datasets...</p>
	</div>

	<!-- Dashboard Content (Hidden initially) -->
	<div id="dashboard-content" style="display:none;">

	<!-- KPI Cards Row -->
	<div class="row g-3 mb-4">
	<div class="col-md-3">
	<div class="card kpi-card border-start border-4 border-primary">
	<div class="card-body">
	<h6 class="card-subtitle mb-2 text-muted">Total Revenue</h6>
	<h3 class="card-title" id="kpi-revenue">$0</h3>
	</div>
	</div>
	</div>
	<div class="col-md-3">
	<div class="card kpi-card border-start border-4 border-success">
	<div class="card-body">
	<h6 class="card-subtitle mb-2 text-muted">Total Transactions</h6>
	<h3 class="card-title" id="kpi-transactions">0</h3>
	</div>
	</div>
	</div>
	<div class="col-md-3">
	<div class="card kpi-card border-start border-4 border-info">
	<div class="card-body">
	<h6 class="card-subtitle mb-2 text-muted">Avg Order Value</h6>
	<h3 class="card-title" id="kpi-aov">$0</h3>
	</div>
	</div>
	</div>
	<div class="col-md-3">
	<div class="card kpi-card border-start border-4 border-warning">
	<div class="card-body">
	<h6 class="card-subtitle mb-2 text-muted">Member Sales %</h6>
	<h3 class="card-title" id="kpi-member-rate">0%</h3>
	</div>
	</div>
	</div>
	</div>

	<!-- Charts Row 1 -->
	<div class="row g-3 mb-4">
	<div class="col-md-6">
	<div class="card shadow-sm h-100">
	<div class="card-header bg-white fw-bold">Sales by Region</div>
	<div class="card-body">
	<div id="chart_region" class="chart-container"></div>
	</div>
	</div>
	</div>
	<div class="col-md-6">
	<div class="card shadow-sm h-100">
	<div class="card-header bg-white fw-bold">Sales by Category</div>
	<div class="card-body">
	<div id="chart_category" class="chart-container"></div>
	</div>
	</div>
	</div>
	</div>

	<!-- Charts Row 2 -->
	<div class="row g-3">
	<div class="col-md-8">
	<div class="card shadow-sm h-100">
	<div class="card-header bg-white fw-bold">Monthly Sales Trend</div>
	<div class="card-body">
	<div id="chart_trend" class="chart-container"></div>
	</div>
	</div>
	</div>
	<div class="col-md-4">
	<div class="card shadow-sm h-100">
	<div class="card-header bg-white fw-bold">Member vs Non-Member</div>
	<div class="card-body">
	<div id="chart_member" class="chart-container"></div>
	</div>
	</div>
	</div>
	</div>

	</div> <!-- End Dashboard Content -->
	</div>

	<!-- Google Charts Loader -->
	<script src="https://www.gstatic.com/charts/loader.js"></script>
	<?!= include('JavaScript'); ?>
	</body>
	</html>
	<script>
	// Global variable to store raw data
	let rawData = [];

	// Load Google Charts
	google.charts.load('current', {'packages':['corechart']});
	google.charts.setOnLoadCallback(loadDashboardData);

	function loadDashboardData() {
	document.getElementById('loader').style.display = 'block';
	document.getElementById('dashboard-content').style.display = 'none';

	google.script.run
	.withSuccessHandler(processData)
	.withFailureHandler(error => alert("Error: " + error))
	.getDashboardData();
	}

	function processData(jsonString) {
	rawData = JSON.parse(jsonString);

	// Calculate KPIs
	calculateKPIs(rawData);

	// Draw Charts
	drawRegionChart(rawData);
	drawCategoryChart(rawData);
	drawTrendChart(rawData);
	drawMemberChart(rawData);

	// Show UI
	document.getElementById('loader').style.display = 'none';
	document.getElementById('dashboard-content').style.display = 'block';
	}

	// --- KPI Calculation ---
	function calculateKPIs(data) {
	const totalRevenue = data.reduce((sum, item) => sum + item.sales, 0);
	const totalTransactions = data.length;
	const avgOrderValue = totalTransactions ? totalRevenue / totalTransactions : 0;

	const memberSales = data.reduce((sum, item) => item.isMember ? sum + item.sales : sum, 0);
	const memberRate = totalRevenue ? (memberSales / totalRevenue) * 100 : 0;

	// Format Currency
	const fmt = new Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' });

	document.getElementById('kpi-revenue').innerText = fmt.format(totalRevenue);
	document.getElementById('kpi-transactions').innerText = totalTransactions.toLocaleString();
	document.getElementById('kpi-aov').innerText = fmt.format(avgOrderValue);
	document.getElementById('kpi-member-rate').innerText = memberRate.toFixed(1) + "%";
	}

	// --- Helper: Group Data ---
	function groupData(data, keyFn, valueFn) {
	const groups = {};
	data.forEach(item => {
	const key = keyFn(item);
	const val = valueFn(item);
	groups[key] = (groups[key] \|\| 0) + val;
	});
	return Object.entries(groups).sort((a,b) => b[1] - a[1]); // Sort Descending by value
	}

	// --- Chart 1: Sales by Region (Column) ---
	function drawRegionChart(data) {
	const grouped = groupData(data, item => item.region, item => item.sales);
	const chartData = [['Region', 'Sales', { role: 'style' }]];

	// Add colors dynamically
	const colors = ['#4285F4', '#DB4437', '#F4B400', '#0F9D58'];
	grouped.forEach((item, index) => {
	chartData.push([item[0], item[1], colors[index % colors.length]]);
	});

	const dataTable = google.visualization.arrayToDataTable(chartData);
	const options = {
	legend: { position: "none" },
	chartArea: { width: '80%', height: '70%' },
	vAxis: { format: 'currency' }
	};

	const chart = new google.visualization.ColumnChart(document.getElementById('chart_region'));
	chart.draw(dataTable, options);
	}

	// --- Chart 2: Sales by Category (Pie/Donut) ---
	function drawCategoryChart(data) {
	const grouped = groupData(data, item => item.category, item => item.sales);
	const chartData = [['Category', 'Sales']];
	grouped.forEach(item => chartData.push(item));

	const dataTable = google.visualization.arrayToDataTable(chartData);
	const options = {
	pieHole: 0.4,
	chartArea: { width: '90%', height: '80%' },
	legend: { position: 'right' }
	};

	const chart = new google.visualization.PieChart(document.getElementById('chart_category'));
	chart.draw(dataTable, options);
	}

	// --- Chart 3: Monthly Trend (Line) ---
	function drawTrendChart(data) {
	// Group by YYYY-MM
	const grouped = {};
	data.forEach(item => {
	if(!item.date) return;
	const d = new Date(item.date);
	const key = d.getFullYear() + "-" + String(d.getMonth() + 1).padStart(2, '0'); // YYYY-MM
	grouped[key] = (grouped[key] \|\| 0) + item.sales;
	});

	// Sort chronologically
	const sortedKeys = Object.keys(grouped).sort();
	const chartData = [['Month', 'Revenue']];
	sortedKeys.forEach(key => {
	chartData.push([key, grouped[key]]);
	});

	const dataTable = google.visualization.arrayToDataTable(chartData);
	const options = {
	curveType: 'function',
	legend: { position: 'bottom' },
	chartArea: { width: '85%', height: '70%' },
	vAxis: { format: 'currency' },
	pointSize: 5
	};

	const chart = new google.visualization.LineChart(document.getElementById('chart_trend'));
	chart.draw(dataTable, options);
	}

	// --- Chart 4: Member vs Non-Member (Bar) ---
	function drawMemberChart(data) {
	const grouped = groupData(data, item => item.isMember ? "Member" : "Guest", item => item.sales);
	const chartData = [['Type', 'Sales', { role: 'style' }]];

	grouped.forEach(item => {
	const color = item[0] === "Member" ? "gold" : "silver";
	chartData.push([item[0], item[1], color]);
	});

	const dataTable = google.visualization.arrayToDataTable(chartData);
	const options = {
	legend: { position: "none" },
	chartArea: { width: '80%', height: '70%' },
	hAxis: { format: 'currency' }
	};

	const chart = new google.visualization.BarChart(document.getElementById('chart_member'));
	chart.draw(dataTable, options);
	}

	// Handle Resize
	window.onresize = function() {
	if(rawData.length > 0) processData(JSON.stringify(rawData));
	};
	</script>