Apps Script Fun Coding Exercises 4

by

Learn more about Google Apps Script – Free coding lesson – Source Code included

Apps Script Fun Coding Exercises 4

Adding a UI menu option
How to create a new sheet
Sending Custom emails from data
Add image button to Doc
Creating calendar events and trigger

Adding a UI menu option

Create a script that adds a custom menu to a Google Sheet and runs a function when a menu item is clicked.

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu(“Custom Menu”)

    .addItem(“Function 1”, “function1”)

    .addItem(“Function 2”, “function2”)

    .addToUi();

}

function function1() {

  // code to run when menu item “Function 1” is clicked

}

function function2() {

  // code to run when menu item “Function 2” is clicked

}

How to create a new sheet

Create a script that automatically creates a new Google Sheet and copies data from a template sheet.

function createNewSheet() {

  var template = DriveApp.getFileById(“templateSheetId”);

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

  var newFile = template.makeCopy(“New Sheet”, folder);

  var newSheet = SpreadsheetApp.openById(newFile.getId()).getSheets()[0];

  var data = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues();

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

}

Sending Custom emails from data

Create a script that sends a customized email to each recipient in a Google Sheet.

function sendCustomizedEmails() {

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

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

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

    var recipient = data[i][0];

    var subject = “Hello, ” + data[i][1] + “!”;

    var message = “This is a customized message for you.”;

    MailApp.sendEmail(recipient, subject, message);

  }

}

Add image button to Doc

Create a script that adds a new row to a Google Sheet when a button is clicked in a Google Doc.

function addButtonToDocument() {

  var doc = DocumentApp.getActiveDocument();

  var body = doc.getBody();

  var button = body.appendInlineImage(

    UrlFetchApp

      .fetch(“https://www.gstatic.com/images/icons/material/system/1x/add_white_24dp.png”)

      .getBlob()

      .setName(“Add Row”)

  );

  button.setLinkUrl(“javascript:addRowToSheet()”);

}

function addRowToSheet() {

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

  sheet.appendRow([“new data”]);

}

Creating calendar events and trigger

Create a script that automatically creates a new Google Calendar event when a new row is added to a Google Sheet.

function createCalendarEvent(e) {

  var title = e.namedValues[“Title”][0];

  var start = new Date(e.namedValues[“Start”][0]);

  var end = new Date(e.namedValues[“End”][0]);

  var calendar = CalendarApp.getDefaultCalendar();

  calendar.createEvent(title, start, end);

}

function createSheetTrigger() {

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

  ScriptApp.newTrigger(“createCalendarEvent”)

    .forSpreadsheet(sheet)

    .onFormSubmit()

    .create();

}

#GoogleAppsScript #GAS #AppsScript #GoogleScript #GoogleSheets #GoogleDocs #GoogleForms #GoogleSlides #GoogleWorkspace #GSuite #GSuiteEdu #GoogleEdu #onlinecourses #elearning #distancelearning #onlinetraining #learningonline #onlineeducation #MOOCs #virtuallearning #edtech #educationtechnology #learningsolutions #traininganddevelopment #professionaldevelopment 

Apps-Script-Fun-Coding-Exercises-4Download