Apps Script Coding Examples

Creating a Google Calendar Event

This example shows how to create a new event on a Google Calendar using Google Apps Script.

function createCalendarEvent() {

  var calendarId = “your_calendar_id”;

  var calendar = CalendarApp.getCalendarById(calendarId);

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

}

Explanation:

In this script, we first define the calendar ID of the Google Calendar we want to create the event on. We then call the CalendarApp.getCalendarById() function to get the calendar with the specified ID. We then create a new event on the calendar using the createEvent() function and passing the event title, start date, and end date as arguments.

Create a Calendar event in the user calendar by ID

function makeEvent(){

  const id = ‘gappscourses@gmail.com’

  const cal = CalendarApp.getCalendarById(id);

  const start = new Date(‘March 10, 2023 09:00:00’);

  const end = new Date(‘March 10, 2023 11:00:00’);

  const event = cal.createEvent(‘Laurence’,start , end);

  Logger.log(cal);

}

This code defines a function named makeEvent() that creates a new event on a Google Calendar. Here’s a line-by-line explanation of the code:

const id = ‘gappscourses@gmail.com’

This line defines a constant variable named id that contains the email address of the Google Calendar to use.

const cal = CalendarApp.getCalendarById(id);

This line retrieves the Google Calendar with the specified id and assigns it to a constant variable named cal.

const start = new Date(‘March 10, 2023 09:00:00’);

const end = new Date(‘March 10, 2023 11:00:00’);

These lines define two constant variables named start and end, which represent the start and end times of the event. The Date() function is used to create a new date object with the specified date and time.

const event = cal.createEvent(‘Laurence’,start , end);

This line creates a new event on the Google Calendar using the createEvent() method of the cal object. The createEvent() method takes three arguments: the event title, the start time, and the end time. In this case, the event title is “Laurence” and the start and end times are defined by the start and end variables.

Copy code

Logger.log(cal);

This line logs the cal object to the execution log. The Logger.log() method is used to log messages and values to the log for debugging purposes.

In summary, this code defines a function that creates a new event on a Google Calendar with the specified start and end times. It uses the CalendarApp service of Google Apps Script to interact with the Google Calendar API and the Logger service to log messages to the execution log for debugging.

Extracting Data from a Google Form

This example shows how to extract data from a Google Form using Google Apps Script.

function extractFormData() {

  var formId = “your_form_id”;

  var form = FormApp.openById(formId);

  var responses = form.getResponses();

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

    var response = responses[i];

    var itemResponses = response.getItemResponses();

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

      var itemResponse = itemResponses[j];

      var question = itemResponse.getItem().getTitle();

      var answer = itemResponse.getResponse();

      Logger.log(“Question: ” + question + “, Answer: ” + answer);

    }

  }

}

Explanation:

In this script, we first define the form ID of the Google Form we want to extract data from. We then call the FormApp.openById() function to open the form with the specified ID. We then get all the form responses using the getResponses() function and loop through each response. For each response, we get the item responses using the getItemResponses() function and loop through each item response. We then get the question and answer for each item response using the getTitle() and getResponse() functions and log them to the console.

Get data from Google Form Responses and add to Sheet data

function getMyData(){

  const id = ‘1Es22J95HkuZPmPmbkJRfMZ4MpnwFRGvddGxR8gcPFJU’;

  const sid = ‘1P9R_b-dTdoBAAWAGF6kPZyKSXJ6r2P_LFgrKeReNmrY’;

  const ss = SpreadsheetApp.openById(sid).getSheetByName(‘data’);

  const form = FormApp.openById(id);

  const responses = form.getResponses();

  responses.forEach(response =>{ 

    Logger.log(response.getItemResponses());

    const items = response.getItemResponses();

    items.forEach(item=>{

      const question = item.getItem().getTitle();

      const answer = item.getResponse();

      ss.appendRow([`Question:${question}`,`Answer:${answer}`]);

      Logger.log(`Question:${question} Answer:${answer}`);

    })

  })

}

This is a Google Apps Script function that retrieves data from a Google Form and writes it to a Google Sheet. Here’s a line-by-line explanation of the code:

const id = ‘1Es28gcPFJU’;

const sid = ‘1P9R_b-dTLFgrKeReNmrY’;

These lines define two variables: id and sid. id contains the ID of the Google Form, while sid contains the ID of the Google Sheet that will store the form responses.

const ss = SpreadsheetApp.openById(sid).getSheetByName(‘data’);

This line opens the Google Sheet with the ID stored in sid and retrieves the sheet named “data”. The SpreadsheetApp class is used to open the spreadsheet, and the getSheetByName() method is used to retrieve the sheet.

const form = FormApp.openById(id);

const responses = form.getResponses();

These lines open the Google Form with the ID stored in id and retrieve all of the form responses. The FormApp class is used to open the form, and the getResponses() method is used to retrieve the responses.

responses.forEach(response =>{ 

  Logger.log(response.getItemResponses());

  const items = response.getItemResponses();

  items.forEach(item=>{

    const question = item.getItem().getTitle();

    const answer = item.getResponse();

    ss.appendRow([`Question:${question}`,`Answer:${answer}`]);

    Logger.log(`Question:${question} Answer:${answer}`);

  })

})

These lines loop through each form response and write the response data to the Google Sheet. The forEach() method is used to iterate over each response, and the getItemResponses() method is used to retrieve the responses to each individual question. These responses are then looped over using another forEach() method.

For each question and answer pair, the getTitle() method is used to retrieve the title of the question and the getResponse() method is used to retrieve the answer to the question. The question and answer are then logged to the script’s log using Logger.log(), and a new row is added to the Google Sheet using the appendRow() method. The appendRow() method takes an array of values as its parameter, and in this case, it creates a new row with two columns: one for the question and one for the answer. The appended row is written to thess` sheet we retrieved earlier.

In summary, this code retrieves data from a Google Form and writes it to a Google Sheet. It uses the SpreadsheetApp and FormApp classes of Google Apps Script to interact with the Google Sheets and Google Forms APIs. The form responses are retrieved using the getResponses() method and are looped over to extract the responses to each individual question. The question and answer data are then written to the Google Sheet using the appendRow() method. The script also logs the question and answer data to the script’s log for debugging purposes.

Creating a Google Slides Presentation

This example shows how to create a new presentation on Google Slides using Google Apps Script.

function createPresentation() {

  var presentationTitle = “Test Presentation”;

  var presentation = SlidesApp.create(presentationTitle);

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

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

}

Explanation:

In this script, we first define the title of the new presentation we want to create. We then call the SlidesApp.create() function to create a new presentation with the specified title. We then get the first slide of the presentation using the getSlides() function and insert a rectangle shape on the slide using the insertShape() function.

Create Slides add chart from Sheet into Slide

Function makeSlides()

function makeSlides(){

  const title = ‘Laurence Svekis’;

  const slides = SlidesApp.create(title);

  const slide = slides.getSlides()[0];

  slide.insertTextBox(title);

  const slide2 = slide.duplicate();

}

This function creates a new Google Slides presentation with the given title (Laurence Svekis) using the SlidesApp.create() method. It then retrieves the first slide in the presentation using slides.getSlides()[0], which is the slide that was automatically created when the presentation was created. It inserts a text box with the same title as the presentation using slide.insertTextBox(title), and duplicates the slide using slide.duplicate() to create a second slide with the same content as the first slide.

Function upSlides()

function upSlides(){

  const id = ‘1AArjO5fgaNtf-iFWmPFDsaC4k’;

  const slides = SlidesApp.openById(id);

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

  const sid = ‘132trziyDq_foIdRW0vciq2rm2NT-UKvLa8IvLsk8qIk’;

  const sheet = SpreadsheetApp.openById(sid).getSheetByName(‘data’);

  const chart = sheet.getCharts()[0];

  Logger.log(chart);

  slide1.insertSheetsChartAsImage(chart);

}

This function retrieves a Google Slides presentation using SlidesApp.openById(id), where id is the ID of the presentation. It then retrieves the first slide in the presentation using slides.getSlides()[0]. It also retrieves a Google Sheets spreadsheet using SpreadsheetApp.openById(sid), where sid is the ID of the spreadsheet, and gets the sheet with the name data using getSheetByName(‘data’). It then retrieves the first chart in the sheet using sheet.getCharts()[0].

The function logs the chart object using Logger.log(chart). Finally, it inserts the chart as an image on the first slide using slide1.insertSheetsChartAsImage(chart). This method creates an image of the chart and inserts it into the slide as a shape.

Overall, these two functions demonstrate how to create and manipulate Google Slides presentations using Google Apps Script. The first function creates a new presentation and adds content to it, while the second function retrieves an existing presentation and inserts a chart from a Google Sheets spreadsheet onto a slide.

function makeSlides(){

  const title = ‘Laurence Svekis’;

  const slides = SlidesApp.create(title);

  const slide = slides.getSlides()[0];

  slide.insertTextBox(title);

  const slide2 = slide.duplicate();

}

function upSlides(){

  const id = ‘1AArjO5fgOh1ZnGWmicIPFDsaC4k’;

  const slides = SlidesApp.openById(id);

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

  const sid = ‘132trziyDq_fLa8IvLsk8qIk’;

  const sheet = SpreadsheetApp.openById(sid).getSheetByName(‘data’);

  const chart = sheet.getCharts()[0];

  Logger.log(chart);

  slide1.insertSheetsChartAsImage(chart);

}

Using Google Translate API

This example shows how to use the Google Translate API to translate text using Google Apps Script.

function translateText() {

  var textToTranslate = “Hello World!”;

  var targetLanguage = “es”;

  var translatedText = LanguageApp.translate(textToTranslate, ”, targetLanguage);

  Logger.log(“Translated Text: ” + translatedText);

}

Explanation:

In this script, we first define the text we want to translate and the target language we want to translate the text into. We then call the LanguageApp.translate() function to translate the text using the Google Translate API and passing the text, source language, and target language as arguments. We then log the translated text to the console using the Logger.log() function.

Translate Google Sheet Quotes to Different Languages

const ID = ‘1P9ReNmrY’;

function textTranslator(){

  const textEng = ‘Hello World’;

  const targetLang = ‘es’;

  const newText = LanguageApp.translate(textEng,’en’,targetLang);

  Logger.log(newText);

}

function sheetQuotes(){

  const ss = SpreadsheetApp.openById(ID).getSheetByName(‘quotes’);

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

  Logger.log(data);

  const lang = ‘is’;

  data.forEach((quote,index)=>{

    const newText = LanguageApp.translate(quote,’en’,lang);

    Logger.log(newText);

    const range = ss.getRange(index+1,2);

    range.setValue(newText);

  })

}

This code defines two functions, textTranslator() and sheetQuotes(), which use Google Apps Script to translate text from one language to another and save the translated text to a Google Sheet.

The first function, textTranslator(), translates a single string of text (‘Hello World’) from English (‘en’) to a target language (‘es’). The function then logs the translated text using the Logger class.

The second function, sheetQuotes(), accesses a Google Sheet using its ID (‘1P9RPZyKSrKeReNmrY’) and sheet name (‘quotes’). It retrieves all the data in the sheet using getDataRange() and then iterates over each row using forEach(). For each row, the function translates the first column (which presumably contains a quote in English) to a target language (‘is’) using the LanguageApp.translate() method. It then logs the translated text using the Logger class and saves the translated text to the second column of the same row using setValue().

Copying a Google Drive Folder

This example shows how to copy a Google Drive folder using Google Apps Script.

function copyFolder() {

  var folderId = “your_folder_id”;

  var folder = DriveApp.getFolderById(folderId);

  var newFolderName = folder.getName() + ” – Copy”;

  var newFolder = DriveApp.createFolder(newFolderName);

  var files = folder.getFiles();

  while (files.hasNext()) {

    var file = files.next();

    file.makeCopy(file.getName(), newFolder);

  }

}

Explanation:

In this script, we first define the folder ID of the Google Drive folder we want to copy. We then call the DriveApp.getFolderById() function to get the folder with the specified ID. We then create a new folder with the same name as the original folder but with ” – Copy” appended to it using the DriveApp.createFolder() function. We then get all the files in the original folder using the getFiles() function and loop through each file. For each file, we make a copy of the file in the new folder using the makeCopy() function and passing the file name and the new folder as arguments.

Create and copy files into new Folder

function cpyFolder(){

  const folderID = ’17hhl91aO1Uc’;

  const folder = DriveApp.getFolderById(folderID);

  const newName = ‘Laurence’;

  const newFolder = DriveApp.createFolder(newName);

  const files = folder.getFiles();

  while(files.hasNext()){

    const file = files.next();

    file.makeCopy(file.getName(),newFolder);

  }

  Logger.log(folder);

}

This code defines a function called cpyFolder() that creates a copy of a Google Drive folder and all its files.

The function starts by defining the ID of the original folder that will be copied (’17hhl91_O1Uc’) and uses DriveApp.getFolderById() to retrieve the corresponding folder object.

It then defines a new name for the copied folder (‘Laurence’) and uses DriveApp.createFolder() to create a new folder object with that name.

Next, the function retrieves a list of files in the original folder using folder.getFiles(), and uses a while loop to iterate through each file in the list. For each file, the function creates a copy of the file using file.makeCopy(), passing in the file’s original name and the new folder object as parameters.

Finally, the function logs the original folder object using Logger.log().

In summary, this function copies a Google Drive folder and all its files to a new folder with a specified name.

Adding a Custom Menu to a Google Sheet

This example shows how to add a custom menu to a Google Sheet using Google Apps Script.

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(‘Custom Menu’)

      .addItem(‘First Item’, ‘menuItem1’)

      .addSeparator()

      .addItem(‘Second Item’, ‘menuItem2’)

      .addToUi();

}

function menuItem1() {

  // Do something for the first menu item

}

function menuItem2() {

  // Do something for the second menu item

}

Explanation:

In this script, we first define the onOpen() function, which is a special function that runs automatically when the Google Sheet is opened. We then use the SpreadsheetApp.getUi() function to get the UI service for the Google Sheet. We then create a new custom menu using the createMenu() function and add two menu items using the addItem() function. We then define the two menu item functions menuItem1() and menuItem2() which will be executed when the corresponding menu item is clicked.

Custom Function directly from Sheet

function onOpen(){

  const ui = SpreadsheetApp.getUi();

  ui.createMenu(‘adv’)

    .addItem(‘first’,’firstFun’)

    .addSeparator()

    .addItem(‘second’,’secondFun’)

    .addToUi();

}

function firstFun(){

  SpreadsheetApp.getUi().alert(‘Hello 1’);

}

function secondFun(){

  SpreadsheetApp.getUi().alert(‘Hello 2’);

}

This code defines three functions: onOpen(), firstFun(), and secondFun(), which create a custom menu in a Google Sheets document and add menu items that execute the other two functions when clicked.

The onOpen() function is a special function that is automatically called when the Google Sheets document is opened. It starts by getting the user interface object using SpreadsheetApp.getUi(). It then uses the createMenu() method to create a new menu in the user interface object with the label ‘adv’.

The createMenu() method returns a Menu object, which is used to add menu items to the menu. The addItem() method is used to add two menu items to the menu with the labels ‘first’ and ‘second’. The second argument of the addItem() method is the name of the function that will be executed when the menu item is clicked.

The addSeparator() method is used to add a separator line between the two menu items.

Finally, the addToUi() method is called to add the custom menu to the user interface.

The firstFun() and secondFun() functions simply display an alert message when they are called using SpreadsheetApp.getUi().alert(). These functions are defined as the actions to be taken when the corresponding menu items are clicked.

In summary, this code creates a custom menu in a Google Sheets document with two menu items, ‘first’ and ‘second’, and executes the firstFun() and secondFun() functions when the respective menu items are clicked. When firstFun() is executed, an alert message displaying ‘Hello 1’ is shown. When secondFun() is executed, an alert message displaying ‘Hello 2’ is shown.

Converting a Google Sheet to PDF

This example shows how to convert a Google Sheet to PDF using Google Apps Script.

function conSheet(){

  const id = ‘1P9KeReNmrY’;

  const ss = SpreadsheetApp.openById(id);

  const sheets = ss.getSheets();

  const sheetName = ‘quotes’;

  sheets.forEach(sheet =>{

    if(sheet.getName() != sheetName){

      sheet.hideSheet();

    }

  })

  const blob = ss.getBlob().getAs(“application/pdf”).setName(`${sheetName}.pdf`);

  DriveApp.createFile(blob);

  sheets.forEach(sheet =>{

    if(sheet.getName() != sheetName){

      sheet.showSheet();

    }

  })

}

This code defines a function called conSheet() that exports a single sheet from a Google Sheets document as a PDF file.

The function starts by defining the ID of the Google Sheets document (‘1P9RKeReNmrY’) and uses SpreadsheetApp.openById() to retrieve the corresponding spreadsheet object.

It then retrieves an array of all the sheets in the spreadsheet using ss.getSheets(), and defines a variable sheetName with the name of the sheet to be exported (‘quotes’).

The function then uses a forEach() loop to iterate through each sheet in the spreadsheet. For each sheet, it checks whether its name is different from the sheet name to be exported. If so, it calls the hideSheet() method to hide the sheet from view.

Next, the function retrieves the spreadsheet as a PDF file using ss.getBlob().getAs(“application/pdf”), and sets the name of the file using .setName(). It then uses DriveApp.createFile() to create a new file in the user’s Google Drive with the PDF blob.

Finally, the function uses another forEach() loop to iterate through each sheet in the spreadsheet again, and checks whether its name is different from the sheet name to be exported. If so, it calls the showSheet() method to unhide the sheet.

In summary, this code exports a single sheet from a Google Sheets document as a PDF file, by hiding all other sheets, exporting the sheet as a PDF, creating a file with the PDF blob, and then unhiding all the other sheets.

Setting a Google Sheet’s Cell Value

This example shows how to set a cell value in a Google Sheet using Google Apps Script.

function setCellValue() {

  var sheetId = “your_sheet_id”;

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

  sheet.getRange(“A1”).setValue(“Hello World!”);

}

Explanation:

In this script, we first define the sheet ID of the Google Sheet we want to set the cell value for. We then call the SpreadsheetApp.openById() function to open the sheet with the specified ID. We then get the sheet with the name “Sheet1” using the getSheetByName() function. We then set the value of cell A1 to “Hello World!” using the setValue() function.

Uploading a File to Google Drive

This example shows how to upload a file to Google Drive using Google Apps Script.

function uploadFile() {

  var file = DriveApp.createFile(“Test File”, “This is a test file”);

  var folderId = “your_folder_id”;

  var folder = DriveApp.getFolderById(folderId);

  folder.addFile(file);

}

Explanation:

In this script, we first create a new file in Google Drive using the DriveApp.createFile() function. We then define the folder ID of the Google Drive folder we want to upload the file to. We then get the folder with the specified ID using the DriveApp.getFolderById() function. We then add the newly created file to the folder using the addFile() function.

Sending an Email from Google Sheets

This example shows how to send an email from a Google Sheet using Google Apps Script.

function sendEmail() {

  var emailAddress = “recipient@example.com”;

  var subject = “Test Email”;

  var body = “This is a test email”;

  MailApp.sendEmail(emailAddress, subject, body);

}

Explanation:

In this script, we first define the recipient email address, subject, and body of the email we want to send. We then call the MailApp.sendEmail() function to send the email to the specified recipient with the specified subject and body. Note that this function requires the necessary authorization to be granted in order to send emails.

Sending Emails

Google Apps Script can be used to send emails directly from Google Sheets, Docs, or Forms. This is useful when you need to send automated emails to a large number of recipients or when you need to customize the content of the email for each recipient. Here’s an example code:

function sendEmail() {

  var recipient = “example@example.com”;

  var subject = “Test Email”;

  var body = “This is a test email.”;

  MailApp.sendEmail(recipient, subject, body);

}

Creating a Calendar Event

Google Apps Script can also be used to create calendar events directly from Google Sheets or Forms. Here’s an example code:

function createEvent() {

  var calendar = CalendarApp.getDefaultCalendar();

  var title = “Test Event”;

  var start = new Date(“2023-03-05T12:00:00Z”);

  var end = new Date(“2023-03-05T13:00:00Z”);

  calendar.createEvent(title, start, end);

}

Creating a Google Form

Google Apps Script can be used to create Google Forms programmatically. This is useful when you need to create a large number of forms or when you need to customize the content of the form for each respondent. Here’s an example code:

function createForm() {

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

  form.addTextItem().setTitle(“What is your name?”);

  form.addMultipleChoiceItem()

    .setTitle(“What is your favorite color?”)

    .setChoices([

      form.createChoice(“Red”),

      form.createChoice(“Blue”),

      form.createChoice(“Green”),

    ]);

}

Reading Data from Google Sheets

Google Apps Script can be used to read data from Google Sheets and process it programmatically. Here’s an example code that reads the values from the first row of a sheet:

function readData() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var values = sheet.getRange(“1:1”).getValues()[0];

  Logger.log(values);

}

Writing Data to Google Sheets

Google Apps Script can also be used to write data to Google Sheets. Here’s an example code that writes the values “John” and “Doe” to the first row of a sheet:

function writeData() {

  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.getRange(“A1:B1”).setValues([[“John”, “Doe”]]);

}

Send Emails Automatically:

With Apps Script, you can write a script that sends an email automatically to a recipient or a group of recipients at a specific time or interval. The code below sends an email message containing a subject and a body to a specified email address.

function sendEmail() {

  var recipient = “recipient@email.com”;

  var subject = “Hello from Apps Script”;

  var body = “This email was sent using Google Apps Script!”;

  MailApp.sendEmail(recipient, subject, body);

}

Custom Emails and HTML option

function senderEmail(){

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

  const emailSend = ‘gappscourses@gmail.com’;

  const subject = ‘Hello World’;

  const body = ‘<h1>Laurence Svekis</h1>’;

  MailApp.sendEmail({

    to:email,

    subject:subject,

    htmlBody:body});

  Logger.log(email);

}

const email = Session.getActiveUser().getEmail(); – This line gets the email address of the currently active user using the Session.getActiveUser() method and stores it in the email constant. The getEmail() method retrieves the email address associated with the user’s account.

const emailSend = ‘gappscourses@gmail.com’; – This line defines a constant variable named emailSend that contains the email address of the recipient.

const subject = ‘Hello World’; – This line defines a constant variable named subject that contains the subject of the email message.

const body = ‘<h1>Laurence Svekis</h1>’; – This line defines a constant variable named body that contains the body of the email message. It is in HTML format.

MailApp.sendEmail({to:email, subject:subject, htmlBody:body}); – This line uses the MailApp service to send an email message to the email address stored in the email constant. The email message contains the subject stored in the subject constant and the body stored in the body constant. The htmlBody option specifies that the body of the message is in HTML format.

Logger.log(email); – This line logs the email address of the currently active user to the execution log using the Logger.log() method.

Create Google Forms:

Google Forms is a powerful tool for collecting data from users. With Apps Script, you can programmatically create Google Forms, customize them, and collect the responses. The code below creates a new Google Form and adds a multiple-choice question with two options.

function createForm() {

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

  var item = form.addMultipleChoiceItem();

  item.setTitle(“Choose an option”)

      .setChoices([

          item.createChoice(“Option 1”),

          item.createChoice(“Option 2”)

      ]);

}

Creating a custom form

function creatorForm(){

  const form = FormApp.create(‘Laurence Svekis’);

  const item = form.addMultipleChoiceItem();

  item.setTitle(‘Select One’)

    .setChoices([

      item.createChoice(‘One’),

      item.createChoice(‘Two’),

      item.createChoice(‘Three’)

      ]);

}

The creatorForm() function creates a Google Form and adds a multiple choice question to it. Here is a line-by-line explanation of the code:

const form = FormApp.create(‘Laurence Svekis’); – This line creates a new Google Form with the title “Laurence Svekis” using the FormApp.create() method. The create() method returns a Form object that represents the newly created form.

const item = form.addMultipleChoiceItem(); – This line adds a multiple choice question to the form using the addMultipleChoiceItem() method of the Form object. The addMultipleChoiceItem() method returns a MultipleChoiceItem object that represents the newly created question.

item.setTitle(‘Select One’) – This line sets the title of the multiple choice question to “Select One” using the setTitle() method of the MultipleChoiceItem object.

.setChoices([ – This line begins an array of answer choices for the multiple choice question using the setChoices() method of the MultipleChoiceItem object. The setChoices() method takes an array of Choice objects as its argument.

item.createChoice(‘One’), – This line creates a new answer choice with the text “One” using the createChoice() method of the MultipleChoiceItem object. The createChoice() method returns a Choice object that represents the newly created answer choice.

item.createChoice(‘Two’), – This line creates a new answer choice with the text “Two” using the createChoice() method of the MultipleChoiceItem object.

item.createChoice(‘Three’) – This line creates a new answer choice with the text “Three” using the createChoice() method of the MultipleChoiceItem object.

]); – This line ends the array of answer choices for the multiple choice question using the setChoices() method of the MultipleChoiceItem object.

After executing the creatorForm() function, a new Google Form titled “Laurence Svekis” will be created with a multiple choice question that has three answer choices: “One”, “Two”, and “Three”.

Generate Google Docs:

Google Docs is a cloud-based word processing tool that allows users to create and edit documents. With Apps Script, you can programmatically generate Google Docs, populate them with data, and format them. The code below creates a new Google Doc and adds a paragraph of text.

function createDoc() {

  var doc = DocumentApp.create(“My Doc”);

  var body = doc.getBody();

  body.insertParagraph(0, “Hello from Apps Script!”);

}

Create and update a Google Doc

function createmyDoc(){

  const doc = DocumentApp.create(‘Laurence Svekis’);

  const body = doc.getBody();

  body.insertParagraph(0,’My new Doc, Laurence Svekis’);

}

function updatemyDoc(){

  const id = ‘1JJyHruUbZQv4Q5zLHTVebDDbs’;

  const doc = DocumentApp.openById(id);

  const body = doc.getBody();

  body.insertParagraph(10,’*****Laurence Svekis’);

}

The createmyDoc() function and the updatemyDoc() function both manipulate a Google Docs document. Here’s what each function does:

createmyDoc(): This function creates a new Google Docs document titled “Laurence Svekis”, inserts a paragraph into the document, and sets the text of the paragraph to “My new Doc, Laurence Svekis”. Here’s what each line of the function does:

• const doc = DocumentApp.create(‘Laurence Svekis’);: This line creates a new Google Docs document titled “Laurence Svekis” using the create() method of the DocumentApp class. The create() method returns a Document object that represents the newly created document.
• const body = doc.getBody();: This line gets the Body object of the newly created document using the getBody() method of the Document object.
• body.insertParagraph(0,’My new Doc, Laurence Svekis’);: This line inserts a new paragraph at the beginning of the document using the insertParagraph() method of the Body object. The insertParagraph() method takes two arguments: the position where the new paragraph should be inserted (in this case, the beginning of the document), and the text of the paragraph (“My new Doc, Laurence Svekis” in this case).

updatemyDoc(): This function updates an existing Google Docs document by inserting a new paragraph into it. Here’s what each line of the function does:

• const id = ‘1JJyHruUbZQv5LHTVebDDbs’;: This line sets the id variable to the ID of the Google Docs document that we want to update. This ID is a unique identifier that is assigned to each Google Docs document.
• const doc = DocumentApp.openById(id);: This line opens the Google Docs document with the specified ID using the openById() method of the DocumentApp class. The openById() method returns a Document object that represents the opened document.
• const body = doc.getBody();: This line gets the Body object of the opened document using the getBody() method of the Document object.
• body.insertParagraph(10,’*****Laurence Svekis’);: This line inserts a new paragraph at position 10 (i.e., after the tenth paragraph) in the document using the insertParagraph() method of the Body object. The text of the new paragraph is “*****Laurence Svekis”. The insertParagraph() method takes two arguments: the position where the new paragraph should be inserted (in this case, after the tenth paragraph), and the text of the paragraph (“*****Laurence Svekis” in this case).

Add Data to Google Sheets:

Google Sheets is a cloud-based spreadsheet tool that allows users to create and edit spreadsheets. With Apps Script, you can programmatically add data to a Google Sheet, retrieve data from it, and perform various operations. The code below adds a new row of data to a Google Sheet.

function addData() {

  var sheet = SpreadsheetApp.getActiveSheet();

  sheet.appendRow([“John Doe”, “johndoe@email.com”, “555-1234”]);

}

Create and update a Google Sheet

function makeSheets(){

  const ss = SpreadsheetApp.create(‘Laurence Sheet’);

  const sheet = ss.getSheets()[0];

  const row = [‘Laurence’,’Svekis’,’100′];

  sheet.appendRow(row);

  Logger.log(ss.getId());

}

function updateSheet(){

  const id = ‘1P9R_b-dTdoBAAWAGF6kPZyKSXJ6r2P_LFgrKeReNmrY’;

  const ss = SpreadsheetApp.openById(id);

  const sheet = ss.getSheets()[0];

  const row = [‘1 Laurence’,’1 Svekis’,’55’];

  sheet.appendRow(row);

}

The first function makeSheets() creates a new Google Sheets file with the name “Laurence Sheet” and then retrieves the first sheet in the file. It then creates a new row containing three values, ‘Laurence’, ‘Svekis’, and ‘100’, and appends the row to the end of the sheet. Finally, it logs the ID of the created spreadsheet using the Logger class.

The second function updateSheet() opens an existing Google Sheets file using its ID and retrieves the first sheet in the file. It then creates a new row containing three values, ‘1 Laurence’, ‘1 Svekis’, and ’55’, and appends the row to the end of the sheet. The function updates the sheet in the file without creating a new sheet.

Automate Google Calendar:

Google Calendar is a powerful tool for managing events and schedules. With Apps Script, you can automate various tasks in Google Calendar, such as creating events, updating events, and sending notifications. The code below creates a new event in Google Calendar.

function createEvent() {

  var calendar = CalendarApp.getDefaultCalendar();

  var title = “My Event”;

  var start = new Date(“March 1, 2023 10:00:00”);

  var end = new Date(“March 1, 2023 11:00:00”);

  var event = calendar.createEvent(title, start, end);

}

Add new event to Default Calendar

function myEventCal(){

  const cal = CalendarApp.getDefaultCalendar();

  const title = ‘My Birthday’;

  const start = new Date(‘March 1, 2023 10:00:00’);

  const end = new Date(‘March 1, 2023 11:00:00’);

  const event = cal.createEvent(title,start,end);

}

The function myEventCal() creates a new calendar event on the default calendar of the user. First, it retrieves the default calendar object using the getDefaultCalendar() method. Then, it creates variables for the title of the event, the start time, and the end time. In this case, the title of the event is “My Birthday”, the start time is set for March 1, 2023, at 10:00 AM, and the end time is set for March 1, 2023, at 11:00 AM.

Finally, the createEvent() method is called on the cal object, passing in the title, start, and end variables to create a new event on the default calendar. The newly created event will be visible on the user’s calendar at the specified start and end times with the specified title.

Sending Automated Emails with Google Sheets and Apps Script

In this project, we will create a Google Apps Script that automates the process of sending emails to a list of recipients from a Google Sheets spreadsheet. We will use the Gmail service of Google Apps Script to send emails and the Sheets service to retrieve data from the spreadsheet.

Step 1: Set up the Spreadsheet

Create a new Google Sheets spreadsheet and enter the recipient email addresses in the first column, and the email subject and body in the second and third columns, respectively. Save the spreadsheet and take note of the spreadsheet ID in the URL.

Step 2: Create the Script

Open the script editor in Google Sheets by selecting “Tools” > “Script Editor”. In the script editor, copy and paste the following code:

function sendEmails() {

  var sheet = SpreadsheetApp.getActiveSheet();

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

  var numRows = sheet.getLastRow() – 1;   // 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];

    var subject = row[1];

    var message = row[2];

    MailApp.sendEmail(emailAddress, subject, message);

  }

}

Explanation:

This script defines a function named sendEmails(), which retrieves data from the Google Sheets spreadsheet and sends emails to the recipients. First, we get the active sheet using the SpreadsheetApp.getActiveSheet() function. We then define the starting row and the number of rows to process. Next, we get the range of data to process using the sheet.getRange() function and retrieve the values using the getValues() function.

In the for loop, we loop through each row of data and get the email address, subject, and message from each row. We then send an email to each recipient using the MailApp.sendEmail() function.

Step 3: Run the Script

Save the script and return to the spreadsheet. Click on the “Run” menu and select “sendEmails”. This will execute the script and send the emails to the recipients.

Step 4: Set up a Trigger

To automate the process of sending emails, we can set up a trigger to run the sendEmails() function at a specific time interval. Click on the “Edit” menu and select “Current project’s triggers”. Click on the “Add Trigger” button and set up the trigger to run the function every day or at a specific time interval.

Conclusion:

In this project, we created a Google Apps Script that automates the process of sending emails to a list of recipients from a Google Sheets spreadsheet. We used the Gmail service of Google Apps Script to send emails and the Sheets service to retrieve data from the spreadsheet. By setting up a trigger, we can automate the process of sending emails at a specific time interval.

Google Apps Script project that retrieves data from a Google Sheet and sends an email based on the data:

function sendEmail() {

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

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

  const emailTemplate = HtmlService.createTemplateFromFile(‘EmailTemplate’);

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

    const name = data[i][0];

    const email = data[i][1];

    const amount = data[i][2];

    emailTemplate.name = name;

    emailTemplate.amount = amount;

    const htmlMessage = emailTemplate.evaluate().getContent();

    const subject = ‘Payment Received’;

    GmailApp.sendEmail(email, subject, ”, {htmlBody: htmlMessage});

  }

}

This code defines a function named sendEmail() that retrieves data from a Google Sheet and sends an email to each row in the sheet (excluding the header row). Here’s a line-by-line explanation of the code:

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

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

These lines retrieve the data from a sheet named “Data” in the active spreadsheet. The getSheetByName() method is used to retrieve the sheet, and the getDataRange() method is used to retrieve the range of data in the sheet. The getValues() method is used to retrieve the values in the data range.

const emailTemplate = HtmlService.createTemplateFromFile(‘EmailTemplate’);

This line creates an HTML email template from a file named “EmailTemplate”. The HtmlService class is used to create the template, and the createTemplateFromFile() method is used to load the HTML file.

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

  const name = data[i][0];

  const email = data[i][1];

  const amount = data[i][2];

  emailTemplate.name = name;

  emailTemplate.amount = amount;

  const htmlMessage = emailTemplate.evaluate().getContent();

  const subject = ‘Payment Received’;

  GmailApp.sendEmail(email, subject, ”, {htmlBody: htmlMessage});

}

These lines loop through each row of data (excluding the header row) and send an email to the email address listed in the second column of the sheet. The name and amount variables are used to populate the HTML email template, and the evaluate() method is used to render the template with the populated variables. The resulting HTML content is passed to the sendEmail() method of the GmailApp class, along with the email address and subject line.

In summary, this code defines a function that retrieves data from a Google Sheet and sends an HTML email to each row in the sheet (excluding the header row). It uses the SpreadsheetApp, HtmlService, and GmailApp services of Google Apps Script to interact with the Google Sheet and Gmail APIs.