We have all been there. Although it has nothing to do with the development project you are working on, a need arises internally in your organization to make someone’s life easier. There could be a task that is a horrible manual job such as taking information from emails and populating a spreadsheet, or a process that is prone to errors like transferring data from one file to another. Regardless of the problem, it is yours now. You need a solution, and cannot invest much time or money to fix.
The problem with most other solutions is that they may employ some obscure scripting language that most people cannot spell or pronounce, let alone install and run. If you find something more mainstream, it may not be very customizable or extendable. Worse yet, it may not fully integrate with the tools that have the problem in the first place, causing you to “sort of” fix the problem. Phenomenal and cosmic is a bit of an exaggeration, but it may come pretty close when you try this platform using Google Apps Script to solve that seemingly minor but important problem.
Google Apps Script was released in 2009 and it is written in JavaScript. It was created as a scripting language based on JavaScript 1.6 with elements of 1.7 and 1.8 and operates in the Google Cloud. If you are familiar with JavaScript, then learning Google Apps Script will be very easy to pick up. Even if you do not know JavaScript, you can learn how to use Google Apps Script through beginner courses currently online through sites like Codecademy.
It requires nothing to install, and it comes with its own code editor, which has features like autocomplete and debugging. One of Google Apps Script bigger draws is that it is a scripting language that allows you to interact with Google Apps such as Docs, Sheets, or Forms or work with Google Services like Drive, Map, and Calendar. You can can publish web apps that are standalone or that are in Google Sites.You can even develop add-ons for internal or external use. With the increasing popularity of Google services, there is a good chance that you can utilize this tool as a suitable solution for your next internal problem.
As an example, let us create a script that takes a list of emails in a Google Sheets file and send an email to them. When you use Google Apps Script in an app like Sheets or Docs, it is called a bound script because it is bound to the app.
We first start by clicking on Tools > Script Editor.
The first time you go to the editor a new tab will be created, andyou will get a modal dialog box allowing you to choose the type of script you want to create on the left-side. On the right, links to tutorials are available. We will choose a Blank Project for this example.
Choosing a Blank Project will start us with an empty function in a file called, Code.gs.The project will be named “Untitled project”. You can click on the project title at any time to change it.
To allow us to send the emails, we will create a menu, called Send Emails, by replacing myFunction() with the onOpen() that will be called when the Sheet file is first opened. We will also add one item to that menu called, Send emails to recipients. (Google Apps automatically save as you work, but Google Apps Script does not, so do not forget to save often.) You may have to refresh the page to see the new menu.
|
|
Since we are bound to a Google Sheet, we can use the SpreadsheetApp service. The service is accessible through a global object similar to JavaScript’s Math object. Like the Math object, these global objects contain methods and classes you can use when you create your script. Each Google Service has at least one global object.
Another aspect to using global objects, you are able to utilize the content assist also known as autocomplete. As with many IDEs, you can press Ctrl+Space and suggestions will appear.
In order to add the menu to the sheet, we have to get the Ui object, which is returned by getUi(). We tell the Ui object what the name of the menu will be. The method, addItem(), will add an item to our menu. The second argument of the method names the method to be invoked when a user selects the menu item, which we will create later.
Next, adding to our existing code, we will retrieve the emails addresses so we can send an email to them. Since we are bound to the sheet, we will have access to methods that standalone scripts do not, such as getActiveSpreadsheet(), getActiveSheet(), getActiveRange(), and getActiveCell(). The methods listed are specific to Google Sheets. Each Google App has specific methods you can use.
|
|
The code here is pretty straightforward. We use the SpreadsheetApp global object to get the Spreadsheet object. Then we retrieve the data from the sheet. The method,
getDataRange(), returns a Range object that is the dimensions of the data present. We could use other methods, such as getActiveRange(), to return the data. This method returns a Range of cells that are currently active, which would be the cells the user has selected, i.e. highlighted.Since we have not written the code for sending emails, we use the Logger global object to log the recipient’s name and email address from the sheet. Once you run the script, you can press Ctrl+Enter to make the log window appear to see our log messages. We can utilize the built-in functionality of the editor to run scripts. To do this we can click the Select function button and choose sendEmails and click the Run button. We can also run the code by selecting Run > sendEmails. We can even debug our code. You can set breakpoints by simply clicking in the line number section, and red dot will appear indicating the line has a breakpoint.
One other note when you run your script for the first time, a dialog box will appear informing you that the script requires authorization to run. Click Continue to move the next dialog box. This box will request authorization for the Google services being utilized in your script. For example, you will need to give authorization to view and manage your spreadsheets in Google Drive and view and manage your mail for this script. Click Accept to continue.
We will now create the code to send an email to our recipients by using the Mail service.
|
|
By using the GmailApp global object, we can use sendEmail() to send emails. We just provide the necessary arguments: recipient, subject, body, and options. Options is a JavaScript object that has advanced parameters such as attachment, bcc, from, name, and noReply. Since we are using our Google account to write the script, the email will come from us as well.
If for some reason we want to send emails on regular basis from our script and torment these people, Google Apps Script has the ability to do this through triggers. You can access triggers through the icon that looks like a clock.
I can create triggers that are time or event based. Some event based options are On open, On edit, On change, and On form submit. I will create one that occurs every Monday around 12 to 1 a.m. I can also set failure notifications in this dialog if I choose to as well.
As you can see, getting started is pretty easy, and there is so much more you can do. I have used Google Apps Script for a couple internal projects. On one of these projects, I made it so a user can highlight someone’s email address in a cell of a spreadsheet and send them email to inform them to fill out a form. The email contained link to that form, which I created using Google App Forms. Because Forms creates and updates a spreadsheet from submitted responses, I was able to track when someone did not fill out the form. I could check the response spreadsheet by using a trigger that invoked a method to evaluate the spreadsheet for form submissions. If someone did not submit their response, then I automatically sent another email reminding them to fill it out. The only manual step in this entire process is highlighting a cell in a spreadsheet and clicking the menu item created through Google Apps Script.
You will have to decide if Google Apps Script has phenomenal or cosmic powers. At the very least, you can build an automated process for someone who has spent numerous hours toiling over a manual operation, and they will think that was pretty phenomenal and cosmic.
