How To Match Data In Columns In Google Sheets Using Google Apps Script?

In this article we will look at how to match Column A values with all the values in Column B in Google Sheets using Google Apps Script code to check if the Column A value is matching with any Column B value.

For example, Column A has 258 values and Column B has 1000+ values. The script should match each of Column A values with all the values in Column B to check if the Column A value is matching with any Column B value. If match found the script writes yes in Column C.

Challenge: How we will know which Column A row is matching with which Column B row? For that the script can write row number also in Column C.

Here is the Google Apps Script code to implement this requirement:

function matchColumnsWithRowNumbers() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Assuming data starts from row 2, adjust as needed
var columnARange = sheet.getRange("A2:A" + sheet.getLastRow());
var columnBRange = sheet.getRange("B2:B" + sheet.getLastRow());
var columnCRange = sheet.getRange("C2:C" + sheet.getLastRow());

var columnAValues = columnARange.getValues();
var columnBValues = columnBRange.getValues();

var resultArray = [];

for (var i = 0; i < columnAValues.length; i++) {
    var matchFound = false;
    var matchingRowNumbers = "";

    for (var j = 0; j < columnBValues.length; j++) {
      if (columnAValues[i][0] === columnBValues[j][0]) {
        matchFound = true;
        matchingRowNumbers += (j + 2) + ","; // Adding 2 because row numbers start from 1, and we're starting from row 2
      }
    }

    resultArray.push([matchFound ? "Yes (Row(s): " + matchingRowNumbers.slice(0, -1) + ")" : ""]);
}

columnCRange.setValues(resultArray);
}

About Google Apps Script
Google Apps Script is a scripting language based on JavaScript that lets you automate tasks and extend the functionality of various Google Workspace (formerly G Suite) applications such as Google Sheets, Google Docs, and Gmail.

Here's a basic guide on how to use Google Apps Script:

Accessing Google Apps Script:

Open the Google Workspace application you want to work with (e.g., Google Sheets, Google Docs, Google Drive).
Click on "Extensions" in the top menu.
Select "Apps Script" from the dropdown menu.

Creating a New Script:
In the Apps Script editor, you can create a new script by clicking on the "+" button or by selecting File > New > Script in the menu.

Writing Code:

Google Apps Script uses JavaScript, so if you are familiar with JavaScript, you'll find it easy to work with.
You can write functions that perform tasks, manipulate data, or interact with Google Workspace applications.

Accessing Google Workspace Services:
Google Apps Script provides built-in classes and methods to interact with various Google services. For example, to work with Google Sheets, you can use the SpreadsheetApp class.

Running the Script:

Save your script using the floppy disk icon or by pressing Ctrl + S (Windows) or Command + S (Mac).
You can run your script by clicking the play button in the toolbar or by selecting Run > Run function > yourFunctionName from the menu.

Setting Triggers:
You can set up triggers to run your script automatically at certain intervals or in response to specific events. Click on the clock icon in the toolbar to access the trigger settings.

Logging and Debugging:

Use console.log() statements to log information in the Apps Script editor's log. This can help you debug your code.
You can also use the debugger by setting breakpoints in your code.

Publishing your Script:
If you want to share your script with others, you can publish it as a web app or deploy it as an add-on.