How do i automatically insert data from another sheet in google sheets?

Google Sheets is a fantastic tool to collate and manage different types of data in one place. However, the process of moving all of this data can be a daunting one. Manually transporting large datasets from one Google Sheets file to another can be an extremely time-consuming process, often leaving you with inaccurate data thanks to human error. So, is there any way to automatically transfer data between Google Sheets? And is there any way to do this whilst preserving the original formatting of the source? The answer is yes.

There are many instances where you may need to automatically transfer data between different Sheets whilst preserving its format. Let’s say I am a marketing manager transferring various datasets from multiple sources to a Google Sheets file. This file will contain all the data I need to create a comprehensive end-of-year report. I need to preserve the formatting for my insights using dates, percentages, etc. to maintain accurate results. But I also need to preserve the cell formatting, which highlights some of the most important data that will be integral to my report. I need to find a way to automatically transfer data that will save me time, without sacrificing the formatting from the original data.

Let’s explore the different ways you can transfer both data and formats automatically between Sheets, and how to use them in your own operations.

Why automatically transfer data and formats between Google Sheets?

There are many advantages of automatically transferring data and formats between Google Sheets.

Here are just some of the advantages:

• Save time: Automatic transferral dramatically reduces the amount of time and manual effort required, allowing you to spend more time on more important tasks.
• Avoid human error: With no manual copy and pasting of data, there is no risk of any human error.
• More accurate data: By maintaining the important formatting of your data, you avoid the likelihood of inaccurate data [for example, losing the currency format may change the way your numbers are presented].
• Read and interpret data easily: By preserving the visual formatting, you can easily navigate to and interpret the particularly vital parts of your data.

Now that we understand the importance of transferring data and its original formatting to new Sheets, let’s take a look at the two ways you can achieve this.

What are the ways to automatically transfer data?

There are two main ways that you can automatically transfer your data along with formatting between different Google Sheets files.

1. IMPORTRANGE

The first is using the IMPORTRANGE formula in Google Sheets. This uses a simple formula that allows you to import the data within your entire spreadsheet from one Google Sheets file to another. Let’s take a look at its advantages and disadvantages:

Advantages of IMPORTRANGE

• Straightforward formula: Unlike some Google Sheets formulas, the IMPORTRANGE has a simple syntax that any Google Sheets user can easily understand.
• Choose your exact data range: Part of the IMPORTRANGE syntax allows you to choose the specific cell range of your data that you wish to transfer, offering more flexibility.
• Automatic updates of your transferred data: IMPORTRANGE will update the transferred data if your source data changes.

Disadvantages of IMPORTRANGE

• Does not preserve visual formatting: Although IMPORTANGE does preserve data formatting [dates, currency, time, etc], it doesn’t preserve the visual formatting of your data. This means that you will lose any font weight, font size, cell color, etc. from your transferred data.
• Delayed updates you have to manage manually: If you’re transferring large amounts of data using IMPORTRANGE, it will often take a long time for the formula to update your files. What’s more, there is no way to check whether you have the updated data version. You must go through all of your spreadsheets manually to compare the new file to the source file. This can lead to a time-consuming management process and an enormous risk of having inaccurate data.

2. Sheetgo add-on

The second option is to use the Sheetgo add-on to transfer your data whilst preserving your formatting. The Sheetgo add-on for Google Sheets creates an automated workflow that will transfer your data from one sheet to another.

Let’s take a look at the advantages and disadvantages of this option:

Advantages of the Sheetgo add-on

• User-friendly, intuitive tool: Although the IMPORTRANGE formula is relatively straightforward, the Sheetgo add-on offers an intuitive process that many users will prefer to use.
• Preserve date AND visual formatting: Unlike IMPORTRANGE, the Sheetgo add-on will preserve all of your formatting when you transfer your data.
• Receive and schedule automatic updates: Sheetgo will not only automatically update any transferred data that has been updated in the source files, but it also offers you the ability to schedule custom updates to suit your needs. This means you will always know that you have the latest data.

Disadvantages of the Sheetgo add-on

• Copy formatting limitations in some circumstances: Although the Sheetgo add-on preserves all types of formatting for your data, the formatting ability can change. For example, if you use any of the Filter options, or the Consolidate or Distribute features, formatting will not be applied. To find out more, take a look at the support page on How do I transfer formatting?

How to transfer data and formats between Sheets

Let’s use the example mentioned above as our use case to demonstrate how to transfer data and formats between Sheets using the IMPORTRANGE formula and the Sheetgo add-on.

Here is just one of the multiple Google Sheets containing marketing data that I want to transfer into one large Google Sheets file:

As you can see, certain columns and rows of this spreadsheet contain either data or visual formatting, or both.

Using our two options, let’s see how we can transfer this data quickly whilst maintaining the formatting.

Option 1: Transfer data using the IMPORTRANGE formula

Copy source spreadsheet URL

First, head to your source spreadsheet containing the data you want to transfer. Select and copy the URL, ready to add to your IMPORTRANGE formula.

Add formula to your destination spreadsheet

Open your destination spreadsheet. Open the tab where you want your data to appear. Click on the cell where you wish your data to be located, and enter the IMPORTRANGE formula, inserting the URL you copied where needed. You can also choose to specify the data range within the formula.

Click here for more information on how to input your IMPORTRANGE formula.

In this example, my IMPORTRANGE formula will look like this:

=IMPORTRANGE[“//docs.google.com/spreadsheets/d/1v89dDZf0gvHXbe4wJUnUxwIEtYhTyxMtecpy0RnwVNU/edit#gid=0″,”Blog!A1:D13”]

Before you can receive your data, you may encounter the following alert if this is your first time using the IMPORTRANGE formula.

Simply press Allow access.

Your transferred data will look something like this:

As you can see, the IMPORTRANGE formula has successfully transferred the data as well as the data formatting, however, I have lost the visual formatting of my data.

Let’s move on to the Sheetgo add-on to see how we can preserve all formatting when transferring data between Google Sheets.

OPTION 2: Automatically transfer data using the Sheetgo add-on

1. Install the Sheetgo add-on

Simply click the blue button below to install the Sheetgo add-on directly from the Google Workspace Marketplace.

Sheetgo add-on

Alternatively, get Sheetgo from directly inside your spreadsheet. In the Google Sheets file containing your data, head to Add-ons > Get add-ons and search for Sheetgo. Click on the Sheetgo add-on result and click Install.

2. Open the Sheetgo add-on

Now that you have the Sheetgo add-on installed, it’s time to open it. Navigate to Add-ons > Sheetgo > Start. The add-on should open up on the right-hand side of your screen.

3. Export data and activate transfer formatting

Under the Export data section, click Select data.

Choose the tab within your Google Sheets where the data you want to transfer is located.

Then expand the Settings.

Under the Transfer formatting section, you have two buttons – one to transfer all number formatting, and one to transfer all visual formatting. Slide the two buttons over to activate them and transfer all formatting.

Once finished, press Continue.

4. Send data to another Google Sheets file

Now it’s time to select the file you wish to transfer your data to. Make sure that you choose the Google Sheets option. Otherwise, the transfer formatting won’t work.

The Sheetgo add-on allows you to either choose to create a new Google Sheets file for your data transfer or to use an existing Sheet.

For this example, I will choose to send my data to an existing Google Sheets file named ‘Marketing overview’. You can also edit the name of the tab to which your data will be added.

Once finished, click Finish and save.

Now that the workflow has been completed, the data has successfully transferred to my other spreadsheet, preserving all of its original formattings.

5. Schedule automatic updates

Sheetgo offers the ability to schedule automatic updates for your data transfer so that if you ever change the data in the original Google Sheets file, your new file will be automatically updated accordingly.

Head back to the source Sheet. Click on the Automate button located in the workflow overview.

Edit the schedule settings to suit your needs. Sheetgo allows you to alter the days and hours of your automatic updates, with a maximum update frequency of 15 minutes!

Once you’re happy with your schedule, click Save.

Transfer data and preserve formatting in Google Sheets

And there you have it! Although the IMPORTRANGE formula is a good option to transfer data automatically, the Sheetgo web app allows you to maintain all the formatting of your data.

Want to make the most of formatting your data in Google Sheets? Why not discover our comprehensive Google Sheets data format guide?

For more how-to’s on using the Sheetgo add-on, take a look at the related posts below!

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

How do I auto pull data from another sheet in Google Sheets?

How do I automatically import data into Google Sheets?

How do I link data from one spreadsheet to another?