When it comes to my work as a content marketer, tackling complex equations for reporting makes my palms sweat. Lucky for me, a coworker happens to be a Google Sheets genius. He created a custom add-on that analyzes keyword research and auto-populates topic scores to help prioritize the most important content early on in our engagement—no math required.

But you don’t need to have a Google Sheets wizard on staff to benefit from time-saving add-ons. There are hundreds of Google Sheets add-ons that can do complex calculations, auto-populate missing data, translate text, and much more.

I’ve tried out some of the top add-ons in the Google Workplace Marketplace to make life a little easier.

How to use Google Sheets add-ons

To get started with Google Sheets add-ons, open a new or existing spreadsheet in your browser, select the Extensions menu in the top-right navigation, hover over Add-ons, and click Get Add-ons. That’ll open the Sheets Add-ons store, where you can search through over 250 add-ons or sort them by category.

Installing add-ons in Sheets is simple.

  1. Select the blue Install button, and accept the permissions.

  2. After a moment, click the Extensions menu again to see your new add-on along with any others you’ve installed listed below.

  3. Then click the add-on to run it—typically add-ons will open in a sidebar or a pop-over window over your spreadsheet.

Need to clean up your add-ons? Navigate back to Extensions, hover over Add-ons, click Manage Add-ons in the menu, then select the Manage button. A window will pop up with all of your add-ons, where you can choose to uninstall or request help if you’re having an issue with an add-on.

Most Google Sheets add-ons are installed inside Google Sheets, so they’ll work in any browser you choose. Add-ons are also available for the Google Sheets Android mobile app, but not for the iPhone app.

I tested add-ons for Chrome, Safari, and Firefox for this article.

27 Google Sheets add-ons to enhance your work

Now that you know how to give your spreadsheets superpowers, here are the best add-ons for Google Sheets to help you gather data, format text, crunch numbers, share your creations, and more.

Note: Most Google Sheets add-ons are free, so each tool listed here is free unless otherwise noted.

Google Sheets add-ons for:

Forms

Need to quickly add contact info, customer feedback, and more to a spreadsheet? A form is the simplest way to do that—and Google Sheets comes with one built in. Google’s free form tool, Google Forms, lets you create simple forms and share them with anyone. And it’s just a click away in Google Sheets.

Click Tools > Create a New Form in Google Sheets, and the Google Forms editor will open with a new form that’s automatically connected to your spreadsheet. Add the fields you want, share the form with your colleagues or followers, and the results will show up in your spreadsheet automatically.

While these add-ons work with Google Forms, they’re meant to be installed within Google Sheets to analyze and parse the data you collect from your forms.

Tip: Google Forms looks simple, but it’s packed with hidden features. Check out our ultimate guide to Google Forms for tips on navigating this helpful tool.

Once you have your form set up, there are extra add-ons to help you manage your results.

Copy Down

Best for: Adding functions to form responses

Formula responses are just plain text and numbers by default. To calculate values, reformat text, or use any other functions, you’ll need to add them in later. Or you can use Copy Down to automatically copy formulas to new rows.

Just add the formulas to a top row in the spreadsheet worksheet—row 2 by default—and turn on Copy Down. Then, as results get added to your spreadsheet, it’ll copy those formulas down to each new row and calculate values, evaluate results, and more automatically.

rowCall

Best for: Sorting entries into their own sheet

If you want to organize your form entries by the person who added them, the category of feedback they gave, or the location they visited, then rowCall is the add-on for you. It creates a new sheet for each unique type of response and sorts the data into the appropriate sheet automatically, so you never have to search through and filter form data manually again.

Want to use another form with your spreadsheet instead of Google Forms? Use Zapier to connect Google Sheets to hundreds of form apps. Here are a few examples.

Data gathering

Spreadsheets are a great way to sort through your survey results and financial data—but how do you get your data into the spreadsheet if you’re not using a form?

With many apps, you can export your data as a .csv file and open it in Google Sheets. Or you could pull in data automatically with these extensions.

Sheets Genie

Best for: Automating tasks within Google Sheets

If your work process is always the same, Sheets Genie is the best Google Sheet add-on that will save you both time and money. With Online Success Genie, you can scrape websites, and easily organize and process your data however you want with just one click. 

Cost: Online Success Genie has a free version and an upgraded paid version which charges depending on the number of rows you need to work with. Currently, working with 1000 rows at once is $44.70.

Wolfram|Alpha for Sheets

Best for: Finding data about the world around you

Wolfram|Alpha powers some of Siri’s best answers, answering data about population, finance, nutrition, and more. Add it to your spreadsheet and you can answer most questions in seconds.

Just install Wolfram|Alpha for Sheets, and type in the items you want to calculate. Then click the Compute Selection with Wolfram|Alpha option in the Add-ons menu, and after a few seconds, your text will be replaced with the answer. Or you can search the Wolfram|Alpha sidebar for an answer and insert the data or a graph directly into your spreadsheet.

Coupler.io

Best for: Exporting data from various apps on a set schedule

If you work on reports at a regular interval, Coupler.io can be a helpful tool that automatically exports data from popular software like QuickBooks, Shopify, and HubSpot into Google Sheets on a set schedule. You can choose how often you’d like to pull data from external tools, with options including hourly, daily, monthly, or on specific days of the week.

You can also do this using Schedule by Zapier with Google Sheets and the other apps you rely on for your business-critical workflows.

Supermetrics

Best for: Pulling and blending data from 90+ marketing and sales tools

Supermetrics is a powerful tool to import your business data into Google Sheets. It connects to analytics tools like Google Analytics and Moz, social networks including Facebook, Twitter, and YouTube, payment services like Stripe, along with ads services, databases, and more.

To import data, select the connection and query type, add filters to only watch for specific data, and then choose the fields you want added to your spreadsheet. Supermetrics will then pull in your data and can refresh it on a schedule to keep your spreadsheet up to date. The tool also offers a variety of pre-made templates to help you streamline your reporting process.

Cost: Free Google Analytics integration with up to 100 queries per month; from $99/month for one of the paid plans, which add more data sources and more frequent refresh schedules

Wikipedia and Wikidata Tools

Best for: Finding facts from Wikipedia

Speed up your research with the Wikipedia and Wikidata Tools add-on, which adds a bundle of new Wikipedia functions to your spreadsheet. Then, using functions like =WIKIDATAFACTS(), you can import info, translations, synonyms, categories, and more into your spreadsheet.

Related: Want to import info from RSS feeds? Check out RSS by Zapier.

Knoema DataFinder

Best for: Finding global statistics

Need detailed statistics on population, crime statistics, exports, land use, and other academic topics? Knoema Data Finder gives you a sidebar to search through and import data on these and dozens of other topics from governments and industry sources around the globe. Just search for what you’re looking for, click the result that looks closest, and Knoema will insert a table of the data into your spreadsheet.

Tweet Archiver

Best for: Saving tweets for any search keyword or hashtag

Tweet Archiver allows you to glean insights from Twitter without actually having to open the app. With the add-on, you can save tweets for any search query, keyword, or hashtag and the results will populate into a Google Sheet. Tweets are pulled every hour, and matching tweets are automatically pulled into your sheet.

Use this tool to track customer sentiment by adding queries and keywords associated with your brand, or save tweets from conferences and webinars.

Google Analytics

Best for: Analyzing your website data

For a simpler way to see just the data you need—and turn it into reports—the Google Analytics Sheets add-on can pull in any analytics data you want and save it to spreadsheet tables. You’ll need to manually refresh it when you want new data, but that’s perfect if you want to compare data over time and already do that in a spreadsheet on a regular basis.

Hunter

Best for: Finding and verifying email addresses

Want to email everyone at a specific company, or desperately searching for an address for that one person you need to contact? Hunter’s Sheets add-on can do that work right from a spreadsheet.

To use the tool, create an account with Hunter, and then install the add-on for Google Sheets. You’ll be prompted to add in your API code—found by logging into your account on Hunter’s site—and clicking on your name in the top-right corner. From there, click  </> API, and you can copy and paste your personal API key into the Google Sheets prompt.

To use the add-on, type in the website of the company you want to find email addresses for. Hunter will then fill your spreadsheet with every email it can find for that company, ranked by how confident it is that this email address is valid.

Cost: Free for 25 searches a month and 50 email verifications; starting from $49/month for paid plans that start at 500 searches and 1,000 email verifications

Text tools

Now that your spreadsheet is filled with data, what will you do with it? Numbers can be calculated and graphed, but text can be harder to manipulate in a spreadsheet. Here are some helpful tools to translate, analyze, and map your text in a sheet.

Translate My Sheet

Best for: Translating an entire spreadsheet

Translate My Sheet can translate foreign language data in seconds. Install the add-on, then either choose the source and target languages or have it auto-detect the source. It can then translate your whole sheet or a selection automatically, with the new text showing up in seconds.

Tip: Want to translate individual cells? Just use the built-in =GOOGLETRANSLATE() function to identify languages, translate to a specific language, or detect the language and then translate.

Geocode by Awesome Table

Best for: Finding location data from addresses

Need precise GPS coordinates for addresses in your spreadsheet? Geocode by Awesome Table is the tool for you.

Just run it on a spreadsheet containing addresses, and it’ll crunch their locations for you. If your address is spread across columns, you can tell Geocode which part of the address is in each column, and it’ll merge them into a full address. The add-on finds the latitude and longitude of each address, adds them to new columns, and offers to make a Google Maps map inside its companion app, Awesome Table. There, you can customize labels and sort through the addresses in a table.

Mapping Sheets

Best for: Creating custom maps

Mapping Sheets is a handy tool to make customized Google Maps maps that show general locations alongside specific addresses. Just enter location names and addresses—or their general area, perhaps a city or country name. Add categories or other filter data as well, if you’d like.

Mapping Sheets can then turn that into a Google Maps map, where you can filter down addresses based on location or categories from your spreadsheet. Those maps are built from .json files stored in an xsMapping folder in Google Drive. Make that folder public, then you can share the links Mapping Sheets gives you with anyone.

Autocrat

Best for: Turning your data into template documents

Autocrat allows you to turn your data into beautiful documents. All you need to do is add your data to a spreadsheet, then create your own Autocrat template document in Google Docs, with <<tags>> that reference columns in your spreadsheet. You can use any of Google Docs’ font and formatting options and even add image blocks.

Then, create an Autocrat job in your spreadsheet, make sure it’s mapped the spreadsheet fields to your document correctly, and set conditions to have it only run when specific columns are filled or have certain data in them. Choose whether you want the finished document saved as a Google Doc or PDF, and add email info to send when you finish the document.

Autocrat will then automatically turn your spreadsheet data into finished documents, either with the data you’ve already entered or as new data is added. It even adds a link to the finished document for each row in your spreadsheet, so you can re-open them again easily later.

ChangeCase

Best for: Changing the case of text in block sections

More often than not, the data you import won’t be perfectly formatted. Some entries may be entirely lowercase, while others are sentence case. Rather than manually updating each row, ChangeCase does the heavy lifting for you with the option to convert all characters to uppercase or lowercase, or adjust for title case capitalization.

Formatting

With all the new data in your spreadsheet, chances are things are looking a bit messy. These tools will help you get rid of blank rows and columns, find and replace text better than Sheets’ default tools, and turn your sheets into beautiful tables.

Power Tools

Best for: Formatting and cleaning up your spreadsheets

Search through Google Sheets’ add-ons, and one name will stand out in particular: AbleBits. They’re best known for their Excel, Outlook, and Word add-ons, and put the same focus and care into their Google Sheets add-ons.

If you just need a tool to clean up one thing in your spreadsheet—perhaps with better find and replace or to merge sheets and remove duplicates—you can use one of their individual add-ons for that.

Or just install Power Tools, their all-in-one add-on that lets you clean up data, reformat it, split or combine sheets and columns, customize formulas in bulk, and more. You can choose a new function each time, or run recent or favorite tools again for an easy way to do the same cleanups again.

Here are some of the add-ons that are built into Power Tools:

  • Merge Sheets to combine data from multiple sheets.

  • Merge Values to merge data in columns, rows, or ranges.

  • Remove Duplicates to remove or mark duplicates from multiple sheets.

  • Split Names to break name parts into multiple columns.

  • Random Generator to fill a spreadsheet with random passwords, dates, names, or other sample data.

  • Table Styles saves your company’s colors and formatting to apply to new spreadsheets.

  • Advanced Find and Replace to search through values, links, and notes, and save the results to a new table.

  • IF Formula Builder creates if-then formulas of any complexity.

  • Find Fuzzy Matches to find all items that are similar to your query.

Awesome Table 

Best for: Turning your data into visual tables

Sorting and filtering data in a spreadsheet is easy enough, especially with pivot tables, but it’s not exactly pretty. Awesome Table gives you a custom interface to browse your spreadsheet data in table, maps, cards, and Gantt chart visualizations.

You can customize the view, choose the number of rows and columns, and set other view options. Then you can share your Awesome Table design or use it to sort through and filter data with dropdowns and dynamic sliders. It’s like a custom app just for viewing your spreadsheet data.

Crop Sheet

Best for: Cropping sheets automatically

Want your spreadsheet to show only the data you need? Crop Sheet is the simplest way to clean it up. Just click Add-ons > Crop Sheet > Crop to Data, and Crop Sheet will remove all blank cells and rows, leaving just your data in a compact spreadsheet. Or you can have it crop to a selection to get rid of data you don’t need, too.

Icons for Slides & Docs

Best for: Accessing millions of free and customizable icons for your spreadsheets

Customize your spreadsheets with the millions of customizable icons available with Icons for Slides & Docs Sheets add-on. The tool lets you create multiple icon collections, customize their colors and sizes, and share them across your company to achieve consistent branding.

Lucidchart Diagrams

Best for: Turning spreadsheet data into collaborative diagrams

Have you ever wanted a simple, formattable diagram tool to build flowcharts and map data flows? The Lucidchart Diagrams add-on allows you to visualize complex data into one easy-to-read chart. This add-on is ideal for building org charts, wireframes, mockups, network diagrams, and more.

Number crunching

Spreadsheets are designed for math, and some of Google Sheets’ best add-ons make it better at statistics, finance, predictions, and more. These add-ons can help you make better graphs and figure out what your numbers mean.

Solver

Best for: Calculating and modeling your data

The Solver set of add-ons have been a mainstay of statistics and analytics work for years, typically with their Excel add-ons. Each of those tools are available for Google Sheets, with XLMiner for statistical analysis, Solver for linear programming and solution optimization, and Risk Solver to perform risk analysis with Monte Carlo simulations.

Just install the add-ons, tell it how to model your data, and Solver will run the calculations on their servers and send the data back to your Google Sheets spreadsheet. Most calculations will run in the free add-on, with up to 1k Monte Carlo trials, and you can contact their team for paid options beyond that.

BigML

Best for: Creating predictions from data

BigML brings machine learning to your spreadsheet, letting you fill gaps in your data with predicted text from your broader datasets. You’ll first add your data models to BigML, then install the add-on for Google Sheets and connect it with your API key.

Then run predictions on your spreadsheet data based on that model, and it can predict missing values and a confidence value. It’s a powerful way to fill in scores, categories, and other broad data based on similarities in your data sets.

Cost: Free for two parallel tasks; from $30/month for paid plans with unlimited tasks starting at 64 MB each

Sharing and publishing

Now that you’ve gathered data, organized and formatted it, and crunched your numbers, it’s time to share your spreadsheets with the world. You could just save it as a PDF or hit Share, or you could turn it into an app or add the data to your site with these tools.

AppSheet

Best for: Turning your spreadsheet into an app

AppSheet makes building a new mobile app as simple as adding your data to a spreadsheet. Just list all of your data—contacts, products, inventory, property, or anything else you need to track in an app—to a spreadsheet, and connect it to AppSheet. You can also choose from a variety of app templates and then tweak them to suit your company’s needs.

There, you can customize the displayed columns, pull in images from Dropbox and other file storage apps, chart locations on maps, and tweak its design to fit your branding. You can even add forms to gather text, images, and signatures on the go for a full mobile data collection tool built around your spreadsheet.

Cost: Free to develop apps and share with up to 10 users; once published, from $5/month per user for paid plans for unlimited apps and core features

Export Sheet Data

Best for: Turning your sheet into XML or JSON data

Export Sheet Data allows you to import spreadsheet data into an app that doesn’t support .csv files. It’ll turn your data into XML or JSON files for a simple way to create structured data without having to hand-edit code.

This comes in handy if you want to import your spreadsheet to your wiki in JSON format or need to import your data into a database that doesn’t support .csv.

Email and communication

Spreadsheets are also a great tool for sending personalized messages to an entire email list or managing your outreach. Google Sheets can handle your mass email needs with these add-ons.

Yet Another Mail Merge

Best for: Importing contacts and sending template messages

Yet Another Mail Merge is a one-stop shop to send emails based on templates to your contacts. It can first import contacts from Google Contacts or Salesforce into your spreadsheet. Then you’ll need an email template in Gmail, which you can either make from one of Yet Another Mail Merge’s pre-made graphical templates, or you can hand-design using <<fields>> to merge in names and other info.

Save that template as a draft email in Gmail, run Yet Another Mail Merge, and it can send personalized emails to everyone on your spreadsheet. Have it send you a test message to make sure everything looks perfect, schedule message sending for later, and track opens and clicks in one place.

Cost: Free for 50 emails per day; starting from $25/year for paid plans, that add scheduled delivery and sending up to 1,500 emails per day

Mail Merge with Attachments

Best for: Scheduling emails with attachments in a spreadsheet

Mail Merge with Attachments lets you send any file in Google Drive as an email attachment from within your spreadsheet. Import your Google Contacts—or list your own emails—then select a file from Google Drive that you want to send to the list. You can even select different files for each recipient if you want.

Then, write a template plain text or HTML email—or choose an existing draft message in Gmail—and Mail Merge will send the messages along with your file attachments. It even includes a companion HTML editor to help you create an email template, if you’d like.

Form Mule

Best for: Sending conditional emails

Need to vary your email for different types of recipients? Form Mule is the email add-on you need. You can add up to 15 template emails with conditions for which one to send—perhaps to send a different email to those who’ve purchased different products from your company.

Templates can be HTML or plain text and can include any of your spreadsheet fields for personalization. There’s even a language field to automatically translate the email if you want, and a scheduler to send emails automatically when a form is filled out or on a timed schedule.

As a bonus, it also adds new =RANGETOTABLE() functions to Google Sheets, which can turn your spreadsheet data into an HTML table in a click. That’s much simpler than merging cells and hand-coding HTML tables from your spreadsheets.

Build your own Google Sheets add-ons with Zapier

You can accomplish what many of the add-on functions we outlined above do with Zapier, a no-code automation platform that connects all your apps. So if you don’t find the add-on you’re looking for, take a look at Zapier’s Google Sheets integrations for more possibilities. Here are a few examples to get you started.

You can also create custom Google Sheets automations between your favorite apps and services—without any code.

Related reading:

This article was originally published by Matthew Guay in July 2016. The most recent update was in December 2022.

Source link

[adsanity_group align=’alignnone’ num_ads=1 num_columns=1 group_ids=’15192′]

Need Any Technology Assistance? Call Pursho @ 0731-6725516