Google Apps Script Guide | Automate Google Sheets

Google Apps Script Guide

Extend Google Sheets with Custom Scripts & Automation

🚀 What Is Google Apps Script?

Google Apps Script (GAS) is a JavaScript-based cloud scripting language that lets you automate, extend, and connect Google Workspace apps like Sheets, Docs, Gmail, Drive, and more.

Key Features

  • Built directly into Google Sheets — no setup required!
  • Cloud-based execution — no servers to manage
  • Free to use with Google Workspace
  • Extensive library of built-in services
Perfect For
  • Automating repetitive spreadsheet tasks
  • Creating custom functions and formulas
  • Building custom menus and interfaces
  • Integrating with external APIs
  • Sending automated emails and notifications

🧩 Getting Started

1 Open Script Editor

Open your Google Sheet and click Extensions → Apps Script

A new tab opens with a code editor and a default function:

function myFunction() { // Your code here }

2 Your First Script

Let's write a simple script that adds text to a cell:

function helloSheet() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.getRange("A1").setValue("Hello, Google Apps Script!"); }

💡 Run It:

Click ▶ Run → Authorize permissions → Check your Sheet → Cell A1 now says "Hello, Google Apps Script!"

🤖 Automation
📊 Custom Functions
📦 Working with Data
🔄 Integrations
🧰 Resources

⚙️ Automating Tasks with Triggers

You can run scripts automatically using triggers.

Example: Run Script Every Time the Sheet Opens

function onOpen() { const ui = SpreadsheetApp.getUi(); ui.alert("Welcome! Your automation is ready."); }

Built-in triggers include:

  • onOpen() → when the Sheet is opened
  • onEdit(e) → when a cell is edited
  • Time-based triggers → run every hour/day/week

⏰ Creating Time Triggers

In Apps Script Editor → Click Triggers (⏰ icon) → Choose your function and schedule (e.g., every 1 hour)

🧠 Creating Custom Menus

Add your own buttons to the Google Sheets menu bar:

function onOpen() { const ui = SpreadsheetApp.getUi(); ui.createMenu("💡 My Tools") .addItem("Say Hello", "helloSheet") .addItem("Send Report", "sendReport") .addToUi(); }

After reload, you'll see a "💡 My Tools" menu on top!

📊 Custom Functions (like built-in formulas)

You can make your own formulas that work just like SUM() or AVERAGE()!

Example: Custom tax calculator

function TAX(amount) { return amount * 0.2; // 20% tax }

Now in your Sheet: =TAX(1000) → returns 200

Example: Convert text to uppercase

function TO_UPPERCASE(text) { return text.toString().toUpperCase(); }

Use in Sheets: =TO_UPPERCASE("hello") → returns "HELLO"

💡 Pro Tip

Custom functions can accept multiple arguments and return arrays for dynamic ranges!

📦 Working with Data

Reading Data

function readData() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const data = sheet.getRange("A1:B5").getValues(); Logger.log(data); }

Writing Data

function writeData() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); sheet.getRange("C1").setValue("Automated Entry"); }

⚡ Advanced Automation Example

Automatically copy new rows from one sheet to another every day:

function copyNewRows() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const source = ss.getSheetByName("Data"); const target = ss.getSheetByName("Archive");const data = source.getRange("A2:B").getValues(); const newData = data.filter(r => r[0]); // remove empty rows target.getRange(target.getLastRow() + 1, 1, newData.length, newData[0].length).setValues(newData); }

Then, schedule it with a time-based trigger → runs daily.

🔄 Integrations & External APIs

📧 Sending Automated Emails

Integrate Gmail to send alerts, reports, or summaries:

function sendReport() { const email = "you@example.com"; const subject = "Daily Sheet Report"; const body = "Your Google Sheet has been updated."; MailApp.sendEmail(email, subject, body); }

Combine this with a daily trigger for full automation!

🌐 Using External APIs

Fetch external data right into Sheets!

function getBitcoinPrice() { const response = UrlFetchApp.fetch("https://api.coindesk.com/v1/bpi/currentprice.json"); const data = JSON.parse(response.getContentText()); const price = data.bpi.USD.rate; SpreadsheetApp.getActiveSheet().getRange("A1").setValue(`BTC Price: $${price}`); }

🧭 Useful Built-In Services

SpreadsheetApp

Access Sheets: getRange(), setValue()

DriveApp

Manage Drive files: createFile(), getFilesByName()

MailApp

Send emails: sendEmail()

UrlFetchApp

Call APIs: fetch(url)

CalendarApp

Manage Google Calendar: createEvent()

🧰 Useful Resources

🧱 Best Practices

  • Use try/catch to handle errors gracefully
  • Comment your code for clarity
  • Store config data in a hidden sheet
  • Avoid using too many time triggers
  • Test functions before scheduling automations
  • Use batch operations when processing large datasets

Ready to Start Automating?

Beginner Projects

Start with simple automations like formatting cells or sending basic notifications.

View Projects →

Intermediate Templates

Ready-to-use templates for data processing, reports, and integrations.

Get Templates →

Advanced Examples

Complex workflows with multiple services and external API integrations.

Explore Examples →

Community Support

Join forums and communities to get help and share your creations.

Join Community →
Scroll to Top