Apps Script is JavaScript that runs inside Google Sheets (and Docs, Drive, Gmail, etc.). It's the automation layer that turns a spreadsheet into a real business tool. No servers to set up. No packages to install. It just works.
Opening the Script Editor
- Open any Google Sheet
- Go to Extensions > Apps Script
- You'll see a code editor with a blank file called
Code.gs
That's it. You're ready to write code.
Your First Script: Read and Write Data
Read a cell value
function readCell() {
const sheet = SpreadsheetApp.getActiveSheet();
const value = sheet.getRange('A1').getValue();
Logger.log(value); // Check via View > Execution log
}
Write to a cell
function writeCell() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('B1').setValue('Hello from Apps Script!');
}
Read an entire range into an array
function readRange() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange('A1:D10').getValues();
// data is a 2D array: data[row][col]
data.forEach(row => {
Logger.log(row[0] + ' - ' + row[1]);
});
}
Write multiple rows at once
function writeRows() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = [
['Alice', 'alice@example.com', 5000],
['Bob', 'bob@example.com', 7500],
['Carol', 'carol@example.com', 3200]
];
sheet.getRange(2, 1, data.length, data[0].length)
.setValues(data);
}
Performance tip
Always use getValues() and setValues() for batch operations. Calling getValue() in a loop (one cell at a time) is 100x slower.
Custom Functions (Use in Formulas)
Write a function, and it becomes a formula you can use in cells, just like =SUM() or =VLOOKUP().
Custom formula: slugify text
/**
* Converts text to a URL slug.
* @param {string} text The text to slugify.
* @return {string} The slugified text.
* @customfunction
*/
function SLUGIFY(text) {
return text.toString().toLowerCase()
.replace(/\s+/g, '-')
.replace(/[^a-z0-9-]/g, '')
.replace(/-+/g, '-')
.replace(/^-|-$/g, '');
}
Now in any cell: =SLUGIFY(A2) and it returns "hello-world" from "Hello World".
Triggers: Run Code Automatically
Time-based triggers
Run a function on a schedule: every minute, hour, day, or week.
Set up a daily trigger at 7 AM
function createDailyTrigger() {
ScriptApp.newTrigger('myDailyFunction')
.timeBased()
.everyDays(1)
.atHour(7)
.create();
}
function myDailyFunction() {
// This runs every day at 7 AM
Logger.log('Daily task executed');
}
onEdit trigger
Run code whenever someone edits the sheet. Great for auto-timestamps, validation, and notifications.
Auto-timestamp when status changes
function onEdit(e) {
const sheet = e.source.getActiveSheet();
const col = e.range.getColumn();
const row = e.range.getRow();
// If column E (Status) is edited
if (col === 5 && row > 1) {
sheet.getRange(row, 6)
.setValue(new Date()); // Timestamp in col F
}
}
onFormSubmit trigger
Run code when a Google Form response is submitted.
Send confirmation email on form submit
function onFormSubmit(e) {
const responses = e.values;
const email = responses[1]; // Column B
const name = responses[2]; // Column C
GmailApp.sendEmail(email,
'Thanks for your submission',
'Hi ' + name + ',\n\n' +
'We received your form. We\'ll be in touch.'
);
}
Send Emails from a Sheet
Send personalized emails to a list
function sendEmails() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange('A2:C' + sheet.getLastRow())
.getValues();
data.forEach(row => {
const name = row[0];
const email = row[1];
const amount = row[2];
if (email) {
GmailApp.sendEmail(email,
'Your Invoice for $' + amount,
'Hi ' + name + ',\n\n' +
'Your invoice for $' + amount +
' is attached.\n\nThanks!'
);
}
});
}
Gmail daily limit
Free Google accounts can send 100 emails/day via Apps Script. Google Workspace accounts get 1,500/day. For bulk email, use a proper email service and connect via API.
Fetch Data from APIs
UrlFetchApp lets you call any REST API from Apps Script.
Pull data from a public API
function fetchWeather() {
const url = 'https://api.open-meteo.com/v1/forecast'
+ '?latitude=40.71&longitude=-74.01'
+ '¤t_weather=true';
const res = UrlFetchApp.fetch(url);
const data = JSON.parse(res.getContentText());
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setValue('Temperature');
sheet.getRange('B1').setValue(
data.current_weather.temperature + '°C'
);
}
Call an authenticated API (Stripe example)
function fetchStripeBalance() {
const key = PropertiesService.getScriptProperties()
.getProperty('STRIPE_KEY');
const res = UrlFetchApp.fetch(
'https://api.stripe.com/v1/balance', {
headers: { 'Authorization': 'Bearer ' + key }
});
const balance = JSON.parse(res.getContentText());
const available = balance.available[0].amount / 100;
SpreadsheetApp.getActiveSheet()
.getRange('B1').setValue(available);
}
Storing API keys securely
Never hardcode API keys. Use PropertiesService.getScriptProperties() to store them. Go to Project Settings > Script Properties to add key-value pairs.
Create Custom Menus
Add a custom menu to the toolbar
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('My Tools')
.addItem('Send Invoices', 'sendEmails')
.addItem('Refresh Data', 'fetchStripeBalance')
.addSeparator()
.addItem('Generate Report', 'generateReport')
.addToUi();
}
Now your team sees a "My Tools" menu in the toolbar with buttons for your custom functions. No coding required for end users.
Generate PDFs from Sheets
Export a sheet as PDF
function exportPDF() {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName('Invoice');
const url = ss.getUrl()
.replace(/edit.*$/, '')
+ 'export?format=pdf'
+ '&gid=' + sheet.getSheetId()
+ '&portrait=true'
+ '&fitw=true';
const token = ScriptApp.getOAuthToken();
const pdf = UrlFetchApp.fetch(url, {
headers: { 'Authorization': 'Bearer ' + token }
}).getBlob().setName('Invoice.pdf');
// Save to Drive
DriveApp.createFile(pdf);
// Or email it
GmailApp.sendEmail('client@example.com',
'Your Invoice',
'Please find your invoice attached.',
{ attachments: [pdf] }
);
}
Common Patterns
- Daily data refresh: Time trigger + UrlFetchApp to pull API data every morning
- Auto-email on status change: onEdit trigger checks column, sends email
- Form response handler: onFormSubmit sends confirmation + adds to CRM tab
- Report generator: Custom menu button compiles data, creates PDF, emails it
- Data cleanup: Loop through rows, standardize formats, remove duplicates
Apps Script turns Google Sheets from a spreadsheet into a platform. Free, built-in, and powerful enough to replace most SaaS automation tools.