Google Sheets

Getting Started With Apps Script In Google Sheets

by

If you live in Google Sheets all day, at some point you hit the limits of formulas and filters. You spend time doing the same clicks over and over. Copy this. Clean that. Send those reminders.

Google Apps Script lets you turn that repetitive stuff into one-click actions or even fully automatic workflows, right inside Sheets.

This guide walks through:

  • What Apps Script actually is
  • How to open it from a Sheet
  • A simple custom function
  • A menu item that cleans up data
  • A basic email reminder using triggers

You do not need to be a full time developer to follow along. Basic comfort with spreadsheets and copy paste is enough.

What is Google Apps Script

Google Apps Script is basically JavaScript that runs on Google’s servers and talks to Google tools.

With it you can:

  • Add custom functions to Google Sheets
  • Build custom menus and buttons
  • Send emails from a sheet
  • Talk to other Google apps like Docs, Calendar, Drive
  • Run code on a schedule (daily, hourly, etc.)

Think of it as “lightweight automation for people who already use Sheets.”

Step 1: Open the Script Editor

Pick any Sheet you want to experiment in. A fresh test sheet is a good idea.

  1. Open your Google Sheet
  2. In the top menu, go to Extensions > Apps Script
  3. A new tab opens with the Apps Script editor
  4. You will see a default file called Code.gs

That file is where you paste or write your scripts.

Step 2: Create Your First Custom Function

Let’s build the classic “hello world” for Sheets, but slightly more useful.

We will create a function called DOUBLE that takes a number and returns double the value. Simple on purpose.

  1. In the Apps Script editor, delete anything that is there now
  2. Paste this code:
/**
 * Doubles a number.
 * Usage in Google Sheets: =DOUBLE(A1)
 */
function DOUBLE(value) {
  return value * 2;
}
  1. Click the Save icon (or press Ctrl + S / Cmd + S)
  2. Go back to your Sheet and in any cell type:
=DOUBLE(5)

You should see 10. If you reference a cell, for example =DOUBLE(A1), it will double that cell.

You just created a custom function. That is the basic building block you can use for all kinds of custom logic.

Step 3: Add a Custom Menu To Run Actions

Custom functions are nice, but sometimes you want a button style action instead of a formula. For example, “Clean this sheet for me.”

Let’s add a menu called “Tools” with a “Clean Data” item. When you click it, the script will:

  • Trim leading and trailing spaces
  • Remove empty rows at the bottom of the data range

Here is the script.

/**
 * Runs when the spreadsheet opens.
 * Adds a custom menu called "Tools".
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Tools')
    .addItem('Clean Data', 'cleanData')
    .addToUi();
}

/**
 * Cleans up the active sheet:
 * - Trims extra spaces
 * - Deletes empty rows at the bottom of the data range
 */
function cleanData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  // Trim spaces in all cells
  for (var r = 0; r < values.length; r++) {
    for (var c = 0; c < values[r].length; c++) {
      if (typeof values[r][c] === 'string') {
        values[r][c] = values[r][c].trim();
      }
    }
  }

  range.setValues(values);

  // Delete completely empty rows at the bottom
  var lastRow = sheet.getLastRow();
  for (var row = lastRow; row >= 1; row--) {
    var rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
    var isEmpty = rowValues.join('') === '';
    if (isEmpty) {
      sheet.deleteRow(row);
    } else {
      // Stop when we hit a row that actually has data
      break;
    }
  }
}

Save the project, then reload your Sheet.

You should now see a Tools menu at the top. Click Tools > Clean Data and the script will run.

First time you run it, Google will ask for permission. Review the scopes and click Allow. After that, the menu item works like a built in feature.

Step 4: Build an Email Reminder From Sheet Data

Next up, a very common pattern. You have a list of tasks or projects with due dates and owners. You want an automatic daily reminder that emails people when something is due today.

We will assume a sheet layout like this starting in row 2:

  • Column A: Task name
  • Column B: Owner email
  • Column C: Due date (actual date type in Sheets)
  • Column D: Status (optional, for example “Done” or “Open”)

First, paste this in your script file (below any existing functions):

/**
 * Sends reminder emails for tasks due today.
 * Expected columns:
 * A: Task name
 * B: Owner email
 * C: Due date
 * D: Status (optional)
 */
function sendDueTodayReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');
  if (!sheet) {
    Logger.log('Tasks sheet not found');
    return;
  }

  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  var today = new Date();
  today.setHours(0, 0, 0, 0);

  // Skip header row (start at i = 1)
  for (var i = 1; i < values.length; i++) {
    var row = values[i];
    var taskName = row[0];
    var email = row[1];
    var dueDate = row[2];
    var status = row[3];

    // Skip rows without an email or due date
    if (!email || !(dueDate instanceof Date)) {
      continue;
    }

    // Skip completed tasks
    if (status && status.toString().toLowerCase() === 'done') {
      continue;
    }

    // Normalize due date
    var due = new Date(dueDate);
    due.setHours(0, 0, 0, 0);

    // If due date is today, send reminder
    if (due.getTime() === today.getTime()) {
      var subject = 'Reminder: "' + taskName + '" is due today';
      var body = 'Quick reminder that the task "' + taskName + '" is due today.\n\n' +
                 'Row: ' + (i + 1) + '\n' +
                 'Due date: ' + due.toDateString() + '\n\n' +
                 'Please update the status when it is done.';

      MailApp.sendEmail(email, subject, body);
    }
  }
}

Now configure it to run automatically.

  1. In the Apps Script editor, click the Triggers icon (clock symbol) on the left
  2. Click Add Trigger in the bottom right
  3. Choose:
    • Function: sendDueTodayReminders
    • Event source: Time driven
    • Type: for example Day timer at 8am
  4. Save and accept the permissions

Every morning around 8am, the script will run, scan the Tasks sheet, and email owners for anything due that day.

You can tweak the wording, the sheet name, and the columns to match your setup.

Step 5: A Few Practical Tips So You Do Not Hate It

A couple of small habits make Apps Script much easier to live with.

Name your project
At the top left of the editor, click the name (usually “Untitled project”) and give it something clear like Task Reminders or Sheet Tools.

Use Logger.log while you debug
You can add lines like:

Logger.log('Row ' + i + ' email: ' + email);

Then run the function manually and check View > Logs in the editor. It helps you see what the script is actually doing.

Test on a copy
For anything that edits or deletes data, make a copy of your sheet and test there first. Once you trust it, point it at the real thing.

Keep scripts small and focused
It is easier to maintain three short functions than one 400 line monster that “handles everything.”

What You Can Try Next

If you want to keep going from here, good small projects are:

  • A script that auto formats new rows (currency, dates, data validation)
  • A button that sends a summary email of the current sheet to your team
  • A simple approval workflow where status changes trigger emails

You do not need to build a full internal tool on day one. Pick one annoying manual task in Sheets and see if you can turn it into a custom function, menu item, or scheduled trigger.

That is how you actually learn Apps Script. Not by reading every doc first, but by solving one real problem at a time.