Apps Script Coding Examples
Sending an email using Gmail service 2
Accessing and modifying data in a Google Sheet 3
Creating a new Google Calendar event 5
Copying a Google Sheet to a new spreadsheet 5
Adding a custom menu to a Google Sheet 6
Generating a PDF from a Google Doc 7
Creating a new folder in Google Drive 7
Creating a new Google Form 8
Accessing a Google Sheet by ID 8
Reading data from a Google Sheet 8
Creating a new Google Slides presentation 9
Adding a slide to a Google Slides presentation 10
Adding an image to a Google Slides slide 10
1
Reading data from an external API and writing it to a Google Sheet 11
Copying a file to a specific folder in Google Drive 11
Sending an email from a Google Sheet 12
Creating a new Google Document 13
Appending text to a Google Document 13
Creating a new Google Form with multiple choice questions 14
Creating a new Google Sheet and populating it with data 15
Sorting data in a Google Sheet 15
Creating a new Google Drive folder 16
Adding a file to a Google Drive folder 16
Adding a trigger to run a function at a specific time 17
Copying data from one Google Sheet to another 18
Getting data from a Google Form response 18
Creating a new Google Slides presentation 19
Copying a Google Document to another user's Drive 20
Creating a new Google Forms quiz 21
Adding a custom menu to a Google Sheets 21
Setting the font family of a range of cells in a Google Sheets document 22
Creating a new Google Calendar event 23
Deleting all rows in a Google Sheets document that match a certain
condition 23
This script sends an email with the subject "My Subject" and message "My
Message" to the email address "example@[Link]" using the MailApp
service. 25
Creating a new Google Forms response 25
Importing data from a CSV file to a Google Sheets document 26
Copying a file in Google Drive to a different folder 26
Laurence Svekis [Link]
2
Creating a new Google Document 27
Setting the background color of a range of cells in a Google Sheets
document 28
Exporting a Google Sheets document to a PDF file 28
Creating a new Google Calendar event 29
Updating a row of data in a Google Sheets document 30
Sending an email with a PDF attachment 31
Creating a new Google Slides presentation 32
Sending an email using Gmail service
function sendEmail() {
const rep = "example@[Link]";
const sub = 'Hello World';
const message = 'Hi, Laurence';
[Link](rec, sub, message);
}
function mySender2() {
const rep = "example@[Link]";
const sub = 'Hello World';
const message = 'Hi, Laurence';
[Link](rep,sub,message);
Laurence Svekis [Link]
3
This script uses the Gmail service to send an email to a specified
recipient with a given subject and message. You can call this
function from a Google Sheets, Forms, or Docs script. Using the
MailApp service is a lighter way to send emails if all you are doing
is sending emails using apps script.
Accessing and modifying data in a Google
Sheet
function updater1(){
const sheet = [Link]();
const val = 'Laurence Svekis';
const range = [Link]('A1');
[Link](val);
[Link](sheet);
}
Laurence Svekis [Link]
4
Bound script example script updates the value in cell A1 of the
active sheet in the current Google Sheet. You can use this script
to update values in specific cells or ranges in a Google Sheet.
function updater1(){
const id = '132trziyDvLsk8qIk';
const sheet =
[Link](id).getSheetByName('Sheet12');
const val = 'Laurence Svekis 2';
const range = [Link](1,4,2,2);
[Link](val);
const range2 = [Link](4,1,2,2);
[Link]([[val,val],[val,val]]);
[Link](sheet);
}
Standalone script examples, selecting the sheet, and then
selecting the desired range to use. getRange(row, column,
numRows, numColumns) If you use the setValue all the cells
within the range will receive the same string value, if you want
specific values for the cells within the range, use the setValues()
method as it will set different values within the cells, using an
array with nested arrays for each row.
Laurence Svekis [Link]
5
Creating a new Google Calendar event
function createEvent() {
var calendar =
[Link]("calendar-id");
var title = "Meeting";
var start = new Date("February 19, 2023 [Link]
GMT-05:00");
var end = new Date("February 19, 2023 [Link]
GMT-05:00");
var event = [Link](title, start, end);
}
This script creates a new event in a specified Google Calendar
with a given title, start time, and end time. You can modify the
values to create events at different times and with different titles.
Copying a Google Sheet to a new spreadsheet
function copySheet() {
var sheet = [Link]();
Laurence Svekis [Link]
6
var newSheet = [Link]("Copy of My Sheet");
var newUrl = [Link]();
}
This script copies the current Google Sheet to a new spreadsheet
with a specified name. The new spreadsheet's URL is returned, so
you can use it to access the new sheet.
Adding a custom menu to a Google Sheet
function onOpen() {
var ui = [Link]();
[Link]("Custom Menu")
.addItem("Update Sheet", "updateSheet")
.addItem("Send Email", "sendEmail")
.addToUi();
}
This script adds a custom menu to the current Google Sheet.
When the user clicks on the "Custom Menu" option, a dropdown
menu appears with options to run the "updateSheet" and
"sendEmail" functions.
Laurence Svekis [Link]
7
Generating a PDF from a Google Doc
function createPDF() {
var doc = [Link]();
var pdf = [Link]([Link]('application/pdf'));
var pdfUrl = [Link]();
}
This script generates a PDF version of the current Google Doc and
creates a new file in Google Drive with the PDF content. The URL
of the new file is returned, so you can use it to access the PDF.
Creating a new folder in Google Drive
function createFolder() {
var folderName = "My Folder";
var folder = [Link](folderName);
var folderUrl = [Link]();
}
This script creates a new folder in Google Drive with a specified
name. The URL of the new folder is returned, so you can use it to
access the folder.
Laurence Svekis [Link]
8
Creating a new Google Form
function createForm() {
var form = [Link]('My Form');
var formUrl = [Link]();
}
This script creates a new Google Form with the specified name
and returns its published URL.
Accessing a Google Sheet by ID
function getSheetById() {
var sheetId = "your-sheet-id";
var sheet = [Link](sheetId);
var sheetName = [Link]();
}
This script opens a Google Sheet with the specified ID and returns
its name.
Reading data from a Google Sheet
Laurence Svekis [Link]
9
function readSheet() {
var sheet =
[Link]().getSheetByName('S
heet1');
var data = [Link]().getValues();
var firstRow = data[0];
}
This script retrieves data from the specified sheet in the current
Google Sheet and returns the values of the data range as a
two-dimensional array. In this example, the first row of the data
range is assigned to the firstRow variable.
Creating a new Google Slides presentation
function createSlides() {
var presentation = [Link]('My
Presentation');
var presentationUrl = [Link]();
}
This script creates a new Google Slides presentation with the
specified name and returns its URL.
Laurence Svekis [Link]
10
Adding a slide to a Google Slides presentation
function addSlide() {
var presentation = [Link]();
var slide = [Link]();
var slideIndex = [Link]();
}
This script adds a new slide to the end of the current Google
Slides presentation and returns its index.
Adding an image to a Google Slides slide
function addImage() {
var presentation = [Link]();
var slide = [Link]()[0];
var imageUrl = "[Link]
var image = [Link](imageUrl);
}
This script adds an image from the specified URL to the first slide
of the current Google Slides presentation.
Laurence Svekis [Link]
11
Reading data from an external API and
writing it to a Google Sheet
function writeData() {
var apiKey = "your-api-key";
var url = "[Link] + apiKey;
var response = [Link](url);
var data = [Link]([Link]());
var sheet =
[Link]().getSheetByName('S
heet1');
[Link](1, 1, [Link],
data[0].length).setValues(data);
}
This script retrieves data from an external API, parses the JSON
response, and writes the data to the specified sheet in the current
Google Sheet.
Copying a file to a specific folder in Google
Drive
Laurence Svekis [Link]
12
function copyFile() {
var fileId = "your-file-id";
var folderId = "your-folder-id";
var file = [Link](fileId);
var folder = [Link](folderId);
var copy = [Link]([Link](), folder);
}
This script copies a file with the specified ID to the specified
folder in Google Drive.
Sending an email from a Google Sheet
function sendEmail() {
var sheet =
[Link]().getSheetByName("S
heet1");
var data = [Link]().getValues();
var emailAddress = "recipient@[Link]";
var message = "Hello, " + data[1][0] + "! This is an
automated message.";
var subject = "Automated email";
[Link](emailAddress, subject, message);
Laurence Svekis [Link]
13
}
This script retrieves data from a specified sheet in the current
Google Sheet and sends an email to the specified recipient with
the message and subject line.
Creating a new Google Document
function createDoc() {
var doc = [Link]("My Document");
var docUrl = [Link]();
}
This script creates a new Google Document with the specified
name and returns its URL.
Appending text to a Google Document
function appendText() {
var doc = [Link]();
var body = [Link]();
[Link]("This is a new paragraph.");
}
Laurence Svekis [Link]
14
This script appends a new paragraph to the end of the current
Google Document.
Creating a new Google Form with multiple
choice questions
function createForm() {
var form = [Link]('My Form');
var item = [Link]();
[Link]('What is your favorite color?')
.setChoices([
[Link]('Red'),
[Link]('Blue'),
[Link]('Green')
]);
var formUrl = [Link]();
}
This script creates a new Google Form with a multiple choice
question asking about a favorite color.
Laurence Svekis [Link]
15
Creating a new Google Sheet and populating
it with data
function createSheet() {
var sheet = [Link]("My Sheet");
var data = [
["Name", "Age", "Location"],
["John", 32, "New York"],
["Mary", 25, "Chicago"],
["David", 43, "Los Angeles"]
];
[Link](1, 1, [Link],
data[0].length).setValues(data);
var sheetUrl = [Link]();
}
This script creates a new Google Sheet, populates it with the
specified data, and returns its URL.
Sorting data in a Google Sheet
function sortData() {
Laurence Svekis [Link]
16
var sheet =
[Link]().getSheetByName("S
heet1");
[Link]("A2:C").sort(1);
}
This script sorts the data in columns A to C of the specified sheet
in ascending order.
Creating a new Google Drive folder
function createFolder() {
var folder = [Link]("My Folder");
var folderUrl = [Link]();
}
This script creates a new folder in Google Drive with the specified
name and returns its URL.
Adding a file to a Google Drive folder
function addFileToFolder() {
var fileId = "your-file-id";
var folderId = "your-folder-id";
Laurence Svekis [Link]
17
var file = [Link](fileId);
var folder = [Link](folderId);
[Link](file);
}
This script adds a file with the specified ID to the specified folder
in Google Drive.
Adding a trigger to run a function at a specific
time
function addTrigger() {
[Link]("myFunction")
.timeBased()
.atDate(2023, 3, 1)
.create();
}
This script creates a new trigger that will run the myFunction
function on March 1, 2023.
Laurence Svekis [Link]
18
Copying data from one Google Sheet to
another
function copyData() {
var sourceSheet =
[Link]().getSheetByName("S
heet1");
var targetSheet =
[Link]().getSheetByName("S
heet2");
var sourceData =
[Link]().getValues();
[Link](1, 1, [Link],
sourceData[0].length).setValues(sourceData);
}
This script copies the data from Sheet1 to Sheet2 in the same
Google Sheet.
Getting data from a Google Form response
function getFormResponse() {
var form = [Link]("form-url");
Laurence Svekis [Link]
19
var responses = [Link]();
var lastResponse = responses[[Link] - 1];
var itemResponses = [Link]();
var name = itemResponses[0].getResponse();
var email = itemResponses[1].getResponse();
}
This script retrieves the name and email address from the most
recent response to a Google Form.
Creating a new Google Slides presentation
function createSlides() {
var presentation = [Link]("My
Presentation");
var slide = [Link]();
[Link]("Hello, world!", 100, 100);
var presentationUrl = [Link]();
}
This script creates a new Google Slides presentation with a single
slide that contains a text box.
Laurence Svekis [Link]
20
Copying a Google Document to another user's
Drive
function copyDocToDrive() {
var docId = "document-id";
var targetEmail = "target-email@[Link]";
var file = [Link](docId);
var targetFolder =
[Link](targetFolderId);
var targetFile = [Link]("Copy of My Document",
targetFolder);
var targetUser =
[Link](targetEmail);
[Link](targetFolderId).addViewer(target
User);
[Link](targetEmail, "Document copied",
"Here is a copy of My Document: " +
[Link]());
}
This script makes a copy of a Google Document and shares it with
the specified user via email.
Laurence Svekis [Link]
21
Creating a new Google Forms quiz
function createQuiz() {
var form = [Link]('My Quiz');
var item = [Link]();
[Link]('What is the capital of France?')
.setPoints(1)
.setChoices([
[Link]('Paris', true),
[Link]('London'),
[Link]('Berlin')
]);
[Link](true);
[Link]("Thanks for taking the
quiz!");
var formUrl = [Link]();
}
This script creates a new Google Forms quiz with a multiple
choice question about the capital of France and sets the correct
answer to Paris.
Adding a custom menu to a Google Sheets
Laurence Svekis [Link]
22
function onOpen() {
var ui = [Link]();
[Link]("My Menu")
.addItem("Item 1", "myFunction1")
.addItem("Item 2", "myFunction2")
.addSeparator()
.addSubMenu([Link]("Submenu")
.addItem("Subitem 1", "mySubFunction1")
.addItem("Subitem 2", "mySubFunction2"))
.addToUi();
}
This script adds a custom menu called "My Menu" to a Google
Sheets document. The menu includes two top-level items, "Item
1" and "Item 2", and a submenu called "Submenu" with two
sub-items.
Setting the font family of a range of cells in a
Google Sheets document
function setFontFamily() {
var sheet =
[Link]().getActiveSheet();
var range = [Link](1, 1, 5, 5);
Laurence Svekis [Link]
23
[Link]("Arial");
}
This script sets the font family of a range of cells in a Google
Sheets document to "Arial".
Creating a new Google Calendar event
function createEvent() {
var calendar = [Link]();
var title = "My Event";
var startTime = new Date("2023-03-01T[Link]Z");
var endTime = new Date("2023-03-01T[Link]Z");
var event = [Link](title, startTime,
endTime);
}
This script creates a new event in the user's default Google
Calendar with the title "My Event" that starts at 9:00 AM and
ends at 10:00 AM on March 1, 2023.
Deleting all rows in a Google Sheets
document that match a certain condition
Laurence Svekis [Link]
24
function deleteRows() {
var sheet =
[Link]().getActiveSheet();
var data = [Link]().getValues();
for (var i = [Link] - 1; i >= 0; i--) {
if (data[i][0] == "Delete") {
[Link](i + 1);
}
}
}
This script deletes all rows in a Google Sheets document that
have the value "Delete" in the first column.
Sending an email from a Google Sheets document
javascript
Copy code
function sendEmail() {
var sheet =
[Link]().getActiveSheet();
var data = [Link]().getValues();
var emailAddress = "example@[Link]";
var subject = "My Subject";
var message = "My Message";
[Link](emailAddress, subject, message);
Laurence Svekis [Link]
25
This script sends an email with the subject
"My Subject" and message "My Message" to
the email address "example@[Link]"
using the MailApp service.
Creating a new Google Forms response
function createFormResponse() {
var form = [Link]("FORM_ID");
var response = [Link]();
var item = [Link](ITEM_ID);
var answer = [Link]().createResponse("My
Answer");
[Link](answer);
[Link]();
}
This script creates a new response to a Google Form with the ID
"FORM_ID", sets the answer to a text item with the ID "ITEM_ID"
to "My Answer", and submits the response using the FormApp
service.
Laurence Svekis [Link]
26
Importing data from a CSV file to a Google
Sheets document
function importCSV() {
var file = [Link]("FILE_ID");
var csvData =
[Link]([Link]().getDataAsString());
var sheet =
[Link]().getActiveSheet();
[Link](1, 1, [Link],
csvData[0].length).setValues(csvData);
}
This script imports data from a CSV file with the ID "FILE_ID" in
Google Drive, parses the data using the Utilities service, and sets
the values in the active sheet of the current Google Sheets
document using the SpreadsheetApp service.
Copying a file in Google Drive to a different
folder
Laurence Svekis [Link]
27
function copyFile() {
var file = [Link]("FILE_ID");
var folder = [Link]("FOLDER_ID");
var copy = [Link]("Copy of " + [Link](),
folder);
}
This script makes a copy of a file with the ID "FILE_ID" in Google
Drive, renames the copy to "Copy of [original file name]", and
moves the copy to a different folder with the ID "FOLDER_ID"
using the DriveApp service.
Creating a new Google Document
function createDocument() {
var title = "My Document";
var body = "My Document Content";
var doc = [Link](title);
[Link]().setText(body);
}
This script creates a new Google Document with the title "My
Document" and body content "My Document Content" using the
DocumentApp service.
Laurence Svekis [Link]
28
Setting the background color of a range of
cells in a Google Sheets document
function setBackgroundColor() {
var sheet =
[Link]().getActiveSheet();
var range = [Link](1, 1, 5, 5);
[Link]("#FF0000");
}
This script sets the background color of a range of cells in a
Google Sheets document to red using the setBackgroundColor
method of the Range class.
Exporting a Google Sheets document to a PDF
file
function exportToPDF() {
var spreadsheet =
[Link]();
var sheet = [Link]();
var url = [Link]();
Laurence Svekis [Link]
29
url = [Link](/edit$/, '') +
'export?exportFormat=pdf&format=pdf&gid=' +
[Link]();
var token = [Link]();
var response = [Link](url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
var pdfBlob = [Link]();
var folder = [Link]("FOLDER_ID");
var file =
[Link](pdfBlob).setName([Link](
) + ".pdf");
}
This script exports the active sheet of the current Google Sheets
document to a PDF file, saves the file to a folder with the ID
"FOLDER_ID" in Google Drive, and names the file after the name
of the Google Sheets document.
Creating a new Google Calendar event
function createEvent() {
Laurence Svekis [Link]
30
var title = "My Event";
var description = "My Event Description";
var startTime = new
Date("2023-02-20T[Link]-08:00");
var endTime = new Date("2023-02-20T[Link]-08:00");
var event =
[Link]().createEvent(title,
startTime, endTime, {
description: description
});
}
This script creates a new event on the default Google Calendar
with the title "My Event", description "My Event Description", start
time of 9:00 AM and end time of 10:00 AM on February 20, 2023,
using the CalendarApp service.
Updating a row of data in a Google Sheets
document
function updateRow() {
var sheet =
[Link]().getActiveSheet();
var data = [Link]().getValues();
Laurence Svekis [Link]
31
for (var i = 0; i < [Link]; i++) {
if (data[i][0] == "John Doe") {
[Link](i+1, 2).setValue("Updated Value");
}
}
}
This script updates a row of data in a Google Sheets document by
searching for the name "John Doe" in the first column, and
setting the value of the corresponding cell in the second column
to "Updated Value", using the getDataRange and getRange
methods of the Sheet class.
Sending an email with a PDF attachment
function sendEmailWithPDF() {
var emailAddress = "recipient@[Link]";
var subject = "PDF Attachment";
var body = "Please see the attached PDF file.";
var pdfFile = [Link]("PDF_FILE_ID");
var pdfBlob = [Link]();
[Link](emailAddress, subject, body,
{attachments: [pdfBlob]});
}
Laurence Svekis [Link]
32
This script sends an email to the specified recipient with the
subject "PDF Attachment" and body "Please see the attached PDF
file". It attaches a PDF file with the ID "PDF_FILE_ID" from
Google Drive to the email, using the MailApp and DriveApp
services.
Creating a new Google Slides presentation
function createPresentation() {
var title = "My Presentation";
var slides = [Link](title);
var slide1 = [Link]()[0];
var shape1 =
[Link]([Link], 50,
50, 100, 100);
[Link]().setSolidFill("#4285F4");
var shape2 =
[Link]([Link], 200,
50, 100, 100);
[Link]().setSolidFill("#EA4335");
var shape3 =
[Link]([Link], 350,
50, 100, 100);
Laurence Svekis [Link]
33
[Link]().setSolidFill("#FBBC05");
}
This script creates a new Google Slides presentation with the title
"My Presentation", using the SlidesApp service. It then inserts
three shapes onto the first slide of the presentation, sets the size
and position of each shape, and sets the fill color of each shape
using the setSolidFill method of the Fill class.
Laurence Svekis [Link]
