Essential add-ons for Google Sheets: Workflows, advanced find and replace, mail merge
Try these tools to help manage document approvals, find hidden data, personalize bulk emails, and more.
By Michael Ansaldo, PCWorldJul 8, 2015 3:30 am PDT
Managing and maintaining spreadsheets is one of the biggest productivity sinkholes in the workplace. Fortunately, if you’re using Google Sheets, there are many add-ons that expand on its capabilities so you can perform common tasks more efficiently and reclaim your time. Here are a few of the most essential.
When you needed document approvals in ye olden days, you could just sprint around the office getting the sign-offs from your colleagues in real time. But in today’s asynchronous, virtual workplace the document approval process is tougher to manage.
Workflows is a Google Sheets add-on that shepherds your document from recipient to recipient, prompting each to review, edit, and approve it. You just add the team members’ email addresses and a message into the Workflows window within your spreadsheet, and click the Request approval button. Once a recipient completes their part of the workflow, the next recipient receives an email prompting them to do the same. As the originator of the approval workflow, you can track who has and hasn’t yet weighed in on the document and receive a notification once the process is complete.
Advanced Find and Replace
Searching for a something in a complex spreadsheet is like trying to find the proverbial needle in a haystack. This add-on extends your Google Sheets searches beyond what you see in the cells to data hidden in the notes, formulas, and hyperlinks behind display text.
To conduct a search, type what you’re looking for in the “Find what” field, and select where to look and the type of data to search in the “Search in” tab below it. By default, the add-on searches only the active sheet, but you can tell it to search only within a range of cells, a selection of specific sheets, or all the sheets in the database.
All found entries are displayed on the “Search results” tab. Clicking an entry will take you to that entry in the source spreadsheet. Using the CTRL button, select any records you want to replace all at once with a new value or export all entries in your search results to a new sheet by clicking the Export all button.
Yet Another Mail Merge
Mass email marketing is a valuable tool in any business, but cutting and pasting contact info from your spreadsheet into Gmail is a nonstarter. This add-on populates your email template with that data automatically.
Start by drafting your message in Gmail, then use Yet Another Mail Merge to import your contacts into a new spreadsheet and perform the merge. You’ll need to manually add some syntax to your message to make sure all the data appears in the appropriate places—our handy how-to can walk you through the steps. Once your emails are sent, you can track which ones are opened from your spreadsheet.
Addressing those bulk emails can quickly become a hassle if you, say, want to address each recipient by their first name, but your spreadsheet is formatted to use their full name. This add on corrects the situation by splitting each part of their name into individual cells so your mail merge can pull just the name parts you want to use.
Once you’ve installed this add-on, highlight the range of cells containing your contacts’ names then go to Split Names > Start. Select from the five possible parts you can split each name into—first, middle, last, salutation/title, and suffix/post-nominals—and click Split. The add-on will insert columns for each select name part to the right of the original entry.