Google Sheets is Google’s answer to Microsoft Excel, the leading spreadsheet software. Sheets is an online based spreadsheet tool, which offers much of the same functionality as Excel and also has an add-on gallery, one of the features that has made Excel so popular over the years. With Google Sheets, you can create professional client reports in a matter of seconds, extract website data, and even schedule your emails.
One of the best things about Google Drive is the collaboration feature, which makes it perfect for small teams, large teams, agencies, onsite workers, offsite workers, clients, and any other collaborators working on the same document. We use Google Drive here at 90 Digital and utilise this collaboration feature to the max. We are all able to work on the same document at the same time, viewing live changes, adding comments to each other, and suggesting edits where necessary.
However, one limitation in Sheets is the ability to group data – this is a major feature in Excel and is vital for some when it comes to reporting and organising data. Unfortunately, this was requested a long time ago and doesn’t seem to be appearing any time soon. But don’t fret, the add-on library offers a vast number of options you can use to spruce up Google Sheets and forget about ever needing the ability to group data. Outlined below are some of the most useful add-ons you can use to make client reporting, data collection, email scheduling, and online research a breeze.
WHERE TO FIND ADD-ONS AND HOW TO INSTALL THEM
A lot of software is so successful because of the ability to use add-ons and extensions; the extra features made available allow users to tailor the tool or service to make it do what they need. This also applies to the Google Suite, which was previously viewed as a simple version of Microsoft Office, with nowhere near as much power, but with the introduction of add-ons in 2014, a user can transform the tools into a customised, powerful machine.
To install an add-on in Google Sheets, just select add-ons in the menu and navigate to ‘Get add-ons…’, this will take you to the library where you can browse over 400 add-ons.
Once you have selected the add-on, you would like to install, simply click the ‘free’ button.
You will then find the add-on has been added to the menu. Clicking on it will allow you to work through the settings for the add-on and tailor it to work how you want it to.
You can also manage your add-ons within this menu, which will allow you to remove, rate, share, or find support for the add-on.
DATA COLLECTION HAS NEVER BEEN SIMPLER
Google Analytics: Analyse your website data and create customised reports (Free).
autoCrat: Take data from a spreadsheet and merge it into a beautiful document via a template (Free).
Blockspring: Automate your workflow by integrating with 100’s of other apps (from $10/month).
AppSheet: Create mobile apps, without any coding required (Free).
Supermetrics: Connect all of your SEO and social media data in one spreadsheet (Free).
Awesome Table: Create visual elements with your data, such as maps and Gantt charts (Free).
If you work with companies online, chances are you know what Google Analytics is, even if you’ve never used it. It is an incredibly powerful tool which allows you to analyse the data of a website, providing information about users, devices, conversions, revenue, and almost anything else you could possibly want to track about how visitors navigate around and use a website.
It’s also completely free. However, the interface can be quite overwhelming at times and confusing, to say the least. The best way around this is to turn your data into reports.
The Google Analytics add-on allows you to extract data from your analytics account and display the information in your spreadsheet. The customisation is limitless, and creating analytics reports via this method creates an easy way to compare data over time.
It saves a lot of time trawling through the Google Analytics interface adding dimensions everywhere to try and find the data you are looking for, and it is reliable. Once you’ve created your reports, you can just share your Google sheet with your team or client and voila!
If you don’t like looking at tables of numbers, autoCrat may be the perfect add-on for you. Once you have your data extracted from Google Analytics, autoCrat enables you to create templates using <<tags>> which reference the columns in your spreadsheet. These templates allow you to turn your tables of data into attractive documents and PDFs. autoCrat is easy to use and has many flexible options. It’s a real time saver if you like to send clients professional and visually pleasing reports each month.
Blockspring is a popular add-on for Sheets users who want to automate their workflow. It allows you to integrate your spreadsheet with 100’s of apps around the web, including SEMrush, Moz, LinkedIn, Facebook, YouTube, Twitter, Instagram, Slack, and Mailchimp.
By connecting these apps, users can automate their reports, operations, recruitment, and sales with ease, pulling in data from websites and social media automatically. This add-on can save hours of work as you select your service and create a schedule, then watch your spreadsheet update itself.
Appsheet is perfect for those who desire to build a mobile app for their company, but don’t know where to begin to code one. This add-on is completely free and allows you to create an app in a matter of minutes, straight from your spreadsheet. Even better, you can create an install link to add to your website, so no need to wait for App Store approval if you’re eager to get your app out to your users as soon as possible.
Appsheet is perfect for users who need to share data for a project, work with remote users, create educational systems (including grading, timetables, study guides, and group work), and even for building customer databases on your phone. Apps can be created so only certain information is visible to app users and the central data remains on the original Google Sheet, making it an ideal tool for project management with limited collaborators.
Like Blockspring, Supermetrics connects your Google Sheets with an array of other apps, very much focused on SEO and social media. The ability to integrate with the same number of apps is not as high as Blockspring, but the choice available is relevant, and the app is also free.
There is a pro version of the app which will unlock more features, but if you’re just testing out add-ons, then this may be the perfect starting option. The add-on is powerful, allowing users to create reports from automatically pulled data from social media, ad accounts, SEMrush, Google Analytics, and personal databases. Reports can then be exported in a number of formats.
Awesome Table is part of a huge suite of apps available to create different types of visualisations with your data, including maps, Gantt charts, and cards. The custom interface allows you to beautify your data into elements which can then be included in your client reports, making the information clear, easy to read, and professional.
CREATE EMAIL CAMPAIGNS WITH YOUR EYES CLOSED
Hunter: Find any email address within a company (Free).
Gmail Scheduler: Schedule your draft emails to be sent on a specific day and time (Free).
Yet Another Mail Merge: Send personalised emails in bulk (Free).
Mail Merge with Attachments: Schedule, send and track personalised emails in bulk and insert different attachments for each recipient (Free).
formMule: Send conditional emails in bulk (Free).
Hunter is a simple, yet powerful add-on. Once you have created an account, you have access to the Hunter sidebar. Enter any domain here, and Hunter will fill your Google Sheet with every email it can find at that company, ranked in order of certainty that the email address is valid. Hunter is free for 150 requests per month and offers paid plans for users who require more.
Plan your email schedule correctly with the Gmail Scheduler. With online businesses continuously expanding across the globe, it’s important to be aware of time zones, and the best way to do that is to send emails to recipients at the optimum time of day for them. This add-on works by pulling a list of your draft emails into a spreadsheet, select the email from a drop-down and then add schedule details and see each email status within the spreadsheet.
Yet Another Mail Merge
If you send email in bulk, then this add-on should be a priority. First, it can import your contacts either from Google or Salesforce, or you can use your own spreadsheet of contacts. Secondly, the add-on also requires a Gmail template, which you can make yourself or use one of the templates which come with Yet Another Mail Merge. Finally, all that’s left to do is send your emails.
The add-on will use the template tags to input the data from your spreadsheet, ensuring each email is personalised. You can even schedule the emails to be sent later and track opens and clicks. Yet Another Mail Merge is free for up to 50 emails a day, with paid plans available for users who need to send more.
Mail Merge with Attachments
Need even more for you email? Mail Merge with Attachments brings more features than Gmail Scheduler and Yet Another Mail Merge (YAMM), combined. As with YAMM, the add-on can import a list of contacts from Google or your own list, and it’s then easy to schedule emails for a particular date or time and send personalised email in bulk.
The main feature in this add-on is the ability to also assign Google Drive attachments to these emails, each of them with a different file if required. The emails can then be sent in one swift motion, as though you spent hours at your keyboard writing each individual email to your contacts – you can also track opens and clicks, as with YAMM.
Mail Merge with Attachments is free for up to 50 emails a day, with paid plans for those who need more. It is a more complex tool, so if not required, stick with YAMM, or Gmail Scheduler if you just need to organise your email scheduling.
formMule is the type of add-on you didn’t know you needed until you had it and then can’t understand how you have worked without it all of these years. It’s a flexible email scheduler, which can store up to 15 email templates, where you can add conditions for each one before sending.
Let’s say that one group of contacts on your list purchased product A, another group purchased product B, and another group has yet to purchase anything; you can schedule personalised emails in bulk from just your contact list, like the add-ons above, but you can assign which template you use per group.
The conditions you can apply are endless, you can also request the email to be automatically translated for individual contacts, and you can easily transform the data into tables, without any effort. formMule is also the perfect scheduling tool for those who do not use Gmail within their work network since it doesn’t rely on Gmail as the other plug-ins do.
CREATING REPORTS FROM YOUR FORMS IS A PIECE OF CAKE
Google Forms: Create customised forms and track results (Free).
Advanced Summary: Turn form results into charts and word clouds automatically (Free).
copyDown: Automatically add formulas to form results as they come in (Free).
rowCall: Sort form results by responses (Free).
Google Forms is built into Sheets and can be accessed from the Tools menu. A form is the simplest way to add contact information, collect customer feedback, and conduct market research. Forms are extremely flexible with many customisation options.
Create whichever fields you want and amend the images, backgrounds, colours, and fonts. By setting up a form from the Sheets menu, the spreadsheet will automatically collect all of the results of the forms submitted, meaning you can analyse the data immediately and start creating your reports as soon as you have enough data.
Advanced Summary is actually a Google Forms add-on, and can be found here. If you have all of your data ready but aren’t sure what to do next to turn it into a report for your team or your client, simple, use Advanced Summary. Run the add-on on your results sheet and the tool will automatically create charts, word clouds, and other visually appealing elements for your report. It can even be downloaded as a PDF once you’re done.
Adding formulas to your form results would typically be done once you have all of the data. However, copyDown can automatically do this for you as the results come in. Just create the formula you want in the top row and turn copyDown on, the add-on will then add the formula to each response as it comes in, so not only will you have a sheet of data, you could already have calculations and evaluations waiting for you.
rowCall is the solution to endless filtering of data. It can sort your form results by creating a new sheet for each answer, so you can easily see the results sorted by each individual response.
SAVE HOURS ON YOUR ONLINE RESEARCH
Citable: Create a detailed bibliography without having to leave the page you’re on (Free).
Twitter Archiver: Search Twitter for any keyword or hashtag (Free).
Citable is a Chrome extension, which can be found here. It allows you to send your bookmarks to Sheets, making research a doddle. When you find something useful, highlighting it and clicking the Citable tool will send the text, link, site, and author info across to your spreadsheet. You can add tags, where necessary, and you can also export the data to a variety of citation apps to create a bibliography for your research report.
This add-on is like Google Alerts for Twitter. Add any topic, user tag, or hashtag you would like to collect research on, and the Twitter Archiver will scan Twitter’s history every hour, adding any relevant data to your spreadsheet. If you need a more regular scan, you can pay to upgrade the tool and have a scan every 10-15 minutes.
ADD EXTRA FUNCTIONALITY AND STYLE IN SECONDS
Having the most comprehensive data in the world is great, but if you can’t create professional looking, visually attractive, and clean reports for your clients, why bother? These add-ons are where you can take your reports to the next level on Google Sheets, with the click of a few buttons.
Power Tools: Cleanup and format spreadsheets with this multi-purpose tool (Free).
Table Styles: Create custom style templates (Free).
Add Rows & Columns: Add rows with ease – no more adding just one at a time (Free).
Merge Sheets: Merge duplicate data across multiple sheets to have the most up to date values (Free).
Data Everywhere: Collaborate with non-Google Sheet users on the same spreadsheet (Free).
If you use just one style add-on, make it this one. Power Tools does everything you could want it to when it comes to formatting and cleaning up your spreadsheets. This add-on can clean up formulas, split or merge sheets, columns, or rows, reformat data, remove duplicates across sheets, fill sheets with random sample data, and tidy everything up. It also has an advanced find and replace function, and a fuzzy match search element, saving hours of trawling through data.
Add Rows & Columns
Take out the tedious task of right clicking and inserting one row above or below with this simple side menu. The add-on offers you a one-click solution to add rows from your current cell and state how many – time and frustration saver!
When you have several tools collecting data at the same time, you may find you have the same data across a number of sheets. Merge sheets can scan two sheets for duplicate values and update your master sheet with any updated information. This is perfect for those who want to keep a data record over time but keep a current track of figures.
Finally, the one add-on which will allow you to keep everything as you have set it with your new found tools. Data Elsewhere ensures that users can collaborate on the same spreadsheet, even if one member of the team still uses Excel, instead of Sheets. Any type of database can connect and it means that the whole team can view the formatted data and a full history of changes, no matter which software they are using.
TIME TO POWER UP YOUR SPREADSHEETS
Spreadsheets are such a powerful tool, and sometimes absolutely necessary for the task at hand. Now, you can have the most powerful, time efficient, spreadsheets ever and they can do exactly what you want them to do.
However, before you go setting up your perfectly tailored spreadsheet, there is one kryptonite to these superpowers, and that’s the ability to work offline. As a web app, you can only access your spreadsheets online, unless you install the Chrome extension to enable you to work in offline mode, but even then, add-ons will remain disabled until you’re back online.
If among the ever-growing library of add-ons you have not found one to do what you would like it to, you can always build your own with some custom code. There are lots of useful guides online to help you through that process, should you choose that option.