140 Google Apps Script Coding Snippets PDF Download Copding Examples Apps Script

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());

}