Google Apps Script for Beginners
Chapter 8. How to Expand your Knowledge
One of the most frequently asked questions on help forums probably is, "How can I learn Google Apps Script?" The answer is almost always the same: learn JavaScript and follow the numerous tutorials available on the Internet.
No doubt, it is one of the possible ways to learn but it is also one of the most difficult ways. I shall express my opinion on that subject at the end of this chapter, but let us first summarize what we really need to be able to use Google Apps Script efficiently.
The first and most important thing we must have is a clear idea of what we want to achieve.
This seems a bit silly because we think, "Oh well, of course I know what I want; I just don't know how to do it!"
As a matter of fact, this is often not the case. Let us have an example: a colleague asked me recently how he could count the time he was spending at school for meetings and other administrative tasks, not taking into account his hours as a teacher.
This was supposed to be a simple problem as everyone in our school has a personal calendar in which all the events that we are invited to are recorded.
So, he began to search for a way to collect every possible event from his calendar to a spreadsheet and from there—since he can definitely use a spreadsheet—he intended to do some data filtering to get the result he wanted.
I told him to have a look at the Google Apps Script documentation and see what tools he had, to pick up data from calendars and import them into a spreadsheet.
A few days later, he came back to me complaining that he didn't find any appropriate methods to do what he needs to.
And, in a way, he was right; nowhere is such a workflow explained and it is actually not surprising. One can't imagine compiling all the possible workflows into a single help resource; there are definitely too many different use cases, each of them needing a particular approach. We had a discussion where I told him to think about his research as a series of simple and accurate parts and steps before trying to get the whole process in one stroke.
The following is what he told me another few days later:
"I knew nothing about this macro language, so I discovered that it is based on JavaScript with the addition of Google's own services that use a similar syntax and that the whole thing is composed of functions calling each other and having parameters. Then, I examined the calendar service and saw that it needs so-called date objects to choose a start and end date. Date object methods are pretty well explained on Mozilla's page, so once I got that I had an array of events, I thought what the heck is an array of objects? You gave me the link to this w3schools site, so I took a look at their definition; that was enough for me to go further and discover that I could use a loop to handle each event separately. Google documentation shows all the methods to get events details; that part was easy and now I have all my calendar events with dates, hours, description, title. All of it! I tell you."
I'm not going to transcribe all of our conversation—it finally took a couple of hours—but towards the end, he was describing the process so well that the actual writing of his script was almost just a formality.
With the help of the Content assist (autocomplete) feature of the script editor and a couple of browser tabs left open on JavaScript and Google documentation, he managed to write his script in one day.
Of course, the script was not perfect and by no way optimized his speed or gave nice-looking results, but it worked and he had the data he was looking for.
At that point, he could post his script on a help forum if something went wrong or try to improve another version if he's a perfectionist, but that depends only on his will to go further or not.
This experience is not far from what I mentioned in the Preface about my own journey in the world of Google Apps Script; the only difference is that I took so much pleasure in it that I decided to continue to learn and explore all the fields of application, not because I'm smarter than anyone but because when I discover something, it always brings me to something else that I didn't know and could be so useful!
As a conclusion for this part, I would simply say one thing: you will learn what you need.
If you don't need it, don't try to learn it as you will forget it faster than you learned it.
If you do, then be prepared to need something else right after; it is an endless journey!
JavaScript versus Google Apps Script
The following is stated on the overview of Google Apps Script documentation page:
Google Apps Script is a scripting language based on JavaScript that lets you do new and cool things with Google Apps like Docs, Sheets, and Forms.
They should use a bigger typeface to make it more visible!
The keyword here is based on JavaScript because it does indeed use most of the JavaScript Version 1.6 (with some portions of Version 1.7 and Version 1.8) and its general syntax. But, it has so many other methods that knowing only JavaScript is clearly not sufficient to use it adequately.
I would even say that you can learn it step-by-step when you need it, looking for information on a specific item each time you use a new type of object.
In Chapter 1, Enhancing Spreadsheets, we looked at the very first function presented in this book using that method (if you remember). The following is the code that was used:
function myAgeInHours(){
var myBirthDate = new Date('1958/02/19 02:00:00').getTime();
myBirthDate = parseInt(myBirthDate/3600000, 10);
var today = parseInt(new Date().getTime()/3600000, 10);
return today-myBirthDate;
}
We looked at the documentation about the Date object to find the getTime() method and then found parseInt to get the integer part of the result.
Well, I'm convinced that this approach is more efficient than spending hours on a site or in a book that shows all JavaScript information from A to Z. We have the opportunity to have powerful search engines in our browsers, so let's use them; they always find the answer for us in less time than it takes to write the question.
Concerning methods specific to Google Apps Script, I think the approach should be different.
The Google API documentation is pretty well organized and is full of code examples that clearly show us how to use almost every single method.
If we start a project in a spreadsheet, it is a good idea to carefully read the section about spreadsheets (https://developers.google.com/apps-script/reference/spreadsheet) at least once and just check if what it says makes any sense.
For example, in the Sheet class, I found this description: Returns the range with the top left cell at the given coordinates, and with the given number of rows.
The following screenshot displays the same description:
If I understand what range and co-ordinates are, then I probably know enough to be able to use that method (getRange(row, column, numRows) or a similar one.
You want me to tell you the truth? I didn't know we could get a range this way by simply defining the top-left cell and just the number of rows (only three parameters.). I always use the next one in the list, which is shown as follows:
The description says: Returns the range with the top left cell at the given coordinates with the given number of rows and columns.
So after all this time I spent on dozens of spreadsheet scripts, there still are methods that I can't even imagine exist!
That's actually a nice confirmation of what I was suggesting: one doesn't need to know everything to be able to use it but it's always a good idea to read the docs from time to time.
Infinite resources
As I have already mentioned a few times, JavaScript is a very popular language; there are thousands of websites that show us examples and explain methods and functions.
We must add all the forums and Q&A sites that return many results when we search something on Google to these websites (or any other search engine), and that is actually an unforeseen difficulty.
It happens quite often that we find false information or code snippets that simply don't work, either because they have typos in them or they are so badly written that they work only in a very specific and peculiar situation.
My personal solution is to use only a couple of websites and perform a search on their search engine, avoiding all the sources I'm not sure of. Maybe I miss something at times, but at least the information I get is trustworthy.
Last but certainly not least, the help forum recommended by the Google Apps Script team, http://stackoverflow.com/questions/tagged/google-apps-script (with the google-apps-script tag), is certainly the best resource that is available.
With more than 5000 questions (as of January, 2014), the help forum probably has threads about every possible use case and an important part of it has answers as well.
There are of course other interesting tags: JavaScript, Google docs, Google spreadsheets, and a few even more specific ones.
I have rarely seen really bad answers—although it does happen sometimes—simply because so many people read these posts that they generally flag or comment answers that show wrong information. There are also people from Google that regularly keep an eye on it and clarify any ambiguous response.
Being a newbie is, by definition, temporary
When I began to use Google spreadsheets and scripts, I found the Google Group Help forum (which does not exist anymore) an invaluable source of information and help, so I asked dozens of questions—some of them very basic and naive—and always got answers.
After a while, since I was spending hours on this forum reading about every post I found, I began to answer too. I was so proud of being able to answer a question!
It was almost like passing an examination; I knew that one of the experts there was going to read what I wrote and evaluate my knowledge; quite stressful but also satisfying when you don't fail!
So after a couple of months I gained my first level point (on the Google Group forum, there are no reputation points but levels, starting from 1 for new arriving members up to TC (Top Contributors), whose level is unknown but is generally more than 15 or 20; anyway, that's not important).
That little story is just a way to encourage any beginner to spend some time on this forum and consider every question as a challenge and try to answer it.
Of course, there is no need to publish your answer every time as there are chances that you may get it all wrong, but just use this as an exercise that will give you more and more expertise.
From time to time, you'll be able to be the first or best answerer and gain a few reputation points; consider it as a game, just a funny game where all you can finally win is knowledge and all you can lose is your newbie status—not a bad deal after all!
Try to find your own best learning method
I'm certainly not pretending that I know the best learning method for anyone.
All the tips I presented in the previous section did work for me—and for a few other people I know—but there is no magic formula that would suit everyone.
As I mentioned in the Preface, I know that each of us has a different background and follows a different path, but I wanted to say loud and clear that you don't need to have to be a graduate in IT to begin with Google Apps Script nor do you have to spend hours learning rules and conventions. Practice will make it easier everyday and motivation will give you enough energy to complete your projects, from simple ones to more ambiguous ones.
Summary
This chapter has given an overview of the many resources available to improve your learning experience. There are certainly more that I don't know of but as I already mentioned a few times before, we have powerful search engines in our browsers to help us.
We also have to keep in mind that Google Apps Script, as explained in this book, will probably be different as compared to what it will be in a couple of years. The last chapter will explain future evolution and development perspectives.