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.
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:
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 manually by typing the address in the Select the range with your main table field.
- Click the Get selection icon to automatically insert the range that is currently selected in your spreadsheet.
- Click the Select range icon to open a dialog box:
The dialog box allows you to pick your data in different ways:
- Enter the range in the Select the range with your data field using your keyboard.
- Pick a cell in the table and click the rectangle icon to select the entire table (up to the first empty row and column).
- Highlight the necessary cells and click the Get selection button to auto-insert the range.
Select the necessary cells and click OK to confirm the reference.
- Use the Auto select button to detect the used range (till the first blank row and column) with your data automatically.
- Select 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.
- 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.
- 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. - To remove any unwanted spreadsheet from the list, select it and use the Exclude button.
- 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 entering the exact range manually.
Alternatively, click the Select range icon and alter the range in the corresponding pop-up window:
- Type the range address manually in the input field.
- Select any cell in your table and click the rectangle icon to automatically select the entire table (up to the first empty row/column).
- Highlight the cells you need in the sheet and click the Get selection button to insert their address.
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:
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.
- 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.
- The 1st row content fields will help you see the top values in the main table columns.
- 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.
- 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.
- If you don't want to match empty cells, pick the option Skip empty cells.
- Tick off Match case to consider the same records written in different text cases as non-matching and update them with different data.
- 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):
- 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:- Add an empty column to your main sheet manually and name it after the repeated column in lookup sheets.
- Select this column on Step 4 and choose Update values in.
- 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:
- 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.
- 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.
- 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:
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 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:
- Or right after the row with 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:
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:
- Either at the end of your main table:
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:
- To avoid copying empty cells from the lookup table(s), tick off the option 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:
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:
You will see your future scenario outline with the options you've just used. You can adjust some of these settings to your convenience:
- Name your scenario in a way to understand what it does and find it among other scenarios quickly.
- 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.
- Pick [Selected sheet] and the add-on will treat your currently active sheet when running the scenario as the main one.
- 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.
- 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.
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.
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.
Share certain scenarios
To share only certain scenarios, go to Extensions > Merge Sheets > Scenarios > required scenario > Export & share:
You will be prompted to save the scenario to your computer:
Click Save, and your browser will let you select a place where you'd like to save a file with a selected scenario. Once your browser downloads the file, you can close the add-on window.
Send this saved file to any person who's using Merge Sheets so they could import it to their add-on and make use of this scenario.
Share all scenarios
To share all scenarios you've created, go to Extensions > Merge Sheets > Scenarios, and then right to Export & share all scenarios:
The add-on will let you know that you're about to save the file with all scenarios to your computer:
Click Save, and your browser will let you select a place where you'd like to save the file with scenarios. Once you download it, you can close the add-on window.
Send this saved file to any person who's using Merge Sheets so they could import it to their add-on and use the same scenarios.
Import scenarios
To import the scenarios someone has shared with you, go to Merge Sheets > Scenarios > Import scenarios:
Merge Sheets will ask you to pick the file with scenarios from your computer using your browser. Click Browse to do that:
Once you add the required file, the add-on will let you know how many scenarios from that file have been imported:
Tip. The add-on imports all scenarios from the files by default. But if you already have some of them or if something goes wrong, the add-on will inform you accordingly.
All imported scenarios will appear in your Merge Sheets > Scenarios menu:
You can run them right away if you know what spreadsheets they're created for, or review and edit them beforehand.
How to run scenarios automatically on a schedule
Once you save a scenario for the Merge Sheets add-on, you can make it run automatically. There's no need to keep these spreadsheets open or your computer on. Scheduled scenarios will carry out the tasks on Google servers even when you're offline.
Note. Currently, the scheduling feature is available only for users with a paid subscription. This is a beta feature at the moment: your feedback is invaluable to us! We'd love to hear your thoughts or ideas to make scheduling even better. Feel free to share them with us at support@ablebits.com 😊.
What is a quota?
A quota is the number of individual sheets the add-on can process automatically according to your subscription plan.
During the beta period, each user receives:
- Annual subscriptions: 100 quota units
- Lifetime subscriptions: 300 quota units
How the quota is calculated
Each time a scenario processes one sheet, it counts as one run: 1 sheet processed = 1 quota unit used.
Suppose you have a scenario that merges 3 sheets and you schedule it to run once a day. In this case, each daily run will take 3 quota units from your balance:
- Day 1: 3 quota units (3 total)
- Day 2: 3 quota units (6 total)
- Day 3: 3 quota units (9 total)
This process repeats until you have no quota left or you cancel the scheduled tasks.
Note. If a scenario fails because of incorrect settings, 1 quota unit will be deducted anyway — even if nothing was processed.
How to increase your quota
While we are in the beta phase, the number of quota units is set according to your subscription. If you need a higher quota, we are happy to consider an increase for your account.
To increase your quota:
- Send an email to support@ablebits.com.
- Briefly describe your use case.
Our team will review your request and adjust your limits accordingly.
Note. Your quota and scheduled tasks are linked to your activation key. If a subscription is not renewed, your scheduled scenarios will be removed after a 1-year period.
How to schedule your scenario
Note. When you schedule a scenario, the add-on creates a copy of the selected scenario as a new, independent recurring run. Your original saved scenario remains completely unchanged.
Step 1: Grant access to process your data on a schedule
To start scheduling scenarios, Merge Sheets will ask you to connect to the Process Sheets service:
See, edit, create, and delete all your Google Sheets spreadsheets
This will let Merge Sheets work with those spreadsheets from your Google Drive that you want to process automatically, strictly following the settings saved in the scenario: match columns, merge the data, update your main sheet or place the results in new files, create & keep a log sheet for your information.
Once you allow this permission, you can proceed to scheduling your scenario.
Step 2: Save your scenario
To make Merge Sheets run scenarios automatically, first configure and save a scenario for the Merge Sheets add-on.
Step 3: Access the schedule settings
To access the scheduling options in the Merge Sheets add-on:
- Go to Extensions > Merge Sheets > Scenarios.
- In the list of your saved scenarios, select the one you want to schedule and click Edit.
- An overview of your scenario outline will appear. Review it and click the Set up recurring run button.
Alternatively, you can access the schedule settings via the Schedule menu:
- Go to Extensions > Merge Sheets > Schedule.
- In the Scenario schedules window, find your scenario under the Available to schedule section and click it.
- The window with your scenario outline will open. Check it and click Set up recurring run.
Step 4: Schedule autostart
At this step, you are to name the scheduled task and configure the interval and time settings.
Note. Due to some Google Sheets peculiarities regarding time-driven triggers, scenarios cannot be run at a certain time on the dot. Google may delay the execution for up to 15–30 minutes after the scheduled time. We recommend scheduling tasks slightly ahead of when you actually need the results.
To set up a daily schedule:
- Tick the checkbox to schedule the scenario to run every day.
- Enter your preferred start time.
- Review the summary line to confirm your settings.
To set up a weekly schedule:
- Tick the checkbox to enable the weekly schedule.
- Check the boxes next to the preferred days of the week.
- Enter the hours, minutes, and AM/PM for your start time.
- Verify the schedule by reviewing the summary line.
To set up a monthly schedule:
- Tick the checkbox to enable the monthly schedule.
- Choose how to repeat the run:
- Select a specific day of the month.
- Select a specific weekday of a specific week of the month (e.g., First Monday, Last Friday, Second Tuesday).
- Enter the hours, minutes, and AM/PM.
- Check the summary line.
Step 5: Review with Scenario outline
Double-check your configuration in the Scenario outline section. You can also change sheets & ranges here while the scenario is not scheduled yet.
Note. Any changes you make here won't affect your original scenario settings.
- Change your Main or Lookup sheets if the structure of your file has changed.
- Adjust the data ranges.
- Verify that the columns selected for matching and updating are still correct.
Step 6: Save and activate
Click the Create schedule button to save and activate your recurring run.
Manage scenario schedules
You can see & easily manage schedules for all your scenarios from one place.
Go to Extensions > Merge Sheets > Schedule.
The Scenario schedules will appear featuring all your scenarios:
- This section lists scenarios that are prepped and waiting for scheduling. To add a scenario to your schedule, click Schedule next to its name. You will then see the scenario outline and be able to make it run automatically.
- This section contains scenarios that are already active and set to autostart.
By clicking on scenarios that are already on the calendar, you'll be able to change the set time or remove scenarios from the timetable:
Whatever changes you make, hit Save to apply them, or Back to return to the summary list without saving. Clicking Delete will remove the scenario from the schedule and stop the automation.
Note. All your automated scenarios are stored for one year. If your subscription is not renewed, all automated tasks and their settings will be deleted after one year.
Keep track of automatic runs
The add-on always keeps the log of the automated scenario performance. You'll find it in the Logs sheet next to your resulting table (either in the original file where your main sheet is updated or in the new file if your scenario places the result there):
It'll show you when each scenario was executed (including the time zone), whether it finished successfully and what the result is.
Please contact us here