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 →