Google Apps Script for Beginners

178 Pages·2014·4.04 MB·English

by  Serge INSAS

Google Apps Script
for Beginners
Customize Google Apps using Apps Script and
explore its powerful features
Serge Gabet
BIRMINGHAM - MUMBAI
Google Apps Script for Beginners
Copyright © 2014 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: February 2014
Production Reference: 1140214
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-78355-217-7
www.packtpub.com
Cover Image by Glen Michael Carrie ([email protected])
Credits
Author Copy Editors
Serge Gabet Tanvi Gaitonde
Insiya Morbiwala
Reviewers Stuti Srivastava
Henrique Abreu
David Bingham
Project Coordinator
Alejandro Leiva Harshal Ved
Rohit Mukherjee
Ajith Ranabahu Proofreaders
Simran Bhogal
Stephen Copestake
Acquisition Editor
Kevin Colaco
Indexer
Monica Ajmera Mehta
Content Development Editor
Shaon Basu
Production Coordinator
Nitesh Thakur
Technical Editors
Mrunal Chavan
Sebastian Rodrigues Cover Work
Nitesh Thakur
About the Author
Serge Gabet has been a professional audio equipment manufacturer for 20 years
and is now working for an artistic upper school in Brussels, Belgium as a teacher
and Technical Manager. He is also in charge of the Google Apps administration of
this school. He develops custom applications using Google Apps Script mainly for
his school, though he also works in other areas.
He has been designated a Top Contributor by Google since June 2011. He was
active on the Google Group Help forum till 2012, then on the Stack Overflow forum
(the Google Help Group forum was closed in June 2012), and became a first ranker
and an all-time contributor on the Stack Overflow forum a few months back.
I'd like to thank all the forum contributors who were on the same
forum at the time that I was new to the forum and helped me take
my first steps. Most of them are now Top Contributors too and even
if their knowledge was (and still is) greater than mine, they never
make me feel it. Thanks for that.

Also, thanks to Google collaborators for their day-to-day presence
and for listening to our concerns.
About the Reviewers
Henrique Abreu is a Google Apps Script Top Contributor; he graduated in
Control Engineering and works as an IT consultant in Brazil. He is a big fan of
Google Apps, especially Spreadsheets and of course, Apps Script.
David Bingham has been developing software and leading product development
teams for more than 25 years, with a side trip into academia as a professor of
Computer Architecture. He is currently working as an R&D leader for a global
provider of Unified Communications and Collaboration software. In his spare time,
he enjoys travelling and spending time with his family. His alter ego, Mogsdad,
is an active contributor to the Google Apps Script community.
Alejandro Leiva has been using computers with modems of 600 bps connected
to pre-Internet networks were used, since the age of 12. He has been working in
the areas of software development, technical leadership, project management,
and building an executive profile where he temporarily needed to switch his
development framework for a spreadsheet and choose Google Apps Scripts as his
new language. He has excellent skills in technical team building, agile environments,
open source software development and accessibility. His Twitter handle is @gloob.
Thanks to my supportive wife and especially my beloved daughter
for bringing new energy into my life.
Rohit Mukherjee is currently studying Computer Engineering at the National
University of Singapore (NUS) on a full scholarship offered by Singapore Airlines
(SIA-NOL). He is passionate about Software Engineering, Linux, Dev Ops, Agile
Methodologies, and Technology Startups. He is interested in the fields of financial
and healthcare technologies. He is currently studying graduate courses in computer
science as an exchange student at ETH Zurich. For more information, visit his web
page rohitmukherjee.github.io.
Rohit works as an independent software developer and has pursued an industrial
interest with The Bank of America Merrill Lynch (Singapore). He has also interned
with Ernst & Young, India and Klinify, Singapore in the past. He is a contributor to
open source software as well.
I would like to thank my parents for their support.
Ajith Ranabahu is an experienced software engineer and an avid open source
enthusiast. He holds a PhD in Computer Science and is a member of the Apache
Software Foundation. He has contributed heavily to the Apache Axis2 project and a
number of other small projects over the years.
www.PacktPub.com
Support files, eBooks, discount offers, and more
You might want to visit www.PacktPub.com for support files and downloads related
to your book.
Did you know that Packt offers eBook versions of every book published, with PDF and
ePub files available? You can upgrade to the eBook version at www.PacktPub.com and
as a print book customer, you are entitled to a discount on the eBook copy. Get in touch
with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up
for a range of free newsletters and receive exclusive discounts and offers on Packt books
and eBooks.
TM
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital
book library. Here, you can access, read and search across Packt's entire library of books.
Why subscribe?
• Fully searchable across every book published by Packt
• Copy and paste, print and bookmark content
• On demand and accessible via web browser
Free access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access
PacktLib today and view nine entirely free books. Simply use your login credentials for
immediate access.

Table of Contents
Preface 1
Chapter 1: Enhancing Spreadsheets 7
Spreadsheet functions versus Google Apps Script – how to choose? 7
An example 8
The spreadsheet-formula approach 9
The Google Apps Script approach – a few comments 10
The Google Apps Script approach – the code 11
Using the development environment – the script editor 15
The advantages of autocompletion 15
Reading the documentation 18
Custom spreadsheet functions 21
Formatting your spreadsheets automatically 23
Menus and custom buttons in spreadsheets 26
Reading and writing data from spreadsheets – best practices
and performances 30
Example 30
Catching events 32
Who is doing what? Script authorizations 32
Protecting your data 33
Publishing your data on the Web 35
Printing and exporting the result 35
Summary 36