1 of 43

GOOGLE SCRIPTS

Amit Agarwal (Google Developer Expert)

​

www.labnol.org

​

2 of 43

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

​

3 of 43

What is Google 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

4 of 43

The Possibilities

  • Spreadsheet functions
  • Standalone web apps
  • Cron Jobs
  • Event based tasks
  • Enhance Docs, Sheets
  • Integrate Google Services
  • Serve Data to External Apps

5 of 43

Types of Google 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

6 of 43

Running Google Scripts

  • Standalone
  • Container Bound
  • Event based
  • Time based
  • Web App

​

​

7 of 43

Script Distribution

  • Web App
  • Container Bound
  • Chrome Store
  • Add-on Store
  • Libraries

8 of 43

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.

9 of 43

CODE EDITOR

All you need is a browser

10 of 43

Tour of IDE

  • Launching the script editor
  • Quick Tour
    • Auto-completion
    • Syntax highlighting
    • Private vs Public Functions
    • Unique filenames
  • Project Properties
  • Revisions

​

​

​

11 of 43

The Logger

  • Analogous to console.log
  • Email Full Log
  • Execution Transcript
    • Time Taken to complete
    • See Script flow
  • Log multiple values (printf)

​

12 of 43

Keyboard Shortcuts

  • Comment Lines
  • Autocomplete
  • Run a function
  • See Log
  • Auto Indent
  • Undo / Redo

13 of 43

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

​

​

14 of 43

GOOGLE SERVICES

Always Available

15 of 43

Gmail

  • Gmail vs MailApp
  • Send HTML Mail
  • Include Attachment
  • Search Email
  • Trash Old messages
  • Email Quota

​

  • Quiz – You have ‘n’ unread messages

​

16 of 43

Drive

  • Search Files
  • File Iterator
  • Create File
  • Copy / Move Files
  • Share Files

​

  • Quiz: Save an email in Gmail and reply the URL

17 of 43

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

18 of 43

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

19 of 43

Calendar

  • See Agenda
  • Create Events
  • Add Guests
  • Recurrence
  • Set Permissions

​

  • Quiz: Find all events for this week across all calendars

20 of 43

Google Contacts

  • Show Groups
  • Get Work Addresses
  • Set Notes

​

​

  • Quiz: Fetch contacts to a sheet

21 of 43

Google Forms

  • Form Responses
  • Route to Manager
  • Acknowledge

​

​

  • Quiz: Create a Document with the Form Response

22 of 43

Google Maps

  • Route Maps
  • Geocode Address

​

​

  • Quiz: Save a Map Image to Drive

23 of 43

SCRIPT SERVICES

Always Available

​

24 of 43

URL Fetch

  • Headers
  • Handle Exceptions
  • Pass Parameters

​

​

  • Quiz: Fetch the Google Homepage and save in Drive

25 of 43

Utilities

  • Sleep
  • Format Date
  • String Encode
  • Hashing
  • Zip

​

26 of 43

Cache & Lock

  • Document
  • User
  • Script Cache

27 of 43

ADVANCED SERVICES

Not enabled by default

28 of 43

Drive API

  • File Revisions
  • Advanced File Search
    • title contains ’vacation' and not title contains ’singapore’
    • fullText contains 'important' and trashed = true
    • modifiedDate > '2016-06-10T12:00:00’
    • sharedWithMe and mimeType contains 'image/’
    • properties has { key=’secret' and value=‘123’ }
  • Save Web Files
  • Empty trash

29 of 43

Advanced Services

  • Tasks
  • URL Shortener
  • Analytics
  • YouTube

​

30 of 43

TRIGGERS

Automate It

31 of 43

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

32 of 43

STORAGE

Properties Services

33 of 43

Properties

  • Key Value pairs
  • Scope
  • Manage Triggers
  • Security
    • Server details – script
    • User preferences – user
    • Data source - document

​

  • Quiz: Save the DB credentials

34 of 43

JDBC

  • MySQL, Microsoft SQL, etc.
  • Create vs Prepare
  • Batch Operations

​

  • Quiz: Create a simple table in the SQL Database

35 of 43

WEB APPS

HTML Service

36 of 43

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

​

37 of 43

Demo

  • Deploy Script as Web App
  • Query Parameters
    • e.queryString
    • e.parameter
  • Manage versions
  • Access Permissions
  • XML, JSON, Text, HTML

38 of 43

Assignment

  • Write a simple proxy server
  • Fetch page on Google server
  • Render in your Browser

​

39 of 43

BUILDING ADD-ONS

The Dummies Guide

40 of 43

Step By Step

  • Create Menus
  • Write UI in HTML/CSS/JS
  • Writer Server Side Code
  • Test
  • Deploy
  • Publish

41 of 43

THE LITTLE THINGS

HTML Service

42 of 43

Things to Know

  • Execution Time
  • EffectiveUser vs ActiveUser
  • Jsdoc Format Functions
  • OnlyCurrentDoc
  • Script Quota
  • Fail Gracefully

​

43 of 43

Amit Agarwal is a web geek, solo entrepreneur and he loves making things on the Internet.

​

In 2019, Google awarded him the Google Developer Expert title for his contributions to the GSuite and Google Apps Script ecosystem.

​

Web:

​

Google Add-ons

​