This story has been updated. It was originally published on November 26, 2018.
In addition to cloud storage, Google Drive gives you access to an office suite that includes text editor Docs and spreadsheet program Sheets. Although these web apps emphasize speed and simplicity, you can add to their features with two types of tools: scripts and macros.
Written in JavaScript, scripts are simple add-ons that give an app extra features. For example, a script in Sheets might let you automatically fire off invoices, while a Docs one might quickly convert your files into PDFs.
A macro is a specific type of script: a group of actions that you launch all at once with a single key press or mouse click. One collection might let you apply the same format to a big chunk of text, while another could automatically duplicate several spreadsheet rows so you don’t have to manually copy them one by one. Once you understand how scripts and macros work, you can adapt them to boost your productivity.
How to add scripts to Google apps
If you’re comfortable with JavaScript, you can write your own original programs. Just open Docs or Sheets and go to Extensions, then click on Apps Script. A new tab with the Apps Script editor interface will open. There, you can create new scripts or run existing ones (use the Run button on the toolbar). If you have no coding expertise, don’t worry—you can still take advantage of scripts by importing those created by other people.
[Related: The 7 best Google Docs anonymous animals, ranked]
Developers have written add-ons for a range of Google app, and you can find these all over the internet. Some include full installation instructions, while others ask you to copy and paste a few lines of code into the script editor. Below, we’ve collected a few that can come in handy.
- Mail Merge fills a Gmail draft with information from a Google Sheets spreadsheet. Google will walk you through how to do this, but once you’re familiar with the process you can edit the source code to suit your needs. For example, you can add BCC or CC parameters if you want to send the information to multiple people or groups.
- Website Monitor creates a log of the uptime and downtime of a given URL, which allows you to track your website’s load time and know immediately if it goes down. In this case, the script is already embedded inside a Google Sheet, so you’ll simply adapt the parameters (like the URL you want to monitor) and let it run.
- Invoice Generator from SheetGo turns the rows of a Google Sheet into invoices. You fill the spreadsheet’s cells with data such as a company’s name, address, and the payment amount. Then the script places that information in a standard PDF template. If you look over the code, you’ll find that it isn’t too difficult to parse—variables like “client_name” are easy to understand—which means you can start tweaking the lines to better suit your invoicing needs.
Once you’ve imported some scripts, you can play around with them in the Apps Script web portal. Click on the information icon (an “i” in a circle) next to any script to see how frequently you’ve been using it. If you decide to change it, click the script itself. Before you tinker with an existing script, make sure to make a backup by going to the file options (three dots next to the file name) and clicking Make a copy.
If you edit enough scripts, you may decide to try coding some from scratch. Although you will quickly grasp the basics, actually turning that into a useful script can take a while. We recommend you consult this Zapier blog post, which introduces some of the concepts and guides beginners through the process of creating a script from scratch.
Create your own shortcuts by recording macros
You can think of macros as an extension of scripts: when you record your actions to create a macro, Google Sheets is actually generating a script to perform the demonstrated tasks. This script produces a mini-app that does repetitive and time-consuming actions for you.
Macros came to Sheets in 2018, but the feature has yet to make its way to Docs and Slides. However, macros have long been a part of the Microsoft Office suite, including text editor Word and spreadsheet program Excel. Within these apps, macros help you change formatting, input data, and perform any series of actions you find yourself repeating again and again.
[Related: Become a Google Drive power user with these 20 tips and tricks]
To get started with macros on Google Sheets, open a spreadsheet in your browser and click Extensions, then Macros, and finally Record macro. A new macro pop-up window will appear and instantly begin recording. Now, work through the steps of your task, just as you would when doing the operations normally. Finally, click Save to store it or Cancel to discard it.
As the macro records, you’ll notice two options on screen: Use absolute references and Use relative references (a downward arrow next to the right of the pop-up window can show or hide these options).
If you pick the former, the macro will run from the same place in the spreadsheet every time, but if you choose the latter, the macro will run from whichever cell is currently highlighted. For example, say you wanted to turn the top row of a spreadsheet pink every time the macro ran. In that case, you would use absolute references, since you always want the top row to be the one that changes color. However, if you wanted to pink-ify the cell to the left of the currently-selected one, you would need relative references.
Once you save your macro, you’ll need to give it a name. You’ll also get the option to assign it a keyboard shortcut. In addition to that, you can run any macro from the submenu under Extensions > Macros. From there, you can also hit Manage macros to change a saved script’s name or keyboard shortcut. After you click on Manage macros, you can also hit the three dots to the right of a macro to delete it or view it in the Apps Script editor. If you’re learning some coding basics, viewing the code for a macro will help you figure out which commands perform which actions.
[Related: Set up voice dictation on your computer and give your fingers a break]
The first time you try to run a macro in Sheets, Google will ask you to give permission. That’s because these snippets of code can be used for malicious purposes—like deleting a whole spreadsheet of data. Before you let the macro run, double-check that it’s one you created yourself.
