Exercise 21: Generating a Table of Contents in Google Docs

Objective: Create a script to automatically generate a table of contents in a Google Document based on heading styles.

Code Sample:

function generateTOC() {

  var doc = DocumentApp.getActiveDocument();

  var body = doc.getBody();

  var toc = body.appendParagraph(“Table of Contents”);

  toc.setHeading(DocumentApp.ParagraphHeading.HEADING1);

  var paragraphs = body.getParagraphs();

  paragraphs.forEach(function(paragraph) {

    if (paragraph.getHeading() === DocumentApp.ParagraphHeading.HEADING2) {

      var text = paragraph.getText();

      var tocEntry = body.appendParagraph(text);

      tocEntry.setLinkUrl(‘#’ + text);

    }

  });

}

Explanation:

This script scans through all paragraphs in a Google Document. It identifies those with the Heading 2 style and creates a table of contents entry for each, linked to the respective headings.

Exercise 22: Batch Updating Spreadsheet Cells

Objective: Write a script to update multiple cells in Google Sheets at once, optimizing performance.

Code Sample:

function batchUpdateCells() {

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

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

  var values = range.getValues();

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

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

      values[i][j] = “Updated ” + values[i][j];

    }

  }

  range.setValues(values);

}

Explanation:

This script reads a range of cells, modifies each cell’s value, and then writes them back in one batch operation. This approach is more efficient than updating each cell individually.

Exercise 23: Automatically Archiving Emails in Gmail

Objective: Develop a script to automatically archive emails older than a specified number of days.

Code Sample:

function archiveOldEmails() {

  var daysOld = 30;

  var date = new Date();

  date.setDate(date.getDate() – daysOld);

  var threads = GmailApp.search(‘before:’ + Utilities.formatDate(date, Session.getScriptTimeZone(), ‘yyyy/MM/dd’));

  threads.forEach(function(thread) {

    thread.moveToArchive();

  });

}

Explanation:

This script calculates the date that is 30 days before the current date. It then searches for and archives all email threads that are older than this date.

Exercise 24: Creating a Google Sheets Chart from Data

Objective: Use Apps Script to create a chart in Google Sheets based on existing data.

Code Sample:

function createChart() {

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

  var range = sheet.getRange(“A1:B10”); // Assuming this range has the data

  var chartBuilder = sheet.newChart();

  chartBuilder.addRange(range)

      .setChartType(Charts.ChartType.BAR)

      .setPosition(5, 5, 0, 0);

  sheet.insertChart(chartBuilder.build());

}

Explanation:

This script creates a bar chart based on the data in the range A1:B10. The newChart method is used to construct the chart, which is then inserted into the sheet.

Exercise 25: Scheduling Calendar Events from Spreadsheet Data

Objective: Create a script to read event data from a spreadsheet and schedule them in Google Calendar.

Code Sample:

function scheduleEventsFromSheet() {

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

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

  var calendar = CalendarApp.getDefaultCalendar();

  rows.forEach(function(row, index) {

    if (index === 0) return; // Skip header row

    var title = row[0];

    var startTime = new Date(row[1]);

    var endTime = new Date(row[2]);

    calendar.createEvent(title, startTime, endTime);

  });

}

Explanation:

This script accesses a sheet named “Events”, where each row contains an event title, start time, and end time. It iterates through each row and creates corresponding events in the user’s default calendar.