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
Access the Spreadsheet:
The script accesses the active spreadsheet and retrieves the main sheet.
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).
Create Lookup Tables:
Creates a map (dictionary) for each of the additional sheets, where keys are the course codes in column A.
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.
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):
A
B
C
D
Course001
Course001
Course002
Course002
Course003
Course003
Course003
Benefits of This Script
Automation:
No manual matching required.
Saves time when working with large datasets.
Scalability:
Easily add more sheets to the otherSheetNames array.
Accuracy:
Ensures consistent and error-free matching.
Customizable:
Adapt the logic to match based on different columns or add new conditions.
How to Use the Script
Open your Google Sheet.
Click on Extensions > Apps Script.
Paste the script into the editor.
Replace Sheet1, Sheet2, and Sheet3 in the otherSheetNames array with your actual sheet names.
Save and run the addCourseCodesToMain function.
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.