VLOOKUP across growing sheets is brittle. A scripted left join materializes Merged for exports and BI tools.
mergeSheetsByKey indexes Subscriptions by customerId, then walks Customers appending plan and renewsOn.
Missing keys yield blank extras so left rows never disappear.
Change RIGHT_COLS to pull additional subscription fields without rewriting the loop.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| Customers | A customerId | Left key |
| Customers | B name | Attributes |
| Subscriptions | A customerId | Right key |
| Subscriptions | B plan | Merged field |
| Subscriptions | C renewsOn | Merged field |
| Merged | output | Join result |
What this script does
In-memory hash join written to Merged.
Prerequisites
Customers and Subscriptions sheets with matching key types.
- Keys stored as text consistently
- Right sheet unique per key (last wins if not)
- Merged can be cleared
Walkthrough
Align two sample ids, run, confirm Merged width = left width + RIGHT_COLS.
Edge cases
Duplicate right keys: later rows overwrite earlier in map — pre-dedupe if needed.
- Number vs text keys — String() both sides
- Header names taken from right
How to test
Include one unmatched customer; expect blank plan.
Hardening for production
Inner join by skipping empty extras; outer join by also emitting unmatched right rows.
Variations
Write back into Customers columns instead of Merged.
Full code: mergeSheetsByKey()
Set LEFT/RIGHT sheet names and key columns, then run mergeSheetsByKey().
/**
* Merge SheetB columns into SheetA rows matched by a key column.
*/
const LEFT = "Customers";
const RIGHT = "Subscriptions";
const LEFT_KEY = 1; // A customerId
const RIGHT_KEY = 1; // A customerId
const RIGHT_COLS = [2, 3]; // plan, renewsOn
function mergeSheetsByKey() {
const ss = SpreadsheetApp.getActive();
const left = ss.getSheetByName(LEFT).getDataRange().getValues();
const right = ss.getSheetByName(RIGHT).getDataRange().getValues();
const map = {};
for (let i = 1; i < right.length; i++) {
map[String(right[i][RIGHT_KEY - 1])] = RIGHT_COLS.map(function (c) {
return right[i][c - 1];
});
}
const header = left[0].concat(RIGHT_COLS.map(function (c) {
return right[0][c - 1];
}));
const out = [header];
for (let i = 1; i < left.length; i++) {
const key = String(left[i][LEFT_KEY - 1]);
const extra = map[key] || RIGHT_COLS.map(function () { return ""; });
out.push(left[i].concat(extra));
}
const dest = ss.getSheetByName("Merged") || ss.insertSheet("Merged");
dest.clearContents();
dest.getRange(1, 1, out.length, out[0].length).setValues(out);
Logger.log("Merged %s customer rows", out.length - 1);
}- Line 17: Builds a hash of right-side fields by key.
- Line 17: Selects which right columns to append.
- Line 28: Left join behavior with blank fillers.
- Line 32: Creates output if missing.
- Line 34: Writes the joined matrix.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: merge by key
- 1Key columns identified on both sheets
- 2Key formatting normalized (text vs number)
- 3RIGHT_COLS lists desired fields
- 4Merged sheet disposable
- 5Duplicate right keys understood
- 6Test with a small fixture
Frequently asked questions
Same idea as many VLOOKUPs, but one pass and easier multi-column pulls.
Skip pushing rows when !map[key].
Flatten to many rows, or aggregate before indexing.
O(n+m) memory — fine for tens of thousands of rows; beyond that use BigQuery.
Use key = id + '|' + region when indexing and looking up.
Prefer values for snapshots; add formulas in a separate reporting tab.
Write extras into empty columns on Customers with setValues per row block.