Complete Apps Script Coding Guide 100+ Examples of Apps Script code in Action Free Download Guide #appsScript #workspace #pdf #free #learn #google #sheets #docs #gmail #gdrive #calendar #automation #productivityhacks #coding

Email Address and Message From Sheets , This script sends an email to the specified email address with the contents of a cell in a Google Sheet. , Create a custom menu in Google Sheets , This script creates a custom menu in Google Sheets with two options: “Sort Ascending” and “Sort Descending”. , Automate Google Forms , This script creates a trigger to run a function when a Google Form is submitted. The function sends an email notification with the form response. , Send an Email with Google Sheets Data , This script sends an email with the data present in a Google Sheet. , Create a Google Form from a Google Sheet , This script creates a Google Form from the data present in a Google Sheet. , Extract Data from a Google Form , This script extracts the responses from a Google Form and writes them to a Google Sheet. , Create a Google Calendar Event , This script creates a Google Calendar event. , Copy a Google Sheet to Another Spreadsheet , This script copies a Google Sheet to another Google Spreadsheet. , Delete Rows Based on Criteria in Google Sheets , This script deletes rows in a Google Sheet based on a specific criteria. , Send Email to Users from Sheet Make HTML and PDF files as attachments , Create a custom function to calculate the average of a range of cells: , Custom Sheet Formula , Automatically send an email reminder based on a specific date: , Send email reminder Automation , Create a custom menu in Google Sheets to run a script: , Custom Sheet UI Menu , Use the Google Drive API to create a new folder: , Create new Folder within Folder , Send an email when a Google Form is submitted: , Use form data in Email , Generate a PDF from a Google Doc and save it to Google Drive: , Create a custom menu in Google Sheets: , Import data from a CSV file and write it to a Google Sheet: , Add a trigger to run a function on a schedule: , Send email from a Google Sheet: , Create a custom menu in Google Sheets: , Automate Google Forms: , Create a custom Google Drive folder: , Generate a random password: , Create a custom function in Google Sheets: , Copy a sheet to a new spreadsheet: , Extract data from a PDF file: , Merge cells in Google Sheets: , Export data from Google Sheets to a CSV file: , Create a Custom Function to Calculate Fibonacci Sequence: , Send Emails to a List of Recipients from Google Sheets: , Create a Custom Menu in Google Sheets: , Dialog with HTML page contents , Automatically Insert the Date in Google Sheets: , Custom Log and onEdit Function , Create a Custom Function to Capitalize the First Letter of Each Word in a String: , Sending an email using Gmail service , Accessing and modifying data in a Google Sheet , Creating a new Google Calendar event , Copying a Google Sheet to a new spreadsheet , Adding a custom menu to a Google Sheet , Generating a PDF from a Google Doc , Creating a new folder in Google Drive , Creating a new Google Form , Accessing a Google Sheet by ID , Reading data from a Google Sheet , Creating a new Google Slides presentation , Adding a slide to a Google Slides presentation , Adding an image to a Google Slides slide , Reading data from an external API and writing it to a Google Sheet , Copying a file to a specific folder in Google Drive , Sending an email from a Google Sheet , Creating a new Google Document , Appending text to a Google Document , Creating a new Google Form with multiple choice questions , Creating a new Google Sheet and populating it with data , Sorting data in a Google Sheet , Creating a new Google Drive folder , Adding a file to a Google Drive folder , Adding a trigger to run a function at a specific time , Copying data from one Google Sheet to another , Getting data from a Google Form response , Creating a new Google Slides presentation , Copying a Google Document to another user’s Drive , Creating a new Google Forms quiz , Adding a custom menu to a Google Sheets , Setting the font family of a range of cells in a Google Sheets document , Creating a new Google Calendar event , Deleting all rows in a Google Sheets document that match a certain condition , This script sends an email with the subject “My Subject” and message “My Message” to the email address “example@gmail.com” using the MailApp service. , Creating a new Google Forms response , Importing data from a CSV file to a Google Sheets document , Copying a file in Google Drive to a different folder , Creating a new Google Document , Setting the background color of a range of cells in a Google Sheets document , Exporting a Google Sheets document to a PDF file , Creating a new Google Calendar event , Updating a row of data in a Google Sheets document , Sending an email with a PDF attachment , Creating a new Google Slides presentation

Complete Apps Script Coding Example Guide

Email Address and Message From Sheets

This script sends an email to the specified email address with the contents of a cell in a Google Sheet.

function sendEmail() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

var cell = sheet.getRange(“A1”);

var value = cell.getValue();

var recipient = “youremail@example.com”;

var subject = “Test Email”;

var body = “The value of cell A1 is: ” + value;

MailApp.sendEmail(recipient, subject, body);

}

Explanation:

getActiveSpreadsheet() gets the active spreadsheet.

getSheetByName() gets the sheet with the specified name.

getRange() gets a range of cells, in this case, cell A1.

getValue() gets the value of the cell.

sendEmail() sends an email with the specified recipient, subject, and body.

Create a custom menu in Google Sheets

This code defines three functions for a Google Sheets add-on that adds a custom menu to the Sheets interface. The menu has two options to sort the data in the active sheet in ascending or descending order based on the first column.

Here is a detailed breakdown of the code:

The “onOpen” function is triggered when the user opens the Google Sheet. It creates a custom menu called “My Menu” using the “createMenu” method from the “SpreadsheetApp” class.
Two menu items are added to the “My Menu” menu using the “addItem” method. The first menu item is labeled “Sort Asc” and the second menu item is labeled “Sort Des”.
The “sortAsc” function is triggered when the user selects the “Sort Asc” menu item. It gets the active sheet using the “getActiveSheet” method from the “SpreadsheetApp” class.
The “getDataRange” method is called on the active sheet to get the range of cells that contain data.
The “sort” method is called on the range object, and it takes an object with two properties as an argument. The “column” property specifies the column to sort by (in this case, column 1), and the “ascending” property specifies whether to sort in ascending order (true) or descending order (false).
The “sortDes” function is triggered when the user selects the “Sort Des” menu item. It is similar to the “sortAsc” function, except that it sorts the data in descending order.

Overall, this code demonstrates how to create a custom menu in Google Sheets and add menu items that trigger custom functions. It also shows how to sort data in a sheet using Google Apps Script.

This script creates a custom menu in Google Sheets with two options: “Sort Ascending” and “Sort Descending”.

function onOpen() {

var menu = SpreadsheetApp.getUi().createMenu(“Custom Menu”);

menu.addItem(“Sort Ascending”, “sortAscending”);

menu.addItem(“Sort Descending”, “sortDescending”);

menu.addToUi();

}

function sortAscending() {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getDataRange();

range.sort({column: 1, ascending: true});

}

function sortDescending() {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getDataRange();

range.sort({column: 1, ascending: false});

}

Explanation:

onOpen() runs when the spreadsheet is opened and creates a custom menu with two options: “Sort Ascending” and “Sort Descending”.

addItem() adds an item to the custom menu with the specified label and function.

addToUi() adds the custom menu to the spreadsheet UI.

sortAscending() and sortDescending() sort the data in the active sheet in ascending or descending order based on the first column.

Automate Google Forms

This script creates a trigger to run a function when a Google Form is submitted. The function sends an email notification with the form response.

This code demonstrates how to use Google Apps Script to create a trigger that sends an email notification when a Google Form is submitted. The code consists of two functions: “createTrigger” and “sendNotification”.

function createTrig(){

const id = ‘1cY89p-9YdZ65vIk’;

const form = FormApp.openById(id);

ScriptApp.newTrigger(‘sendNotification’)

.forForm(form)

.onFormSubmit()

.create();

}

function sendNotification(e){

//const email = Session.getActiveUser().getEmail();

const email = ‘gappscourses@gmail.com’;

const subject = ‘New Submission’;

const body = ‘New Data’;

MailApp.sendEmail(email,subject,body);

}

Here is a detailed explanation of each function:

The “createTrigger” function creates a new trigger that listens for form submission events. It first gets the Google Form object using the “openById” method of the “FormApp” class, passing in the ID of the form. It then creates a new trigger using the “newTrigger” method of the “ScriptApp” class. The “forForm” method is used to specify the form to monitor, and the “onFormSubmit” method is used to specify that the trigger should fire when a form is submitted. Finally, the “create” method is called to create the trigger.
The “sendNotification” function is called when the trigger created by the “createTrigger” function fires, indicating that a form has been submitted. The function takes an event object “e” as a parameter, which contains information about the submitted form. It then sets the email recipient, subject, and body text for the notification email. Finally, it uses the “sendEmail” method of the “MailApp” class to send the email notification to the specified recipient.

Overall, this code provides a simple way to get notified via email when a Google Form is submitted. It is useful for tracking form submissions and responding quickly to new entries.

function createTrigger() {

var form = FormApp.openById(“FORM_ID”);

ScriptApp.newTrigger(“sendNotification”)

.forForm(form)

.onFormSubmit()

.create();

}

function sendNotification(e) {

var recipient = “youremail@example.com”;

var subject = “New Form Submission”;

var body = “A new form submission has been received.”;

MailApp.sendEmail(recipient, subject, body);

}

Explanation:

openById() opens the Google Form with the specified ID.

newTrigger() creates a trigger to run the specified function.

forForm() sets the trigger to run for the specified form.

onFormSubmit() sets the trigger to run when the form is submitted.

create() creates the trigger.

sendNotification() sends an email notification with the specified recipient, subject, and body when the form is submitted.

Send an Email with Google Sheets Data

This script sends an email with the data present in a Google Sheet.

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName(“Sheet1”);

var data = sheet.getDataRange().getValues();

var message = “”;

for (var i = 0; i < data.length; i++) {

message += data[i].join(“, “) + “\n”;

}

MailApp.sendEmail({

to: “recipient@example.com”,

subject: “Sheet Data”,

body: message

});

}

Create a Google Form from a Google Sheet

This script creates a Google Form from the data present in a Google Sheet.

function createForm() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName(“Sheet1”);

var form = FormApp.create(“Form”);

var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

for (var i = 1; i <= headers.length; i++) {

form.addMultipleChoiceItem().setTitle(headers[i-1]).setChoices([ form.createChoice(“Option 1”), form.createChoice(“Option 2”), form.createChoice(“Option 3”) ]);

}

Extract Data from a Google Form

This script extracts the responses from a Google Form and writes them to a Google Sheet.

function extractResponses() {

var form = FormApp.openByUrl(“https://docs.google.com/forms/d/Form-ID/viewform”);

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Responses”);

var responses = form.getResponses();

for (var i = 0; i < responses.length; i++) {

var itemResponses = responses[i].getItemResponses();

var rowData = [];

for (var j = 0; j < itemResponses.length; j++) {

rowData.push(itemResponses[j].getResponse());

}

sheet.appendRow(rowData);

}

Create a Google Calendar Event

This script creates a Google Calendar event.

function createEvent() {

var title = “Meeting”;

var start = new Date(“February 28, 2023 08:00:00”);

var end = new Date(“February 28, 2023 09:00:00”);

var location = “Conference Room”;

var description = “Discuss new project”;

var event = CalendarApp.getDefaultCalendar().createEvent(title, start, end, {

location: location,

description: description

});

}

Copy a Google Sheet to Another Spreadsheet

This script copies a Google Sheet to another Google Spreadsheet.

function copySheet() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName(“Sheet1”);

var destination = SpreadsheetApp.openById(“Destination-Spreadsheet-ID”);

sheet.copyTo(destination);

}

Delete Rows Based on Criteria in Google Sheets

This script deletes rows in a Google Sheet based on a specific criteria.

function deleteRows() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName(“Sheet1”);

var data = sheet.getDataRange().getValues();

for (var i = data.length – 1; i >= 0; i–) {

if (data[i][0] == “Delete”) {

sheet.deleteRow(i + 1);

}

Send Email to Users from Sheet Make HTML and PDF files as attachments

This script sends an email to each user listed in the “Users” sheet of a Google Spreadsheet. The email contains the user’s name, user ID, and an HTML body with some additional content.

Here is a step-by-step breakdown of the code:

The variable “id” contains the ID of the Google Drive file that will be used as an attachment in the email. This file is assumed to be a PDF document, and its ID is hardcoded into the script.
The “ss” variable contains the currently active Google Spreadsheet, and the “sheet” variable references the “Users” sheet within that spreadsheet.
The “rows” variable contains an array of all the rows in the “Users” sheet, except for the header row (which is skipped using the “slice” method).
A “forEach” loop is used to iterate over each row in the “rows” array.
Inside the loop, the script extracts the user’s name, email, and user ID from the current row using array indexing.
The “subject” variable is set to a string that includes the user’s name.
The “body” variable is set to an HTML string that includes the user’s name and user ID.
The “html” variable is set to another HTML string that includes some additional content, as well as the “body” variable from step 7.
A new Blob object is created from the “html” string using the “Utilities.newBlob” method. This Blob will be used as an attachment in the email.
The “file” variable is set to a Drive file object that represents the PDF file with the hardcoded ID.
The “MailApp.sendEmail” method is called with several arguments:

The “to” field is set to the user’s email address.
The “subject” field is set to the subject string from step 6.
The “htmlBody” field is set to the body string from step 7.
The “attachments” field is set to an array that includes the PDF file object from step 10 and the Blob object from step 9.

The script logs the user’s name to the console for debugging purposes.

Overall, this script is useful for sending personalized emails to a group of users using data from a Google Spreadsheet. It demonstrates how to create an HTML email body and attach a file to the email using Google Apps Script.

function senderMail(){

const id = ‘1SCE46vJBcWzMXCwhTBW_Hs_NC00LBIYWKFjiwZCXXVY’;

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(‘Users’);

//const data = sheet.getDataRange().getValues();

const rows = sheet.getDataRange().getValues().slice(1);

Logger.log(rows);

rows.forEach(user =>{

const name = user[0];

const uid = user[2];

const email = user[1];

const subject = `Test Email ${name}`;

const body = `<h1>Hello ${name}</h1>Your user ID is ${uid}`;

const html = `<div>New Div</div><ul><li>1</li><li>2</li></ul> ${body}`;

const blob = Utilities.newBlob(html,’text/html’,`${name}.html`);

const file = DriveApp.getFileById(id);

//MailApp.sendEmail(email,subject,body);

MailApp.sendEmail({

to:email,

subject:subject,

htmlBody:body,

attachments:[file.getAs(MimeType.PDF),blob]

});

Logger.log(name);

})

}

Create a custom function to calculate the average of a range of cells:

function AVERAGE_RANGE(range) {

var sum = 0;

var count = 0;

for (var i = 0; i < range.length; i++) {

for (var j = 0; j < range[i].length; j++) {

sum += range[i][j];

count++;

}

return sum / count;

}

Explanation: This code defines a custom function called AVERAGE_RANGE that takes a range of cells as input and calculates the average value of those cells. The function uses a nested loop to iterate through each cell in the range, adding up the values and counting the number of cells. It then returns the average value by dividing the sum by the count.

function AVE_RANGE(range){

let sum = 0;

let count = 0;

for(let row=0;row<range.length;row++){

for(let col=0;col<range[row].length;col++){

sum += range[row][col];

count++;

}

return Math.ceil(sum/count);

}

Custom Sheet Formula

The AVE_RANGE function takes in a 2D array range, which represents a rectangular range of cells in a spreadsheet. It calculates the average value of all the cells in the range and rounds it up to the nearest integer.

To do this, the function initializes two variables, sum and count, to 0. It then iterates over each cell in the range using a nested for loop. For each cell, it adds its value to sum and increments count.

After all the cells have been processed, the function divides sum by count to calculate the average value of the cells. It then uses the Math.ceil() function to round this value up to the nearest integer, and returns the result.

Overall, this function can be useful in a variety of contexts where you need to calculate the average value of a range of cells, such as in data analysis or financial modeling.

Automatically send an email reminder based on a specific date:

function sendReminderEmail() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Tasks”);

var data = sheet.getDataRange().getValues();

var today = new Date();

for (var i = 1; i < data.length; i++) {

var date = new Date(data[i][2]);

var diff = Math.round((date – today) / (1000 * 60 * 60 * 24));

if (diff === 1) {

var email = data[i][3];

var subject = “Reminder: ” + data[i][1];

var body = “This is a reminder that your task \”” + data[i][1] + “\” is due tomorrow.”;

MailApp.sendEmail(email, subject, body);

}

Explanation: This code defines a function called sendReminderEmail that retrieves data from a Google Sheet and sends an email reminder to users when their task is due the next day. The function calculates the number of days between today’s date and the due date for each task, and sends an email if the difference is 1 day.

Send email reminder Automation

function senderEmail(){

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Users’);

const data = sheet.getDataRange().getValues();

const users = data.slice(1);

const today = new Date();

users.forEach((user,index) => {

if(user[4]==”){

const date = new Date(user[3]);

const diff = Math.round((date-today)/1000*60*60*24);

const range = sheet.getRange(index+2,5);

if(diff<0){

const email = user[1];

const subject = ‘Passed’;

const body = ‘This is a reminder’;

MailApp.sendEmail(email,subject,body);

range.setValue(today);

}

Logger.log(diff);

}else{

Logger.log(user[4]);

}

})

//Logger.log(users);

}

The senderEmail function retrieves data from a Google Sheet named “Users”, which contains information about users including their email addresses, a date of last contact, and a reminder flag. The function then iterates over each row of user data and checks if the reminder flag is empty.

If the reminder flag is empty, the function calculates the difference between today’s date and the date of last contact for the user. If the difference is negative (meaning the last contact date is in the past), the function sends an email to the user reminding them to make contact, and updates the reminder flag with today’s date.

The function also logs the difference in days between today’s date and the last contact date for each user. If the reminder flag is not empty, the function simply logs its value.

Overall, this function appears to be designed to automate the process of sending reminders to users who have not made contact in a while, and to track when those reminders were sent. However, without more context about the purpose and use case of the “Users” sheet, it is difficult to determine the full scope and functionality of the code.

Create a custom menu in Google Sheets to run a script:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Run Script’, ‘myFunction’)

.addToUi();

}

function myFunction() {

// your code here

}

Explanation: This code creates a custom menu in Google Sheets called “Custom Menu” that contains an option to run a script called myFunction. The onOpen function is a built-in function that runs automatically when the Google Sheet is opened, and it creates the custom menu using the Ui class. The myFunction function can be replaced with any script you want to run from the menu.

Add and run functions.

Custom Sheet UI Menu

function onOpen(){

const ui = SpreadsheetApp.getUi();

ui.createMenu(‘adv’)

.addItem(‘Fun1′,’fun1’)

.addItem(‘Fun2′,’fun2’)

.addItem(‘Fun3′,’fun3’)

.addToUi()

}

function fun1(){

SpreadsheetApp.getUi().alert(‘You clicked 1’);

}

function fun2(){

SpreadsheetApp.getUi().alert(‘You clicked 2’);

}

function fun3(){

SpreadsheetApp.getUi().alert(‘You clicked 3’);

}

The above code defines four functions – onOpen, fun1, fun2, and fun3 – that can be used in conjunction with a Google Sheets document.

The onOpen function is a special function that is called automatically when the sheet is opened. It creates a new custom menu in the sheet’s UI called “adv”, which contains three menu items – “Fun1”, “Fun2”, and “Fun3” – each of which calls one of the other three functions when clicked. The addToUi method is then called to add this menu to the sheet’s UI.

The fun1, fun2, and fun3 functions are each simple functions that display an alert message using the SpreadsheetApp.getUi().alert() method. When one of the menu items is clicked, the corresponding function is called, which displays an alert message with a unique message indicating which function was triggered.

Overall, this code creates a simple custom menu in a Google Sheets document, which provides easy access to a few custom functions that can perform various actions or display information to the user. This can be particularly useful for creating custom workflows or automating common tasks within a spreadsheet.

Use the Google Drive API to create a new folder:

function createFolder() {

var folderName = “New Folder”;

var folder = DriveApp.createFolder(folderName);

Logger.log(“Created folder with ID: ” + folder.getId());

}

Explanation: This code defines a function called createFolder that uses the Google Drive API to create a new folder with the name “New Folder”. The function calls the createFolder method of the DriveApp class, which returns a folder object with information about the new folder. The Logger.log method is used to print the ID of the new folder to the Logs.

Create new Folder within Folder

function cFolder(){

const folderName = ‘New Folder Today’;

const folder = DriveApp.createFolder(folderName);

Logger.log(`Created Folder ID ${folder.getId()}`);

}

function addFolder(){

const id = ‘1SjwbFJA5FvHXpNQMSE’;

const folderName = ‘Inside Folder’;

const main = DriveApp.getFolderById(id);

const folder = main.createFolder(folderName);

Logger.log(`Created Folder ID ${folder.getId()}`);

}

The above code defines two functions that use the Google Drive API to create new folders in a Google Drive account.

The cFolder function creates a new folder in the root directory of the user’s Google Drive account. It starts by setting the desired folder name in a variable called folderName. It then calls the DriveApp.createFolder(folderName) method to create a new folder with the specified name, and stores the resulting folder object in a variable called folder. Finally, it logs a message to the console indicating the ID of the new folder that was created using the Logger.log() method.

The addFolder function creates a new folder inside an existing folder with a specific ID. It starts by setting the desired folder name in a variable called folderName, and the ID of the parent folder where the new folder will be created in a variable called id. It then calls the DriveApp.getFolderById(id) method to retrieve the parent folder object based on the specified ID. It then calls the createFolder(folderName) method on the parent folder object to create a new folder with the specified name, and stores the resulting folder object in a variable called folder. Finally, it logs a message to the console indicating the ID of the new folder that was created using the Logger.log() method.

Overall, these functions demonstrate how to use the Google Drive API in Google Apps Script to create new folders in a Google Drive account. The first function creates a new folder in the root directory, while the second function creates a new folder inside an existing folder by specifying its parent folder ID. These functions could be useful for automating file organization or for creating new folders as part of a larger Google Apps Script workflow.

Send an email when a Google Form is submitted:

function sendEmail(e) {

var name = e.namedValues[“Name”][0];

var email = e.namedValues[“Email”][0];

var message = e.namedValues[“Message”][0];

var subject = “New message from ” + name;

var body = “Name: ” + name + “\nEmail: ” + email + “\n\nMessage:\n” + message;

MailApp.sendEmail(“youremail@example.com”, subject, body);

}

This script sends an email to “youremail@example.com” when someone submits a Google Form. It extracts the form data using the namedValues property of the form submission event object.

Use form data in Email

function onFormSubmit(e){

const vals = e.namedValues;

const subject = ‘New Form Submission’;

const email = ‘gappscourses@gmail.com’;

const message = JSON.stringify(vals);

const body = `Message ${vals.message[0]} Name ${vals.name[0]} Name ${vals[“Email Address”][0]}`;

MailApp.sendEmail(email,subject,body);

}

Set up a trigger to capture the form submission.

Generate a PDF from a Google Doc and save it to Google Drive:

function generatePDF() {

var docFile = DriveApp.getFileById(“DOCUMENT_ID”);

var docName = docFile.getName();

var pdfFile = DriveApp.createFile(docFile.getAs(“application/pdf”));

pdfFile.setName(docName + “.pdf”);

}

This script generates a PDF file from a Google Doc with the ID “DOCUMENT_ID” and saves it to the root folder of Google Drive. It uses the getAs method of the file object to convert the Doc to a PDF.

Create a custom menu in Google Sheets:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(“My Menu”)

.addItem(“Item 1”, “menuItem1”)

.addItem(“Item 2”, “menuItem2”)

.addToUi();

}

function menuItem1() {

Browser.msgBox(“You clicked Item 1!”);

}

function menuItem2() {

Browser.msgBox(“You clicked Item 2!”);

}

This script creates a custom menu in a Google Sheets document. When the user clicks on one of the menu items, it displays a message box with a corresponding message. The onOpen function is a special function that runs automatically when the sheet is opened.

Import data from a CSV file and write it to a Google Sheet:

function importCSV() {

var file = DriveApp.getFilesByName(“FILE_NAME”).next();

var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());

var sheet = SpreadsheetApp.getActiveSheet();

sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}

This script imports data from a CSV file with the name “FILE_NAME” and writes it to the active sheet in the current Google Sheets document. It uses the parseCsv method of the Utilities class to parse the CSV data into a 2D array, and the setValues method of the sheet object to write the data to the sheet.

Add a trigger to run a function on a schedule:

function myFunction() {

Logger.log(“This function runs on a schedule!”);

}

function createTrigger() {

ScriptApp.newTrigger(“myFunction”)

.timeBased()

.everyHours(1)

.create();

}

This script creates a trigger that runs the myFunction function every hour. The createTrigger function uses the newTrigger method of the ScriptApp class to create the trigger, and the timeBased method to specify that it should run on a schedule.

Send email from a Google Sheet:

function sendEmail() {

var sheet = SpreadsheetApp.getActiveSheet();

var emailRange = sheet.getRange(“A2:B5”);

var emailValues = emailRange.getValues();

for (var i = 0; i < emailValues.length; i++) {

var recipient = emailValues[i][0];

var subject = emailValues[i][1];

var body = “Dear ” + recipient + “,\n\nThis is an automated email.\n\nBest regards,\nYour Name”;

MailApp.sendEmail(recipient, subject, body);

}

This script sends an automated email to a list of recipients and subjects specified in cells A2:B5 of the active sheet. The recipient and subject are read from the sheet, and the body of the email is generated dynamically.

Create a custom menu in Google Sheets:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Sort A-Z’, ‘sortAtoZ’)

.addItem(‘Sort Z-A’, ‘sortZtoA’)

.addToUi();

}

function sortAtoZ() {

var sheet = SpreadsheetApp.getActiveSheet();

sheet.getRange(“A2:C10”).sort(1);

}

function sortZtoA() {

var sheet = SpreadsheetApp.getActiveSheet();

sheet.getRange(“A2:C10”).sort(1, false);

}

This script creates a custom menu in Google Sheets with two options to sort data in ascending or descending order. When the user selects one of the options, the corresponding function is called to sort the data in the active sheet.

Automate Google Forms:

function onFormSubmit(e) {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn());

var values = range.getValues()[0];

var recipient = values[0];

var subject = “New Form Submission”;

var body = “Hello,\n\nA new form has been submitted.\n\nThank you.”;

MailApp.sendEmail(recipient, subject, body);

}

This script sends an email notification to the recipient specified in the first column of the sheet when a new form submission is received. The function is triggered automatically when a new form submission is added to the sheet.

Create a custom Google Drive folder:

function createFolder() {

var folderName = “My Custom Folder”;

var parentFolder = DriveApp.getRootFolder();

var newFolder = parentFolder.createFolder(folderName);

Logger.log(“New folder created: ” + newFolder.getName());

}

This script creates a new folder called “My Custom Folder” in the root folder of Google Drive. The script logs the name of the newly created folder for verification.

Generate a random password:

function generatePassword() {

var length = 12;

var charset = “abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789”;

var password = “”;

for (var i = 0; i < length; i++) {

password += charset.charAt(Math.floor(Math.random() * charset.length));

}

Logger.log(“New password generated: ” + password);

}

This script generates a random password with a length of 12 characters using a combination of lowercase and uppercase letters and numbers. The script logs the newly generated password for verification.

Create a custom function in Google Sheets:

function addNumbers(a, b) {

return a + b;

}

This script creates a custom function called addNumbers that takes two arguments and returns their sum. To use the custom function in a cell in Google Sheets, type =addNumbers(a, b) where a and b are the values you want to add.

Copy a sheet to a new spreadsheet:

function copySheet() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

var newSpreadsheet = SpreadsheetApp.create(“New Spreadsheet”);

var destinationSheet = newSpreadsheet.getActiveSheet();

sheet.copyTo(destinationSheet);

newSpreadsheet.deleteSheet(newSpreadsheet.getSheetByName(“Sheet1”));

Logger.log(“Sheet copied to new spreadsheet.”);

}

This script copies the contents of a sheet called “Sheet1” in the active spreadsheet to a new spreadsheet called “New Spreadsheet”. The script then deletes the default sheet in the new spreadsheet and logs a message for verification.

Extract data from a PDF file:

function extractPDF() {

var file = DriveApp.getFilesByName(“My PDF File.pdf”).next();

var blob = file.getBlob();

var data = extractTextFromPDF(blob);

Logger.log(data);

}

function extractTextFromPDF(blob) {

var resource = {

title: blob.getName(),

mimeType: blob.getContentType()

};

var options = {

ocr: true,

ocrLanguage: “en”

};

var text = Drive.Files.insert(resource, blob, options).ocrResult.text;

return text;

}

This script extracts text from a PDF file called “My PDF File.pdf” stored in Google Drive using OCR (Optical Character Recognition). The script first retrieves the file as a blob and then passes it to a helper function called extractTextFromPDF. The function uses the Drive API to insert the blob as a new file with OCR enabled and returns the extracted text.

Merge cells in Google Sheets:

function mergeCells() {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getRange(“A1:B2”);

range.merge();

Logger.log(“Cells merged.”);

}

This script merges the cells in the range A1:B2 in the active sheet in Google Sheets. The script logs a message for verification.

Export data from Google Sheets to a CSV file:

function exportCSV() {

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getDataRange();

var values = range.getValues();

var csv = “”;

for (var i = 0; i < values.length; i++) {

csv += values[i].join(“,”) + “\n”;

}

var file = DriveApp.createFile(“Exported Data.csv”, csv, MimeType.CSV);

Logger.log(“CSV file created: ” + file.getName());

}

This script exports the data from the active sheet in Google Sheets to a CSV file called “Exported Data.csv” stored in Google Drive. The script reads the values in the sheet and converts them to a CSV format. The script then creates a new file in Google Drive with the exported data and logs a message for verification.

Create a Custom Function to Calculate Fibonacci Sequence:

function fibonacci(n) {

if (n <= 1) {

return n;

} else {

return fibonacci(n-1) + fibonacci(n-2);

}

Explanation: This script defines a custom function fibonacci that calculates the Fibonacci sequence up to a given number n. The function uses a recursive approach where the base case is when n is 0 or 1, and the recursive case is when n is greater than 1. The function returns the sum of the two previous numbers in the sequence to generate the next number.

Custom function to calculate the Fibonacci sequence using Google Apps Script:

function fibonacci(n) {

if (n <= 1) {

return n;

} else {

return fibonacci(n-1) + fibonacci(n-2);

}

To use this function in a Google Sheet, follow these steps:

Open a new or existing Google Sheet.

Click on the cell where you want to display the first number in the Fibonacci sequence.

Type the equal sign followed by the name of the function, “fibonacci”.

Inside the parentheses, enter the number of the sequence you want to calculate.

For example, to calculate the 7th number in the Fibonacci sequence, type “=fibonacci(7)” in a cell.

The function uses a recursive approach to calculate the Fibonacci sequence. If the input number is less than or equal to 1, the function returns the input number as is. Otherwise, the function calls itself recursively with the input number decreased by 1 and 2, and returns the sum of the results. This approach generates the Fibonacci sequence by summing the two previous numbers in the sequence to generate the next number.

Note that custom functions in Google Sheets have some limitations, such as not being able to modify cells outside of the function’s parent range and not supporting loops or prompts.

Send Emails to a List of Recipients from Google Sheets:

function sendEmails() {

var sheet = SpreadsheetApp.getActiveSheet();

var startRow = 2; // First row of data to process

var numRows = 3; // Number of rows to process

var dataRange = sheet.getRange(startRow, 1, numRows, 3);

var data = dataRange.getValues();

for (var i = 0; i < data.length; ++i) {

var row = data[i];

var emailAddress = row[0]; // First column

var subject = row[1]; // Second column

var message = row[2]; // Third column

MailApp.sendEmail(emailAddress, subject, message);

}

Explanation: This script retrieves data from a Google Sheet starting from row 2 and processes 3 rows. The data is retrieved using the getRange method and the getValues method retrieves the data from the range as a 2D array. The script then loops through each row in the array and retrieves the email address, subject, and message. Finally, the script sends an email to the recipient using the MailApp.sendEmail method.

Create a Custom Menu in Google Sheets:

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(‘Custom Menu’)

.addItem(‘Open Dialog’, ‘openDialog’)

.addToUi();

}

function openDialog() {

var html = HtmlService.createHtmlOutputFromFile(‘Page’)

.setWidth(400)

.setHeight(300);

SpreadsheetApp.getUi().showModalDialog(html, ‘Dialog Title’);

}

Explanation: This script creates a custom menu in a Google Sheet using the onOpen trigger. The menu is called Custom Menu and it contains one item called Open Dialog. When the user clicks on this item, it calls the openDialog function, which creates an HTML dialog box using the HtmlService.createHtmlOutputFromFile method. The dialog box is displayed using the showModalDialog method and the title is set to Dialog Title.

Dialog with HTML page contents

index.html file code

<!DOCTYPE html>

<html>

<head>

<style>

body{

background-color:red;

}

</style>

</head>

<body>

<h1>Laurence Svekis</h1>

</body>

</html>

function onOpen(){

const ui = SpreadsheetApp.getUi();

ui.createMenu(‘Menu’)

.addItem(‘open’,’openDialog’)

.addToUi();

}

function openDialog(){

const html = HtmlService.createHtmlOutputFromFile(‘index’)

.setWidth(400)

.setHeight(300);

SpreadsheetApp.getUi().showModalDialog(html,’Dialog’);

}

Automatically Insert the Date in Google Sheets:

function onEdit(e) {

var range = e.range;

var column = range.getColumn();

if (column == 1) {

var row = range.getRow();

var dateCell = range.offset(0, 1);

dateCell.setValue(new Date());

}

Explanation: This script automatically inserts the current date in a Google Sheet whenever a user edits a cell in the first column. The script uses the onEdit trigger to detect when a cell is edited and retrieves the range of the edited cell. The script checks if the edited cell is in the first column using the getColumn method. If it is, the script retrieves the row and offsets the range by one column to the right using the offset method. Finally, the script sets the value of the offset cell to the current date using the setValue method.

Custom Log and onEdit Function

function onEdit(e){

const range = e.range;

const col = range.getColumn();

if(col == 1){

const row = range.getRow();

const dateCell = range.offset(0,1);

dateCell.setValue(new Date());

logN(col);

logN(JSON.stringify(e));

}

function logN(val){

const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘log’);

ss.appendRow([val]);

}

Create a Custom Function to Capitalize the First Letter of Each Word in a String:

function capitalizeWords(str) {

var words = str.split(‘ ‘);

for (var i = 0; i < words.length; i++) {

var word = words[i];

var firstLetter = word.charAt(0).toUpperCase();

var restOfWord = word.slice(1).toLowerCase();

words[i] = firstLetter + restOfWord;

}

return words.join(‘ ‘);

}

Explanation: This script defines a custom function called capitalizeWords that capitalizes the first letter of each word in a string. The function splits the string into an array of words using the split method and then loops through each word. For each word, the function retrieves the first letter using the charAt method and capitalizes it using the toUpperCase method. The function also retrieves the rest of the word using the slice method and converts it to lowercase using the toLowerCase method. Finally, the function combines the first letter and the rest of the word using the + operator and sets the value of the current word in the array. The function then returns the modified array of words joined back into a string using the join method.

Sending an email using Gmail service

function sendEmail() {

const rep = “example@example.com”;

const sub = ‘Hello World’;

const message = ‘Hi, Laurence’;

GmailApp.sendEmail(rec, sub, message);

}

function mySender2() {

const rep = “example@example.com”;

const sub = ‘Hello World’;

const message = ‘Hi, Laurence’;

MailApp.sendEmail(rep,sub,message);

}

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 = SpreadsheetApp.getActiveSheet();

const val = ‘Laurence Svekis’;

const range = sheet.getRange(‘A1’);

range.setValue(val);

Logger.log(sheet);

}

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 = SpreadsheetApp.openById(id).getSheetByName(‘Sheet12’);

const val = ‘Laurence Svekis 2’;

const range = sheet.getRange(1,4,2,2);

range.setValue(val);

const range2 = sheet.getRange(4,1,2,2);

range2.setValues([[val,val],[val,val]]);

Logger.log(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.

Creating a new Google Calendar event

function createEvent() {

var calendar = CalendarApp.getCalendarById(“calendar-id”);

var title = “Meeting”;

var start = new Date(“February 19, 2023 9:00:00 GMT-05:00”);

var end = new Date(“February 19, 2023 10:00:00 GMT-05:00”);

var event = calendar.createEvent(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 = SpreadsheetApp.getActiveSpreadsheet();

var newSheet = sheet.copy(“Copy of My Sheet”);

var newUrl = newSheet.getUrl();

}

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 = SpreadsheetApp.getUi();

ui.createMenu(“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.

Generating a PDF from a Google Doc

function createPDF() {

var doc = DocumentApp.getActiveDocument();

var pdf = DriveApp.createFile(doc.getAs(‘application/pdf’));

var pdfUrl = pdf.getUrl();

}

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 = DriveApp.createFolder(folderName);

var folderUrl = folder.getUrl();

}

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.

Creating a new Google Form

function createForm() {

var form = FormApp.create(‘My Form’);

var formUrl = form.getPublishedUrl();

}

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 = SpreadsheetApp.openById(sheetId);

var sheetName = sheet.getName();

}

This script opens a Google Sheet with the specified ID and returns its name.

Reading data from a Google Sheet

function readSheet() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);

var data = sheet.getDataRange().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 = SlidesApp.create(‘My Presentation’);

var presentationUrl = presentation.getUrl();

}

This script creates a new Google Slides presentation with the specified name and returns its URL.

Adding a slide to a Google Slides presentation

function addSlide() {

var presentation = SlidesApp.getActivePresentation();

var slide = presentation.appendSlide();

var slideIndex = slide.getIndex();

}

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 = SlidesApp.getActivePresentation();

var slide = presentation.getSlides()[0];

var imageUrl = “https://example.com/image.jpg”;

var image = slide.insertImage(imageUrl);

}

This script adds an image from the specified URL to the first slide of the current Google Slides presentation.

Reading data from an external API and writing it to a Google Sheet

function writeData() {

var apiKey = “your-api-key”;

var url = “https://example.com/api?key=” + apiKey;

var response = UrlFetchApp.fetch(url);

var data = JSON.parse(response.getContentText());

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘Sheet1’);

sheet.getRange(1, 1, data.length, 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

function copyFile() {

var fileId = “your-file-id”;

var folderId = “your-folder-id”;

var file = DriveApp.getFileById(fileId);

var folder = DriveApp.getFolderById(folderId);

var copy = file.makeCopy(file.getName(), 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 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

var data = sheet.getDataRange().getValues();

var emailAddress = “recipient@example.com”;

var message = “Hello, ” + data[1][0] + “! This is an automated message.”;

var subject = “Automated email”;

MailApp.sendEmail(emailAddress, subject, message);

}

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 = DocumentApp.create(“My Document”);

var docUrl = doc.getUrl();

}

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 = DocumentApp.getActiveDocument();

var body = doc.getBody();

body.appendParagraph(“This is a new paragraph.”);

}

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 = FormApp.create(‘My Form’);

var item = form.addMultipleChoiceItem();

item.setTitle(‘What is your favorite color?’)

.setChoices([

item.createChoice(‘Red’),

item.createChoice(‘Blue’),

item.createChoice(‘Green’)

]);

var formUrl = form.getPublishedUrl();

}

This script creates a new Google Form with a multiple choice question asking about a favorite color.

Creating a new Google Sheet and populating it with data

function createSheet() {

var sheet = SpreadsheetApp.create(“My Sheet”);

var data = [

[“Name”, “Age”, “Location”],

[“John”, 32, “New York”],

[“Mary”, 25, “Chicago”],

[“David”, 43, “Los Angeles”]

];

sheet.getRange(1, 1, data.length, data[0].length).setValues(data);

var sheetUrl = sheet.getUrl();

}

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

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

sheet.getRange(“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 = DriveApp.createFolder(“My Folder”);

var folderUrl = folder.getUrl();

}

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”;

var file = DriveApp.getFileById(fileId);

var folder = DriveApp.getFolderById(folderId);

folder.addFile(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() {

ScriptApp.newTrigger(“myFunction”)

.timeBased()

.atDate(2023, 3, 1)

.create();

}

This script creates a new trigger that will run the myFunction function on March 1, 2023.

Copying data from one Google Sheet to another

function copyData() {

var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”);

var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet2”);

var sourceData = sourceSheet.getDataRange().getValues();

targetSheet.getRange(1, 1, sourceData.length, 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 = FormApp.openByUrl(“form-url”);

var responses = form.getResponses();

var lastResponse = responses[responses.length – 1];

var itemResponses = lastResponse.getItemResponses();

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 = SlidesApp.create(“My Presentation”);

var slide = presentation.appendSlide();

slide.insertTextBox(“Hello, world!”, 100, 100);

var presentationUrl = presentation.getUrl();

}

This script creates a new Google Slides presentation with a single slide that contains a text box.

Copying a Google Document to another user’s Drive

function copyDocToDrive() {

var docId = “document-id”;

var targetEmail = “target-email@example.com”;

var file = DriveApp.getFileById(docId);

var targetFolder = DriveApp.getFolderById(targetFolderId);

var targetFile = file.makeCopy(“Copy of My Document”, targetFolder);

var targetUser = UserManager.getUserByEmail(targetEmail);

DriveApp.getFolderById(targetFolderId).addViewer(targetUser);

MailApp.sendEmail(targetEmail, “Document copied”, “Here is a copy of My Document: ” + targetFile.getUrl());

}

This script makes a copy of a Google Document and shares it with the specified user via email.

Creating a new Google Forms quiz

function createQuiz() {

var form = FormApp.create(‘My Quiz’);

var item = form.addCheckboxItem();

item.setTitle(‘What is the capital of France?’)

.setPoints(1)

.setChoices([

item.createChoice(‘Paris’, true),

item.createChoice(‘London’),

item.createChoice(‘Berlin’)

]);

form.setIsQuiz(true);

form.setConfirmationMessage(“Thanks for taking the quiz!”);

var formUrl = form.getPublishedUrl();

}

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

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu(“My Menu”)

.addItem(“Item 1”, “myFunction1”)

.addItem(“Item 2”, “myFunction2”)

.addSeparator()

.addSubMenu(ui.createMenu(“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 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getRange(1, 1, 5, 5);

range.setFontFamily(“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 = CalendarApp.getDefaultCalendar();

var title = “My Event”;

var startTime = new Date(“2023-03-01T09:00:00Z”);

var endTime = new Date(“2023-03-01T10:00:00Z”);

var event = calendar.createEvent(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

function deleteRows() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var data = sheet.getDataRange().getValues();

for (var i = data.length – 1; i >= 0; i–) {

if (data[i][0] == “Delete”) {

sheet.deleteRow(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 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var data = sheet.getDataRange().getValues();

var emailAddress = “example@gmail.com”;

var subject = “My Subject”;

var message = “My Message”;

MailApp.sendEmail(emailAddress, subject, message);

}

This script sends an email with the subject “My Subject” and message “My Message” to the email address “example@gmail.com” using the MailApp service.

Creating a new Google Forms response

function createFormResponse() {

var form = FormApp.openById(“FORM_ID”);

var response = form.createResponse();

var item = form.getItemById(ITEM_ID);

var answer = item.asTextItem().createResponse(“My Answer”);

response.withItemResponse(answer);

response.submit();

}

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.

Importing data from a CSV file to a Google Sheets document

function importCSV() {

var file = DriveApp.getFileById(“FILE_ID”);

var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

sheet.getRange(1, 1, csvData.length, 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

function copyFile() {

var file = DriveApp.getFileById(“FILE_ID”);

var folder = DriveApp.getFolderById(“FOLDER_ID”);

var copy = file.makeCopy(“Copy of ” + file.getName(), 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 = DocumentApp.create(title);

doc.getBody().setText(body);

}

This script creates a new Google Document with the title “My Document” and body content “My Document Content” using the DocumentApp service.

Setting the background color of a range of cells in a Google Sheets document

function setBackgroundColor() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var range = sheet.getRange(1, 1, 5, 5);

range.setBackground(“#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 = SpreadsheetApp.getActiveSpreadsheet();

var sheet = spreadsheet.getActiveSheet();

var url = spreadsheet.getUrl();

url = url.replace(/edit$/, ”) + ‘export?exportFormat=pdf&format=pdf&gid=’ + sheet.getSheetId();

var token = ScriptApp.getOAuthToken();

var response = UrlFetchApp.fetch(url, {

headers: {

‘Authorization’: ‘Bearer ‘ + token

}

});

var pdfBlob = response.getBlob();

var folder = DriveApp.getFolderById(“FOLDER_ID”);

var file = folder.createFile(pdfBlob).setName(spreadsheet.getName() + “.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() {

var title = “My Event”;

var description = “My Event Description”;

var startTime = new Date(“2023-02-20T09:00:00-08:00”);

var endTime = new Date(“2023-02-20T10:00:00-08:00”);

var event = CalendarApp.getDefaultCalendar().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 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var data = sheet.getDataRange().getValues();

for (var i = 0; i < data.length; i++) {

if (data[i][0] == “John Doe”) {

sheet.getRange(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@example.com”;

var subject = “PDF Attachment”;

var body = “Please see the attached PDF file.”;

var pdfFile = DriveApp.getFileById(“PDF_FILE_ID”);

var pdfBlob = pdfFile.getBlob();

MailApp.sendEmail(emailAddress, subject, body, {attachments: [pdfBlob]});

}

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 = SlidesApp.create(title);

var slide1 = slides.getSlides()[0];

var shape1 = slide1.insertShape(SlidesApp.ShapeType.RECTANGLE, 50, 50, 100, 100);

shape1.getFill().setSolidFill(“#4285F4”);

var shape2 = slide1.insertShape(SlidesApp.ShapeType.ELLIPSE, 200, 50, 100, 100);

shape2.getFill().setSolidFill(“#EA4335”);

var shape3 = slide1.insertShape(SlidesApp.ShapeType.CHEVRON, 350, 50, 100, 100);

shape3.getFill().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.

Complete-Apps-Script-Coding-Example-Guide Download