Google Apps Script Coding Examples

by

Google Apps Script Coding Examples

Send Email using Gmail API
Get Data from Google Sheets
Add a Custom Menu to Google Sheets
Create a Google Calendar Event
Access the Google Drive API
Creating Google Calendar Events:
Accessing and Modifying Google Sheets:
Accessing and Modifying Google Docs:
Creating Google Forms:
Creating Google Slides:
Accessing and Modifying Google Forms:
Using Google Sheets as a Database:
Sending Emails With Attachments:
Creating and Modifying Google Calendar Events:

Send Email using Gmail API

function sendEmail() {

  var recipient = “john.doe@example.com”;

  var subject = “Test email”;

  var body = “This is a test email sent from Google Apps Script”;

  GmailApp.sendEmail(recipient, subject, body);

}

This script uses the GmailApp class in Google Apps Script to send an email to a recipient. The recipient’s email address is specified in the recipient variable, the subject of the email is specified in the subject variable, and the body of the email is specified in the body variable. The sendEmail method of the GmailApp class is then called with these parameters to send the email.

Get Data from Google Sheets

function getData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

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

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

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

    Logger.log(data[i][0] + ” ” + data[i][1]);

  }

}

This script uses the SpreadsheetApp class in Google Apps Script to get data from a Google Sheets spreadsheet. The active spreadsheet is retrieved with the getActiveSpreadsheet method, the sheet with the name “Sheet1” is retrieved with the getSheetByName method, and the data in the sheet is retrieved with the getDataRange and getValues methods. The data is then logged to the console using a for loop.

Add a Custom Menu to Google Sheets

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(“Custom Menu”)

      .addItem(“Option 1”, “option1”)

      .addItem(“Option 2”, “option2”)

      .addToUi();

}

function option1() {

  Browser.msgBox(“Option 1 selected”);

}

function option2() {

  Browser.msgBox(“Option 2 selected”);

}

This script adds a custom menu to a Google Sheets spreadsheet using the SpreadsheetApp and Ui classes in Google Apps Script. The onOpen function is called when the spreadsheet is opened, and it creates a new menu called “Custom Menu” with two options: “Option 1” and “Option 2”. When each option is selected, a message box is displayed with the selected option.

Create a Google Calendar Event

function createEvent() {

  var calendar = CalendarApp.getDefaultCalendar();

  var title = “Test event”;

  var startTime = new Date(“March 15, 2023 08:00:00”);

  var endTime = new Date(“March 15, 2023 09:00:00”);

  var description = “This is a test event created with Google Apps Script”;

  var event = calendar.createEvent(title, startTime, endTime, {

    description: description

  });

  Logger.log(“Event created: ” + event.getTitle());

}

This script uses the CalendarApp class in Google Apps Script to create a new event in the user’s default Google Calendar. The title of the event is specified in the title variable, the start time is specified in the startTime variable, the end time is specified in the endTime variable, and the description is specified in the description variable. The createEvent method of the CalendarApp class is then called with these parameters and an object containing the event description to create the event. Finally, the title of the created event is logged to the console using the Logger.log method.

Access the Google Drive API

function listFiles() {

  var folderId = “XXXXXXXXXXXXXXXXXXXXXXX”; // Replace with your folder ID

  var folder = DriveApp.getFolderById(folderId);

  var files = folder.getFiles();

  while (files.hasNext()) {

    var file = files.next();

    Logger.log(file.getName());

  }

}

This script uses the DriveApp class in Google Apps Script to access the Google Drive API and retrieve a list of files in a specified folder. The ID of the folder is specified in the folderId variable, and the folder is retrieved with the getFolderById method of the DriveApp class. The files in the folder are then retrieved with the getFiles method, and a while loop is used to iterate through the files and log their names to the console using the Logger.log method.

Sending Emails From Google Sheets:

One useful application of Google Apps Script is automating email sending. You can use Google Sheets as a data source and send personalized emails to recipients based on the information in the sheet. Here is an example code:

css

Copy code

function sendEmails() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var dataRange = sheet.getDataRange();

  var data = dataRange.getValues();

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

    var row = data[i];

    var emailAddress = row[0];

    var message = ‘Dear ‘ + row[1] + ‘,\n\n’ + ‘This is a test email.’;

    var subject = ‘Test Email’;

    MailApp.sendEmail(emailAddress, subject, message);

  }

}

This code loops through the rows of a sheet, retrieves the email address and name of each recipient, and sends a personalized email using the MailApp.sendEmail() function.

Creating Google Calendar Events:

Another useful application of Google Apps Script is creating Google Calendar events programmatically. Here is an example code:

function createEvent() {

  var calendar = CalendarApp.getDefaultCalendar();

  var event = calendar.createEvent(‘Test Event’, new Date(‘March 2, 2023 09:00:00 EST’), new Date(‘March 2, 2023 10:00:00 EST’), {

    description: ‘This is a test event’

  });

  Logger.log(‘Event ID: ‘ + event.getId());

}

This code creates a new Google Calendar event with a title, start and end date/time, and a description. It also logs the ID of the newly created event for reference.

Accessing and Modifying Google Sheets:

Google Sheets is a popular application for managing and analyzing data. Google Apps Script can be used to access and modify data in Sheets programmatically. Here is an example code:

function modifySheet() {

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

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

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

    var row = data[i];

    if (row[0] == ‘John’) {

      sheet.getRange(i + 1, 2).setValue(‘Active’);

    }

  }

}

This code retrieves data from the active sheet, loops through each row, and checks if the name in the first column is ‘John’. If it is, the script sets the value of the second column to ‘Active’ using the sheet.getRange() and .setValue() functions.

Accessing and Modifying Google Docs:

Google Docs is a popular application for creating and sharing documents. Google Apps Script can be used to access and modify documents programmatically. Here is an example code:

function modifyDoc() {

  var doc = DocumentApp.getActiveDocument();

  var body = doc.getBody();

  var paragraphs = body.getParagraphs();

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

    var paragraph = paragraphs[i];

    if (paragraph.getText().indexOf(‘Lorem ipsum’) !== -1) {

      paragraph.setAttributes({

        bold: true

      });

    }

  }

}

This code retrieves the active Google Doc, loops through each paragraph, and checks if the text contains the phrase ‘Lorem ipsum’. If it does, the script sets the bold attribute of that paragraph to true using the paragraph.setAttributes() function.

Creating Google Forms:

Google Forms is a powerful tool for creating surveys, quizzes, and other types of forms. Google Apps Script can be used to create and customize Google Forms programmatically. Here is an example code:

function createForm() {

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

  form.addTextItem()

    .setTitle(‘What is your name?’)

    .setRequired(true);

  form.addMultipleChoiceItem()

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

    .setChoices([

      form.createChoice(‘Red’),

      form.createChoice(‘Blue’),

      form.createChoice(‘Green’)

    ])

    .setRequired(true);

  Logger.log(‘Form URL: ‘ + form.getPublishedUrl());

}

This code creates a new Google Form with a title, a text question, and a multiple choice question with three choices. It also logs the URL of the published form for reference. The form can be further customized using other methods available in the Form service of Google Apps Script.

These are just a few examples of what you can do with Google Apps Script. With this powerful scripting language, you can automate various tasks and extend the functionality of Google Workspace applications according to your needs.

Creating Google Slides:

Google Slides is a popular application for creating presentations. Google Apps Script can be used to create and customize Google Slides programmatically. Here is an example code:

function createSlide() {

  var presentation = SlidesApp.create(‘Test Presentation’);

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

  slide.insertTextBox(‘Title’, 0, 0).getText().setText(‘Test Slide’);

  slide.insertShape(SlidesApp.ShapeType.RECTANGLE, 50, 50, 100, 100);

  Logger.log(‘Presentation URL: ‘ + presentation.getUrl());

}

This code creates a new Google Slides presentation with a title, adds a new slide to the presentation, sets the title of the slide to ‘Test Slide’, and inserts a rectangle shape on the slide. It also logs the URL of the newly created presentation for reference.

Accessing and Modifying Google Forms:

Google Forms is a powerful tool for creating surveys, quizzes, and other types of forms. Google Apps Script can be used to access and modify existing Google Forms programmatically. Here is an example code:

function modifyForm() {

  var form = FormApp.openByUrl(‘https://forms.google.com/…’);

  var items = form.getItems();

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

    var item = items[i];

    if (item.getTitle() == ‘Favorite color’) {

      item.asMultipleChoiceItem().setChoiceValues([‘Red’, ‘Blue’, ‘Green’]);

    }

  }

  Logger.log(‘Form URL: ‘ + form.getPublishedUrl());

}

This code opens an existing Google Form by URL, retrieves all the form items, and modifies the choices of the multiple choice item with the title ‘Favorite color’. It also logs the URL of the published form for reference.

Using Google Sheets as a Database:

Google Sheets can be used as a database to store and retrieve data. Google Apps Script can be used to interact with the Sheets database programmatically. Here is an example code:

function getCustomerData(customerId) {

  var sheet = SpreadsheetApp.openById(‘…’).getSheetByName(‘Customers’);

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

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

    var row = data[i];

    if (row[0] == customerId) {

      return {

        name: row[1],

        email: row[2],

        phone: row[3]

      };

    }

  }

  return null;

}

This code retrieves customer data from a Google Sheets database by ID. It loops through all the rows of the ‘Customers’ sheet, matches the customer ID with the first column of each row, and returns an object with the name, email, and phone number of the customer if a match is found. If no match is found, it returns null.

Sending Emails With Attachments:

Google Apps Script can be used to send emails with attachments using the Gmail service. Here is an example code:

function sendEmailWithAttachment() {

  var recipient = ‘example@example.com’;

  var subject = ‘Test Email with Attachment’;

  var body = ‘This is a test email with an attachment.’;

  var attachment = DriveApp.getFileById(‘…’);

  GmailApp.sendEmail(recipient, subject, body, {

    attachments: [attachment]

  });

}

This code sends an email with an attachment to a recipient using the GmailApp.sendEmail() function. The attachment is retrieved by ID using the DriveApp.getFileById() function.

Creating and Modifying Google Calendar Events:

Google Calendar is a powerful tool for managing events and schedules. Google Apps Script can be used to create and modify Google Calendar events programmatically. Here is an example code:

function createCalendarEvent() {

  var calendar = CalendarApp.getCalendarById(‘…’);

  var event = calendar.createEvent(‘Test Event’,

    new Date(‘2023-03-03T10:00:00.000Z’),

    new Date(‘2023-03-03T11:00:00.000Z’),

    {description: ‘This is a test event.’});

  Logger.log(‘Event ID: ‘ + event.getId());

}

function modifyCalendarEvent() {

  var calendar = CalendarApp.getCalendarById(‘…’);

  var eventId = ‘…’;

  var event = calendar.getEventById(eventId);

  event.setTitle(‘New Title’);

  event.setDescription(‘New Description’);

  event.setTime(new Date(‘2023-03-03T12:00:00.000Z’),

    new Date(‘2023-03-03T13:00:00.000Z’));

  Logger.log(‘Event ID: ‘ + event.getId());

}

The first function creates a new Google Calendar event with a title, start and end times, and a description. It logs the ID of the newly created event for reference.

The second function modifies an existing Google Calendar event by ID. It changes the title, description, and start and end times of the event. It also logs the ID of the modified event for reference.

These are just a few examples of what you can do with Google Apps Script. With this powerful scripting language, you can automate various tasks and extend the functionality of Google Workspace applications according to your needs.

Apps-Script-Code-Snippets-V4Download