Boost your productivity on Google Docs and Sheets using scripts and macros
These little pieces of code will do the heavy lifting for you.
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.
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.
Here’s how scripts and macros work, and how you can adapt them to boost your productivity.
Developers have written add-ons for a range of Google apps, including Gmail. You’ll 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.
- Send Emails fires out automated Gmail messages. Just drop a list of email addresses and message texts into Google Sheets, and the script will do the rest. Copy and paste the linked code into a new project in the Script Editor, then save the result by clicking on the disk icon on the menu bar.
- Website Uptime 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.
- Docs to Markdown will convert a Google Doc into the popular Markdown format that many content management systems require. Again, the developer provided all the code, so you can just copy and paste it into a blank Scripts Editor project.
- 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 Script Editor web portal. Click on any script to see how frequently you’ve been using it. If you decide to change it, select Open Project. Before you tinker with an existing script, make sure to make a backup going to the file options (three dots next to the file name) and clicking on 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.
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.
To get started with macros on Google Sheets, open a spreadsheet in your browser and click Tools, 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 Tools, and then Macros. From there, you can also hit Manage macros to change a saved script’s name or keyboard shortcut. You can also click the three dots to the right to delete a macro or to view it in the Google 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.
The first time you try to run a macro in Sheets, Google will ask you to sign in again and 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.