Automate Course Code Matching in Google Sheets with Google Apps Script

Google Apps Script is a versatile tool that can simplify repetitive tasks in Google Sheets. In this post, we’ll explore a script that automates the process of matching course codes from multiple sheets and adding them as additional columns in a main sheet. This solution is perfect for managing large datasets where matching values across multiple sources is required.


The Script

Here’s the complete script:

function addCourseCodesToMain() {
// Open the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();

// Get the main sheet
var mainSheet = ss.getSheetByName('main');
if (!mainSheet) {
throw new Error("Sheet 'main' not found");
}

// Get the data in column A of the main sheet
var mainData = mainSheet.getRange(1, 1, mainSheet.getLastRow()).getValues();

// Sheets to match with
var otherSheetNames = ['Sheet1', 'Sheet2', 'Sheet3']; // Replace with actual sheet names

// Create a lookup table for course codes
var courseCodeMaps = otherSheetNames.map(sheetName => {
var sheet = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error("Sheet '" + sheetName + "' not found");
}
var data = sheet.getRange(1, 1, sheet.getLastRow()).getValues();
var map = {};
data.forEach(row => {
if (row[0]) { // Only consider non-empty values
map[row[0]] = row[0]; // Key and value are the same (course code)
}
});
return map;
});

// Build the result array for additional columns
var additionalColumns = mainData.map(row => {
var value = row[0]; // Value in column A of main
return courseCodeMaps.map(map => map[value] || ''); // Match course code or leave blank
});

// Write the additional columns to the main sheet
if (additionalColumns.length > 0) {
var startColumn = 2; // Start writing at column B
var numRows = additionalColumns.length;
var numColumns = additionalColumns[0].length;
mainSheet.getRange(1, startColumn, numRows, numColumns).setValues(additionalColumns);
}
}

What Does This Script Do?

This script automates the process of matching course codes from multiple sheets (Sheet1, Sheet2, Sheet3, etc.) with the rows in the main sheet. It extracts data from the main sheet’s column A and checks if the same value exists in the first column of the other sheets. Matches are added to additional columns in the main sheet.


How It Works

  1. Access the Spreadsheet:
    • The script accesses the active spreadsheet and retrieves the main sheet.
  2. Retrieve Data:
    • Extracts all rows from column A of the main sheet.
    • Fetches data from the first column of the specified sheets (Sheet1, Sheet2, Sheet3).
  3. Create Lookup Tables:
    • Creates a map (dictionary) for each of the additional sheets, where keys are the course codes in column A.
  4. Match and Populate:
    • For each value in main’s column A, it checks the maps for a match.
    • Populates a row with the matching course codes, leaving blanks for unmatched rows.
  5. Write to main Sheet:
    • Adds the matched course codes as new columns (B, C, D, etc.) in the main sheet.

Example Input and Output

Input:

main Sheet:

A
Course001
Course002
Course003

Sheet1:

A
Course001
Course004

Sheet2:

A
Course002
Course003

Sheet3:

A
Course003
Course005

Output (main Sheet):

ABCD
Course001Course001
Course002Course002
Course003Course003Course003

Benefits of This Script

  1. Automation:
    • No manual matching required.
    • Saves time when working with large datasets.
  2. Scalability:
    • Easily add more sheets to the otherSheetNames array.
  3. Accuracy:
    • Ensures consistent and error-free matching.
  4. Customizable:
    • Adapt the logic to match based on different columns or add new conditions.

How to Use the Script

  1. Open your Google Sheet.
  2. Click on Extensions > Apps Script.
  3. Paste the script into the editor.
  4. Replace Sheet1, Sheet2, and Sheet3 in the otherSheetNames array with your actual sheet names.
  5. Save and run the addCourseCodesToMain function.
  6. Grant permissions if prompted.

Conclusion

This script demonstrates the power of Google Apps Script to automate tedious tasks. Whether you’re managing course codes or any other type of data, this script can be a valuable tool to streamline your workflow.