Fill Google Spreadsheet with Random Data

How to Fill a Google Spreadsheet with Random Data Using Apps Script – A Complete Guide

When you're building spreadsheets for development, testing, demos, training, or prototyping data workflows, it's often helpful to populate your sheet with random data. Whether you're simulating sales records, customer data, test scores, or random numbers, you can use Google Apps Script to automatically generate and fill random data into your Google Spreadsheet.

In this detailed blog post, you’ll learn how to use Apps Script to generate and fill Google Sheets with different types of random data including:

• Numbers (integer, decimal)

• Names

• Email addresses

• Dates

• Cities or countries

• Custom values (e.g., product types, categories)

Let’s walk through how to create this with full flexibility and customization.

Why Generate Random Data in Google Sheets?

Here are some common use cases:

• Testing formulas and scripts before working on real data

• Building mock dashboards or prototypes

• Training teams on spreadsheet features

• Populating examples for tutorials or demos

• Developing templates or models without relying on sensitive data

Manual data entry can be time-consuming and repetitive. With Google Apps Script, you can automate the process and regenerate data any time with one click.

Step 1: Create Your Spreadsheet

Open Google Sheets and set up your header row with fields like:

We'll populate random values under each column.

Step 2: Open the Script Editor

1. Go to Extensions > Apps Script

2. Clear any existing code in the editor.

Step 3: Paste the Script to Generate Random Data

function fillRandomData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const numRows = 100; // Number of rows of random data to generate
  const headers = ["Name", "Email", "Age", "City", "Join Date", "Sales"];
  sheet.clear(); // Clear existing content

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]); // Set headers

  const names = ["Alice", "Bob", "Carol", "David", "Eva", "Frank", "Grace", "Henry", "Ivy", "Jake"];
  const domains = ["example.com", "mail.com", "demo.org", "test.net"];
  const cities = ["Mumbai", "Delhi", "Bangalore", "Kolkata", "Chennai", "Pune", "Hyderabad"];
  const data = [];

  for (let i = 0; i < numRows; i++) {
    const firstName = names[Math.floor(Math.random() * names.length)];
    const lastName = String.fromCharCode(65 + Math.floor(Math.random() * 26)) + String.fromCharCode(97 + Math.floor(Math.random() * 26));
    const email = `${firstName.toLowerCase()}.${lastName.toLowerCase()}@${domains[Math.floor(Math.random() * domains.length)]}`;
    const age = Math.floor(Math.random() * 43) + 18; // 18 to 60
    const city = cities[Math.floor(Math.random() * cities.length)];
    const joinDate = randomDate(new Date(2020, 0, 1), new Date()); // Jan 1, 2020 to today
    const sales = (Math.random() * 10000).toFixed(2); // Random sales amount

    data.push([`${firstName} ${lastName}`, email, age, city, joinDate, parseFloat(sales)]);
  }

  sheet.getRange(2, 1, data.length, data[0].length).setValues(data);
  Logger.log("Random data filled successfully.");
}

// Generate a random date between two dates
function randomDate(start, end) {
  const date = new Date(start.getTime() + Math.random() * (end.getTime() - start.getTime()));
  return Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");
}

Script Breakdown

Step 1: Setup

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const numRows = 100;

We define which sheet to work with and how many rows of random data to generate.

Step 2: Headers and Data Sources

const names = ["Alice", "Bob", "Carol", "David"];
const domains = ["example.com", "demo.org"];
const cities = ["Mumbai", "Delhi", "Chennai"];

You can customize these arrays to match your context (countries, products, departments, etc.).

Step 3: Loop to Create Each Row

for (let i = 0; i < numRows; i++) {
  // Generate random first/last names, email, city, date, etc.
}

Each row is a unique random combination of values.

Step 4: Random Date Function

function randomDate(start, end) {
  ...
}

This helper generates a random date in a specific format between two given dates.

Step 4: Save and Run the Script

1. Save the project (Ctrl + S) and name it something like "Random Data Generator".

2. Click the Run button ▶.

3. The first time, you’ll need to authorize the script.

4. Once executed, your spreadsheet will be filled with 100 rows of random data.

Sample Output

Advanced Customization Ideas

You can expand this script to include:

• Phone numbers

• Departments or job titles

• Boolean fields (e.g., active/inactive)

• Custom number ranges

• Multi-sheet generation

• Gender, salary, scores, etc.

For example, to generate random phone numbers:

const phone = "+91 " + Math.floor(1000000000 + Math.random() * 9000000000);

Use Cases

• Developers creating UI dashboards with placeholder data

• Trainers needing example data for students

• Analysts mocking test cases for dashboards

• Product managers designing data templates

• QA engineers simulating performance on large datasets

Automate Data Regeneration

To add a custom menu in the spreadsheet so users can regenerate data:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("⚙️ Utilities")
    .addItem("Fill Random Data", "fillRandomData")
    .addToUi();
}

Add this function at the end of your script. Now every time you open the spreadsheet, a menu called “⚙️ Utilities” will appear with an option to "Fill Random Data".

Conclusion

Generating random data in Google Sheets using Apps Script is a powerful way to automate and simulate datasets. With this flexible script, you can populate spreadsheets in seconds, enabling easier testing, demos, templates, and educational materials.

Whether you’re a developer, analyst, instructor, or just need a working dataset fast, this tool will save time and add professionalism to your work. You can always extend the script to match your exact requirements. Use it, customize it, and build your spreadsheet environments faster and smarter.