Contents
Productivity

How to automate Google Sheets

Reading time

When you think of fun web apps, Google Sheets probably doesn’t sit at the top of the list. But it’s great for simple data manipulation, stats visualization, and data organization. When your data is organized, for work and life, it makes everything easier. Unfortunately, getting it to that point can take up a lot of time (endless copy-pasting!) which is better spent elsewhere.

Many of us might’ve dipped our toes into automating mundane tasks. But more often than not, that might’ve led to further difficulty. Fortunately, using macros, scripts, Bardeen, and other tools, you can make automation easier and quicker. You’ll learn all about that in this article!

Automate Google Sheets using macros

Whenever you ask someone how to automate tasks in a spreadsheet, ‘macros’ will be the first answer you’ll get. A macro can be very powerful, allowing you to record UI interactions and then replay them to automate repetitive tasks. Macros can input text into a cell, add complex formulas, change the formatting of the sheet, and much more. Lots of possibilities!

How do you create and use macros? Let's go through the steps. First, open a Google Sheets spreadsheet. At the top toolbar, click Extensions -> Macros -> Record macro.

Now, you’ll have to choose between the two types of cell references: Absolute and Relative.

What’s the difference between the two? Relative references can change when you copy a formula to a different cell. Whereas, as the name suggests, absolute references stay rooted to the exact cell you record. Google Sheets selects absolute references by default when recording a macro.

Once you’ve made up your mind about which reference type to use, go ahead and perform the series of actions you want to record. Click ‘Save’ when you’re done.

In the ‘Save new macro’ box, you can name the macro, or create a custom keyboard shortcut for it! You can also put it into action by clicking Extensions, Macros, and then the macro name.

What if you want that macro to be triggered automatically? That’s possible as well. Click on Extensions and Apps Script. On the left-hand panel, click on Triggers. On this new page, click on Add Trigger.

You get many options regarding the type of trigger for the macros, like a specific date, time, and frequency. After you’re done setting up the trigger, click on Save.

Behind every macro you create is a bunch of JavaScript code. This code, saved in a file named ‘macros.gs’, is attached to the Google Sheets spreadsheet it was created on. If you know your way around JavaScript and wish to modify this code yourself, click on Extensions and Apps Script.

As you might’ve picked up by now, the main limiting factor of trying to automate Google Sheets using macros is that any macro you record is only usable on that particular Google Sheet. Plus, not everyone knows JavaScript!

If you want a simpler UI to work with and no code to deal with, a workflow automation tool will feel like a breath of fresh air. There are many options, like Zapier, Make, and Bardeen. In this article, we’ll focus on Bardeen.

Top 5 Google Sheet no code automations

A no-code workflow automation tool, Bardeen is integrated with Google Sheets (along with many other web apps) and has a built-in web scraper! This makes it optimal for collecting data and automating Google Sheets.

In order to use the Bardeen automations, you’ll need to install it in the Chrome browser. Don’t worry, it operates locally and keeps your data safe. Plus, it’s free to get started!

Downloaded it? Now, let’s go ahead and test out some exciting automations!

1. Copy LinkedIn company data to Google Sheets

When looking for new sales prospects, hiring for an opening, or researching market or competitors, there’s nothing quite like LinkedIn. It has an extensive database of companies and connections that you can capitalize on. But the same can’t be said about organizing those connections in Google Sheets. Yes, it can be a chore.

With this automation, you can copy LinkedIn company data to a Google Sheet at scale automatically, and keep the data well organized. Similar automations are available for copying job posts and profile data as well.

If you want to collect profile data from social media platforms, we also have automations to do that using Instagram, Twitter, and Facebook links. Along with that, some other examples of social media automations are saving following/followers from Instagram, saving Facebook group members, and copying a tweet to Google Sheets.

Are you an Upwork user? No worries! You can instead use this automation to copy Upwork job data to a Google Sheet. We’ve also kept in mind Zillow. There is a similar collection of automations available for saving Zillow Listings, Agents, and Properties.

So, you can bid goodbye to the days of endless copy-pasting! With these automations, you can instantly copy relevant data to a Google Sheet and then move on to the next task.

2. Copy a list of meetings during a timeframe to a Google Sheet

Managing meetings is always hard. If you have many of them to go through and want to copy them over from Google Calendar to Google Sheets, this automation has you covered.

With this automation, you can create a Google Sheets spreadsheet that will consolidate all Google Calendar meetings which include a meeting link. In order to activate this automation, you need to input a time range wherein you want to copy the meetings. After that, the meetings will be copied over to a Google Sheet. This can help you stay organized and avoid any delays.

Copy a list of meetings during a timeframe to a Google Sheet

3. Enrich TikTok links and save them to Google Sheets

Our favorite automations are those that enrich links. Basically, you give them a list of links and get a neat spreadsheet with relevant data! How cool is that?!

Although this automation is for TikTok, we also have similar ones available for Instagram and Facebook pages. They work in just the same way: input a list of links, run it, and specify a Google Sheet to add the extracted data. It’ll include data like page name, description, bio, and more!

This can come in handy when you’re creating a list of influencers or just trying to find some people online. Having a bland list of links will confuse you and won’t get you very far. Getting their relevant data neatly organized in a spreadsheet is the way to go, and this automation makes that possible without too much copy-pasting!

4. Copy ClickUp tasks to Google Sheets

Other than copying profile data from social media platforms to Google Sheets, copy-pasting tasks might just be the most mundane task ever. Fortunately, it’s possible to copy your tasks on other platforms to Google Sheets with a click!

This automation works for ClickUp. For it to work, you’ll first need to integrate Bardeen with ClickUp (as you did with Google Sheets), select the relevant ClickUp list, and the Google Sheet you’d like to copy those tasks over to. There are similar automations for GitHub, Asana, Jira, and Pipedrive. So, feel free to go with the one you need!

5. Get Google search results for a keyword and save them to Google Sheets

Google Sheets can also serve as a great tool for researching and SEO. Is there a certain query that you want to rank for or that you’re researching? If yes, this automation will save you from a ton of copy-pasting. With only a few clicks, it’ll directly scrape the Google search results for that query and then save them to a Google Sheet.

There’s a similar automation for copying Tweets related to a keyword as well! So, those were five great Google Sheet automations. There are many more automations available for Google Sheets with the Bardeen extension. Feel free to browse through the list here!

Conclusion

Like many people, if you rely on Google Sheets as your central platform to collect and store data, automating it presents a great opportunity for saving time and being more efficient.

Automations can be created in many ways, using both built-in and third-party tools. It depends on your requirements and technical expertise. If you only want to automate simple tasks related to spreadsheet formatting and data placement, macros might be sufficient. To go beyond that and integrate Sheets with other web apps, you can try out a workflow automation tool like Bardeen.

If you haven’t tried Bardeen yet, download it and try out a pre-built automation.

If you’ve recently switched from Notion to Google Sheets, you can skip hours of copy-pasting by following this article.

About the author:
Renat Gabitov
Product & Marketing
Building the future of automation.
Little Rocket Ship

Launch your productivity boost today.

By clicking “Accept All Cookies”, you agree to the storing of cookies. View our Privacy Policy for more information.
Help us win the Golden Kitty Award
Vote for Bardeen