Going GAS · From VBA to Google Apps Script by Mcpherson, Bruce -- Read -- Imperial Library of Trantor

Index

Preface

Why Read This Book? Why Transition from VBA? Intended Audience The VBA Library Reading Order The Examples Conventions Used in This Book Using Code Examples Safari® Books Online How to Contact Us Acknowledgments

1. Introduction

What Is VBA?

Extending Fragility Security Asynchronicity Efficiency and Performance Maintainability

What is VBA good for?

What Is JavaScript?

A Quick History Versions JavaScript Is Not Java Learning JavaScript

What Is Apps Script?

Versions It Runs on a Server Services Fully Authenticated Environment Quotas

Rate limits Quota limits

Performance Asynchronicity Events Triggers Web Apps Maintainability IDE What Is Apps Script Good For?

What Are Google Add-Ons?

Types of Add-Ons Security The Publishing Process

What Are Google Forms? What Are Microsoft Add-Ins? Comparison

2. Language Basics

Style

Hungarian Camel Case Case Sensitivity Copy/Paste Porting Types

Operators

Mathematical Operators Assignment Operators String Operators Comparison Operators Logical Operators Bitwise Operators

Variables

Variable Types

Functions

Assigning Functions to Variables Anonymous Functions Functional Programming Loops and Iteration

forEach reduce filter map some every

Layout

Whitespace and Newlines Semicolons Curly Braces

Scope Objects

JavaScript Object Notation JSON Examples

Classes

Prototypes and Constructors Inheritance Methods A Note on this Getters and Setters VBA Example

The Mammal class The SeaMammal class

Using Object.create

Creating object instances Using getters and setters The prototype chain Traversing the prototype chain

Conclusion

Namespaces

Google Services Libraries Creating Your Own Namespace

The namespace Assigning properties and methods

Avoiding Namespace Collisions

The IDE

Container-Bound Scripts Standalone Scripts Code Examples Accessing the IDE Running a Function Examining Results

Libraries

Libraries in the Cloud Namespaces and Libraries Managing Library Versions Adding Libraries to a Project Some Notes on Library Sprawl

3. Translating VBA Functions

Conventions Library and Namespace JSDOC

JSDOC Example

VBA Built-Ins Translated to JavaScript

Helper Functions String Functions

Asc Chr InStr InStrRev Join LCase Left Len LTrim Mid Right RTrim Space Split Trim UCase

Conversion Functions Math Functions Informational Functions and Constants Date and Time Functions

Now Values DatePart Portions Weekdays Weekday and week number constants Week numbers Date calculations DateHelpers

Optional Arguments Testing for undefined Applying Default Values Named Arguments

Handling Errors

VBA Exception Branching

Resuming Detecting the error

JavaScript try/catch Raising an Error

VBA JavaScript

VBA Built-In Objects

Type

Copying types Cloning objects

Key/Value Pairs Collections

VBA JavaScript collection VBA.Collection for JavaScript The default method

4. Living with the Quotas

The Quotas

Daily Limits Limitations Triggers Rate Limits

Throttling

Sleeping Exponential Backoff

Code for exponential backoff

Splitting Libraries Batching Parallel Running Offloading

Avoiding Service Calls

Cache Service

Cache scopes Cache data expiry Sharing cache

5. The Properties Service

APIs Versus Built-In Services Getting Started with Properties Service Uses and Types of Property Stores Selecting a Property Store The Registry Versus the Property Store

Comparisons

Writing to the registry Writing to the property store Reading the registry Reading the property store Deleting from the registry Deleting from the properties store

6. The Spreadsheet Service

Custom Formulas Container-Bound Versus Standalone Scripts Getting Started with the Spreadsheet Service

A Note About Authorization Opening the Active Sheet

The Range Class

Creating a Range Returning the Data Range Getting the Values of a Range

VBA Apps Script

Writing Values to a Range

Walkthrough

Returning Selected Data

Walkthrough

Reading and Writing for Partial Ranges Reading Attributes from a Range

VBA get background colors Apps Script get background colors

Writing Attributes to a Range

Apps Script and VBA get/set equivalence

Inserting and Deleting Rows and Columns Opening Other Sheets

Iterating All Sheets Getting a Sheet by Name or Index

Opening Other Workbooks

Creating a Standalone Script Accessing Multiple Workbooks Working with Multiple Workbooks Updating Sheets

Showing Messages

Toast Showing Messages with Buttons

Dialog with a simple OK button Dialog with a title and buttons

Getting Input

Getting and Setting Properties

Document, User, or Script? Setting Properties in the Registry Setting Properties Using the Properties Service Changing Settings

Custom Formulas

Copy/Paste Port Native Port Arguments to Custom Formulas

Workaround

Performance

Timing functions Improving performance with array formulas

Documentation and Autocomplete

Adding Functions to Menus Tables

Converting Values to an Object Emulating Tables in Apps Script ListObject

Creating a table reference Table ranges Getting data from a ListObject ListObject JavaScript code

7. The Document App

Opening Documents Working with Elements Traversing the Document

Traversing in VBA Annotating the Document

Ranges

VBA Range VBA Discontiguous Ranges RangeElements The showRange Utility

Partial RangeElements

RangeBuilder

VBA range collections Building partial element ranges Finding text Merging RangeElements VBA find

Named Ranges

Setting a Cursor Using a Named Range

Position

Position Within Element

Setting the position

Creating a Selection Creating a VBA Selection Inserting Text

Bookmarks

IDs VBA Bookmark Insert Bookmark Appearance Traversing Bookmarks Text Bookmarks Creating Links Setting a Cursor Position

VBA cursor

Removing Bookmarks

Editing Text in Elements Adding Elements

Containers

PARAGRAPH

Element childIndex VBA childIndex

Inserting Elements Tables List Items

VBA ListParagraph

Images Docs Automation Example

Selecting the Target Area Inserting the Table Adding to Custom Menu

Attributes

Text Attributes Attribute Equivalence Partial Attributes Attribute Indices

8. Gmail, Calendar, and Contacts Apps

Email Automation Exercise

Scenario

Threads

Searching

Querying the message body

Messages

Message Filtering Regular Expression Searching

Message body

Searching the body

Name Lookup Body Errors Result Reduction Generate a Regular Expression Attachments Organizing

Recipients

Organizing by Recipient

Formatting Organizing by recipient with VBA

Sending

Labels

VBA Categories

Calendar

Events The Courses Namespace

Finding the next event Adding guests Email invites

Advanced Calendar Service

Enabling advanced services Adding guests with the Calendar API CoursesAdvanced namespace

Contacts

ContactGroups Contacts Namespace Organizing Courses

Setting Up the Example

The Settings

Properties service VBA registry

Triggers

Apps Script Main Function VBA Main Function Scheduling

9. Drive and DriveApp

Microsoft OneDrive Reading and Writing Files

VBA FileSystemObject Apps Script DriveApp Service

Paths in Drive

Splitting up the path

Apps Script reading and writing to Drive

fileExists fileRead fileWrite

VBA library FileSystemObject

Drive authorization Dependency-free VBA library

The VBA.FileSystemObject code

10. HTML Service

Why Client Execution?

The Downside The VBA Connection

HTML Service Varieties

Web Apps Dialogs Sidebars

jQuery Event Handling Templates

Structure

Installing the menu option Opening the sidebar The template Stylesheet

JavaScript

The result

Controlling Apps Script from the Client

Using Namespaces in HtmlService Multiple Menu Items indexRun.html mainRun.js Client Namespace

google.script.run

Render.js App.js

Dialog HtmlService HtmlService Web Apps VBA User Form

Create a User Form Initialize the Combo Box Listen for Changes The Form

11. Content Service

The Content Service Where to Use the Content Service Types of Content Example

Request Response Details doGet The Settings SheetOb getFlight getRegex Initial Result

JSONP XML

makeXml

Publishing

Script Files The Publishing Process Permissions

Delegation from VBA

Querying Apps Script

12. Charts

Chart Data VBA Charts

Code

Sheets Charts EmbeddedChartBuilder

setPosition Types of Chart

Visualization API

Google Visualization HtmlService App

code.gs index.html main.js client.js app.js render.js

Other Chart Formats

13. Sites

Apps Script Gadgets Code Lockdown Advertising VBA The Future of Sites

14. Advanced Google Services

What Are Advanced Google Services? Advanced Services Versus Google APIs Developers Console

Enabling Advanced Services

Fusion Tables Example

Settings Namespace Copy Sheet to Fusion Fusion Namespace Fusion Quotas

Scripts Structure Currently Available Advanced Services

15. Authentication and Security

OAuth 2.0

Setup Access Refresh

Scopes

Limiting Scope to the Current Document

Listing Authorized Apps Revoking Access ScriptApp Service Accounts Libraries OAuth 2.0 Example

Creating the Cloud Console Project

Credentials Enabling the Datastore API

Scopes

Storing the credentials Finishing the app

The callback function The consent page The web app The redirect URL

Accessing Other Oauth 2.0 Services

OneDrive Authentication

Get consent Access OneDrive

Other OAuth 2.0 Services

OAuth 2.0 with VBA

Developers Console getGoogled Content Service with OAuth2

Other Kinds of Authentication

Basic Authentication

JWT (JSON Web Tokens)

What Is a JWT?

Firebase Authentication

JWT Format FirebaseAuth Namespace

Standardized OAuth 2.0 Process

16. External APIs and Integration

REST API

Code Reuse

Databases Firebase

Main Code Permissions Settings Firebase Class Reuse Result

Databases and Apps Script

17. Execution API

What Is Incremental Migration? Migration Process The Execution API Inventory for Execution API Authentication and Access

Credentials Publishing Scopes Getting Googled

JSON SheetExec Example Workflow Migration from Office Moving a Workbook to Sheets

VBA Code to Read a Sheet Apps Script Code to Read a Sheet VBA Code to Write Data to a Local Workbook VBA Code to Write to a Sheet from a Local Workbook Apps Script Code to Write to a Sheet from a Local Workbook

Migrating Logic

VBA Code to Initiate Logic on Apps Script Logic Code Delegated to Apps Script

VBA Orchestration

VBA Process Orchestration Code Apps Script Logging Code

Final Migration Steps Testing JavaScript on the PC

VBA Code to Get Source Code from Apps Script Apps Script Code to Return Source Code Getting the Source and Testing Local Execution

Execution API Potential

18. Office Add-Ins and Google Add-Ons

Add-Ons Add-Ins The Same... ...But Different Add-On Example

The Dataset Capabilities

Apps Script Add-On

What You Will Learn The Namespaces Sharing Code Between Client and Server

requireGs

index.html main.js styles.css App Namespace Cors Add-On Script Reused Namespaces Server Namespace Client Namespace

Binding

Render Namespace

fitBounds Render.js code

Testing an Add-On Office Add-In

What You Will Learn The IDE Structure

Changing the start page

index.html mainOffice.js App.js Client.js

Testing the Add-In Result Comparison Further Exercises

Afterword A. Further Resources

GitHub Repository

Repository Structure gscript Files

Other Resources Keys and Credentials

Index

← Prev
Back
Next →

← Prev
Back
Next →