📋Build a Charts Dashboard with Google Sheets and HTML Service

1. Overview

\[ \begin{array}{l} \textbf{You can quickly populate a Google Spreadsheet with random data --} \\ \text{using Google Apps Script to generate sample names, numbers, and dates.} \\ \textbf{This is useful for testing charts, dashboards, and formulas --} \\ \text{without needing real or sensitive information.} \end{array} \]

2. Core Steps

\[ \begin{array}{ll} \mathbf{Step\ 1:} & \text{Open a new or existing Google Spreadsheet.} \\ \mathbf{Step\ 2:} & \text{Go to Extensions} \rightarrow \text{Apps Script.} \\ \mathbf{Step\ 3:} & \text{Write a function to generate arrays of random data.} \\ \mathbf{Step\ 4:} & \text{Insert random numbers using Math.random() or Math.floor().} \\ \mathbf{Step\ 5:} & \text{Insert random text values from a predefined list.} \\ \mathbf{Step\ 6:} & \text{Insert random dates by adding days to a base date.} \\ \mathbf{Step\ 7:} & \text{Run the script to fill the spreadsheet automatically.} \end{array} \]

3. Sample Google Apps Script

// Code.gs
function fillRandomData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
  
  // Headers
  sheet.appendRow(["Name", "Age", "Join Date", "Score"]);
  
  var names = ["Alice", "Bob", "Charlie", "Diana", "Ethan"];
  
  for (var i = 0; i < 20; i++) {
    var name = names[Math.floor(Math.random() * names.length)];
    var age = Math.floor(Math.random() * 30) + 20; // 20-50
    var date = new Date(2020, 0, 1);
    date.setDate(date.getDate() + Math.floor(Math.random() * 365));
    var score = Math.floor(Math.random() * 100) + 1; // 1-100
    
    sheet.appendRow([name, age, date, score]);
  }
}

4. Important Notes

\[ \begin{array}{l} \text{• The dataset size and range can be adjusted by changing loop values.} \\ \text{• You can generate more complex data like emails, addresses, or IDs.} \\ \text{• Use Utilities.formatDate() to customize date display formats.} \\ \text{• Be cautious when running scripts repeatedly to avoid exceeding quotas.} \\ \text{• Random values will differ each time you run the script.} \end{array} \]

5. Conceptual Flow (in LaTeX)

The process can be described as:

\[ \text{Random Data Generator (Apps Script)} \xrightarrow{\text{Append to Sheet}} \text{Test Dataset in Google Sheets} \]

Where: \[ \text{Random Row} = \{\text{Name}, \text{Age}, \text{Date}, \text{Score}\} \]