Find a row by a key column (email, order ID, SKU) and update it — or append if it's new. Header-aware, so you pass a plain object instead of remembering column indexes.
What you'll need
- A sheet with headers in row 1
- A key header name that uniquely identifies a row
- A values object whose keys match those headers
How to use this snippet
function upsertRowByKey(sheet, keyHeader, keyValue, valuesByHeader) {
var data = sheet.getDataRange().getValues();
var headers = data[0];
var keyCol = headers.indexOf(keyHeader);
if (keyCol === -1) {
throw new Error('Missing header: ' + keyHeader);
}
var rowIndex = -1; // 0-based index into `data`
for (var r = 1; r < data.length; r++) {
if (String(data[r][keyCol]) === String(keyValue)) {
rowIndex = r;
break;
}
}
var row = headers.map(function (h) {
if (valuesByHeader.hasOwnProperty(h)) {
return valuesByHeader[h];
}
// Preserve existing cells on update; blank on insert
return rowIndex === -1 ? '' : data[rowIndex][headers.indexOf(h)];
});
if (rowIndex === -1) {
sheet.appendRow(row);
return sheet.getLastRow();
}
sheet.getRange(rowIndex + 1, 1, 1, row.length).setValues([row]);
return rowIndex + 1;
}
Tips:
- Keys are compared as strings so
00123and123don't silently diverge — normalize upstream if you need numeric equality. - On update, omitted headers keep their current values; on insert, omitted headers become blank.
- For large sheets, cache the key→row map once per run instead of rescanning for every upsert.
Example
function syncContact(contact) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts');
return upsertRowByKey(sheet, 'email', contact.email, {
email: contact.email,
name: contact.name,
updatedAt: new Date()
});
}
Happy Coding!
