Google Apps Script
1
The Scope
■ Introduction to Apps Script
■ Authentication, Security & Sharing
■ Cloud Script IDE & Debugging
■ Overview of various services
■ Forms, Drive, Gmail, Sheets, Calendar
■ Building UI with HTML & CSS
■ Connecting to external APIs
■ Live Coding Sessions
■ Building apps from start to finish
■ Deployment
■ Best Practices
2
What is Google Apps Script
■ Mike Harm created in 2009 (20%)
■ Cloud based JavaScript engine
■ No SDKs
■ Deep hooks into Google Products
■ No complex Oauth
■ Backup / Revisions
■ Date, RegExp, Math objects
■ No DOM or Windows API
3
The Possibilities
■ Spreadsheet functions
■ Standalone web apps
■ Cron Jobs
■ Event based tasks
■ Enhance Docs, Sheets
■ Integrate Google Services
■ Serve Data to External Apps
4
Types of Apps Script
■ Standalone
– Not bound but can still talk
– Deploy as web app
– Time based triggers
– Searchable
■ Container based
– Can perform actions based on events
– Hidden in the container
– Get special access to container
5
Running Apps Scripts
■ Standalone
■ Container Bound
■ Event based
■ Time based
■ Web App
6
Apps Script Distribution
■ Web App
■ Container Bound
■ Chrome Store
■ Add-on Store
■ Libraries
7
Apps Script Ideas
■ Reporting / Charts
■ Manage and Automate Email
■ Approval Workflows
■ Extend Sheet with Custom Functions
■ Mail Merge
■ Employee Orientation
■ Tracking Document Revisions
■ Talk to Twitter, YouTube, Salesforce, etc.
8
CODE EDITOR
All you need is a browser
9
Tour of IDE
■ Launching the script editor
■ Quick Tour
– Auto-completion
– Syntax highlighting
– Private vs Public Functions
– Unique filenames
■ Project Properties
■ Revisions
10
The Logger
■ Analogous to [Link]
■ Email Full Log
■ Execution Transcript
– Time Taken to complete
– See Script flow
■ Log multiple values (printf)
11
Keyboard Shortcuts
■ Comment Lines
■ Autocomplete
■ Run a function
■ See Log
■ Auto Indent
■ Undo / Redo
12
The Debugger
■ Troubleshoot the code
■ Breakpoints
■ Pass parameters for web apps with wrappers
■ Hover to see full details
■ Try / Catch
■ Limitations
– No user input
– Connections to Google services
13
GOOGLE SERVICES
Always Available
14
Gmail
■ Gmail vs MailApp
■ Send HTML Mail
■ Include Attachment
■ Search Email
■ Trash Old messages
■ Email Quota
■ Quiz – You have ‘n’ unread messages
15
Drive
■ Search Files
■ File Iterator
■ Create File
■ Copy / Move Files
■ Share Files
■ Quiz: Save an email in Gmail and reply the URL
16
Spreadsheet
■ Add Custom Function
■ Store Data in rows
■ Email a row
■ Charts
■ Add Menu
■ On Edit
Quiz: Save the sender’s email of recent 5 spam emails that are in your Gmail
17
Document
■ Create Document
■ Add Menus
■ Add Text
■ Style Text
■ Replace Text
■ Quiz: Append the link of the document to the body on the next page
18
Calendar
■ See Agenda
■ Create Events
■ Add Guests
■ Recurrence
■ Set Permissions
■ Quiz: Find all events for this week across all calendars
19
Google Contacts
■ Show Groups
■ Get Work Addresses
■ Set Notes
■ Quiz: Fetch contacts to a sheet
20
Google Forms
■ Form Responses
■ Route to Manager
■ Acknowledge
■ Quiz: Create a Document with the Form Response
21
Google Maps
■ Route Maps
■ Geocode Address
■ Quiz: Save a Map Image to Drive
22
SCRIPT SERVICES
Always Available
23
URL Fetch
■ Headers
■ Handle Exceptions
■ Pass Parameters
■ Quiz: Fetch the Google Homepage and save in Drive
24
Utilities
■ Sleep
■ Format Date
■ String Encode
■ Hashing
■ Zip
25
Cache & Lock
■ Document
■ User
■ Script Cache
26
ADVANCED
SERVICES
Not enabled by default
27
Drive API
■ File Revisions
■ Advanced File Search
– title contains ’vacation' and not title contains ’singapore’
– fullText contains 'important' and trashed = true
– modifiedDate > '2025-06-10T12:00:00’
– sharedWithMe and mimeType contains 'image/’
– properties has { key=’secret' and value=‘123’ }
■ Save Web Files
■ Empty trash
28
Advanced Services
■ Tasks
■ URL Shortener
■ Analytics
■ YouTube
29
TRIGGERS
Automate It
30
Triggers
■ Simple or Installable
■ Trigger Builder
■ doGet & doPost
■ Notifications, Exceptions
■ Manage Triggers
■ Limitations
– Private Functions
– Not installed with document copy
– Simple triggers limited
■ Quiz: Create a weekly trigger that runs on Wednesdays and Fridays only
31
STORAGE
Properties Services
32
Properties
■ Key Value pairs
■ Scope
■ Manage Triggers
■ Security
– Server details – script
– User preferences – user
– Data source - document
■ Quiz: Save the DB credentials
33
JDBC
■ MySQL, Microsoft SQL, etc.
■ Create vs Prepare
■ Batch Operations
■ Quiz: Create a simple table in the SQL Database
34
WEB APPS
HTML Service
35
Web Apps
■ Supports Standalone & Container based
– Common Inbox
– Shared Dropbox
– Team Calendar
■ doGet() or doPost
■ ContentService (JSON, RSS)
■ HTMLService (HTML, CSS)
■ Permissions
■ Limitations
– Cookies & Windows API unavailable
36
Demo
■ Deploy Script as Web App
■ Query Parameters
– [Link]
– [Link]
■ Manage versions
■ Access Permissions
■ XML, JSON, Text, HTML
37
Assignment
■ Write a simple proxy server
■ Fetch page on Google server
■ Render in your Browser
38
BUILDING ADD-ONS
The Dummies Guide
39
Step By Step
■ Create Menus
■ Write UI in HTML/CSS/JS
■ Writer Server Side Code
■ Test
■ Deploy
■ Publish
40
THE LITTLE THINGS
HTML Service
41
Things to Know
■ Execution Time
■ EffectiveUser vs ActiveUser
■ Jsdoc Format Functions
■ OnlyCurrentDoc
■ Script Quota
■ Fail Gracefully
42
Google Help
Web: Google Add-ons
❏ [Link]/apps-script ❏ Document Studio
❏ Download Gmail
❏ Gmail Mail Merge
43