Transform Your Google Workspace with Advanced Google Apps Script Exercises

by

Exercise 11: Generating a Report from Google Forms Responses

Objective: Create a script to generate a summary report from Google Forms responses in a Google Sheet.

Code Sample:

function generateFormReport() {

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

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

  var report = {};

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

    var question = data[i][1]; // Assuming question is in the second column

    if (!report[question]) {

      report[question] = 0;

    }

    report[question]++;

  }

  Logger.log(report);

}

Explanation:

This script processes responses from a Google Form (stored in a Google Sheet). It tallies responses for each question and logs a summary report. This is useful for quick analysis of form data.

Exercise 12: Automating Document Creation Based on Sheet Data

Objective: Create Google Docs automatically based on rows in a Google Sheet.

Code Sample:

function createDocsFromSheet() {

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

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

  rows.forEach(function(row, index) {

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

    var doc = DocumentApp.create(‘Document for ‘ + row[0]); // Assuming the first column has a unique identifier

    var body = doc.getBody();

    body.appendParagraph(‘Data for ‘ + row[0]);

    // Add more content as needed

    doc.saveAndClose();

  });

}

Explanation:

This script iterates through rows in the active sheet and creates a Google Doc for each row. The first column is assumed to contain a unique identifier for each document. This is useful for generating personalized documents in bulk.

Exercise 13: Syncing Contacts to a Google Sheet

Objective: Import contacts from Google Contacts into a Google Sheet.

Code Sample:

function syncContactsToSheet() {

  var contacts = ContactsApp.getContacts();

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

  sheet.clear(); // Clear existing data

  sheet.appendRow([“Name”, “Email”]); // Header row

  contacts.forEach(function(contact) {

    var name = contact.getFullName();

    var emails = contact.getEmails();

    if (emails.length > 0) {

      var email = emails[0].getAddress();

      sheet.appendRow([name, email]);

    }

  });

}

Explanation:

This script retrieves contacts from the user’s Google Contacts and writes their names and email addresses to the active Google Sheet. This can be useful for managing contact lists or for marketing purposes.

Exercise 14: Parsing JSON Data from an API into Google Sheets

Objective: Fetch JSON data from an external API and parse it into a Google Sheet.

Code Sample:

function parseJSONToSheet() {

  var response = UrlFetchApp.fetch(‘https://api.example.com/data’);

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

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

  json.forEach(function(item) {

    sheet.appendRow([item.name, item.value]); // Adjust depending on JSON structure

  });

}

Explanation:

This script makes a GET request to an external API, parses the JSON response, and appends each item’s properties as a new row in the active sheet. This is particularly useful for importing and working with dynamic data from external sources.

Exercise 15: Creating a Custom Data Dashboard in Google Sheets

Objective: Build a custom data dashboard in Google Sheets using script to update it regularly.

Code Sample:

function updateDashboard() {

  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Data”);

  var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Dashboard”);

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

  // Example: Summarize data

  var total = data.reduce(function(sum, row) {

    return sum + row[1]; // Assuming data to sum is in the second column

  }, 0);

  dashboardSheet.getRange(“B2”).setValue(total); // Update a specific cell in the dashboard

}

Explanation:

This script reads data from a specified “Data” sheet, performs a calculation (in this case, a sum), and updates a specific cell in a “Dashboard” sheet. This is useful for creating automated, real-time dashboards in Google Sheets.

Google-Apps-Script-Exercises-15Download