Google Sheets Programming With Google Apps Script

277 Pages·2015·1.62 MB·English

by  Michael Maguire

Google Sheets Programming With
Google Apps Script (2015 Revision
In Progress)
Your Guide To Building Spreadsheet
Applications In The Cloud
Michael Maguire
©2016
Tweet This Book!
PleasehelpMichaelMaguirebyspreadingthewordaboutthis
bookonTwitter!
Thesuggestedhashtagforthisbookis
#googlespreadsheetprogramming.
Findoutwhatotherpeoplearesayingaboutthebookbyclicking
onthislinktosearchforthishashtagonTwitter:
https://twitter.com/search?q=#googlespreadsheetprogramming
Contents
Chapter1:Introduction . . . . . . . . . . . . . . . . . . . . 1
1.1GoogleSheets . . . . . . . . . . . . . . . . . . . . . 1
1.2GoogleAppsScript(GAS) . . . . . . . . . . . . . . . 2
1.3JavaScriptorGoogleAppsScript? . . . . . . . . . . 3
1.4SummaryOfTopicsCovered . . . . . . . . . . . . . 3
1.5SoftwareRequirementsForThisBook . . . . . . . . 5
1.6IntendedReadership . . . . . . . . . . . . . . . . . . 5
1.7BookCodeAvailableOnGitHub . . . . . . . . . . . 6
1.8MyBlogOnGoogleSpreadsheetProgramming . . . 7
1.8GuidelineOnUsingThisBook . . . . . . . . . . . . 7
1.92015UpdateNotes . . . . . . . . . . . . . . . . . . . 8
Chapter2:GettingStarted . . . . . . . . . . . . . . . . . . 9
2.1Introduction . . . . . . . . . . . . . . . . . . . . . . 9
2.2GoogleAppsScriptExamples . . . . . . . . . . . . . 9
2.2ExecutingCode–OneFunctionAtATime . . . . . 11
2.3Summary . . . . . . . . . . . . . . . . . . . . . . . . 14
Chapter3:User-DefinedFunctions . . . . . . . . . . . . . 15
3.1Introduction . . . . . . . . . . . . . . . . . . . . . . 15
3.2Built-inVersusUser-DefinedFunctions . . . . . . . 16
3.3WhyWriteUser-DefinedFunctions . . . . . . . . . 17
3.4WhatUser-DefinedFunctionsCannotDo . . . . . . 18
3.5IntroducingJavaScriptFunctions . . . . . . . . . . . 21
3.6User-DefinedFunctionsVersusJavaScriptFunctions 25
3.7UsingJSDocToDocumentFunctions. . . . . . . . . 26
CONTENTS
3.8CheckingInputAndThrowingErrors . . . . . . . . 26
3.9EncapsulatingAComplexCalculation . . . . . . . . 29
3.10NumericCalculations . . . . . . . . . . . . . . . . 31
3.11DateFunctions . . . . . . . . . . . . . . . . . . . . 33
3.12TextFunctions . . . . . . . . . . . . . . . . . . . . 39
3.13UsingJavaScriptBuilt-InObjectMethods. . . . . . 42
3.14UsingAFunctionCallback . . . . . . . . . . . . . 43
3.15ExtractingUsefulInformationAboutTheSpreadsheet 45
3.16UsingGoogleServices . . . . . . . . . . . . . . . . 49
3.18Summary . . . . . . . . . . . . . . . . . . . . . . . 50
Chapter4:SpreadsheetsandSheets . . . . . . . . . . . . . 52
4.1ANoteOnNomenclature . . . . . . . . . . . . . . . 52
4.2NativeAndHostObjects . . . . . . . . . . . . . . . 53
4.3ANoteOnMethodOverloadingInGoogleAppsScript 53
4.5ObjectHierarchies . . . . . . . . . . . . . . . . . . . 54
4.6SpreadsheetApp . . . . . . . . . . . . . . . . . . . . 57
4.7TheSpreadsheet Object . . . . . . . . . . . . . . . . 58
4.8TheSheet Object. . . . . . . . . . . . . . . . . . . . 62
4.9 Practical Examples Using Spreadsheet And Sheet
Objects . . . . . . . . . . . . . . . . . . . . . . . 63
4.10Summary . . . . . . . . . . . . . . . . . . . . . . . 69
Chapter5:TheRangeObject . . . . . . . . . . . . . . . . . 71
5.1Introduction . . . . . . . . . . . . . . . . . . . . . . 71
5.2RangeObjectsAreComplex . . . . . . . . . . . . . 73
5.3CreatingARangeObject . . . . . . . . . . . . . . . 74
5.4GettingAndSettingRangeProperties . . . . . . . . 75
5.5TheRangeoffset() Method . . . . . . . . . . . . . . 78
5.6TheSheetDataRange . . . . . . . . . . . . . . . . . 80
5.7TransferringValuesBetweenJavaScriptArraysAnd
Ranges . . . . . . . . . . . . . . . . . . . . . . . . 82
5.8NamedRanges . . . . . . . . . . . . . . . . . . . . . 89
5.9PracticalExamples . . . . . . . . . . . . . . . . . . . 91
5.11ConcludingRemarks . . . . . . . . . . . . . . . . . 104
CONTENTS
5.12Summary . . . . . . . . . . . . . . . . . . . . . . . 105
Chapter6:MySQLAndJDBC . . . . . . . . . . . . . . . . 106
6.1Introduction . . . . . . . . . . . . . . . . . . . . . . 106
6.2WhatIsJDBC?. . . . . . . . . . . . . . . . . . . . . 107
6.3MySQLPreliminaries . . . . . . . . . . . . . . . . . 108
6.4 Connecting to a Cloud MySQL Database from the
mysql Client. . . . . . . . . . . . . . . . . . . . . 109
6.5AnOverviewofJDBC . . . . . . . . . . . . . . . . . 111
6.6NoteonCodeExamples . . . . . . . . . . . . . . . . 112
6.7ConnectingtotheDatabase . . . . . . . . . . . . . . 113
6.8 Create, Load, Query, Update and Delete a Database
Table . . . . . . . . . . . . . . . . . . . . . . . . . 116
6.9PreparedStatements . . . . . . . . . . . . . . . . . . 126
6.10Transactions . . . . . . . . . . . . . . . . . . . . . 129
6.11DatabaseMetadata . . . . . . . . . . . . . . . . . . 131
6.12APracticalGASExample . . . . . . . . . . . . . . 133
6.13Summary . . . . . . . . . . . . . . . . . . . . . . . 143
Chapter7:UserInterfaces-MenusandForms . . . . . . . 144
7.1Introduction . . . . . . . . . . . . . . . . . . . . . . 144
7.2AddingAMenu . . . . . . . . . . . . . . . . . . . . 145
7.3BuildingFormsWithHtmlService . . . . . . . . . . 147
7.3.3DefiningFormLayoutinCSS . . . . . . . . . . . . 158
7.4TransferringDatafromGoogleSheetsToanHtmlSer-
viceWebApplication . . . . . . . . . . . . . . . . 163
7.5 Create Professional-looking Forms the Easy Way -
UseBootstrap . . . . . . . . . . . . . . . . . . . . 173
7.6Summary . . . . . . . . . . . . . . . . . . . . . . . . 177
Chapter8:GoogleDrive,Folders,Files,AndPermissions 178
8.1Introduction . . . . . . . . . . . . . . . . . . . . . . 178
8.2ListGoogleDriveFileAndFolderNames . . . . . . 180
8.3CreatingAndRemovingFilesAndFolders . . . . . . 183
8.4AddingFilesToAndRemovingFilesFromFolders . 186
CONTENTS
8.5FileAndFolderPermissions . . . . . . . . . . . . . 190
8.6PracticalExamples . . . . . . . . . . . . . . . . . . . 197
8.7Summary . . . . . . . . . . . . . . . . . . . . . . . . 210
Chapter9:EmailandCalendars . . . . . . . . . . . . . . . 211
9.1Introduction . . . . . . . . . . . . . . . . . . . . . . 211
9.2SendingAnEmailUsingMailApp . . . . . . . . . . 212
9.3SendingAnEmailWithAnAttachmentUsingMailApp214
9.4GmailApp . . . . . . . . . . . . . . . . . . . . . . . 216
9.5Calendars . . . . . . . . . . . . . . . . . . . . . . . 229
9.6Summary . . . . . . . . . . . . . . . . . . . . . . . . 236
Appendix A: Excel VBA And Google Apps Script Com-
parison . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Introduction . . . . . . . . . . . . . . . . . . . . . . . . 237
SpreadsheetsandSheets . . . . . . . . . . . . . . . . . 238
Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
AppendixB:FinalNotes . . . . . . . . . . . . . . . . . . . 268
AdditionalResources . . . . . . . . . . . . . . . . . . . 268
JSLint . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
GettingSourcecodeForThisBookFromGithub . . . . 270
BlogUpdates. . . . . . . . . . . . . . . . . . . . . . . . 270
Chapter 1: Introduction
1.1 Google Sheets
Google Sheets is one of the core components of Google cloud
applications. If you have a Gmail account, you can create and
share your spreadsheets with others, even with those who do not
have a Gmail account. Google Sheets offers a comprehensive set
of standard spreadsheet features and functions similar to those
found in other spreadsheet applications such as Microsoft Excel.
In addition, it also supports some novel features such as the very
versatile QUERY function and regular expression functions such
REGEXMATCH.
WhatreallydistinguishedGoogleSheetsfromdesktopspreadsheet
applications like Excel is its cloud nature. The spreadsheet appli-
cation runs in a browser and the spreadsheet files themselves are
stored remotely. The spreadsheet files can be shared with others
in read-only or read-edit modes making them ideal collaborative
tools. Spreadsheets form just one part, albeit an important one,
of the Google suite of products. Others are Google Documents,
Gmail, calendars, forms, and so on and all of these products are
inter-operableatleasttosomedegreeresultinginaveryproductive
environmentperfectlysuitedtocollaborativework.
WhenIbeganusingGoogleSheetsbackin2010itwasquitelimited
intermsofdatavolume,speedandfunctionality.Ithasundergone
significant development since then and got a major upgrade in
March 2014¹. If your experience of Google Sheets was negatively
influenced by experience with earlier versions, I encourage you
to try it again, I think you will notice a big improvement. The
¹https://support.google.com/docs/answer/3544847?hl=en
Chapter1:Introduction 2
old 400,000 cell limit per spreadsheet is gone and is now at least
2,000,000. It will comfortably deal with tens of thousands of rows
which is, I believe, quite acceptable for any spreadsheet. Other
spreadsheet applications such as Excel can handle a million plus
rows but when data volumes grow to this size, it is advisable to
switchtoadatabaseoradedicatedstatisticalapplicationtohandle
suchdatasizes.
1.2 Google Apps Script (GAS)
TheGoogleSheetsapplicationalsohostsaprogramminglanguage
calledGoogleAppsScript(GAS)thatisexecuted,notinthebrowser
but remotely on the Google cloud. Google define Google Apps
Scriptasfollows:
“Google Apps Script is a JavaScript cloud scripting language that
provides easy ways to automate tasks across Google products and
thirdpartyservices.”
IfGoogleSheetsissofeature-rich,youmightwonderwhyitneeds
tohostaprogramminglanguage.HerearefewreasonswhyGASis
needed:
• Writeuser-definedfunctionsforGoogleSheets
• Writesimple“macro”typeapplications
• Developspreadsheet-basedapplications
• IntegrateotherGoogleproductsandservices
• DevelopGraphicalUserInterfaces(GUIs)thatcanberunas
webapplications
• Interact with cloud-based relational databases via Google
JDBC Services.
GASplaysasimilarroleinGoogleSheetstothatplayedbyVisual
Basic for Applications (VBA) in Excel. Both are hosted by their
Chapter1:Introduction 3
respective applications and both are used to extend functionality
andintegratewithotherapplicationsandservices.
1.3 JavaScript or Google Apps Script?
TheemphasishereisonusingGAStoenhanceandcontrolGoogle
Sheets. Other Google services are discussed in the context of how
they can be used with Google Sheets. Since GAS is JavaScript
(Google describe it as a sub-set of JavaScript 1.8), there will in-
evitably be discussion of JavaScript as a programming language.
There is, therefore, some discussion of JavaScript topics as they
relatetothecodeexamplesgiven.
Regarding terminology, when discussing a general JavaScript fea-
ture,thecodemaybereferredtoas“JavaScript”butwhendealing
with a Google App specific example, it may be referred to as
“GoogleAppsScript”or“GAS”.Themeaningofwhichevertermis
usedshouldbeclearfromthecontext.Forexample,theSpreadsheet
objectiscentraltoGoogleSheetsprogramming.Itis,however,pro-
videdbythehostingenvironmentandisnotpartofJavaScriptitself.
Thisdualityoftheprogramminglanguageandtheobjectsprovided
bythehostingenvironmentissimilartoJavaScriptrunningonthe
webclientandtheDocumentObjectModel(DOM)entitiesthatit
manipulates.
1.4 Summary Of Topics Covered
Thisbookaimstoprovidethereaderwithasolidknowledgeofthe
GASlanguagebothasitappliestoGoogleSheetsandhowitisused
toallowGoogleSheetstointer-operatewithotherGoogleproducts
andservicesaswellaswithrelationaldatabases.
Chapter 2 introduces the GAS language and sets the scene for
the chapters that follow. One of the most important applications