I know there may not be a single person in the world who has access to the Internet who has not used at least one of Google's products or services in their lifetime.
Google is known for its famous search engine, the video serving portal YouTube, and now by its numerous web applications, namely Gmail, Calendar, Drive, Docs, Sheets, and Forms. It also provides cloud computing and other software services.
The word "Google" has even become a verb, referring to conducting a web search. Nowadays, you hear people saying "I Googled something" rather than "I searched the web for something". In this chapter, you will learn about Google Applications, Application Scripts, and how to create a custom formula/function.
Google Applications are a collection of applications, namely Gmail, Calendar, Drive, Docs, Sheets, and Forms. From now on, we will use the term "Google Apps" or just "Apps".
Before we start, I'll quickly answer a few questions you may have:
Where do all these Apps run? On your computer?
No, all these Apps run on Google's Cloud-based servers.
How can you get access to these applications?
You can interact with these Apps through web browsers. No special hardware or software installations are required except for a modern web browser installed on your desktop, laptop, tablet, or smartphone.
You can customize or automate Google Apps using the JavaScript scripting language with Google-defined classes, known as Google Apps Script (GAS). Google implements GAS based on JavaScript 1.6 with some portions of 1.7 and 1.8. The GAS services and APIs provide easy access so users can automate tasks across Google products and third-party services.
You can write code in Google Docs, Sheets, and Forms using GAS and can automate tasks similar to what Visual Basic for Applications does in Microsoft Office. However, GAS runs on Google's server and the results are rendered in your browser. The integrated script editor allows you to edit and debug your scripts within your browser, and you do need not install anything. You can activate your debugged and tested script functions to run either based on your interactions or based on a trigger in response to an event or timed intervals (in minutes, hours, days, weeks, future dates, and so on). These events include onOpen, onEdit, onInstall, and many more. GAS is also used to create add-ons for Docs, Sheets, and Forms.
GAS can help you with every aspect of automating a task—you can even use it to order a pizza at predetermined date/time!
Microsoft implements Visual Basic for Applications (VBA) to help automate Office applications such as Excel and Word. For each respective application, VBA is known as Excel VBA or Word VBA and so on. Using Excel VBA, you can create macros for Excel known as "Excel macros". GAS is for Google Applications, and operates in the same way as VBA does for Microsoft Office applications. Although both VBA and GAS do not require a separate compilation process, they are very different scripting languages and use different programming APIs, methods, and properties.
I hope many of you are familiar with using VBA for Office applications; if not, then never mind—that's not an obstacle to learning GAS.
Version-independence: Sheets/Docs along with scripts are automatically saved in the cloud, attached to your Google account, and accessible from any computer with a browser. There is no need to worry whether the other computer has the same version of Sheets/Docs installed or not, whereas we can never be sure that one version of the Excel/Word macros will work on another version.
Platform-independence: When you create VBA macros in Excel/Word on the Windows platform, they may not work on the Mac platform and vice versa. With Google Sheets/Docs, it doesn't matter what platform you're working on—it'll work.
GAS runs on Google's server, so it cannot run continuously for more than six minutes (this may vary in the future). All of your functions should finish running and should return results within this time limit. Don't panic, as you'll learn how to use triggers effectively to overcome these limitations later.
In the following sections, we will take a look at the most popular Google Apps and how we can use GAS to customize and/or automate tasks.
Google Drive is a file storage application, which from now on we will just refer to as "Drive", where you can store and synchronize your files on Google's server. Let's look at some of the advantages of using Drive:
You can edit and share Google Docs, Sheets, and Forms with your friends or collaborators in real time.
You can even stop editing a document on one of your desktops and continue with your smartphone or tablet, and vice versa, no matter where you are and what device you are using. This is possible because your files are stored on Google's Cloud server.
Files created with Google Apps are stored in Drive with Google's native formats and extensions. For example, Google Docs (documents) files are
.gdoc, Google Sheets (spreadsheets) are.gsheet, and so on.In addition to Google's native files, you can also store or upload any other type of file from your desktop to Google Drive.
You may be wondering, what is the purpose of synchronizing files? Sometimes you may need to, or someone may ask you to, parse a CSV file stored on a desktop using GAS to process the data and organize it into a Sheet. In this case, GAS won't execute on the desktop, but it can on the Google server. This way you can access your Drive files and parse data within your synchronized CSV file. You don't have to upload the CSV file manually every time to Drive.
The following screenshot shows the Drive folder view:
Gmail is the most popular web-based e-mail service and is provided by Google. With it, occasionally composing and sending e-mail messages manually to one or a few people is not a problem. But what if you want to send an e-mail at a predefined time when you are not awake or to multiple recipients? Consider the following scenarios:
You want to send a surprise birthday greeting to your friend at a fixed time; neither earlier nor later
You need to send customized e-mails to hundreds of people at a time
You need to send e-mails periodically
For all these scenarios, GAS has the answers:
Using GAS, you can build a mail merger application to send e-mails with customized greetings or messages to n number of people.
You can extract information buried in e-mails from your inbox and store and organize them in Google Sheets or Docs.
You can even convert the data or contents of a Google Sheet or Docs to a PDF or any other file format and send it as an e-mail attachment, or just save the created file in Drive and include only the file's URL as a hyperlink in e-mail messages.
In addition, GAS also allows you to mark selected messages as important, or starred. You can also add, delete, and update your Gmail Contacts using the
Contactsservice.
The following screenshot shows how Gmail classifies or groups messages with labels:
Google's online Calendar service is integrated with Gmail. GAS provides access to Calendar service by using the CalendarApp class. Using GAS code, you can access and modify your Calendar and those you have subscribed to. Using GAS, you can create Calendar events and invite your friends programmatically. Alternatively, you can grab event details and populate them in Sheets.
Google Docs is a word processing program, and runs on web-based software within the Google Drive service. Docs allows you to create and edit documents online while collaborating with other users in real time. Using GAS, you can create documents, format the contents, translate them to other language, save them in Drive, or e-mail them to your friends.
Google Sheets is a spreadsheet program much like Microsoft Excel. You can create Sheets, share them with others, and edit them in real time. Google provides built-in formulae/functions in Sheets. You can also create your own simple to complex formulae. In other words, you can create custom formulae. Using the SpreadsheetApp class in your GAS code, you can interact with other applications.
Google provides Apps services to enable GAS to interact with the Apps. Almost all of the Apps provide one or more services. You can use these service classes in your GAS code to customize or automate Apps. Services are grouped as basic and advanced. You can use basic services directly, but for advanced services you need to enable them before using them. You will see how to enable them later on.
Here are the steps to create a Google Sheet:
Run your favorite browser and type https://drive.google.com/ in the address bar.
Now the Google Drive page will open. In the left pane, click on the NEW button and on Google Sheets:
After creating a new Sheet, right-click on it (Windows) or context click (Mac) and select the Share... option:
A new pop-up window will open as shown in the following screenshot. After that, enter the e-mail address, or addresses, with which you would like to share the document. Finally, click on the Done button:
Google will send a share notification to your friend(s). When your friend(s) click on the access link provided, they will get access to your document.
Congratulations! You have created a new Sheet and successfully shared it with your friend(s).
Scripts are organized as projects. Projects can be of two types, standalone and bounded to a gtype (Google Drive native file type, such as Sheets, Docs, and Forms) file. Standalone scripts are created in a separate script file, you can see these files listed among other files in Drive. Bounded scripts are embedded within individual gtype files and created using the respective applications. As you can see, the standalone script files, among other files in Drive, you can open directly from Drive, but bounded script can be opened within respective applications only. However, bounded script will have more privileges over parent file than standalone scripts. For example, you can get access to the active document within bounded scripts, but not within standalone scripts.
To create a standalone script file follow these steps:
Follow the steps as described in the Creating Google Sheets in Drive and sharing them with your friends and the public section.
Navigate to NEW | More | Google Apps Script rather than the spreadsheet, as shown in the following screenshot:
A new untitled project will open in a new browser tab or window. The new project includes one code file,
Code.gs, with a blank function,myFunction, as shown in the following screenshot:
To save or rename the new project, press Ctrl + S on your keyboard or click on the Save icon (floppy disk) in the editor. If you are saving the project for the first time then a prompt will appear to enter a new project name. Enter the project name (whatever you like) and click on the OK button. The new script file will be saved in the current folder:
Create a new Sheet or open the existing one. You will see a number of menu items at the top of the window. Now, follow these steps:
Click on Tools and select Script editor..., as shown in the following screenshot:
A new browser tab or window with a new project selection dialog will appear, as shown in the following screenshot:
Click on Blank Project or close the dialog (you do not need to always select Blank Project, just this time). A new untitled project will open in a new browser tab/window.
Congratulations! You have created a new script project. By following the preceding steps you can create script projects in Docs and Forms too.
Open the spreadsheet you created earlier and make the following changes:
In columns A and B, type a few first and last names.
In cell C2, type (including the equals sign)
=CONCATENATE(A2," ", B2).
Now you can see the first name and last name in cells A2 and B2 respectively, concatenated with a space in between.
CONCATENATE is Google Sheet's built-in formula. You can also create your own, called custom formula:
Open the script editor and copy-paste this code:
function myFunction(s1,s2) { return s1 + " " + s2; }Here is the screenshot for the same:
Press Ctrl + S on your keyboard or click on the Save icon in the editor to save the script.
Now return to the spreadsheet, and in cell C2, type
=myFunction(A2,B2).This works in exactly the same way as the built-in formula. You can extend your formula to other cells below C2. This is a simple formula, but you can create complex formulae as per your requirements.
Your custom formula should return a single value or a two-dimensional array. The following screenshot shows how a custom function will work:
Congratulations! You have created a custom formula.
Google Forms is a Google App that you can use to collect information from your users. User responses or answers are collected and stored as responses in the Form itself and then can be populated in the connected Sheet. You can also change the response's target Sheet when required. You can create Google Forms dynamically using GAS.
In the spreadsheet you created earlier, click on the Tools menu and select the Create a form option. A new Form will be created and is bound to a new Sheet automatically. The new Sheet's name will be similar to Form Responses 1. In the new Form, create form fields with headings exactly same as in the Sheet's column headers:
On completion, try submitting the data using a live Form.
In this chapter, you learned about Google Apps and got an introduction to GAS, as well as how to create a project and custom formulas. There are many more Google Apps available but we just covered the most popular ones. It will not be hard to adopt the same scripting concepts and principles for other Apps. In the next chapter, you will learn to create basic elements such as custom menus, dialogs, and sidebars.
