Merge data from two & more Google spreadsheets

The Merge Sheets add-on for Google Sheets brings the latest data from additional sheets into your main table. Find and pull matching values, or add non-matching rows, if needed, in five simple steps.
Merge two tables in Google Sheets.

Video: How to merge data in Google Sheets

Before you start

We care about your data and suggest you always create backup copies of your spreadsheets. A special option of the add-on will do that for you if you select it.

How to use Merge Sheets

Start the add-on

To open the tool, go to Extensions > Merge Sheets > Start:
Run the add-on from the Google Sheets menu.

Tip. You can also find Merge Sheets in the Merge & Combine group in the Power Tools add-on:
The Merge Sheets icon in Power Tools.

Step 1: Select your main table

Your main table is the one that will be updated as the result of the data processing:
Select the sheet where you want to update records.
You can choose the sheet with your main table from the Select your main sheet drop-down list.

By default, the add-on highlights the used range (cells with data till the first empty column & row) in your main sheet. You can edit the range either by manually typing the address in the Select the range with your table field or by clicking the Select range icon. You will then see a corresponding dialog box that will let you pick the necessary cells:
Adjust the range in this pop-up window.

Tip. Click on the Auto select button to highlight that used range back.

Don't forget the Create a backup copy of the sheet checkbox to keep the original table.

Click Next to go to the second step.

Step 2: Choose the lookup sheets

The lookup sheet is the table that contains the information you want to copy to your main sheet. You can add multiple lookup sheets to get the data from all of them, one by one, in one go.

Note. The add-on updates your main sheet with the info from the lookup sheets one by one: in the order lookup sheets appear in the add-on tree view.
Tip. Your lookup tables will not be modified in any way.

Select the sheets with the values to pull.

  1. Tick off the Sheets checkbox if you want to use all sheets from the tree view as lookup ones.
    Tip. The disabled sheet is the one you selected as the main one on the previous step.
  2. To add more lookup sheets to the add-on, hit Add files from Drive.
    Tip. To find the lookup spreadsheet in the Drive quickly, make use of a quick search. Just enter the part of the file name into the Search field of the Import files from Drive window and hit Find. Merge Sheets will check your entire Drive and get you the list of all files that match partially or completely.

    If you're not sure where the found spreadsheet is located, simply hover your mouse cursor over it and you'll see a tip with a full path:
    Quickly search your files within the Drive.

  3. To remove any unwanted spreadsheet from the list, select it and use the Exclude button.
  4. By default, the add-on includes all data from the selected sheets to the merge. But you can specify the range by clicking All data and doing one of the following:
    • enter the exact range manually
    • highlight the required cells on the sheet (for sheet from the current file only)
    • click the Select range icon and alter the range in the corresponding pop-up window:
      Adjust the range of the lookup table.

      Tip. The Auto select button will automatically define the used range for you.
    Note. If your lookup sheet is in another file, you can click the Open link to open the file in a new browser tab, check the range, and then change it in the add-on manually:
    Open the file in a new browser tab to check the lookup range.

Click Next to proceed to the third step, or edit the main table by going Back.

Step 3: Identify matching columns

Here you are to pick those columns that appear in in all your tables (key columns). The add-on will compare these columns to identify the same (matching) records:
Identify matching columns.

Tip. The columns you select on this step will not be modified.
Note. If you tick off more than one column, a record will be considered the same if the values in all these columns match.

Identify key columns and their equivalents in the lookup tables.

  1. To see column titles, check the Main table has headers and Lookup table has headers boxes respectively. If there are no headers in your tables, uncheck these boxes to display column names instead.
  2. The 1st row content fields will help you see the top values in the main table columns.
  3. The add-on can find complete row matches if you select all columns using the top checkbox.
    Note. Tick off all columns only if your lookup table(s) has(ve) non-matching columns that you're going to add to your main sheet. Otherwise, there will be no columns to update on the next step and you won't be able to proceed further.
  4. Make use of the Auto detect button to select the first column that appears in both tables with the same header.
    Tip. All matching columns are marked in bold in the Main table columns list.
    Note. The option is available only when there are header rows in your tables.
  5. If you don't want to match empty cells, pick the option Skip empty cells.
  6. Tick off Match case to consider the same records written in different text cases as non-matching and update them with different data.
  7. Make sure you choose the lookup table(s) columns to compare to the main table columns.

Click Next to go on to step 4.

Step 4: Pick columns to add or update in the main sheet

On this step, you are to decide what to do with other columns of the lookup table(s):
Choose the columns to add or update in the main sheet.

  1. Tick off the column of interest, click on the Action field and choose what to do with the column:
    • All columns selected as matching on step 3 are marked as Match with in light gray and are grouped at the top of the list by default.
    • The Update values in option will look up the same contents in matching columns and replace corresponding records in the columns you pick on this step:
      Update values in the main table.
    • Choose to Add a missing column to the end, and its values will be inserted for all matching records in a new column to the right of the original table:
      Add values to the end of the main table.
      If you have several lookup sheets and a few of them have the same column that you decided to add, each such column from each lookup sheet will be added to the main one: one next to the other. Thus, your main sheet will contain a few columns with identical headers next to each other, each with the data from a different lookup:
      Add multiple columns to the end of the main table.

      Tip. You can avoid this and have the data from several identical columns put to one such column in the main sheet:

      1. Add an empty column to your main sheet manually and name it after the repeated column in lookup sheets.
      2. Select this column on Step 4 and choose Update values in.
      3. On Step 5, tick off the option Update only empty cells in the main table.

      The add-on will update all empty cells in this column with the related data from the same column in each lookup sheet:
      Update the column rather that add several of them.

  2. Check this box to select all columns at once and add their values to the end of the original table. Uncheck back to unselect all columns if you don't want to add or update any.
  3. The Auto detect button will automatically pick the Update values in action for all remaining columns with the same headers.
    Note. The option is available only when there are header rows in your tables.
  4. If there are lots of columns, you can filter the list using the respective option in the Show columns drop-down list:
    Filter the list of columns you want to see on this step.

Click Next to follow to the last step.

Step 5: Tweak additional options

This step offers a set of additional options to match, update, or add values:
Choose the way to merge the data.

Place results to

  • The standard option would be to Update your main table. This way, the tool makes all changes to the same sheet that you chose on the first step.
  • Or have the tool Create a new spreadsheet and place the updated main sheet there.
    Tip. Once the add-on merges your data, it will provide you with the link to open the resulting spreadsheet right away.

Add rows and columns

  • Add non-matching rows to the end of the main table. The add-on will insert the rows with key records that are present only in your lookup table(s). Thus, if you have any new records, you'll transfer them to the appropriate columns in the main table even if their order is different:
    Add non-matching rows to the end of the main table.
  • Add a status column. This one will show the changes made to the rows and mark them as Matching, Matching and updated, Non-matching, or New row.
  • Insert additional matching rows. If there is more than 1 matching key value in the lookup sheet that may contain some unique info in other columns, this option will bring such additional rows to your main sheet:
    • Either at the end of your main table:
      Insert additional matching rows at the end of the main table.
    • Or right after the row with same key value:
      Insert additional matching rows after the row with the same key value.
    • Note. The latter option works slower if you merge data from several lookup sheets since the add-on handles them one by one.
      Tip. You can combine these rows beforehand to have all unique info in one cell using the Combine Duplicate Rows add-on.
      Note. If there is more than 1 matching key value in the main sheet, only the value for the 1st one will be updated. All extra rows from the lookup sheets will also be added:
      The record for the 1st key is updated.
      Without this option, all values for all matching key records in the main sheet will be updated with the 1st matching record from the lookup sheet:
      The records for all keys are updated.

Update cells

  • Update only empty cells in the main table. If you have blank cells in the column you are updating in your main table, keep all cells that already have records untouched. The existing values in the main table will not be overwritten:
    Update only empty and new cells in the main table.
  • To avoid copying empty cells from the lookup table(s), tick off the option Update only if cells from the lookup table contain data:
    Update only if cells from the lookup table contain data.

Highlight cells

  • If you already have some rows highlighted, you can remove their current filling using the option to Clear background color in the columns you are updating. Any existing background color in the column you selected to update will be removed.
  • You can see all cells that were changed in the main table by selecting the option Set background color of updated cells.
Tip. Click on the arrow next to this option to pick a hue from the list.

Get the results

Click Finish to merge Google spreadsheets and you'll see the number of processed lookup sheets, inserted columns, added and updated rows:
Google Sheets - join tables.
You'll also get a link to open a new spreadsheet if you chose to place the result there instead of updating your main table.

How to work with scenarios

If you rely on Merge Sheets for Google Sheets to pull data from other tabs, most likely you have to update lots of different sheets or even the same sheet often. To save your time on these operations, we introduced scenarios.

What is a scenario?

The scenario is a saved set of the options you select on each step of the add-on.

You will have a chance to save the settings once the add-on processes your data. Then, just run this scenario, and the tool will start updating the info in your spreadsheet immediately.

Save the scenario

To create your scenario, click Save scenario in the result message once the add-on processes your data:
Save scenario in Merge Sheets.
You will see your future scenario outline with the options you've just used. You can adjust some of these settings to your convenience:

Adjust settings for your scenario in Merge Sheets.

  1. Name your scenario in a way to understand what it does and find it among other scenarios quickly.
  2. Choose your main and lookup sheets:
    • Pick [Selected sheet] and the add-on will treat your currently active sheet when running the scenario as the main one.
      Note. This option is available for the Main sheet only.
    • Choose specific sheets from the lists to always pull the data from the same lookup tables and/or update the info in the same main table.
  3. Identify ranges to process in your main and lookup sheets:
    • Select [All data] to automatically detect the entire used ranges for work.
    • For the tool to handle the exact cells, simply enter the desired ranges of cells.
  4. Check if all other options you've just used are correct:
    Look through all additional settings for your scenario in Merge Sheets.

    Note. You cannot change these options. To modify them, please restart the add-on to pick the correct settings on steps 1-5.

Once everything's set, click Save and the scenario will be created.

Run your scenario

To start the scenario, go to Extensions > Merge Sheet > Scenarios, find the necessary name and click Start.

Note. If you don't see Scenarios in the menu, just click Extensions > Merge Sheets > Start to open the add-on itself once. This is necessary for new spreadsheets and those where the add-on hasn't been run yet. Your scenario (and all future ones) will then appear in the menu automatically.

Start the necessary scenario from the spreadsheet menu.
This will run the tool and start processing your data right away per all the settings.

In the end, you will get the result message saying what scenario has been used, how many lookup sheets have been processed and what number of rows and columns have been found and modified:
Merge data in Google Sheets.

Manage scenarios

To edit your existing scenario, go to Extensions > Merge Sheet > Scenarios, pick the scenario and click Edit this time:
Edit scenarios in Merge Sheets.
You will see the familiar scenario outline. The editable fields are the same: name, sheets, and data ranges.
Edit or delete scenarios in Merge Sheets.
Press Delete to remove the scenario forever, Save to keep the changes, and Run to start it right away with the new settings.

Responses

Good afternoon,

My team at work recently started using the Merge Sheets add on. It gave us a notification that we were on a 30-day trial. At the end of the trial do we need to by the add on for each teammate or can we purchase it for the company and all teammates can use it? If so, where do I see pricing for these options?

Hello Taylor,

Thank you for your question.

Our subscription is account-based. If the product is going to be used under different Google accounts at the same time, you need to have several subscriptions, so there is one for each account.

Please note that you can purchase Merge Sheets as a separate add-on or as a part of our Power Tools for Google Sheets. Please see the plans available and their actual prices on the product pages.

If you have any other questions, feel free to email us at support@ablebits.com.

Unusable to do function window being too small.
I have long column names, and they are cut short, so I can't see the full name to match up the columns.

I would really help if the developers used real world data to build these add ons.

Weird issue: merging time values subtracts 5 hours. I'm in the eastern time zone, so I'm guessing one of the time zone bases involved in the calculation is using UTC. Unfortunately, it's a deal-breaker for my purposes.

Hello James,

Thank you for reporting this issue to us.

For us to investigate the problem and find its cause, please send us the following details at support@ablebits.com:

1 - Do you use the single Merge Sheets add-on or the one from Power Tools?
2 - Please go to File > Settings and send us a screenshot with the Time zone set for each spreadsheet you are merging.
3 - Please include the screenshots of the options you choose on each step of the add-on.
4 - Also, please send us the screenshots showing time values you have before and after merging the data.

If you could record a short video illustrating the issue, it would be very helpful.

We appreciate your time and look forward to the details from you. They will help our developers reproduce the issue and find its cause.

Stefan Boeters says:
November 8, 2021 at 12:00 pm

When I performed today what I thought was a routine job in merging two Google spreadsheets, I got the error message:

The add-on couldn't paste data
Invalid argument: timeZone. Should be of type: String

Is there anything I can do to identify which data item triggers this error? It's a quite large spreadsheet. (The two sheets have approximately 80000 and 30000 rows, respectively.)

Amanda McCallister says:
August 22, 2021 at 11:55 pm

When I run merge sheets and it adds rows for new data, the formulas from the main spreadsheet are not copying into the new rows. Is there a way for it to maintain the formulas when adding new data sets?

Hello Amanda,

If I understand you correctly and you choose the option 'Add non-matching rows', those rows are taken from your lookup sheet, not the main one. At the moment, the add-on doesn't transfer formulas from the lookup sheet as this would break them or result in the wrong calculations in a new sheet. We'll continue to collect votes and comments on this idea to gauge interest and impact.

If, however, your issue is different, please email us at support@ablebits.com with the screenshots of the problem. Thank you.

Keep getting this error when trying to merge.
The add-on couldn't paste data
Service timed out: Spreadsheet

Hello Jerry,
Thank you for contacting us.

We have just replied to you via email. Please provide us with the requested information and we’ll do our best to help. Thank you.

Hi Mary,
I have a similar problem as Jerry and I can't merge two sheets.

After the merge tool process I receive this info:

The add-on couldn't paste data
Status - Please select one of predefined status values.

Katerina Bespalaya (Ablebits Team) says:
March 2, 2020 at 7:01 pm

Hello Fabiano,

Sorry to hear that you are having a problem with our add-on. I've just requested some details regarding the issue you faced by email. Please send them to us and we'll try to help you. Thank you.

Why can we not use the "set background color of the updated cells" option? I am using the trial version but it does not allow me to activate that. Also, the process seems simple enough but it does not work. It simply says that:

930 of 959 matching rows were found.
0 columns were inserted.
0 rows were updated.
0 rows were inserted.

Katerina Bespalaya (Ablebits Team) says:
December 16, 2019 at 2:51 pm

Hello Josh,

Thank you for contacting us. Please note that the "Set background color of updated cells" option is enabled only if you choose to "update values in" any column in step 4 of the Wizard.

If you are still having difficulties to get the result you need, then please describe your task in more detail. If you can share a small sample spreadsheet with us (support@4-bits.com) with your source data and the expected result, it will be of great help. If you have confidential information in your spreadsheet, you can replace it with some irrelevant data, just keep the format. I also kindly ask you to shorten the tables to 10-20 rows.

Note: We keep support@4-bits.com for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by sending a message to support@ablebits.com.

We'll look into your task and try to find a solution for you.

My main sheet/data set I'm always adding rows to, it would be nice to be able to update the columns I'm pulling data from my database sheet without having to run the process again. otherwise, works as intended.. helpful.

I would like the option to save a Merge, and re-run the same settings. This way I could keep updating the spreadsheet as new information comes in.

Stefan Boeters says:
February 8, 2019 at 10:00 pm

I would appreciate the option "not case sensitive" in the matching columns. (As you may guess, I have a main sheet which is all lower case and a lookup sheet that uses upper case letters.)

I trialed the Merge function and it worked without fail. I purchased the product and now a simple merge of 40 rows of data repeatedly fails. The 2 columns in the main table and lookup tables contain First Name & Surname. I have checked that names in the main table exactly match the names in the lookup table. I have deleted and re-typed the names in new columns. I have removed and re-installed and re-activated the Powertool. After this data in one of the missing 4 rows was inserted. Is there some known issue?
Thank you.

Thank you for reporting this problem to us, Barb. So sorry you're having difficulties with our tool.

If it's not a big trouble for you, I kindly ask you to share your spreadsheet with us (gapps.ablebits@gmail.com) with your source data and the result you're getting/expect to get. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.

The screenshots of the options you select on each step of the wizard would also help.

We'll investigate the problem.
Thanks in advance for your responsiveness.

One of my columns has a number like 80. When I merge, the column turns into a date. What am I doing wrong?

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.