How to Create a New Spreadsheet and Populate it with Random User Data Using Google Apps Script

📢 Introduction

If you’ve ever needed test data for a project or wanted to see how to fetch and store API data into a spreadsheet, this guide is for you! With just a few lines of Google Apps Script, you can:

Create a new Google Spreadsheet.
Fetch 50 random users from the Random User API.
Format the user data (name, gender, email, phone, and location) into rows.
Populate the data into the new spreadsheet automatically.

This script is perfect for generating sample test data for development, analysis, or practice with API integrations.

🎉 What Will This Script Do?

This script will:

Create a new Google Spreadsheet called Random Users Report.
Fetch 50 Random Users from the Random User API.
Extract Key User Details: Full Name, Gender, Email, Phone, City, State, and Country.
Populate the New Spreadsheet with this data.

By the end of this guide, you’ll have a working script that creates a live, populated spreadsheet in seconds.

📘 Full Apps Script Code

Here is the complete script to create a new spreadsheet, fetch user data, and populate it.

function createNewSpreadsheetAndFetchUsers() {
  // Step 1: Create a new Google Spreadsheet
  const spreadsheet = SpreadsheetApp.create('Random Users Report');
  const sheet = spreadsheet.getActiveSheet();
  
  // Step 2: Fetch 50 random users from the Random User API
  const url = 'https://randomuser.me/api/?results=50'; 
  const response = UrlFetchApp.fetch(url); 
  const jsonData = JSON.parse(response.getContentText()); 
  const users = jsonData.results; // Extract the 'results' array from the JSON response
  
  // Step 3: Prepare the headers for the sheet
  const headers = ['Full Name', 'Gender', 'Email', 'Phone', 'City', 'State', 'Country'];
  
  // Step 4: Extract and format user data for each user
  const userData = users.map(user => [
    `${user.name.first} ${user.name.last}`,  // Full Name
    user.gender,                             // Gender
    user.email,                              // Email
    user.phone,                              // Phone
    user.location.city,                      // City
    user.location.state,                     // State
    user.location.country                    // Country
  ]);
  
  // Step 5: Populate the spreadsheet with headers and user data
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]); // Set headers
  sheet.getRange(2, 1, userData.length, userData[0].length).setValues(userData); // Set user data
  
  // Log the URL of the new spreadsheet
  const spreadsheetUrl = spreadsheet.getUrl();
  Logger.log(`New spreadsheet created: ${spreadsheetUrl}`);
}

📘 How It Works

Create a New Spreadsheet const spreadsheet = SpreadsheetApp.create('Random Users Report'); This line creates a new Google Spreadsheet called Random Users Report.
Fetch Random User Data const url = 'https://randomuser.me/api/?results=50'; const response = UrlFetchApp.fetch(url); const jsonData = JSON.parse(response.getContentText()); const users = jsonData.results; The script calls the Random User API to request 50 random users. It parses the response as JSON and extracts the results array, which contains the 50 user objects.
Format User Data const userData = users.map(user => [ `${user.name.first} ${user.name.last}`, user.gender, user.email, user.phone, user.location.city, user.location.state, user.location.country ]); For each user, the script extracts key details like:
- Full Name (combines first and last names)GenderEmailPhoneLocation (City, State, Country)
Each user becomes an array, and all the user data is stored in the userData array.
Populate the Spreadsheet sheet.getRange(1, 1, 1, headers.length).setValues([headers]); sheet.getRange(2, 1, userData.length, userData[0].length).setValues(userData); The script inserts headers into the first row and places the user data in the rows below.
Log the URL of the New Spreadsheet const spreadsheetUrl = spreadsheet.getUrl(); Logger.log(`New spreadsheet created: ${spreadsheetUrl}`); Once the spreadsheet is created, the URL is logged so you can easily access it.

📘 Usage Instructions

Add the Script
- Open Google Sheets.
- Click Extensions → Apps Script.
- Delete any existing code and paste the script.
- Click Save (💾) or press Ctrl + S.
Run the Script
- Click Run (▶️) next to createNewSpreadsheetAndFetchUsers().
- Grant permissions to access external services and Sheets.
View the Results
- Check the Logs (View → Logs) to get the URL of the newly created spreadsheet.
- Click the link to open the spreadsheet.

📘 Sample Data Table

Full Name	Gender	Email	Phone	City	State	Country
John Smith	male	john.smith@mail.com	555-1234	New York	NY	USA
Emma Brown	female	emma.brown@mail.com	555-5678	Los Angeles	CA	USA
Sarah Johnson	female	sarah.johnson@mail.com	555-9876	Miami	FL	USA
Mark Taylor	male	mark.taylor@mail.com	555-3456	Dallas	TX	USA

Each row represents 1 user with details like name, gender, email, phone, and location.

📘 Customization Options

1️⃣ Number of Users

Change the number of users to 100 or 25:

const url = 'https://randomuser.me/api/?results=100';

Change results=50 to results=100 to fetch 100 users instead of 50.

2️⃣ User Data Fields

You can customize which fields to extract from the API. To add more fields (like DOB or Nationality), update the userData and headers like this:

const userData = users.map(user => [
  `${user.name.first} ${user.name.last}`,  
  user.gender,                             
  user.email,                              
  user.phone,                              
  user.location.city,                      
  user.location.state,                     
  user.location.country,                   
  user.dob.date,                           // Date of birth
  user.nat                                // Nationality
]);

const headers = ['Full Name', 'Gender', 'Email', 'Phone', 'City', 'State', 'Country', 'DOB', 'Nationality'];

📘 Possible Use Cases

Test Data Generation: Generate realistic test data for your web apps or projects.
Data Analysis: Use the script to practice data analysis techniques.
API Integration Practice: Learn how to work with REST APIs using Apps Script.
Data-Driven Testing: Generate large datasets for software testing and automation.

📘 Final Thoughts

This Google Apps Script allows you to create a new Google Spreadsheet and populate it with live user data from the Random User API. In just seconds, you’ll have a fully functional data sheet for development, testing, or demo purposes.

Key Features Recap:

Create New Spreadsheet
Fetch Data from API
Populate Sheet with Data

With this script, you can easily generate large amounts of sample data for testing, analysis, and development. It’s a simple but powerful example of API integration with Google Apps Script.