Consolidating Data from Two Tabs to One in Google Sheets

If you have a Google Sheets spreadsheet with multiple tabs containing different types of data, you might want to consolidate them into one sheet. This article will discuss how to create a third sheet that automatically updates with data from two tabs containing different types of jobs (on-site and off-site).

Consolidating Data from Multiple Tabs

Google Sheets allows you to consolidate data from multiple tabs or sheets into one sheet using various methods. The most common methods are using the CONCATENATE, VLOOKUP, and QUERY functions. However, this article will focus on using a custom script to consolidate data from two tabs into one sheet.

Creating a Custom Script

To create a custom script that consolidates data from two tabs into one sheet, we need to follow these steps:

1. Create a new sheet in the same spreadsheet.
2. Write a custom script that copies data from the two tabs and appends them to the new sheet.
3. Set up a trigger that runs the script every time data is added or modified in either of the two tabs.

Step 1: Creating a New Sheet

To create a new sheet, click on the + button located at the bottom left corner of the Google Sheets window. Rename the new sheet as "Consolidated."

Step 2: Writing a Custom Script

To write a custom script that consolidates data from two tabs into the "Consolidated" sheet, follow these steps:

1. Click on Tools and then select Script editor.
2. Write the following script:

The script gets the values from both tabs and concatenates them into one array. The DATA1.SLICE(1) and DATA2.SLICE(1) methods remove the header row from each tab. Finally, the concatenated array is written to the "Consolidated" sheet.

Step 3: Setting Up a Trigger

To set up a trigger that runs the CONSOLIDATE DATA script every time data is added or modified in either of the two tabs, follow these steps:

1. Click on Edit and then select Current project's triggers.
2. Click on the Add Trigger button.
3. Configure the trigger as follows:

• Choose which function to run: consolidateData
• Select event source: From spreadsheet
• Select event type: On change

In this article, we discussed how to consolidate data from two Tabs into one Google Sheets sheet using a custom script. We explained the three steps required to create the script and set up a trigger that runs it every time data is added or modified.

References

• Google Sheets API documentation
• Google Apps Script documentation for Sheets