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. Show
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:
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. IMPORTRANGEThe 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
Disadvantages of IMPORTRANGE
2. Sheetgo add-onThe 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
Disadvantages of the Sheetgo add-on
How to transfer data and formats between SheetsLet’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 formulaCopy source spreadsheet URLFirst, 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 spreadsheetOpen 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(“https://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-on1. Install the Sheetgo add-onSimply click the blue button below to install the Sheetgo add-on directly from the Google Workspace Marketplace. Sheetgo add-onAlternatively, 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-onNow 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 formattingUnder 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 fileNow 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 updatesSheetgo 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 SheetsAnd 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?Get data from other sheets in your spreadsheet
Select a cell. Type = followed by the sheet name, an exclamation point, and the cell being copied. For example, =Sheet1! A1 or ='Sheet number two'!
How do I automatically import data into Google Sheets?In a Google Sheet, select Add-ons from the main menu, then Supermetrics > Launch. Select a data source from the list of available sources and authorize it to share data with Supermetrics. Build a query using the options available and then click Get Data to Table.
How do I link data from one spreadsheet to another?Type = (equal sign). Switch to the source workbook, and then click the worksheet that contains the cells that you want to link. Press F3, select the name that you want to link to and press Enter.
|