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 certain type of script is called a macro: 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 won’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. We’ve collected a few handy scripts below.
- Send Emails fires out automated Gmail messages. You 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 with File > Save.
- 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 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 simply 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 like company name, address, and 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 easily understandable—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—although you should remember to make a backup (File > Make a copy) before you tinker with an existing script.
If you edit enough scripts, you may decide to try coding some from scratch. Although you will quickly grasp the basics, turning that into an actually 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 auto-generating a script to perform the demonstrated tasks. This script produces a mini-app that does those repetitive, time-consuming actions for you.
Macros are relatively new to Google: They came to Sheets only this year, and 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.
So how do you use macros in Google Sheets? To get started, open a spreadsheet in your browser and click Tools > Macros > 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 (an arrow next to them can show or hide these options). If you pick the former, then 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. Here’s an 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, and you’ll get the option to assign it a keyboard shortcut. In addition to the keyboard shortcut, you can run any macro from the Tools > Macros submenu. From that same submenu, 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 do permit the macro to run, double-check that it’s one you’ve created yourself.