10-Day Google Apps Script Learning Guide

by
10-Day Google Apps Script Learning GuideDownload

Day 1 – Introduction to Google Apps Script

Goals:

  • Understand what Apps Script is.
  • Learn where to write and run scripts.
  • Write your first script.

Topics:

  • What is Apps Script? (JavaScript in Google Workspace)
  • Accessing the Script Editor (Extensions → Apps Script in Google Sheets/Docs).
  • The Logger.log() function.

Code Example:

function helloWorld() {
  Logger.log("Hello, Google Apps Script!");
}

Practice:

  • Run the function.
  • Open View → Logs to see the message.

Quiz (3 questions):

  1. Apps Script is based on which programming language?
    • a) Python
    • b) JavaScript ✅
    • c) Java
  2. Where do you open the Apps Script editor in Google Sheets?
    • a) File → Settings
    • b) Extensions → Apps Script ✅
    • c) Tools → Developer
  3. What function is used to output logs in Apps Script?
    • a) Console.log
    • b) Print()
    • c) Logger.log ✅

Day 2 – Working with Google Sheets

Goals:

  • Learn to open a spreadsheet.
  • Access and modify cells.

Topics:

  • SpreadsheetApp.getActiveSpreadsheet()
  • Reading and writing values.

Code Example:

function writeToSheet() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A1").setValue("Welcome to Apps Script!");
}

Practice:

  • Modify the script to write your name into cell B2.

Quiz (2 questions):

  1. Which method sets a value into a cell?
    • a) setCell
    • b) setValue ✅
    • c) writeValue
  2. What object do you use to work with Sheets in Apps Script?
    • a) DocsApp
    • b) SpreadsheetApp ✅
    • c) DriveApp

Day 3 – Reading and Writing Ranges

Goals:

  • Read data from multiple cells.
  • Write arrays of data back.

Code Example:

function readAndWriteRange() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A1:A5").getValues();
  Logger.log(values);
  
  const newValues = [["One"], ["Two"], ["Three"]];
  sheet.getRange("B1:B3").setValues(newValues);
}

Practice:

  • Read a row of numbers and write them doubled into another column.

Quiz (2 questions):

  1. What method returns multiple cell values?
    • a) getValue
    • b) getValues ✅
    • c) readRange
  2. To write an array of values, which method is correct?
    • a) setValues ✅
    • b) setValue
    • c) putValues

Day 4 – Loops and Logic in Apps Script

Goals:

  • Use loops (for) to process rows.
  • Apply conditions (if).

Code Example:

function markPassed() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getRange("A2:A10").getValues();
  
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] >= 50) {
      sheet.getRange(i+2, 2).setValue("Pass");
    } else {
      sheet.getRange(i+2, 2).setValue("Fail");
    }
  }
}

Practice:

  • Write a script to label scores above 80 as “Excellent”.

Quiz (2 questions):

  1. What loop type is commonly used for iterating through rows?
    • a) while
    • b) for ✅
    • c) do-while
  2. How do you check if a value is greater than 50?
    • a) value = 50
    • b) value > 50 ✅
    • c) value < 50

Day 5 – Custom Functions

Goals:

  • Create custom functions for Sheets.

Code Example:

function DOUBLE(number) {
  return number * 2;
}

Now use =DOUBLE(5) directly in a Google Sheet.

Practice:

  • Create a custom function FULLNAME(first, last) that returns “first last”.

Quiz (2 questions):

  1. Can Apps Script custom functions be used like normal formulas?
    • a) Yes ✅
    • b) No
  2. What keyword is used to return a value?
    • a) yield
    • b) return ✅
    • c) output

Day 6 – Triggers

Goals:

  • Understand simple and installable triggers.
  • Automate running scripts.

Code Example (onOpen):

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Custom Menu")
    .addItem("Say Hello", "helloWorld")
    .addToUi();
}

Practice:

  • Add a menu item that clears the sheet.

Quiz (2 questions):

  1. Which trigger runs when a spreadsheet is opened?
    • a) onOpen ✅
    • b) onEdit
    • c) onLoad
  2. Where are installable triggers managed?
    • a) Extensions → Triggers ✅
    • b) File → Settings
    • c) Tools → Developer

Day 7 – Google Docs with Apps Script

Goals:

  • Work with DocumentApp.
  • Insert text into a document.

Code Example:

function writeDoc() {
  const doc = DocumentApp.getActiveDocument();
  doc.getBody().appendParagraph("Hello from Apps Script!");
}

Practice:

  • Write a script that adds today’s date into a document.

Quiz (2 questions):

  1. What class is used to work with Google Docs?
    • a) DocsApp
    • b) DocumentApp ✅
    • c) TextApp
  2. Which method adds a new paragraph?
    • a) addText
    • b) appendParagraph ✅
    • c) insertLine

Day 8 – Gmail Automation

Goals:

  • Send emails with GmailApp.

Code Example:

function sendEmail() {
  GmailApp.sendEmail("example@gmail.com", "Test Email", "This is a test sent from Apps Script.");
}

Practice:

  • Send yourself an email with today’s date in the subject.

Quiz (2 questions):

  1. Which service sends emails?
    • a) MailApp ✅
    • b) GmailApp ✅
    • c) Both ✅ (both work)
  2. What arguments are required for sendEmail?
    • a) To, Subject, Body ✅
    • b) To, CC, BCC
    • c) Subject, Body

Day 9 – Google Drive Automation

Goals:

  • Access and create files/folders with DriveApp.

Code Example:

function createFolder() {
  const folder = DriveApp.createFolder("My Apps Script Folder");
  Logger.log("Folder created: " + folder.getUrl());
}

Practice:

  • Write a script to list all file names in your Drive root folder.

Quiz (2 questions):

  1. Which class manages Google Drive?
    • a) DriveManager
    • b) DriveApp ✅
    • c) FileApp
  2. What method creates a folder?
    • a) makeFolder
    • b) createFolder ✅
    • c) newFolder

Day 10 – Putting It All Together (Mini Project)

Goal:
Build a project combining Sheets, Gmail, and Drive.

Project Example – Email Grade Report

function emailGrades() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getRange("A2:C10").getValues();
  
  for (let i = 0; i < data.length; i++) {
    const name = data[i][0];
    const email = data[i][1];
    const grade = data[i][2];
    
    const subject = "Your Grade Report";
    const body = "Hello " + name + ", your grade is: " + grade;
    
    GmailApp.sendEmail(email, subject, body);
  }
}

Practice:

  • Extend this to create a PDF report in Drive and attach it to the email.

Quiz (Final Challenge):

  1. Which Apps Script services were used in this project?
    • a) SpreadsheetApp ✅
    • b) GmailApp ✅
    • c) DriveApp ✅
  2. What loop structure is best for sending multiple emails?
    • a) for ✅
    • b) while
    • c) do-while

👉 By the end of these 10 days, learners will:

  • Understand the Apps Script environment.
  • Automate Google Sheets, Docs, Gmail, and Drive.
  • Build real-world mini projects.