Google Apps Script (GAS) is a powerful tool for automating, customizing, and enhancing Google Sheets. Recently, I investigated various automation techniques using GAS, and I am sharing a step-by-step guide to help others use it for their own projects.Whether you are a developer or new to scripting, this guide will walk you through basic automation to UI enhancements.

Why Use Google Apps Script?

Automate repetitive tasks in Google Sheets
Improve data processing efficiency
Create custom functions and triggers
Enhance user experience with UI elements like modals and loaders

Getting Started: Writing Your First Google Apps Script

How to Access Google Apps Script

1. Open Google Sheets.
2. Click on Extensions > Apps Script.
3. The above command opens the apps script editor, where you can start coding.

Writing Your First Script

Try this simple script to write “Hello, World!” into a cell:

function writeHelloWorld() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sheet.getRange("A1").setValue("Hello, World!");
}

Run the script by clicking ▶️ in the Apps Script editor.

Automating Data Processing in Google Sheets

Iterating Over Specific Rows & Columns

Many tasks require looping through certain rows and columns. You can dynamically iterate over a list of rows and update values:

function iterateRows() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var rowNumbers = [2, 5, 8, 12]; // Modify this array as needed

    rowNumbers.forEach(row => {
        sheet.getRange(row, 1).setValue("Processed");
    });
}

Use case: Updating specific rows dynamically.

Reading & Writing Data Efficiently

To read a column’s data, use:

function readColumnData() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var values = sheet.getRange("B2:B20").getValues(); // Read Column B
    Logger.log(values);
}

Use case: Fetching data from a range for processing.

To write data to multiple rows at once, use:

function writeBulkData() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = [["Value 1"], ["Value 2"], ["Value 3"]]; // Data to insert
    sheet.getRange("B2:B4").setValues(data);
}

Bulk processing saves execution time!

Handling Data Formatting Issues

Preventing Google Sheets from Converting Numbers to Dates

Google Sheets often converts values like 7/10 to a date (e.g.,7/10/2023). To force an integer, use:

function insertInteger() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var cell = sheet.getRange("A1");

    cell.setNumberFormat("0"); // Force integer format
    cell.setValue(9);
}

Prevents unwanted date formatting in numeric fields.

Running Code Automatically on Edits

Using onEdit(e) to Detect Changes

To trigger actions when a specific cell is edited, use:

function onEdit(e) {
    var sheet = e.source.getActiveSheet();
    var editedCell = e.range;

    if (sheet.getName() === "MySheet" && editedCell.getRow() === 1 && editedCell.getColumn() === 3) {
        Logger.log("Cell C1 was edited!");
    }
}

Use case: performing calculations, updating other cells, or logging data when users edit a specific area.

Enhancing User Experience with a Preloader

Displaying a “Processing…” Message

Google Apps Script blocks UI interactions while running, but you can simulate a preloader by updating a cell before execution:

function showPreloader() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    sheet.getRange("A1").setValue("Processing... ⏳");

    // Simulate processing delay
    Utilities.sleep(2000);

    sheet.getRange("A1").setValue("Done ✅");
}

Simple yet effective way to indicate script execution!

Creating a Pop-up Loader (Modal Dialog)

Would you like a genuine loading popup? Use an HTML modal dialog.

Step 1: Create a Function to Show the Modal

function showLoadingScreen() {
    var html = HtmlService.createHtmlOutputFromFile('Loading')
        .setWidth(300)
        .setHeight(100);
    SpreadsheetApp.getUi().showModalDialog(html, 'Processing...');
}

Step 2: Create a New HTML File (Loading.html)

<!DOCTYPE html>
<html>
<head>
    <style>
        .spinner {
            border: 4px solid #f3f3f3;
            border-top: 4px solid #3498db;
            border-radius: 50%;
            width: 30px;
            height: 30px;
            animation: spin 1s linear infinite;
        }
        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }
    </style>
</head>
<body>
    <p>Processing...</p>
    <div class="spinner"></div>
</body>
</html>

Creates a pop-up with a loading spinner while the script runs!

Best Practices for Google Apps Script

Efficient Execution

Use getValues() and setValues() instead of looping row by row
Use Logger.log() for debugging instead of alert()
Use installable triggers for background execution (e.g.,onEdit

Conclusion

Google Apps Script is a game-changer for automating tasks in Google Sheets. By implementing:

Data processing automation
On-edit triggers
Preventing formatting issues
Enhancing user experience
Displaying preloader modals

You can transform your workflow and build powerful, interactive spreadsheets!

Let’s Connect!

Have you used Google Apps Script? Share your experiences, and let’s discuss how we can optimize automation in Google Sheets!