Welcome to Google Apps Script: 140 Common Coding Examples, a comprehensive guide designed to equip you with essential tools and techniques for automating and enhancing Google Workspace. This book is packed with practical, easy-to-understand code snippets that streamline workflows, simplify data handling, and add powerful functionality to Google Docs, Sheets, Forms, and more.
Whether you’re a beginner or a seasoned developer, this book offers a wide range of examples to fit your needs. You’ll explore a variety of solutions, from automating repetitive tasks to creating custom functions, building user interfaces, integrating with APIs, and much more. Each example is crafted to be directly usable, making it easy to incorporate into your projects or customize for specific needs.
Apps Script has opened new doors in automation, bridging the gap between productivity and coding. This book aims to make this process accessible, practical, and, most importantly, efficient. So dive in, experiment, and see how each example can transform the way you work with Google Workspace!

Reading and Writing Data to Google Sheets
Read data from a sheet and write data to specific cells.
function readWriteSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);
var data = sheet.getRange(‘A1:B10’).getValues(); // Read data
sheet.getRange(‘C1’).setValue(‘New Value’); // Write data
}
Sending Emails via Gmail
Send an email with a custom subject and body.
function sendEmail() {
GmailApp.sendEmail(‘recipient@example.com’, ‘Subject’, ‘Email body text’);
}
Creating Custom Menus in Google Sheets
Add a custom menu item that triggers a function when clicked.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Run Function’, ‘myFunction’)
.addToUi();
}
Setting Up Triggers
Create a time-based trigger to run a function every hour.
function createTimeTrigger() {
ScriptApp.newTrigger(‘myFunction’)
.timeBased()
.everyHours(1)
.create();
}
Fetching Data from URLs (UrlFetchApp)
Fetch JSON data from an external API.
function fetchData() {
var response = UrlFetchApp.fetch(‘https://api.example.com/data’);
var data = JSON.parse(response.getContentText());
Logger.log(data);
}
Parsing and Manipulating JSON Data
Parse JSON data and access its properties.
function parseJson() {
var jsonString = ‘{“name”:”Alice”,”age”:30}’;
var obj = JSON.parse(jsonString);
Logger.log(obj.name); // Outputs ‘Alice’
}
Creating and Managing Google Calendar Events
Create a calendar event with a specified title and time.
function createEvent() {
var calendar = CalendarApp.getDefaultCalendar();
calendar.createEvent(‘Meeting’, new Date(), new Date());
}
Sending Emails with Attachments
Send an email with a file attachment from Google Drive.
function sendEmailWithAttachment() {
var file = DriveApp.getFileById(‘FILE_ID’);
MailApp.sendEmail({
to: ‘recipient@example.com’,
subject: ‘Subject’,
body: ‘Email body text’,
attachments: [file.getAs(MimeType.PDF)]
});
}
Working with Google Drive Files and Folders
List all files in a specific Google Drive folder.
function listFilesInFolder() {
var folder = DriveApp.getFolderById(‘FOLDER_ID’);
var files = folder.getFiles();
while (files.hasNext()) {
var file = files.next();
Logger.log(file.getName());
}
}
Creating Dialogs and Sidebars
Display a custom HTML dialog in Google Docs.
function showDialog() {
var html = HtmlService.createHtmlOutput(‘<p>Hello, world!</p>’);
DocumentApp.getUi().showModalDialog(html, ‘My Dialog’);
}
Batch Operations to Improve Performance
Use arrays to read and write data efficiently in bulk.
function batchWrite() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = [[‘A1’, ‘B1’], [‘A2’, ‘B2’]];
sheet.getRange(1, 1, 2, 2).setValues(data);
}
Manipulating Cell Formatting
Set the background color of a range of cells.
function formatCells() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘A1:A10’).setBackground(‘yellow’);
}
Creating Web Apps with Google Apps Script
Deploy a simple web app that returns HTML content.
function doGet() {
return HtmlService.createHtmlOutput(‘<h1>Hello, world!</h1>’);
}
Importing Data from External Sources
Import CSV data into Google Sheets from a URL.
function importCsv() {
var url = ‘https://example.com/data.csv’;
var response = UrlFetchApp.fetch(url);
var csv = response.getContentText();
var data = Utilities.parseCsv(csv);
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Using Logger for Debugging
Log variable values to help debug code.
function debugFunction() {
var value = ‘Test’;
Logger.log(‘Value is: ‘ + value);
}
Copying Data Between Sheets
Copy data from one sheet to another within the same spreadsheet.
function copySheetData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sourceSheet = ss.getSheetByName(‘Sheet1’);
var targetSheet = ss.getSheetByName(‘Sheet2’);
var data = sourceSheet.getDataRange().getValues();
targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Converting Google Docs to PDF
Save a Google Doc as a PDF file in Google Drive.
function docToPdf() {
var doc = DocumentApp.getActiveDocument();
var blob = doc.getAs(MimeType.PDF);
DriveApp.createFile(blob).setName(doc.getName() + ‘.pdf’);
}
Working with Form Responses
Access and log responses from a Google Form.
function getFormResponses() {
var form = FormApp.openById(‘FORM_ID’);
var responses = form.getResponses();
responses.forEach(function(response) {
var itemResponses = response.getItemResponses();
itemResponses.forEach(function(itemResponse) {
Logger.log(‘Question: ‘ + itemResponse.getItem().getTitle());
Logger.log(‘Response: ‘ + itemResponse.getResponse());
});
});
}
Error Handling in Apps Script
Use try-catch blocks to handle errors gracefully.
function errorHandling() {
try {
// Code that might throw an error
var result = someFunction();
} catch (e) {
Logger.log(‘An error occurred: ‘ + e.message);
}
}
Creating Charts in Google Sheets
Add a chart to a sheet based on a data range.
function addChart() {
var sheet = SpreadsheetApp.getActiveSheet();
var chartBuilder = sheet.newChart();
chartBuilder.addRange(sheet.getRange(‘A1:B10’));
chartBuilder.setChartType(Charts.ChartType.LINE);
chartBuilder.setPosition(5, 5, 0, 0);
sheet.insertChart(chartBuilder.build());
}
Appending Data to Google Sheets
Add new rows of data to the end of a sheet.
function appendData() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = [‘New Data 1’, ‘New Data 2’];
sheet.appendRow(data);
}
Sorting Data in a Sheet
Sort data in a sheet based on a specific column.
function sortSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.sort(1); // Sorts by the first column
}
Filtering Data in a Sheet
Apply a filter to display only rows that meet certain criteria.
function applyFilter() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
range.createFilter();
var filter = range.getFilter();
filter.setColumnFilterCriteria(1, SpreadsheetApp.newFilterCriteria().whenTextContains(‘Keyword’));
}
Protecting Sheets or Ranges
Protect a sheet or a specific range from editing.
function protectRange() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(‘A1:A10’);
var protection = range.protect().setDescription(‘Protected Range’);
protection.removeEditors(protection.getEditors());
}
Sending Emails with Inline Images
Send an email that includes an inline image.
function sendEmailWithInlineImage() {
var file = DriveApp.getFileById(‘IMAGE_FILE_ID’);
var blob = file.getBlob();
var inlineImages = {};
inlineImages[blob.getName()] = blob;
MailApp.sendEmail({
to: ‘recipient@example.com’,
subject: ‘Subject’,
htmlBody: ‘Here is an image:<br/><img src=”cid:’ + blob.getName() + ‘”>’,
inlineImages: inlineImages
});
}
Creating and Managing Google Docs
Create a new Google Doc and write text into it.
function createGoogleDoc() {
var doc = DocumentApp.create(‘New Document’);
var body = doc.getBody();
body.appendParagraph(‘Hello, world!’);
}
Creating and Managing Google Slides
Create a new Google Slides presentation and add a slide.
function createGoogleSlide() {
var presentation = SlidesApp.create(‘New Presentation’);
var slide = presentation.appendSlide(SlidesApp.PredefinedLayout.BLANK);
slide.insertTextBox(‘Hello, world!’);
}
Generating PDFs from Google Sheets
Export a Google Sheet as a PDF file.
function sheetToPdf() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getId();
var url = ‘https://docs.google.com/spreadsheets/d/’ + sheetId + ‘/export?format=pdf’;
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: { ‘Authorization’: ‘Bearer ‘ + token }
});
var blob = response.getBlob().setName(ss.getName() + ‘.pdf’);
DriveApp.createFile(blob);
}
Creating Custom Functions for Google Sheets
Create a custom function that can be used within Google Sheets.
function DOUBLE(number) {
return number * 2;
}
Working with Named Ranges
Get and set values using named ranges.
function useNamedRange() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRangeByName(‘MyNamedRange’);
var value = range.getValue();
Logger.log(value);
}
Using SpreadsheetApp.flush()
Ensure that pending changes are applied immediately.
function flushChanges() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘A1’).setValue(‘Processing…’);
SpreadsheetApp.flush();
// Perform time-consuming operations
sheet.getRange(‘A1’).setValue(‘Done’);
}
Formatting Numbers and Dates in Sheets
Apply number formatting to a range.
function formatNumbers() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘B1:B10’).setNumberFormat(‘#,##0.00’);
}
Using onEdit Triggers
Automatically run code when a cell is edited.
function onEdit(e) {
var range = e.range;
if (range.getA1Notation() == ‘A1’) {
SpreadsheetApp.getActiveSheet().getRange(‘B1’).setValue(‘A1 was edited’);
}
}
Sending Notifications
Send an email notification when a condition is met.
function sendNotification() {
var sheet = SpreadsheetApp.getActiveSheet();
var value = sheet.getRange(‘A1’).getValue();
if (value > 100) {
MailApp.sendEmail(‘recipient@example.com’, ‘Value Exceeded’, ‘The value in A1 is over 100.’);
}
}
Reading Data from Another Spreadsheet
Access data from a different spreadsheet file.
function readOtherSpreadsheet() {
var ss = SpreadsheetApp.openById(‘OTHER_SPREADSHEET_ID’);
var sheet = ss.getSheetByName(‘Sheet1’);
var data = sheet.getRange(‘A1:B10’).getValues();
Logger.log(data);
}
Creating Forms Programmatically
Create a Google Form using Apps Script.
function createForm() {
var form = FormApp.create(‘New Form’);
form.addTextItem().setTitle(‘What is your name?’);
form.addMultipleChoiceItem().setTitle(‘Choose an option’).setChoices([
form.createChoice(‘Option 1’),
form.createChoice(‘Option 2’)
]);
}
Updating Charts
Refresh charts after data changes.
function refreshCharts() {
var sheet = SpreadsheetApp.getActiveSheet();
var charts = sheet.getCharts();
charts.forEach(function(chart) {
sheet.updateChart(chart);
});
}
Moving or Renaming Files in Drive
Move a file to a different folder and rename it.
function moveAndRenameFile() {
var file = DriveApp.getFileById(‘FILE_ID’);
var folder = DriveApp.getFolderById(‘FOLDER_ID’);
file.moveTo(folder);
file.setName(‘New File Name’);
}
Setting Cell Validation
Add data validation to cells (e.g., dropdown lists).
function setDataValidation() {
var sheet = SpreadsheetApp.getActiveSheet();
var cell = sheet.getRange(‘A1’);
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList([‘Option 1’, ‘Option 2’], true)
.build();
cell.setDataValidation(rule);
}
Creating and Updating Pivot Tables
Generate a pivot table from data in a sheet.
function createPivotTable() {
var sheet = SpreadsheetApp.getActiveSheet();
var pivotTableParams = {
source: sheet.getRange(‘A1:C10’),
rows: [{ sourceColumnOffset: 0, showTotals: true }],
columns: [{ sourceColumnOffset: 1, showTotals: true }],
values: [{ summarizeFunction: ‘SUM’, sourceColumnOffset: 2 }]
};
sheet.addPivotTable(pivotTableParams);
}
Using PropertiesService
Store and retrieve script properties.
function propertiesExample() {
var properties = PropertiesService.getScriptProperties();
properties.setProperty(‘key’, ‘value’);
var value = properties.getProperty(‘key’);
Logger.log(value);
}
Parsing XML Data
Parse XML content from a URL.
function parseXml() {
var url = ‘https://www.example.com/data.xml’;
var response = UrlFetchApp.fetch(url);
var document = XmlService.parse(response.getContentText());
var root = document.getRootElement();
Logger.log(root.getName());
}
Adding Custom Images to Sheets
Insert an image into a cell.
function insertImage() {
var sheet = SpreadsheetApp.getActiveSheet();
var imageUrl = ‘https://www.example.com/image.png’;
sheet.getRange(‘A1’).setFormula(‘=IMAGE(“‘ + imageUrl + ‘”)’);
}
Hiding and Showing Sheets or Rows
Hide or unhide specific sheets or rows.
function hideSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);
sheet.hideSheet();
}
function unhideSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);
sheet.showSheet();
}
Conditional Formatting
Apply conditional formatting to a range.
function setConditionalFormatting() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(‘A1:A10’);
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberGreaterThan(100)
.setBackground(‘red’)
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
Importing Data from Another Spreadsheet
Import data using the IMPORTRANGE function.
function importRange() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘A1’).setFormula(‘=IMPORTRANGE(“SPREADSHEET_ID”, “Sheet1!A1:B10”)’);
}
Interacting with Google Maps
Get the distance between two locations.
function getDistance() {
var origin = ‘New York, NY’;
var destination = ‘Los Angeles, CA’;
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.getDirections();
var distance = directions.routes[0].legs[0].distance.text;
Logger.log(‘Distance: ‘ + distance);
}
Using Session Service
Get information about the current user’s session.
function sessionExample() {
var email = Session.getActiveUser().getEmail();
Logger.log(‘User email: ‘ + email);
}
Using JDBC to Connect to External Databases
Connect to a MySQL database and run a query.
function queryDatabase() {
var conn = Jdbc.getConnection(‘jdbc:mysql://hostname:port/dbname’, ‘username’, ‘password’);
var stmt = conn.createStatement();
var results = stmt.executeQuery(‘SELECT * FROM table’);
while (results.next()) {
var value = results.getString(1);
Logger.log(value);
}
results.close();
stmt.close();
conn.close();
}
Using OAuth for External APIs
Authenticate with an external API using OAuth2.
// Requires OAuth2 library: https://github.com/googleworkspace/apps-script-oauth2
function oauthExample() {
var service = getService();
if (service.hasAccess()) {
var url = ‘https://api.example.com/data’;
var response = UrlFetchApp.fetch(url, {
headers: { Authorization: ‘Bearer ‘ + service.getAccessToken() }
});
Logger.log(response.getContentText());
} else {
Logger.log(service.getAuthorizationUrl());
}
}
function getService() {
return OAuth2.createService(‘example’)
.setAuthorizationBaseUrl(‘https://accounts.example.com/o/oauth2/auth’)
.setTokenUrl(‘https://accounts.example.com/o/oauth2/token’)
.setClientId(‘YOUR_CLIENT_ID’)
.setClientSecret(‘YOUR_CLIENT_SECRET’)
.setCallbackFunction(‘authCallback’)
.setPropertyStore(PropertiesService.getUserProperties());
}
Translating Text Using LanguageApp
Translate text from one language to another.
function translateText() {
var text = ‘Hello, world!’;
var translated = LanguageApp.translate(text, ‘en’, ‘es’);
Logger.log(translated); // Outputs ‘¡Hola, mundo!’
}
Performing Calculations in Scripts
Compute mathematical operations and write results to a sheet.
function calculate() {
var sheet = SpreadsheetApp.getActiveSheet();
var a = 5;
var b = 10;
var sum = a + b;
sheet.getRange(‘A1’).setValue(sum);
}
Using Utilities.sleep()
Pause script execution for a specified time.
function pauseExecution() {
Logger.log(‘Start’);
Utilities.sleep(5000); // Sleep for 5 seconds
Logger.log(‘End’);
}
Converting Currencies
Fetch current exchange rates and convert currencies.
function convertCurrency() {
var amount = 100;
var from = ‘USD’;
var to = ‘EUR’;
var rate = FinanceApp.getExchangeRate(from + to);
var converted = amount * rate;
Logger.log(converted);
}
Sending Calendar Invites
Create a calendar event and invite attendees.
function sendCalendarInvite() {
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent(‘Meeting’, new Date(), new Date(), {
guests: ‘attendee@example.com’,
sendInvites: true
});
}
Creating Add-ons
Set up a basic structure for a Google Workspace Add-on.
// In Code.gs
function buildAddOn(e) {
return CardService.newCardBuilder()
.setHeader(CardService.newCardHeader().setTitle(‘My Add-on’))
.build();
}
// In manifest file, set the appropriate add-on properties
Using Google Analytics API
Retrieve data from Google Analytics.
function getAnalyticsData() {
var profileId = ‘YOUR_PROFILE_ID’;
var results = Analytics.Data.Ga.get(
‘ga:’ + profileId,
‘7daysAgo’,
‘today’,
‘ga:sessions’,
{ ‘dimensions’: ‘ga:source,ga:medium’ }
);
Logger.log(results);
}
Implementing RESTful APIs
Set up a web app that responds to HTTP requests.
function doGet(e) {
var action = e.parameter.action;
if (action == ‘getData’) {
var data = { message: ‘Hello, world!’ };
return ContentService.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
}
Customizing the Editor UI
Add custom dialogs and menus to the script editor.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(‘Custom Menu’)
.addItem(‘Show Dialog’, ‘showCustomDialog’)
.addToUi();
}
function showCustomDialog() {
var html = HtmlService.createHtmlOutput(‘<p>This is a custom dialog.</p>’);
SpreadsheetApp.getUi().showModalDialog(html, ‘Custom Dialog’);
}
Migrating from VBA to Apps Script
Replicate simple VBA macros in Apps Script.
function simpleMacro() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(‘A1’);
var value = range.getValue();
range.setValue(value * 2);
}
Interacting with Other Google Cloud Services
Use Cloud Storage to store and retrieve files.
function useCloudStorage() {
var bucket = ‘your-bucket-name’;
var file = CloudStorage.getDefaultBucket().createFile(‘hello.txt’, ‘Hello, world!’);
Logger.log(‘File URL: ‘ + file.getMediaLink());
}
Sending Push Notifications
Send notifications to a device using Firebase Cloud Messaging.
function sendPushNotification() {
var url = ‘https://fcm.googleapis.com/fcm/send’;
var payload = {
‘to’: ‘DEVICE_TOKEN’,
‘notification’: {
‘title’: ‘Test Notification’,
‘body’: ‘This is a test message.’
}
};
var options = {
‘method’: ‘post’,
‘contentType’: ‘application/json’,
‘payload’: JSON.stringify(payload),
‘headers’: {
‘Authorization’: ‘key=SERVER_KEY’
}
};
UrlFetchApp.fetch(url, options);
}
Integrating with Slack
Send messages to a Slack channel via webhook.
function sendToSlack() {
var url = ‘https://hooks.slack.com/services/YOUR/WEBHOOK/URL’;
var payload = {
‘text’: ‘Hello, Slack!’
};
var options = {
‘method’: ‘post’,
‘contentType’: ‘application/json’,
‘payload’: JSON.stringify(payload)
};
UrlFetchApp.fetch(url, options);
}
Implementing Pagination
Retrieve data in chunks or pages.
function getPagedData() {
var pageSize = 50;
var pageToken = null;
do {
var response = SomeApi.getData({ pageSize: pageSize, pageToken: pageToken });
processData(response.items);
pageToken = response.nextPageToken;
} while (pageToken);
}
Reading Email Attachments
Access and save attachments from Gmail messages.
function getEmailAttachments() {
var threads = GmailApp.search(‘has:attachment’);
threads.forEach(function(thread) {
var messages = thread.getMessages();
messages.forEach(function(message) {
var attachments = message.getAttachments();
attachments.forEach(function(attachment) {
DriveApp.createFile(attachment);
});
});
});
}
Setting Time Zones
Adjust script and spreadsheet time zones.
function setTimeZone() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setSpreadsheetTimeZone(‘America/New_York’);
ScriptApp.getScriptProperties().setProperty(‘timeZone’, ‘America/New_York’);
}
Copying a Spreadsheet
Make a copy of a spreadsheet file.
function copySpreadsheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var copy = ss.copy(‘Copy of ‘ + ss.getName());
}
Setting Up Webhooks
Receive POST requests to trigger script functions.
function doPost(e) {
var data = JSON.parse(e.postData.contents);
// Process data
return ContentService.createTextOutput(‘Success’);
}
Integrating with Third-Party APIs
Fetch data from an external API using API keys.
function fetchFromApi() {
var apiKey = ‘YOUR_API_KEY’;
var url = ‘https://api.example.com/data?api_key=’ + apiKey;
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
Logger.log(data);
}
Automating Data Backups
Regularly back up data to another location.
function backupData() {
var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Data’);
var targetSheet = SpreadsheetApp.openById(‘TARGET_SPREADSHEET_ID’).getSheetByName(‘Backup’);
var data = sourceSheet.getDataRange().getValues();
targetSheet.clearContents();
targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Encrypting Data
Encrypt and decrypt data using Utilities.
function encryptData() {
var text = ‘Sensitive Information’;
var key = Utilities.base64Encode(‘your-secret-key’);
var encrypted = Utilities.base64Encode(Utilities.computeHmacSha256Signature(text, key));
Logger.log(encrypted);
}
Using the Advanced Drive Service
Access advanced features of Google Drive.
function advancedDriveExample() {
var fileId = ‘FILE_ID’;
var permissions = Drive.Permissions.list(fileId);
Logger.log(permissions.items);
}
Using the Advanced Sheets Service
Use advanced features like batch updates.
function advancedSheetsExample() {
var spreadsheetId = ‘SPREADSHEET_ID’;
var requests = [{
updateCells: {
range: {
sheetId: 0,
startRowIndex: 0,
startColumnIndex: 0,
endRowIndex: 1,
endColumnIndex: 1
},
rows: [{
values: [{
userEnteredValue: { stringValue: ‘Hello’ }
}]
}],
fields: ‘userEnteredValue’
}
}];
Sheets.Spreadsheets.batchUpdate({ requests: requests }, spreadsheetId);
}
Implementing OAuth2 Authentication
Authenticate users with OAuth2 for web apps.
function doGet(e) {
var service = getOAuthService();
if (!service.hasAccess()) {
var authorizationUrl = service.getAuthorizationUrl();
var html = HtmlService.createHtmlOutput(‘<a href=”‘ + authorizationUrl + ‘”>Authorize</a>’);
return html;
}
// Proceed with authorized actions
}
function getOAuthService() {
// Similar to previous OAuth example
}
Parsing HTML Content
Extract data from HTML using regex or parsing libraries.
function parseHtml() {
var url = ‘https://www.example.com’;
var response = UrlFetchApp.fetch(url);
var content = response.getContentText();
var regex = /<title>(.*?)<\/title>/;
var matches = content.match(regex);
if (matches && matches[1]) {
Logger.log(‘Page title: ‘ + matches[1]);
}
}
Error Logging to a Spreadsheet
Log errors to a dedicated sheet for monitoring.
function logError(error) {
var ss = SpreadsheetApp.openById(‘ERROR_LOG_SPREADSHEET_ID’);
var sheet = ss.getSheetByName(‘Errors’);
sheet.appendRow([new Date(), error.message, error.stack]);
}
function safeFunction() {
try {
// Code that might throw an error
} catch (e) {
logError(e);
}
}
Version Control with Apps Script
Manage versions of your script project.
function createVersion() {
var description = ‘Version description’;
ScriptApp.newVersion(description);
}
Using the ContentService for JSON Responses
Return JSON data from a web app.
function doGet(e) {
var data = { message: ‘Hello, JSON!’ };
return ContentService.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
Setting Up CORS in Web Apps
Allow cross-origin requests to your web app.
function doGet(e) {
var data = { message: ‘CORS enabled’ };
return ContentService.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON)
.setHeader(‘Access-Control-Allow-Origin’, ‘*’);
}
Implementing Authentication in Web Apps
Protect your web app endpoints.
function doGet(e) {
var token = e.parameter.token;
if (token !== ‘EXPECTED_TOKEN’) {
return ContentService.createTextOutput(‘Unauthorized’).setMimeType(ContentService.MimeType.TEXT);
}
// Authorized actions
}
Using the YouTube API
Retrieve data from YouTube using the API.
function getYouTubeVideos() {
var results = YouTube.Search.list(‘id,snippet’, { q: ‘Google Apps Script’, maxResults: 5 });
results.items.forEach(function(item) {
Logger.log(item.snippet.title);
});
}
Using Cloud SQL
Connect to a Cloud SQL database from Apps Script.
function connectToCloudSql() {
var conn = Jdbc.getCloudSqlConnection(‘instanceConnectionName’, ‘username’, ‘password’);
var stmt = conn.createStatement();
var results = stmt.executeQuery(‘SELECT * FROM table’);
while (results.next()) {
Logger.log(results.getString(1));
}
results.close();
stmt.close();
conn.close();
}
Processing Large Datasets Efficiently
Use batch operations to handle large amounts of data.
function processLargeDataset() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
data.forEach(function(row) {
// Process each row
});
// Write back updates in bulk
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Implementing Multi-Step Workflows
Chain multiple functions together for complex tasks.
function mainWorkflow() {
var data = stepOne();
data = stepTwo(data);
stepThree(data);
}
function stepOne() { /*…*/ }
function stepTwo(data) { /*…*/ }
function stepThree(data) { /*…*/ }
Interacting with Google Photos API
Access Google Photos using Apps Script.
function listAlbums() {
var url = ‘https://photoslibrary.googleapis.com/v1/albums’;
var response = UrlFetchApp.fetch(url, {
headers: { ‘Authorization’: ‘Bearer ‘ + ScriptApp.getOAuthToken() }
});
var data = JSON.parse(response.getContentText());
Logger.log(data.albums);
}
Using the Vision API
Analyze images using Google’s Vision API.
function analyzeImage() {
var imageUrl = ‘https://example.com/image.jpg’;
var vision = GoogleAppsScript.CloudVision.ImageAnnotatorClient();
var results = vision.labelDetection(imageUrl);
Logger.log(results);
}
Using Machine Learning APIs
Integrate machine learning models into your scripts.
function predict() {
var model = ‘projects/your-project/models/your-model’;
var instances = [{ ‘input’: ‘Sample data’ }];
var prediction = MlModel.Predict.predict(model, { ‘instances’: instances });
Logger.log(prediction);
}
Using the Advanced Gmail Service
Access advanced Gmail features.
function advancedGmailExample() {
var message = Gmail.Users.Messages.get(‘me’, ‘MESSAGE_ID’);
Logger.log(message.snippet);
}
Implementing a Custom Sidebar
Add a custom sidebar to Google Docs or Sheets.
function showSidebar() {
var html = HtmlService.createHtmlOutput(‘<p>Custom Sidebar Content</p>’);
SpreadsheetApp.getUi().showSidebar(html);
}
Creating Custom Charts
Generate charts programmatically with Chart API.
function createCustomChart() {
var chart = Charts.newPieChart()
.setTitle(‘My Chart’)
.setDataTable([
[‘Category’, ‘Value’],
[‘A’, 60],
[‘B’, 40]
])
.build();
var blob = chart.getAs(‘image/png’);
DriveApp.createFile(blob);
}
Using HTML Templates
Use templated HTML for dynamic content in web apps.
function doGet() {
var template = HtmlService.createTemplateFromFile(‘Index’);
template.data = { message: ‘Hello, world!’ };
return template.evaluate();
}
Working with Google Slides
Create a new Google Slides presentation and add a slide.
function createPresentation() {
var presentation = SlidesApp.create(‘New Presentation’);
var slide = presentation.appendSlide(SlidesApp.PredefinedLayout.BLANK);
}
Using Regular Expressions
Extract numbers from a string using regex.
function extractNumbers() {
var str = ‘Order number is 12345’;
var match = str.match(/\d+/);
Logger.log(match[0]); // Outputs ‘12345’
}
Sending Calendar Invites via Email
Send a calendar event invitation via email.
function sendCalendarInvite() {
var calendar = CalendarApp.getDefaultCalendar();
var event = calendar.createEvent(‘Meeting’, new Date(), new Date());
MailApp.sendEmail(‘recipient@example.com’, ‘Invitation’, ‘Please join the meeting.’, {
attachments: [event.getAs(MimeType.ICAL)]
});
}
Generating PDFs from Sheets
Export a specific sheet as a PDF.
function sheetToPdf() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetId = ss.getSheetByName(‘Sheet1’).getSheetId();
var url = ss.getUrl().replace(/edit$/, ”) + ‘export?format=pdf&gid=’ + sheetId;
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: { ‘Authorization’: ‘Bearer ‘ + token }
});
var blob = response.getBlob().setName(‘Sheet1.pdf’);
DriveApp.createFile(blob);
}
Adding Notes to Cells
Add a note to a specific cell in Google Sheets.
function addCellNote() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘A1’).setNote(‘This is a note.’);
}
Protecting Ranges and Sheets
Protect a range of cells so that only specific users can edit them.
function protectRange() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(‘A1:A10’);
var protection = range.protect().setDescription(‘Protected Range’);
protection.addEditor(‘user@example.com’);
protection.removeEditors(protection.getEditors());
}
Creating Filters and Filter Views
Apply a filter to a range in Google Sheets.
function applyFilter() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
var criteria = SpreadsheetApp.newFilterCriteria()
.whenTextContains(‘Apple’)
.build();
var filter = sheet.getFilter() || range.createFilter();
filter.setColumnFilterCriteria(1, criteria);
}
Adding Conditional Formatting
Apply conditional formatting to a range based on certain criteria.
function addConditionalFormatting() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(‘A1:A10’);
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberGreaterThan(100)
.setBackground(‘#FF0000’)
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
Retrieving User Information
Get the email address of the current user.
function getUserEmail() {
var email = Session.getActiveUser().getEmail();
Logger.log(email);
}
Formatting Dates and Times
Format dates according to a specific pattern.
function formatDateExample() {
var date = new Date();
var formattedDate = Utilities.formatDate(date, ‘GMT’, ‘yyyy-MM-dd HH:mm:ss’);
Logger.log(formattedDate);
}
Creating Custom HTML Dialogs
Display a custom HTML dialog in Google Sheets.
function showCustomDialog() {
var html = HtmlService.createHtmlOutput(‘<p>Enter your name:</p><input id=”name”><button onclick=”google.script.host.close()”>Close</button>’);
SpreadsheetApp.getUi().showModalDialog(html, ‘Custom Dialog’);
}
Appending Rows to Sheets
Add a new row at the end of the sheet.
function appendRow() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow([‘Data1’, ‘Data2’, ‘Data3’]);
}
Deleting Rows or Columns
Delete specific rows or columns from a sheet.
function deleteRow() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.deleteRow(5); // Deletes the 5th row
}
Hiding and Unhiding Sheets
Hide or show sheets within a spreadsheet.
function hideSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);
sheet.hideSheet();
}
function unhideSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);
sheet.showSheet();
}
Sending Slack Notifications
Post a message to a Slack channel using a webhook.
function sendSlackMessage() {
var url = ‘https://hooks.slack.com/services/your/webhook/url’;
var payload = JSON.stringify({ text: ‘Hello from Apps Script!’ });
var options = {
method: ‘post’,
contentType: ‘application/json’,
payload: payload
};
UrlFetchApp.fetch(url, options);
}
Creating and Deleting Drive Files
Create a text file and delete it afterward.
function createAndDeleteFile() {
var file = DriveApp.createFile(‘Sample.txt’, ‘Hello, world!’);
Logger.log(‘File created: ‘ + file.getName());
file.setTrashed(true); // Moves file to trash
}
Exporting Sheets to Excel
Save a Google Sheet as an Excel file in Drive.
function exportSheetToExcel() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var url = ss.getUrl().replace(/edit$/, ”) + ‘export?format=xlsx’;
var options = { headers: { ‘Authorization’: ‘Bearer ‘ + ScriptApp.getOAuthToken() } };
var response = UrlFetchApp.fetch(url, options);
var blob = response.getBlob().setName(ss.getName() + ‘.xlsx’);
DriveApp.createFile(blob);
}
Using the Properties Service
Store and retrieve script-level properties.
function setProperty() {
PropertiesService.getScriptProperties().setProperty(‘API_KEY’, ‘your_api_key’);
}
function getProperty() {
var apiKey = PropertiesService.getScriptProperties().getProperty(‘API_KEY’);
Logger.log(apiKey);
}
Creating Google Forms Programmatically
Create a new Google Form and add a question.
function createForm() {
var form = FormApp.create(‘New Form’);
form.addTextItem().setTitle(‘What is your name?’);
}
Using Spreadsheet Formulas via Apps Script
Set a formula in a cell using Apps Script.
function setFormula() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘A1’).setFormula(‘=SUM(B1:B10)’);
}
Sending Emails Based on Sheet Data
Send personalized emails using data from a sheet.
function sendPersonalizedEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
data.forEach(function(row) {
var email = row[0];
var name = row[1];
MailApp.sendEmail(email, ‘Hello ‘ + name, ‘This is a personalized message.’);
});
}
Manipulating Pivot Tables
Create a pivot table from sheet data.
function createPivotTable() {
var sheet = SpreadsheetApp.getActiveSheet();
var pivotTable = sheet.getRange(‘A1:D10’).createPivotTable(sheet.getRange(‘F1’));
var pivotGroup = pivotTable.addRowGroup(1);
pivotGroup.addValue(3, SpreadsheetApp.PivotTableSummarizeFunction.SUM);
}
Integrating with External APIs
Call an external API with authentication.
function callExternalApi() {
var url = ‘https://api.example.com/data’;
var options = {
method: ‘get’,
headers: { ‘Authorization’: ‘Bearer your_token’ }
};
var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());
Logger.log(data);
}
Creating Drop-down Lists in Cells
Set data validation to create a drop-down list.
function createDropdown() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(‘A1’);
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList([‘Option1’, ‘Option2’, ‘Option3’])
.build();
range.setDataValidation(rule);
}
Importing XML Data
Fetch and parse XML data from a URL.
function importXmlData() {
var url = ‘https://www.example.com/data.xml’;
var response = UrlFetchApp.fetch(url);
var document = XmlService.parse(response.getContentText());
var root = document.getRootElement();
Logger.log(root.getName());
}
Scheduling Functions with Time-driven Triggers
Set up a trigger to run a function daily.
function createDailyTrigger() {
ScriptApp.newTrigger(‘myDailyFunction’)
.timeBased()
.everyDays(1)
.atHour(9)
.create();
}
Parsing CSV Files from Drive
Read and parse a CSV file stored in Google Drive.
function parseCsvFromDrive() {
var file = DriveApp.getFileById(‘FILE_ID’);
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
Logger.log(csvData);
}
Sorting Data in Sheets
Sort a range based on a specific column.
function sortData() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getDataRange();
range.sort({ column: 2, ascending: true });
}
Using Cache Service
Store and retrieve temporary data using Cache Service.
function setCache() {
var cache = CacheService.getScriptCache();
cache.put(‘key’, ‘value’, 600); // Expires in 10 minutes
}
function getCache() {
var cache = CacheService.getScriptCache();
var value = cache.get(‘key’);
Logger.log(value);
}
Adding Images to Sheets
Insert an image into a cell.
function insertImage() {
var sheet = SpreadsheetApp.getActiveSheet();
var url = ‘https://www.example.com/image.jpg’;
sheet.getRange(‘A1’).setFormula(‘=IMAGE(“‘ + url + ‘”)’);
}
Using Lock Service
Prevent concurrent execution of a script.
function lockedFunction() {
var lock = LockService.getScriptLock();
lock.waitLock(10000); // Wait up to 10 seconds
try {
// Critical section
} finally {
lock.releaseLock();
}
}
Getting Spreadsheet Metadata
Retrieve the spreadsheet’s URL and ID.
function getSpreadsheetInfo() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
Logger.log(‘URL: ‘ + ss.getUrl());
Logger.log(‘ID: ‘ + ss.getId());
}
Sending SMS via Twilio API
Send an SMS message using Twilio.
function sendSms() {
var accountSid = ‘your_account_sid’;
var authToken = ‘your_auth_token’;
var url = ‘https://api.twilio.com/2010-04-01/Accounts/’ + accountSid + ‘/Messages.json’;
var payload = {
To: ‘+1234567890’,
From: ‘+0987654321’,
Body: ‘Hello from Apps Script!’
};
var options = {
method: ‘post’,
payload: payload,
headers: {
Authorization: ‘Basic ‘ + Utilities.base64Encode(accountSid + ‘:’ + authToken)
}
};
UrlFetchApp.fetch(url, options);
}
Creating Nested Folders in Drive
Create a folder within another folder.
function createNestedFolder() {
var parentFolder = DriveApp.getFolderById(‘PARENT_FOLDER_ID’);
var newFolder = parentFolder.createFolder(‘New Folder’);
Logger.log(‘Folder created: ‘ + newFolder.getName());
}
Uninstalling Triggers
Delete all triggers associated with a script.
function deleteAllTriggers() {
var triggers = ScriptApp.getProjectTriggers();
triggers.forEach(function(trigger) {
ScriptApp.deleteTrigger(trigger);
});
}
Checking for Empty Cells
Identify and log empty cells in a range.
function checkEmptyCells() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
data.forEach(function(row, rowIndex) {
row.forEach(function(cell, colIndex) {
if (cell === ”) {
Logger.log(‘Empty cell at row ‘ + (rowIndex + 1) + ‘, column ‘ + (colIndex + 1));
}
});
});
}
Copying Files in Drive
Make a copy of a file in Google Drive.
function copyFile() {
var file = DriveApp.getFileById(‘FILE_ID’);
var copy = file.makeCopy(‘Copy of ‘ + file.getName());
Logger.log(‘File copied: ‘ + copy.getName());
}
Sending Emails with HTML Content
Send an email with HTML formatting.
function sendHtmlEmail() {
var htmlBody = ‘<h1>Hello</h1><p>This is an email with <b>HTML</b> content.</p>’;
MailApp.sendEmail({
to: ‘recipient@example.com’,
subject: ‘HTML Email’,
htmlBody: htmlBody
});
}
Creating Google Docs from Templates
Generate a Google Doc from a template file.
function createDocFromTemplate() {
var templateId = ‘TEMPLATE_FILE_ID’;
var template = DriveApp.getFileById(templateId);
var copy = template.makeCopy(‘New Document’);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
body.replaceText(‘{{name}}’, ‘John Doe’);
doc.saveAndClose();
}
Handling Form Submit Events
Trigger a function when a Google Form is submitted.
function onFormSubmit(e) {
var responses = e.response.getItemResponses();
responses.forEach(function(response) {
Logger.log(‘Question: ‘ + response.getItem().getTitle());
Logger.log(‘Answer: ‘ + response.getResponse());
});
}
Generating Random Numbers and Strings
Create random numbers or alphanumeric strings.
function generateRandom() {
var randomNumber = Math.random(); // Between 0 and 1
var randomString = Math.random().toString(36).substring(2, 10);
Logger.log(‘Random Number: ‘ + randomNumber);
Logger.log(‘Random String: ‘ + randomString);
}
Using Advanced Google Services
Enable and use the YouTube Data API.
function listYouTubeVideos() {
var results = YouTube.Search.list(‘snippet’, { q: ‘Apps Script’, maxResults: 5 });
results.items.forEach(function(item) {
Logger.log(item.snippet.title);
});
}
Working with Date and Time
Add days to a date and format it.
function addDaysToDate() {
var date = new Date();
date.setDate(date.getDate() + 5); // Adds 5 days
var formattedDate = Utilities.formatDate(date, ‘GMT’, ‘yyyy-MM-dd’);
Logger.log(formattedDate);
}
Using SpreadsheetApp.getUi() for Alerts
Show an alert dialog to the user.
function showAlert() {
var ui = SpreadsheetApp.getUi();
ui.alert(‘This is an alert dialog.’);
}
Creating Buttons in Sheets
Add a drawing that acts as a button to trigger a script.
function createButton() {
var sheet = SpreadsheetApp.getActiveSheet();
var button = sheet.insertDrawing();
button.setOnAction(‘myFunction’);
}
Clearing Contents of a Range
Clear values, formats, and notes from cells.
function clearRange() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(‘A1:C10’).clearContent().clearFormat().clearNote();
}
Protecting Sheets with Passwords
Set a password to protect a sheet (note: limited security).
function protectSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var protection = sheet.protect().setDescription(‘Protected Sheet’);
var ui = SpreadsheetApp.getUi();
var response = ui.prompt(‘Enter password to unprotect the sheet’);
if (response.getResponseText() === ‘your_password’) {
protection.remove();
}
}
Using SpreadsheetApp.openByUrl()
Open a spreadsheet by its URL.
function openSpreadsheetByUrl() {
var url = ‘https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit’;
var ss = SpreadsheetApp.openByUrl(url);
Logger.log(‘Opened spreadsheet: ‘ + ss.getName());
}