Top 30 Useful Google Apps Script Snippets

Here are my favorite 30 Google Apps Script snippets. These snippets will save your time.

SpreadsheetApp

Access to Spreadsheet ( standalone scripts )

const spreadsheet = SpreadsheetApp.openById("Spreadsheet ID")

Spreadsheet ID can be obtained from the spreadsheet URL

https://docs.google.com/spreadsheets/d/[ Spreadsheet ID ]/edit

Access to Spreadsheet ( Container-bound scripts )

const spreadsheet = SpreadsheetApp.getActive()

Get values

const values = spreadsheet
.getSheetByName('sheet name')
.getDataRange()
.getValues()

Set values

spreadsheet
.getRange(range)
.setValues(values)

Add custom menu

const onOpen = () => {
SpreadsheetApp
.getActiveSpreadsheet()
.addMenu('Menu name', [
{name: 'sub menu name 1', functionName: 'functionName1'},
{name: 'sub menu name 2', functionName: 'functionName2'},
])
}

onEdit trigger

const onEdit = e => {
const { range, oldValue, value } = e
}

onSelectionChange trigger

const onSelectionChange = e => {
const { range, user } = e
}

Google Calendar

Create event

CalendarApp.getDefaultCalendar().createEvent(title, start, end)

Get events

const events = CalendarApp
.getDefaultCalendar()
.getEvents(start, end)
.map(event => ({
title: event.getTitle(),
description: event.getDescription(),
start: event.getStartTime(),
end: event.getEndTime()
}))

You can get other properties from CalendarEvent class

Access to not default calendar

const calendar = CalendarApp.getCalendarById('Calendar ID')

Gmail

Send Email

GmailApp.sendEmail('tanabee@example.com', 'subject', 'body')

Search threads

If you’d like to get 10 latest threads,

const threads = GmailApp.search('', 0, 10)

You can set search operators as the first argument.

Get messages

const messages = GmailApp.search('', 0, 10).flatMap(thread => 
thread.getMessages().map(message => ({
subject: message.getSubject(),
body: message.getBody(),
date: message.getDate(),
from: message.getFrom(),
to: message.getTo(),
}))
)

You can get other properties from GmailMessage class

Google Drive

Get folders and files in the specific folder

const folder = DriveApp.getFolderById('Folder ID')const files = folder.getFiles()
while (files.hasNext()) {
let file = files.next()
console.log('file: ', file.getName())
}
const folders = folder.getFolders()
while (folders.hasNext()) {
let folder = folders.next()
console.log('folder: ', folder.getName())
}

Google Form

Get input values

const onSubmit = event => {
const answer = event.response
.getItemResponses()
.map(itemResponse => ({
item: itemResponse.getItem().getTitle(),
response: itemResponse.getResponse()
}))
}

You need to add form submit trigger.

Google Document

Access to Google Document ( standalone scripts )

const doc = DocumentApp.openById('Document ID')

Document ID can be obtained from the spreadsheet URL

https://docs.google.com/document/d/[ Document ID ]/edit

Access to Google Document ( Container-bound scripts )

const doc = DocumentApp.getActiveDocument()

Add custom menu

const onOpen = () => {
DocumentApp
.getUi()
.createMenu('menu name')
.addItem('item name', 'functionName')
.addToUi()
}

Utilities

Format date

const date = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'YYYY-MM-dd HH:mm:ss')

sleep

If you want the script to sleep for 1 second

Utilities.sleep(1000)

Generate UUID

const uuid = Utilities.getUuid()

Trigger

Execute a function after 1 minute

Google Apps Script has a limitation of script runtime. If you want the script to work more than the limit, you can schedule the next execution in advance.

const date = new Date()
date.setMinutes(date.getMinutes() + 1);// after 1 min
ScriptApp.newTrigger('functionName').timeBased().at(date).create();

PropertiesService

Get property

const value = PropertiesService.getScriptProperties().getProperty(key)

Set property

PropertiesService.getScriptProperties().setProperty(key, value)

LanguageApp

translation

const text = LanguageApp.translate('Hello World', 'en', 'ja')

Web Apps

Website ( no HTML template )

const doGet = e => {
const params = JSON.stringify(e.parameter)
return HtmlService.createHtmlOutput(params)
}

Website ( with HTML template )

const doGet = e => {
return HtmlService.createHtmlOutputFromFile('index')
}

You need to create index.html file.

GET API

You can get query string with accessing e.parameter.

const doGet = e => {
const params = JSON.stringify(e.parameter)
return ContentService
.createTextOutput(params)
.setMimeType(MimeType.JSON)
}

POST API

You can get request body with accessing e.postData.contents.

const doPost = e =>  {
const body = e.postData.contents
return ContentService
.createTextOutput(body)
.setMimeType(ContentService.MimeType.JSON)
}

UrlFetchApp

You can access other resources when using UrlFetchApp class.

GET API request

const content = UrlFetchApp.fetch(url).getContentText()

POST API request

const res = UrlFetchApp.fetch(url, {
method: 'POST',
headers: { "Content-Type": 'application/json' },
payload: JSON.stringify(data)
})

Thank you for reading this article!

--

--

--

Experts on various Google products talking tech.

Recommended from Medium

Creating my first mobile app — Status Center

How to Add Sass/Scss In next.js

Add Sass/Scss Inside next.js

Javascript 1O1 series : Learn syntax, variables and datatypes

Trie to Find Words

Rest Api Using Golang And MongoDB

YouTube Video | JavaScript Form Validation

NextJS: why use it?

Creating Blog site using Stackbit, GatsbyJS and DEV -4

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
tanabee

tanabee

Vice President, RPG TEC. Google Developers Expert / Licensed Scrum Master / Website: https://tanabee.github.io

More from Medium

Building a Scalable and Secure Online Presence that’s Ready for the Next Generation of the Internet…

Hybrid Apps vs Native Apps — A Brief Study

VYou app: by developers for developers | VYou

<Open Source Round 1: Read other people’s code> Week 1 learnings