Going GAS

Preface

Why Read This Book?

Perhaps you already use VBA to extend Office, but are considering alternatives. You may have already “gone Google,” or you might be looking at Microsoft’s Apps for Office options. In particular, you’re wondering what to do with all the VBA code built up over the years that you consider essential to enabling your business processes.

This book will show you how to transition from VBA with minimal effort. Even if you are not a VBA user, you will learn how to use Apps Script and its ecosystem to automate processes in the Google Apps platform.

Why Transition from VBA?

In many ways, VBA has been a victim of its own success. Its tight integration with Office and very usable and immediate development environment make it hard to beat. However, it’s been with us since 1991, the same year that Tim Berners-Lee created the first website, which is still running today, but for historical rather any aesthetic or functional reasons.

Although VBA is as far removed from its 1991 forefather as today’s HTML5 sites are from that first website, every version of Office for as long as I can remember has come with a threat that perhaps VBA will not be supported. Office 2008 for Mac did not support VBA, but it was back again by public demand in Office 2011. Office 2016 has just been released, and we can all breathe a sigh of relief to see that VBA is still there. But Microsoft’s focus is shifting to Office 365 from Office for the desktop. According to Satya Nadella of Microsoft, “the most strategic developer surface for us is Office 365.”

With incompatibilities between 32-bit and 64-bit versions and between Office for Mac and Windows, and with a reliance on references to libraries that are no longer shipped with Windows, VBA becomes harder for Microsoft to support and for us to use.

I’m a longtime proponent and fan of VBA. I’m not alone, considering the Office development section of my website still generates almost a million annual page views, and there are still many active VBA communities and forums around. VBA continues to enjoy immense popularity, but (sadly) it’s probably time to move on, as one of these days it really will no longer be shipped with Office.

This book will show you by example how to do some of the same things you do today in VBA, but in Apps Script. The examples will use the Google Apps platform, but we’ll also look at the Microsoft JavaScript API.

Intended Audience

You are probably already a VBA or .NET developer. You might even already be an Apps Script developer who needs to understand something about VBA to assist with a migration, or perhaps you simply want to learn Apps Script. This is not intended to be a book for beginners, and best fits those who are already comfortable with one or more development languages.

It’s not necessary to already know JavaScript, but things will move faster if you do. This is not a JavaScript tutorial, but this book will introduce the language components and syntax and provide enough examples to enable the proficient use of Apps Script.

For add-ins and add-ons (extending Docs and Office with client-side web apps), and the HtmlService sections of the book, you’ll need some understanding of HTML, CSS, and the DOM.

The VBA Library

The overall objective of this book is to demonstrate how to apply Apps Script services best practices to solve problems common across Office automation platforms. A key output is a library that emulates many built-in VBA functions in JavaScript. This will allow you to port some of your VBA code and structure to Apps Script with minimal changes, while concentrating on the Apps Script services capabilities.

Reading Order

The first part of the book compares the capabilities of each platform, then moves on to the fundamentals of the JavaScript language, emphasizing how it differs from VBA. That is followed by a long reference section containing an implementation in JavaScript of each of the main VBA built-in functions, as well as some of the utility objects.

If you decide to use this library, it means that you can still write VBA-style code that calls VBA built-in function names, instead of using the native JavaScript equivalent. Alternatively, you can refer to the translations and implement those when you port your applications.

The rest of the book deals with each Google Apps Script service that has an equivalent in VBA (and a few that don’t), and will generally show you the contrast in navigating and interfacing between the respective object models.

There are both tutorials and reference material, and the order in which you read them is not especially important, although some of the examples refer back to previous chapters and concepts. It is likely that you already have experience in some of the subjects (or don’t plan to use some of the services covered). It’s not really required that you read the content sequentially.

In summary, the scope of the material is not only the contrast between how to do things in Apps Script and VBA, but also how to get things done in the Google universe. This will ease the transition to Google Apps, regardless of where you are coming from.

Apps Script is both young and versatile, with new capabilities being added (and old ones being deprecated) regularly, and unlike with VBA, you don’t have the option to get stuck on an old version (even if you want to).

The Examples

The code illustrations are a mixture of snippets and longer projects, but they quickly become challenging, using the kind of patterns found in real-world scenarios. This is by design. After all, you are probably already an accomplished developer and “Hello, World"–level tutorials are not going to be much help for quickly porting VBA applications that have already benefited from significant investment in intellectual and financial capital.

All examples (VBA and Apps Script) are publicly available on GitHub, the details of which you’ll find in Appendix A, but I encourage you to try to create some of the code in the Apps Script IDE to build up familiarity with the environment. Some of the VBA examples are more appropriate for Office for Windows and may not be fully compatible with Office for Mac. Of course, the Apps Script examples are platform independent.

All code will be in monospace font and accompanied by the appropriate icon, like this for VBA:

VB

Dim someVBA as string

and this for JavaScript:

JS

var someJavaScript;

I’ve omitted much of the exception handling that would normally need to be built in, simply so that the code can remain as focused as possible on the explanation of the topic in hand. If you do reuse any of the code in real applications, don’t forget to extend the error handling to your house standard.

Once you have mastered the techniques demonstrated, you should be in a good position to port all your legacy applications over to Apps Script with minimal effort.

Good luck!

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Indicates new terms, URLs, email addresses, filenames, and file extensions.

Constant width

Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.

Constant width bold

Shows commands or other text that should be typed literally by the user.

Constant width italic

Shows text that should be replaced with user-supplied values or by values determined by context.

Note

This icon signifies a tip, suggestion, or general note.

Warning

This icon indicates a warning or caution.

Using Code Examples

Supplemental material for this book is available for download at https://github.com/brucemcpherson/GoingGas.

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Going GAS by Bruce Mcpherson (O’Reilly). Copyright 2016 Pepada limited, 978-1-4919-4046-4.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

Safari® Books Online

Safari Books Online is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.

Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.

Safari Books Online offers a range of plans and pricing for enterprise, government, education, and individuals.

Members have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and hundreds more. For more information about Safari Books Online, please visit us online.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

• O’Reilly Media, Inc.
• 1005 Gravenstein Highway North
• Sebastopol, CA 95472
• 800-998-9938 (in the United States or Canada)
• 707-829-0515 (international or local)
• 707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at http://bit.ly/going-gas.

To comment or ask technical questions about this book, send email to [e-mail].

For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.

Find us on Facebook: http://facebook.com/oreilly

Follow us on Twitter: http://twitter.com/oreillymedia

Watch us on YouTube: http://www.youtube.com/oreillymedia

Acknowledgments

When I started putting together the material for this book, I never realized there was so much I didn’t know, nor that it would take so long to find it all out and write it down. Many people have helped me along the way, and I’d like to take a little of your time to thank them.

I owe a great debt of gratitude to my truly international and esteemed group of technical reviewers, who gave generously of their time and knowledge to ruthlessly pick apart everything I wrote. Ian Macro (England) gave me a particularly hard time, pointing out those kinds of errors that are easy to miss as an author.

Fellow GDEs (Google Developer Experts) Martin Hawksey (Scotland), Ivan Kutl (Czech Republic), and Riël Notermans (Netherlands) kept me in line when my prose became too extravagant, my claims lacked foundation, I had missed an important detail, or I was just plain wrong.

Microsoft MVPs (Most Valuable Professionals) Andy Pope (England) and Jordan Goldmeier (USA) ventured over to the Google dark side to help me with the accuracy and relevance of the Office and VBA material, as well as some of the JavaScript content and equivalences, and gave me insight into the appropriateness of the material to VBA experts making the transition.

Special thanks go to my longtime friend (and ex-boss), Ron Roberts, who supported me as I became interested in the APIs that Google was creating with such rapidity, and who so graciously allowed me the latitude to become part of the Google Developer Expert community.

Apologies go to my Cairn terriers, who will be especially pleased that these writings are over (for now). Their thrice-daily walks became a little shorter as my book deadline approached, but now we can get back to normal.

Most importantly, thanks to my wife, Blandine, whose rigorous encouragement inspired me to get typing when I would rather have been eating cheese, drinking wine, playing Baldurs Gate, or even washing the car. Without her support and tolerance, I would never have got past the blank page that confronted me on day 1.

Finally, thanks to my editor, Meg Foley, and the team at O’Reilly Media who continue to give ordinary people like us the opportunity to have a voice, and without whose guidance, assistance, and hard work this book would not have been possible.

Without the great products created by both Google and Microsoft, I would have had nothing to write about. The pace of innovation is staggering, the APIs are exciting, and I can’t wait to see what’s coming up next so I can get started on my next project.