Google Sheets Advanced with Apps Script

COURSE CONTENT

1 Day (7 Hours) · Intermediate to Advanced · In-house at your office or online · 100% HRD Corp claimable programmes

Many Google Sheets users repeat the same steps every day or every week. They format new data, copy totals into a summary, export a PDF and email it to the same people. Each step is small. Together they take hours every month, and a missed step means a late or wrong report.

Google Apps Script is built into Google Workspace at no extra cost. It lets Google Sheets do those steps for you. In this programme you learn how to write and run simple scripts, add your own menu and custom functions, and set triggers so a task runs on a schedule or when a form is submitted.

You then connect Sheets with Gmail, Google Forms and Google Drive. By the end of the day you build a daily report that updates itself, saves a PDF to Drive and emails your team at a set time. You do not need a programming background, but you should be comfortable with Google Sheets formulas.

What participants will be able to do

• Explain what Apps Script can do in Google Workspace, and where its limits are.
• Write, run and debug simple scripts in the Apps Script editor.
• Automate repeat formatting, calculation and summary tasks in Google Sheets.
• Build custom menus and custom functions for your team.
• Set up simple and time-driven triggers so tasks run without you.
• Process Google Form responses and send email notifications from Sheets.
• Save generated reports to Google Drive and email them to a list of recipients.
• Maintain scripts safely with logging, error handling and clear naming.

Programme outline

Module 1: Introduction to Google Apps Script

• What Apps Script is, and what it can and cannot automate
• Opening the Apps Script editor from Google Sheets
• Scripts, functions and how a script runs step by step
• Working with the spreadsheet, sheets and ranges in code
• Variables, loops and conditions in plain terms
• Authorising a script the first time it runs
• Reading the execution log and fixing common errors
• Organising and naming a script project

Hands-on activity

• Write a script that cleans and formats a raw data tab
• Add a custom menu to Google Sheets that runs your script
• Break a script on purpose and use the log to fix it

Module 2: Automating Google Sheets Tasks

• Automating formatting that you repeat every week
• Working with rows, columns and ranges that change size
• Writing totals and summaries automatically
• Adding data validation and dropdowns with a script
• Applying conditional formatting from a script
• Protecting sheets and ranges automatically
• Creating and duplicating sheets from a template
• Writing reusable functions you can call from other scripts

Hands-on activity

• Build a one-click sales report from raw data
• Automate the setup of a new month's worksheet from a template

Module 3: Custom Functions and Triggers

• Writing a custom function you can use like a normal formula
• Simple triggers: on open and on edit
• Installable triggers and when you need them
• Running a script when a Google Form is submitted
• Time-driven triggers for daily and weekly tasks
• Sending reminders and notifications automatically
• Logging each run so you can see what happened
• Trigger quotas and limits to plan around

Hands-on activity

• Create a custom function for a business calculation
• Build an attendance tracker that emails an alert when a record is missing
• Schedule a script to run every morning

Module 4: Google Workspace Integration

• Sending email from Google Sheets with Gmail
• Building a formatted HTML email from sheet data
• Reading and processing Google Form responses
• Creating, moving and naming files in Google Drive
• Generating a PDF from a sheet and attaching it
• Simple approval and notification workflows
• Sending to a list of recipients kept in a sheet
• Handling personal data in scripts in line with PDPA

Hands-on activity

• Email a formatted report directly from Google Sheets
• Save each report as a PDF in a named Drive folder
• Process new form submissions and notify the right person

Module 5: Reporting and Dashboard Automation

• Refreshing a dashboard automatically
• Building a report tab from raw data with a script
• Bringing in data from other sheets and files
• Updating charts and KPI figures without manual steps
• Generating a management summary for decision-making
• Structuring a reporting workflow so it can be reused
• Testing, error handling and alerts when a run fails
• Handing a script over so someone else can maintain it

Hands-on activity

• Build a daily sales report that updates, saves a PDF and emails itself
• Add an alert that emails you if the scheduled run fails

What you leave with

You leave with a working daily report that updates itself, saves a PDF to Google Drive and emails your team at a set time. You also leave with a custom menu, a custom function and a form-triggered notification. More importantly, you can read, change and extend these scripts yourself. For the company, routine reporting runs on time without manual effort, and staff time moves from copying numbers to acting on them.

Who should attend

• Google Sheets users at intermediate level who want to automate their work
• Executives and analysts who send the same reports every day or week
• Admin staff who handle forms, registrations and records
• HR and finance staff who send reminders and notifications
• Operations teams that track tasks, stock or attendance in Sheets
• Team leads who want reports to reach management on time without chasing
• Anyone who repeats the same clicks in Google Sheets every week