Merge data from two & more Google spreadsheets

Start the add-on

To open the tool, go to Extensions > Merge Sheets > Start:

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

Step 1: Select your main table

Your main table is the one that will be updated as the result of the data processing:

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:

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.

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 your drives quickly, make use of a quick search.
   
   Before the search, feel free to stay on the Recent tab to look through all recently opened spreadsheets, or switch to a specific tab to search the specific place: My Drive, Shared drives, Shared with me, or Starred.
   

   Then just enter the part of the file name into the Search field and hit Search.
   

   Merge Sheets will check your selected Drive and get you a list of all files that match partially or completely.

   Tip. You can add multiple lookup files at once. Click on them while holding the Shift (for adjacent) or Ctrl (for non-adjacent) keys on your keyboard. The files will be highlighted in light blue.
   
   Click Select to include them in the add-on tree-view.
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:
     
     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:
   

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:

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):

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:
     
   • 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:
     
     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:
     
     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:
     

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:
   

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:

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:

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.

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:

You will see your future scenario outline with the options you've just used. You can adjust some of these settings to your convenience:

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:
   
   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.

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:

Edit or delete scenarios

To edit your existing scenario, go to Extensions > Merge Sheet > Scenarios, pick the scenario and click Edit this time:

You will see the familiar scenario outline. The editable fields are the same: name, sheets, and data ranges.

Press Delete to remove the scenario forever, Save to keep the changes, and Run to start it right away with the new settings.

Share scenarios

You can share all or only certain scenarios with your teammates or between your different Google accounts.