How to connect Google Sheets automatically

Written by
Laura Tennyson

Want to connect Google Sheets?

Did you know that you can transfer data from one Google Sheet to another, automatically?

Google has already revolutionized the way we work with spreadsheets. In the past, spreadsheets were closed, offline files — and many people still use Excel in that way. Changes must be saved, new file versions have to be sent as attachments and if the person who owns the spreadsheet is out of office … you probably have to wait for them to return! Google Sheets changed all that. As an online, cloud-based platform, data is updated in real-time and anyone can access any file, from anywhere.

G Suite’s collaboration functions mean that multiple people can work on the same spreadsheet at the same time. This is a really helpful option for teams, but sometimes you don’t want too many “hands” touching the same spreadsheet. More collaborators means a higher chance of manual mistakes. In addition, one spreadsheet can simply become too large with multiple inputs, making it impossible to analyze data effectively.

You probably don’t want to grant access to everyone in the company — but you may want everyone to be able to view some of the same data.

A solution to all of these problems is to connect Google Sheets in an automated system.

Connect Google Sheets to create an automated workflow

You can import and export data between spreadsheets in 1 click and create a completely automated workflow for almost every operational or data process. Moreover, this workflow can be connected to various data inputs and outputs including databases, Excel or CSV files, Google Forms, PDFs and Google Data Studio.

  • Watch data move between spreadsheets automatically — no more copy-pasting, emails or calls.
  • Control data chaos by giving 1 spreadsheet 1 role.
  • Lighter, cleaner spreadsheets.
  • Get reliable, up-to-date data where you need it, when you want it.
  • Better privacy and access control.

How to Connect Google Sheets

Imagine the following scenario: you run a marketing department and manage many different marketing channels. Inside one spreadsheet, you keep track of your email marketing metrics, another for content marketing, one for SEM and so on.

You want to collate all channels’ metrics into one, centralized spreadsheet where you can analyze the performance of all your marketing channels in a dashboard.

On top of that, you work with a large marketing team, so you can share each spreadsheet with the colleague(s) responsible for that task. They simply have to keep their own spreadsheets updated and Sheetgo connections will pull the data automatically into your “master” sheet. Once you have created a connection it will run automatically — just schedule how frequently you want the data to be updated (e.g. hourly, daily, weekly).

Finally, whenever you need to check a metric or produce a report, you just consult your automated dashboard:

Here’s how to start connecting your Google Sheets:

Step 1:

Step 2:

  • Open your master spreadsheet (this is where you want to gather all your data and create the dashboard). Open Sheetgo by going to the menu Add-ons > Sheetgo > Start.
  • Sheetgo opens as a sidebar on the right-hand side of your screen. Hover over the green + sign and select Import. Now, select your data source (the spreadsheet(s) where you want to import data from).
  • Underneath the name of the source spreadsheet, you will see the tab (Sheet) name. Select your source tab (which tab/sheet you want to import data from).

Step 3:

  • Scroll down to Settings, and give your connection an appropriate name. In this example, we will import data from 2 different spreadsheets (email marketing and content marketing) in a “consolidate” connection. Here we will name the connection “Consolidated Tracking”.
  • Under Settings you will see that Automatic updates are enabled by default. Under Frequency, select how often you want automatic updates to run (hourly, daily, weekly, or monthly).
  • Finally, click Save connection and the data from these two spreadsheets, containing your email marketing and content marketing metrics, is merged into your destination (master) spreadsheet.
  • You will see a new tab appears in your master sheet.

Now you’ve created a connection, Sheetgo automatically imports data from the source spreadsheets into your master sheet.

As a result, your data is now centralized, ready for you to create a dashboard:

Connection complete!

That’s how to connect Google Sheets automatically. We’ve shown you how to consolidate two marketing spreadsheets into one centralized master sheet. However, you can create an automated Sheetgo workflow for almost any business process, from inventory management to budgeting and sales tracking.

If you want some help and tips for creating a data dashboard, take a look at our blog post How to create a dashboard in Google Sheets.

You may also like…