Google Workspace

Google Apps Script for Beginners: Your First Script in Google Sheets

Learn Google Apps Script from scratch. Open the editor, write your first script, and automate Google Sheets — no coding experience needed.

Ash Ganda

— 9 min read

Every Google Workspace user has a moment where they wish their spreadsheet could just do something automatically. Send a reminder when a deadline is close. Highlight rows that have gone overdue. Fill in a cell based on rules rather than manual typing. That moment is the beginning of learning Google Apps Script.

Apps Script is Google's built-in scripting platform, based on JavaScript, that lives inside every Google Workspace account. It lets you write code that talks directly to Google Sheets, Gmail, Drive, Calendar, and other Google services. There is nothing to install, no subscription required beyond your existing Workspace plan, and no server to configure. You write code in a browser editor, click Run, and your script executes on Google's infrastructure.

This guide is written for people with no prior coding experience. You will learn what Apps Script is, open the editor, write a working "Hello World" script, read and write data in a spreadsheet, build a practical automation that highlights overdue rows, and understand how permissions and security work. By the end, you will have a foundation you can build on.

What Is Google Apps Script and Why Learn It?

Google Apps Script is a cloud-based scripting language. The syntax is JavaScript -- the same language used to build websites and web applications -- but instead of running in a browser, your scripts run on Google's servers and have direct access to your Google Workspace data.

The practical benefit is automation. Anything you do manually and repeatedly in Google Sheets is a candidate for automation. Colour-code rows based on a status column. Send an email when a form is submitted. Copy data from one sheet to another based on a condition. Generate a weekly summary report and email it to your manager. All of these can be done with Apps Script.

The reason to learn Apps Script rather than relying on third-party tools like Zapier or Make is control. You write exactly the logic you need, with no per-task pricing, no workflow limits, and no dependency on a third-party platform staying in business. Once a script is written and triggered, it runs silently and reliably.

For Australian small business owners and office managers, Apps Script is particularly valuable in Google Sheets because Sheets is already at the centre of so many workflows -- invoicing, rosters, project tracking, client databases, and expense management. Adding automation to those existing spreadsheets requires no migration or new tools.

Opening the Script Editor

The script editor is where you write, save, and run your code. Every Google Sheet has its own associated script project, accessible directly from the sheet.

  1. Open any Google Sheet in your browser.
  2. Click Extensions in the top menu bar.
  3. Select Apps Script.
  4. The Apps Script editor opens in a new browser tab.

The editor shows a default project called "Untitled project" and a single file called Code.gs. Inside that file, there is an empty function:

function myFunction() {

}

This is a JavaScript function. A function is a named block of code that runs when called. The name is myFunction, the curly braces {} are where your code goes, and Apps Script will execute whatever is between them when you run that function.

Before writing anything, rename your project. Click on "Untitled project" at the top left of the editor, type a name like "My First Script", and press Enter. This is just for your own organisation -- it does not affect how the script runs.

Your First Script: A Hello World Alert

The classic first program in any language is "Hello World" -- code that does the simplest possible thing to prove the environment is working. In Apps Script, the equivalent is showing an alert dialog box.

Replace the default function with this code:

function helloWorld() {
  SpreadsheetApp.getUi().alert('Hello, World!');
}

Here is what each part does:

  • SpreadsheetApp is the built-in Apps Script service for interacting with Google Sheets
  • .getUi() gets a reference to the user interface of the spreadsheet (the visible part in the browser tab)
  • .alert('Hello, World!') displays a popup dialog with the text "Hello, World!"

To run the script:

  1. Make sure the function helloWorld is selected in the dropdown next to the Run button (it will show the function name).
  2. Click the Run button (the triangle/play icon).
  3. A permissions dialog will appear the first time. Click Review permissions, select your Google account, and click Allow. This authorises the script to interact with your spreadsheet.
  4. Switch back to your Google Sheet tab. A dialog box should be visible saying "Hello, World!" with an OK button.

Click OK to dismiss it. You have just run your first Apps Script.

The permissions step is a one-time requirement per script project. Google asks you to review what the script is allowed to do before it runs for the first time. For this script, it only needs access to the spreadsheet UI. More complex scripts request additional permissions as needed.

Reading and Writing Data in Google Sheets

A dialog box is a useful demo, but the real power of Apps Script comes from reading and writing actual spreadsheet data. The primary object for this is SpreadsheetApp, combined with methods that navigate to specific sheets, rows, and cells.

Reading a Value from a Cell

This script reads the value from cell A1 and displays it in a log:

function readCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var value = sheet.getRange('A1').getValue();
  Logger.log('The value in A1 is: ' + value);
}

Breaking this down:

  • SpreadsheetApp.getActiveSpreadsheet() gets the spreadsheet the script is attached to
  • .getActiveSheet() gets the currently visible sheet (the tab you are on)
  • .getRange('A1') refers to a specific cell using standard A1 notation
  • .getValue() reads the contents of that cell
  • Logger.log() prints a message to the Apps Script log (View > Logs) rather than showing a popup

To see the log output after running the script, click View in the Apps Script editor menu, then Logs. The value from A1 will appear there.

Writing a Value to a Cell

To write data, use setValue() instead of getValue():

function writeToCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('B2').setValue('Written by Apps Script');
}

Run this and cell B2 in your spreadsheet will contain the text "Written by Apps Script". You can replace the string with any value -- numbers, dates, and formulas work as well.

Reading an Entire Column

For most real-world use cases, you want to read multiple rows rather than a single cell. getValues() (plural) returns a two-dimensional array -- essentially a grid of values:

function readColumn() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getRange('A1:A10').getValues();

  for (var i = 0; i < data.length; i++) {
    Logger.log('Row ' + (i + 1) + ': ' + data[i][0]);
  }
}

The data variable is an array of rows. Each row is itself an array of column values. So data[0][0] is the first row, first column; data[1][0] is the second row, first column; and so on. The for loop iterates through every row and logs the value.

A Practical Example: Highlight Overdue Rows

Now you have enough to build something genuinely useful. This script reads a task list in Google Sheets and highlights any row where the due date in column B has already passed and the status in column C is not "Complete".

Set up a sheet with the following columns:

  • Column A: Task name
  • Column B: Due date (formatted as a date)
  • Column C: Status ("In Progress", "Complete", "Not Started", etc.)

Add a few rows of sample data, including at least one row with a due date in the past and a status other than "Complete".

Here is the script:

function highlightOverdueRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var today = new Date();

  // Define colours
  var overdueColour = '#f4cccc';   // light red
  var clearColour = '#ffffff';     // white (no highlight)

  // Loop through each data row, starting at row 2 to skip the header
  for (var i = 2; i <= lastRow; i++) {
    var dueDateCell = sheet.getRange(i, 2);  // Column B
    var statusCell  = sheet.getRange(i, 3);  // Column C
    var rowRange    = sheet.getRange(i, 1, 1, 3);  // Columns A to C

    var dueDate = dueDateCell.getValue();
    var status  = statusCell.getValue();

    // Check whether this row is overdue
    if (dueDate instanceof Date && dueDate < today && status !== 'Complete') {
      rowRange.setBackground(overdueColour);
    } else {
      rowRange.setBackground(clearColour);
    }
  }

  SpreadsheetApp.getUi().alert('Done! Overdue rows have been highlighted.');
}

Run this function and any row with a past due date that is not marked Complete will turn light red. Rows that are on time or already complete are cleared back to white, so running the script multiple times keeps the highlighting accurate as statuses change.

A few things worth noting in this script:

  • sheet.getLastRow() dynamically finds the last row with data, so the script works whether you have 10 rows or 1,000.
  • new Date() creates a JavaScript date object representing the current date and time.
  • dueDate instanceof Date checks that the cell actually contains a date value rather than empty text, which prevents errors on blank rows.
  • sheet.getRange(i, 2) uses row number and column number instead of A1 notation -- this is more flexible inside loops because you can use the loop counter i as the row number.
  • setBackground() accepts a hex colour string to change the cell background colour.

Saving and Running Your Script

Scripts save automatically in the Apps Script editor, but you can also save manually using Ctrl+S (Windows) or Cmd+S (Mac). The editor saves the current state of your code to Google's servers.

To run a function:

  1. Select the function name from the dropdown at the top of the editor (next to the Run button). If you have multiple functions in your file, this dropdown lets you choose which one to run.
  2. Click the Run button (the play icon).
  3. Watch the execution log at the bottom of the editor for any errors or Logger.log output.

If the script throws an error, the execution log will show you the line number where it failed and a description of the error. Common errors for beginners include:

  • TypeError: Cannot read property of undefined -- usually means you are trying to use a value that is empty or does not exist. Check your cell references.
  • Exception: Range not found -- you have specified a cell range that does not exist (for example, a column letter that is out of bounds).
  • You do not have permission -- the script is trying to do something it has not been authorised for. Re-run the script and complete the permissions flow again.

Understanding Permissions

Every time you run an Apps Script for the first time (or when it requests new capabilities), Google shows a permissions dialog listing what the script needs access to.

For the scripts in this guide, you will see permissions like:

  • See and edit your spreadsheets in Google Drive -- Required to read and write cell values, change formatting, and access sheet structure.
  • Display and run third-party web content in prompts and sidebars -- Required for .alert() dialogs.

These permissions are scoped to the Google account that authorises the script. The script runs as you and can only access data that you already have permission to access.

You may encounter a warning that says "Google hasn't verified this app" when authorising a script you wrote yourself. This is expected for personal scripts. Click Advanced, then Go to [project name] (unsafe) to proceed. This warning appears because the app has not gone through Google's formal OAuth app verification process -- something only necessary for scripts distributed to external users. Your own scripts, for your own use, do not need to go through that process.

To review and revoke permissions at any time, visit myaccount.google.com/permissions.

What to Learn Next

Once you are comfortable with reading and writing cells and running functions manually, the logical next step is triggers -- scheduling scripts to run automatically without clicking the Run button.

Apps Script supports several trigger types:

  • Time-driven triggers: Run a function every hour, daily at a set time, or weekly on a specific day. Ideal for the overdue row highlighter -- schedule it to run every morning at 8am so the sheet is always up to date before work begins.
  • On form submit: Runs a function each time a Google Form is submitted. Used for sending confirmation emails, logging responses to a separate sheet, or triggering approval workflows.
  • On edit: Runs whenever anyone edits the spreadsheet. Useful for validation logic, auto-filling related cells, or logging changes.
  • On open: Runs each time the spreadsheet is opened.

To set up a trigger, click the clock icon in the left sidebar of the Apps Script editor (labelled Triggers), then click Add Trigger in the bottom right corner.

Other areas worth exploring as you progress:

  • GmailApp: Send emails from a script, read emails, search your inbox, and attach files
  • DriveApp: Create, move, rename, and organise files and folders in Google Drive
  • CalendarApp: Create calendar events, read upcoming meetings, and send invitations
  • UrlFetchApp: Make HTTP requests to external APIs, pulling data into Sheets from any web service
  • PropertiesService: Store configuration values (like email addresses or thresholds) separately from your script code

The official Apps Script documentation is thorough and includes code samples for every service. When you want to do something specific, searching "Apps Script [what you want to do]" in Google will almost always surface a relevant code example.

Conclusion

Google Apps Script is one of the most accessible automation tools available to anyone using Google Workspace. There is no software to install, no separate subscription, and the learning curve is gentler than it first appears -- especially if you start with small, practical scripts like the ones in this guide.

You have now opened the script editor, written a function that interacts with a Google Sheet, read and written cell values, built an automation that highlights overdue rows based on real data, and understood how permissions work. That is a meaningful foundation.

The most effective way to keep learning is to identify a repetitive task in a spreadsheet you use every day and write a script to automate part of it. Even a simple script that formats cells or fills in dates automatically will teach you more than reading documentation ever will. Start small, run the script, see what breaks, fix it, and repeat.

Apps Script rewards curiosity. The more you poke at it, the more you will find it can do.

Ash Ganda is the founder of CloudGeeks, a Google Workspace consultancy helping Australian SMBs get more from their cloud tools. He writes about practical technology strategies at insights.cloudgeeks.com.au.

Read more