Going GAS

Chapter 6. The Spreadsheet Service

Google Sheets is the equivalent of Microsoft Excel, and the Spreadsheet service is the interface into its object model. Spreadsheets are the most likely to be extended, for one thing because you can create custom functions that can be accessed directly as formulas from the Excel or Google spreadsheet, using VBA and Apps Script respectively.

Sheet scripts are also the most likely to be (almost certainly in Excel) container-bound, as custom scripts cannot be accessed as a custom formula. In some ways this is a pity, because it continues the tradition of code sprawl and duplication.

Only a subset of the methods available in the Spreadsheet service will be covered here to illustrate the contrast with VBA. The full reference material is available on the Google Developers site, details of which you’ll find in Appendix A.

Custom Formulas

Accessing scripts as formulas from a sheet is a long tradition in Excel. The idea is that you can write a script that accepts arguments passed from a formula in a spreadsheet cell, do some processing that you couldn’t otherwise do using standard sheet formulas, and return the result to be displayed in the cell.

I’m not a big fan of these kinds of formulas, partly because they introduce a dependency that you can satisfy only by copying code to new sheets, but also because they can be resource-hungry.

Custom sheet formulas generally perform poorly, but we’ll cover various techniques to mitigate that—in particular, using ...