Merging data is often a task handed off or outsourced to third party consultants. This is due to the fact that it can be tedious and time-consuming, but there are ways to avoid merging data one item at a time, and that is by using the Google Spreadsheets Key. 

By leveraging the Google Sheets Key with query functions (and more specifically, by combining the query function with the IMPORTRANGE feature) you can save time and resources at your small business.

First, focus on the Query function so that you will be able to consolidate data from multiple sheets using a formula. It is very simple to learn and once you have the formulas written down you can customize them to your liking.

What is a Google Spreadsheet Key?

Essentially this is a formula that makes you able to import data in a range of cells from one or more Google Sheets into one. The key identifies which Spreadsheets you are importing the data from. This technique of merging data from multiple spreadsheets can help you avoid putting in large amounts of work, manually migrating data between Google Sheets.

How to Find Your Google Spreadsheet Key

There are some keys that are specific to actions, but each individual spreadsheet has its own Google Sheet key.

This can be found in your published Google sheets URL. To find this simply select the string of data found between the slashes after “spreadsheets/d” in your Google Sheet URL:

 

Why use the Query Function to Consolidate Data?

There are many benefits to using the query function as well as benefits to consolidating your data:

• Data consolidation without using a script means that your Spreadsheet will function with better performance.
• If you happen to try and use a plugin for consolidating data, then you will not always be able to ensure that the plugin will be available. Oftentimes developers remove plugins, and there can also be security risks involved with this.
• When you use a formula to consolidate your data, you get a large amount of flexibility. Additionally, this consolidated data will be automatically updated whenever the source data changes.
• It is extremely fast and you only need to set it up one time. 

What is the Formula?

The base formula to utilize the Google Spreadsheets key is:

Breaking Down the Formula

Range_string: Use to reference a specific cell or group of cells in your sheet

An example used to show the range of cells to import: Sheet1!B3 (this would import the data in cell B3 from Sheet 1).

There is also the option to use a formula based on tab names: My tab name!B3 (this would import the data in cell B3 from "My tab name").

This option is not necessary, and as a default the IMPORTANGE formula, the Spreadsheet will import the range from the first available sheet.

IMPORTRANGE Example (Formula You Can Try)

• Using Spreadsheet Key: 1o62M-deXGSI32Fdn9WlGDIR7uMFkBMSu9xb1nKR7KE4
• Sheet name: Sheet 2
• Cell to Pickup: A20
• You should then follow the formula that is read as: 

*Note: Make sure to place both variables in Quotes " "

• Final formula to test after adding your information: 

And this is what you should see when you enter the above formula in your Google Sheet:

How to Combine Data Using a Formula in Google Sheets (Formula You Can Try)

• Start with four sheets. Let’s say you want to combine two sheet tabs that contain data and place them into the third sheet tab but summarize them in the fourth sheet tab.
• The first sheet is titled: “janjunefunds”, the second: “julydecfunds”. The third sheet is titled: “combined”, while the fourth sheet is called: “consolidated.”
• Use query formula: 

• Note: input your data range in place of “A2:H5.”
• If you wanted to add an additional sheet, simply put a semi-colon and enter the sheet name as well as data range:

• Be aware you should never combine a text column with a date or numeric column as you will receive an incorrect output. 
• If you want to use an infinite range you can do so. As an example, if you wanted the above range to be infinite, instead of A2:H5, you would simply place A2:H, into the formula.

How to Consolidate Data Using a Formula in Google Sheets (Formula You Can Try)

• Using a Query formula we will now attempt to combine the two sheets:

• Once again make sure to use your own data, rows, and columns, and adjust it to work for your data.
• This will make it so that when the same item appears more than once, it will be grouped into a single row and show the sum of its values from column H.

Single Formula to Combine and Summarize Data

In order to combine the two formulas simply use:

Using the Google Sheets Spreadsheet Key

Although it can seem a bit difficult, with enough practice and proper data input, using import and query formulas with your Google Sheets spreadsheet key, will be a powerful tool for your small business, allowing you to quickly consolidate data in your Google Spreadsheets quickly and in no time at all.

Just remember that if you're expecting people in your organization to pull data from your Google Sheets, you'll have to share that spreadsheet with them first, so they have permission to access it and import it into their spreadsheet.