Top 30 Useful Google Apps Script Snippets

tanabee
tanabee
Oct 19, 2020 · 3 min read

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

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

Utilities.sleep(1000)

Generate UUID

const uuid = Utilities.getUuid()

Trigger

Execute a function after 1 minute

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

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

POST API

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

UrlFetchApp

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!

Google Developers Experts

Experts on various Google products talking tech.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

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