O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Going GAS

Going GAS

by Bruce Mcpherson
Publisher: O'Reilly Media, Inc.
Release Date: February 2016
ISBN: 9781491940457
View table of contents
Start reading

Book Description

Whether you’re moving from Microsoft Office to Google Docs or simply want to learn how to automate Docs with Google Apps Script, this practical guide shows you by example how to work with each of the major Apps Script services. The book introduces JavaScript basics for experienced developers unfamiliar with the language, and demonstrates ways to build real-world apps using all of the Apps Script services previously covered.

Table of Contents

  1. Preface
    1. Why Read This Book?
    2. Why Transition from VBA?
    3. Intended Audience
    4. The VBA Library
    5. Reading Order
    6. The Examples
    7. Conventions Used in This Book
    8. Using Code Examples
    9. Safari® Books Online
    10. How to Contact Us
    11. Acknowledgments
  2. 1. Introduction
    1. What Is VBA?
      1. Extending
      2. Fragility
      3. Security
      4. Asynchronicity
      5. Efficiency and Performance
      6. Maintainability
    2. What Is JavaScript?
      1. A Quick History
      2. Versions
      3. JavaScript Is Not Java
      4. Learning JavaScript
    3. What Is Apps Script?
      1. Versions
      2. It Runs on a Server
      3. Services
      4. Fully Authenticated Environment
      5. Quotas
      6. Performance
      7. Asynchronicity
      8. Events
      9. Triggers
      10. Web Apps
      11. Maintainability
      12. IDE
      13. What Is Apps Script Good For?
    4. What Are Google Add-Ons?
      1. Types of Add-Ons
      2. Security
      3. The Publishing Process
    5. What Are Google Forms?
    6. What Are Microsoft Add-Ins?
    7. Comparison
  3. 2. Language Basics
    1. Style
      1. Hungarian
      2. Camel Case
      3. Case Sensitivity
      4. Copy/Paste Porting
      5. Types
    2. Operators
      1. Mathematical Operators
      2. Assignment Operators
      3. String Operators
      4. Comparison Operators
      5. Logical Operators
      6. Bitwise Operators
    3. Variables
      1. Variable Types
    4. Functions
      1. Assigning Functions to Variables
      2. Anonymous Functions
      3. Functional Programming
      4. Loops and Iteration
    5. Layout
      1. Whitespace and Newlines
      2. Semicolons
      3. Curly Braces
    6. Scope
    7. Objects
      1. JavaScript Object Notation
      2. JSON Examples
    8. Classes
      1. Prototypes and Constructors
      2. Inheritance
      3. Methods
      4. A Note on this
      5. Getters and Setters
      6. VBA Example
      7. Using Object.create
      8. Conclusion
    9. Namespaces
      1. Google Services
      2. Libraries
      3. Creating Your Own Namespace
      4. Avoiding Namespace Collisions
    10. The IDE
      1. Container-Bound Scripts
      2. Standalone Scripts
      3. Code Examples
      4. Accessing the IDE
      5. Running a Function
      6. Examining Results
    11. Libraries
      1. Libraries in the Cloud
      2. Namespaces and Libraries
      3. Managing Library Versions
      4. Adding Libraries to a Project
      5. Some Notes on Library Sprawl
  4. 3. Translating VBA Functions
    1. Conventions
    2. Library and Namespace
    3. JSDOC
      1. JSDOC Example
    4. VBA Built-Ins Translated to JavaScript
      1. Helper Functions
      2. String Functions
      3. Conversion Functions
      4. Math Functions
      5. Informational Functions and Constants
      6. Date and Time Functions
      7. Optional Arguments
      8. Testing for undefined
      9. Applying Default Values
      10. Named Arguments
    5. Handling Errors
      1. VBA Exception Branching
      2. JavaScript try/catch
      3. Raising an Error
    6. VBA Built-In Objects
      1. Type
      2. Key/Value Pairs
      3. Collections
  5. 4. Living with the Quotas
    1. The Quotas
      1. Daily Limits
      2. Limitations
      3. Triggers
      4. Rate Limits
    2. Throttling
      1. Sleeping
      2. Exponential Backoff
      3. Splitting
      4. Libraries
      5. Batching
      6. Parallel Running
      7. Offloading
    3. Avoiding Service Calls
      1. Cache Service
  6. 5. The Properties Service
    1. APIs Versus Built-In Services
    2. Getting Started with Properties Service
    3. Uses and Types of Property Stores
    4. Selecting a Property Store
    5. The Registry Versus the Property Store
      1. Comparisons
  7. 6. The Spreadsheet Service
    1. Custom Formulas
    2. Container-Bound Versus Standalone Scripts
    3. Getting Started with the Spreadsheet Service
      1. A Note About Authorization
      2. Opening the Active Sheet
    4. The Range Class
      1. Creating a Range
      2. Returning the Data Range
      3. Getting the Values of a Range
      4. Writing Values to a Range
      5. Returning Selected Data
      6. Reading and Writing for Partial Ranges
      7. Reading Attributes from a Range
      8. Writing Attributes to a Range
    5. Inserting and Deleting Rows and Columns
    6. Opening Other Sheets
      1. Iterating All Sheets
      2. Getting a Sheet by Name or Index
    7. Opening Other Workbooks
      1. Creating a Standalone Script
      2. Accessing Multiple Workbooks
      3. Working with Multiple Workbooks
      4. Updating Sheets
    8. Showing Messages
      1. Toast
      2. Showing Messages with Buttons
      3. Getting Input
    9. Getting and Setting Properties
      1. Document, User, or Script?
      2. Setting Properties in the Registry
      3. Setting Properties Using the Properties Service
      4. Changing Settings
    10. Custom Formulas
      1. Copy/Paste Port
      2. Native Port
      3. Arguments to Custom Formulas
      4. Performance
      5. Documentation and Autocomplete
    11. Adding Functions to Menus
    12. Tables
      1. Converting Values to an Object
      2. Emulating Tables in Apps Script
      3. ListObject
  8. 7. The Document App
    1. Opening Documents
    2. Working with Elements
    3. Traversing the Document
      1. Traversing in VBA
      2. Annotating the Document
    4. Ranges
      1. VBA Range
      2. VBA Discontiguous Ranges
      3. RangeElements
      4. The showRange Utility
      5. RangeBuilder
    5. Named Ranges
      1. Setting a Cursor Using a Named Range
    6. Position
      1. Position Within Element
      2. Creating a Selection
      3. Creating a VBA Selection
      4. Inserting Text
    7. Bookmarks
      1. IDs
      2. VBA Bookmark Insert
      3. Bookmark Appearance
      4. Traversing Bookmarks
      5. Text Bookmarks
      6. Creating Links
      7. Setting a Cursor Position
      8. Removing Bookmarks
    8. Editing Text in Elements
    9. Adding Elements
      1. Containers
      2. Element childIndex
      3. VBA childIndex
    10. Inserting Elements
    11. Tables
    12. List Items
      1. VBA ListParagraph
    13. Images
    14. Docs Automation Example
      1. Selecting the Target Area
      2. Inserting the Table
      3. Adding to Custom Menu
    15. Attributes
      1. Text Attributes
      2. Attribute Equivalence
      3. Partial Attributes
      4. Attribute Indices
  9. 8. Gmail, Calendar, and Contacts Apps
    1. Email Automation Exercise
      1. Scenario
    2. Threads
      1. Searching
    3. Messages
      1. Message Filtering
      2. Regular Expression Searching
      3. Name Lookup
      4. Body Errors
      5. Result Reduction
      6. Generate a Regular Expression
      7. Attachments
      8. Organizing
    4. Recipients
      1. Organizing by Recipient
      2. Sending
    5. Labels
      1. VBA Categories
    6. Calendar
      1. Events
      2. The Courses Namespace
      3. Advanced Calendar Service
    7. Contacts
      1. ContactGroups
      2. Contacts Namespace
      3. Organizing Courses
    8. Setting Up the Example
      1. The Settings
    9. Triggers
      1. Apps Script Main Function
      2. VBA Main Function
      3. Scheduling
  10. 9. Drive and DriveApp
    1. Microsoft OneDrive
    2. Reading and Writing Files
      1. VBA FileSystemObject
      2. Apps Script DriveApp Service
  11. 10. HTML Service
    1. Why Client Execution?
      1. The Downside
      2. The VBA Connection
    2. HTML Service Varieties
      1. Web Apps
      2. Dialogs
      3. Sidebars
    3. jQuery
    4. Event Handling
    5. Templates
      1. Structure
    6. Controlling Apps Script from the Client
      1. Using Namespaces in HtmlService
      2. Multiple Menu Items
      3. indexRun.html
      4. mainRun.js
      5. Client Namespace
      6. Render.js
      7. App.js
    7. Dialog HtmlService
    8. HtmlService Web Apps
    9. VBA User Form
      1. Create a User Form
      2. Initialize the Combo Box
      3. Listen for Changes
      4. The Form
  12. 11. Content Service
    1. The Content Service
    2. Where to Use the Content Service
    3. Types of Content
    4. Example
      1. Request
      2. Response
      3. Details
      4. doGet
      5. The Settings
      6. SheetOb
      7. getFlight
      8. getRegex
      9. Initial Result
    5. JSONP
    6. XML
      1. makeXml
    7. Publishing
      1. Script Files
      2. The Publishing Process
      3. Permissions
    8. Delegation from VBA
      1. Querying Apps Script
  13. 12. Charts
    1. Chart Data
    2. VBA Charts
      1. Code
    3. Sheets Charts
    4. EmbeddedChartBuilder
      1. setPosition
      2. Types of Chart
    5. Visualization API
      1. Google Visualization HtmlService App
    6. Other Chart Formats
  14. 13. Sites
    1. Apps Script
    2. Gadgets
    3. Code Lockdown
    4. Advertising
    5. VBA
    6. The Future of Sites
  15. 14. Advanced Google Services
    1. What Are Advanced Google Services?
    2. Advanced Services Versus Google APIs
    3. Developers Console
      1. Enabling Advanced Services
    4. Fusion Tables Example
      1. Settings Namespace
      2. Copy Sheet to Fusion
      3. Fusion Namespace
      4. Fusion Quotas
    5. Scripts Structure
    6. Currently Available Advanced Services
  16. 15. Authentication and Security
    1. OAuth 2.0
      1. Setup
      2. Access
      3. Refresh
    2. Scopes
      1. Limiting Scope to the Current Document
    3. Listing Authorized Apps
    4. Revoking Access
    5. ScriptApp
    6. Service Accounts
    7. Libraries
    8. OAuth 2.0 Example
      1. Creating the Cloud Console Project
      2. Scopes
    9. Accessing Other Oauth 2.0 Services
      1. OneDrive Authentication
      2. Other OAuth 2.0 Services
    10. OAuth 2.0 with VBA
      1. Developers Console
      2. getGoogled
      3. Content Service with OAuth2
    11. Other Kinds of Authentication
      1. Basic Authentication
    12. JWT (JSON Web Tokens)
      1. What Is a JWT?
    13. Firebase Authentication
      1. JWT Format
      2. FirebaseAuth Namespace
    14. Standardized OAuth 2.0 Process
  17. 16. External APIs and Integration
    1. REST API
      1. Code
      2. Reuse
    2. Databases
    3. Firebase
      1. Main Code
      2. Permissions
      3. Settings
      4. Firebase Class
      5. Reuse
      6. Result
    4. Databases and Apps Script
  18. 17. Execution API
    1. What Is Incremental Migration?
    2. Migration Process
    3. The Execution API
    4. Inventory for Execution API
    5. Authentication and Access
      1. Credentials
      2. Publishing
      3. Scopes
      4. Getting Googled
    6. JSON
    7. SheetExec
    8. Example Workflow Migration from Office
    9. Moving a Workbook to Sheets
      1. VBA Code to Read a Sheet
      2. Apps Script Code to Read a Sheet
      3. VBA Code to Write Data to a Local Workbook
      4. VBA Code to Write to a Sheet from a Local Workbook
      5. Apps Script Code to Write to a Sheet from a Local Workbook
    10. Migrating Logic
      1. VBA Code to Initiate Logic on Apps Script
      2. Logic Code Delegated to Apps Script
    11. VBA Orchestration
      1. VBA Process Orchestration Code
      2. Apps Script Logging Code
    12. Final Migration Steps
    13. Testing JavaScript on the PC
      1. VBA Code to Get Source Code from Apps Script
      2. Apps Script Code to Return Source Code
      3. Getting the Source and Testing Local Execution
    14. Execution API Potential
  19. 18. Office Add-Ins and Google Add-Ons
    1. Add-Ons
    2. Add-Ins
    3. The Same...
    4. ...But Different
    5. Add-On Example
      1. The Dataset
      2. Capabilities
    6. Apps Script Add-On
      1. What You Will Learn
      2. The Namespaces
      3. Sharing Code Between Client and Server
      4. index.html
      5. main.js
      6. styles.css
      7. App Namespace
      8. Cors
      9. Add-On Script
      10. Reused Namespaces
      11. Server Namespace
      12. Client Namespace
      13. Render Namespace
    7. Testing an Add-On
    8. Office Add-In
      1. What You Will Learn
      2. The IDE
      3. Structure
      4. index.html
      5. mainOffice.js
      6. App.js
      7. Client.js
    9. Testing the Add-In
    10. Result Comparison
    11. Further Exercises
  20. Afterword
  21. A. Further Resources
    1. GitHub Repository
      1. Repository Structure
      2. gscript Files
    2. Other Resources
    3. Keys and Credentials
  22. Index