Merging Two Sheets Based on One Column With Google Apps Script

Google Apps Script is a powerful tool for automating tasks in G Suite applications, including Google Sheets. One common task is combining two sheets based on a common column. This can be done manually, but with Google Apps Script, you can save time and streamline your workflow.

In this tutorial, we will walk through the steps for merging two sheets in Google Sheets based on one column using Google Apps Script. We will provide a step-by-step guide, including code snippets and explanations for each step.

Step 1: Create a new Google Sheet

Before we begin merging the two sheets, we need to create a new sheet to contain the merged data. To create a new Google Sheet, follow these steps:

1. Open Google Drive and click on the “+ New” button in the upper left-hand corner.
2. Select “Google Sheets” from the drop-down menu.
3. The new sheet will open automatically.

Step 2: Import the data from the two sheets

To import the data from the two sheets that we want to merge, follow these steps:

1. Open the sheet that contains the first set of data.
2. Highlight the range of cells that contains the data.
3. Right-click and select “Copy”.
4. Open the new sheet that we created in Step 1.
5. Click on the first cell where you want to paste the data.
6. Right-click and select “Paste” or use the keyboard shortcut Ctrl+V.
7. Repeat these steps for the second sheet.

Step 3: Identify the common column

To merge the two sheets based on a common column, we need to identify which column is common to both sheets. In our example, we will merge two sheets that contain customer data. The common column is the customer ID.

Step 4: Sort the data by the common column

To merge the two sheets, we need to sort the data by the common column. To do this, follow these steps:

• Highlight the range of cells that contains the data, including the column header.
• Click on the “Data” tab in the menu bar.
• Select “Sort sheet by column”.
• In the pop-up window, select the common column as the sorting column.
• Click “Ascending” or “Descending” depending on how you want to sort the data.
• Click “Sort”.
• Repeat these steps for both sheets.

Step 5: Merge the data using Google Apps Script

Now that we have identified the common column and sorted the data, we can merge the two sheets using Google Apps Script. Here is the code snippet to merge two sheets based on one common column:

function mergeSheets() {
    var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
    var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet3");
    var data1 = sheet1.getDataRange().getValues();
    var data2 = sheet2.getDataRange().getValues();
    var mergedData = [];
    for (var i = 0; i < data1.length; i++) {
        for (var j = 0; j < data2.length; j++) {
            if (data1[i][0] == data2[j][0]) {
                mergedData.push(data1[i].concat(data2[j].slice(1)));
            }
        }
    }
    sheet3.getRange(1, 1, mergedData.length, mergedData[0].length).setValues(mergedData);
}

Let’s break down the code:

1. First, we define three variables: sheet1, sheet2, and sheet3. These represent the sheets we want to merge and the new sheet we created earlier.
2. We then use the getDataRange() method to get the data in each sheet.
3. We create an empty array called mergedData to hold the merged data.
4. We use a nested for loop to iterate over each row in data1 and data2.
5. For each row, we compare the value in the first column to see if they match.
6. If the values match, we use the concat() method to combine the values from both rows into one array.
7. We then push this merged array into the mergedData array.
8. Finally, we use the setValues() method to write the merged data to the new sheet.

Step 6: Run the Script

To run the script, follow these steps:

1. Click on “Tools” in the menu bar.
2. Select “Script editor”.
3. Paste the code snippet into the script editor.
4. Save the script.
5. Return to the sheet where you want to merge the data.
6. Click on “Tools” in the menu bar.
7. Select “Macros”.
8. Click on the “mergeSheets” macro and hit “Run”.
9. The merged data will be displayed in the new sheet.

Conclusion

Merging two sheets based on a common column can be a time-consuming process, but with Google Apps Script, you can automate this task and streamline your workflow. In this tutorial, we provided a step-by-step guide, including code snippets and explanations for each step. By following these steps, you can merge two sheets quickly and easily.