Some Google Sheets App Script Snippets and Examples.
v3:
// v3 — Uses RangeList + .setValue for batch updates, with optional caching of header mappings
// https://developers.google.com/apps-script/reference/spreadsheet/range-list#setValue(Object)
function onEdit(e) {
const sheet = e.range.getSheet();
const row = e.range.getRow();
const col = e.range.getColumn();
// Only trigger on column A edits in the 'Listings' sheet
if (sheet.getName() !== 'Listings' || col !== 1) return;
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const rowValues = sheet.getRange(row, 1, 1, headers.length).getValues()[0];
// Optional caching setup (toggleable)
const USE_CACHE = true; // Feature flag — easily toggle cache usage on/off
let cache = null;
let headerMap = null; // maps key → 0-based index
let cacheNeedsUpdate = false;
const cacheKey = 'headerMap_Listings';
if (USE_CACHE) {
cache = CacheService.getDocumentCache();
const cached = cache.get(cacheKey);
if (cached) {
try {
headerMap = JSON.parse(cached);
} catch {
headerMap = null;
}
}
}
/**
* Helper: resolve a fuzzy header key (substring) to a 0-based column index.
* Uses cached mapping if available and valid, otherwise scans headers and optionally updates cache.
*
* @param {string} key Substring to match within the header (case-insensitive)
* @returns {number|null} 0-based index of matching header, or null if none.
*/
function resolveKeyToIndex(key) {
const lowerKey = key.toLowerCase();
if (USE_CACHE) {
// Check cache first
if (headerMap && headerMap[key] != null) {
const idx = headerMap[key];
const h = headers[idx];
if (h && h.toLowerCase().includes(lowerKey)) {
// Cache hit — valid mapping
return idx;
}
// Cached mapping is stale or mismatched
delete headerMap[key];
}
}
// Scan headers only if no valid cache entry
for (let i = 0; i < headers.length; i++) {
const h = headers[i];
if (h && h.toLowerCase().includes(lowerKey)) {
// Found matching header at column i (0-based)
if (USE_CACHE) {
if (!headerMap) headerMap = {};
headerMap[key] = i;
cacheNeedsUpdate = true;
}
return i;
}
}
return null;
}
/**
* Helper: for a group of fuzzy header keys and one default value,
* finds which columns match those keys, picks only those blank in this row,
* collects their A1 notations, and calls RangeList.setValue on them.
*
* @param {string[]} keys Array of fuzzy header substrings (case-insensitive match)
* @param {string} defaultVal Value to set into all matching blank cells
*/
function applyDefaultsForKeys(keys, defaultVal) {
const a1List = [];
keys.forEach(key => {
const idx = resolveKeyToIndex(key);
if (idx != null && !rowValues[idx]) {
// Found matching header at column idx (0-based) and it's blank → push its A1 notation
a1List.push(sheet.getRange(row, idx + 1).getA1Notation());
}
});
if (a1List.length > 0) {
sheet.getRangeList(a1List).setValue(defaultVal);
}
}
// Apply defaults for each group
applyDefaultsForKeys(
[
'Property Type',
'Bedrooms / Study',
'Living Area Space',
'Bathrooms',
'Floorplan',
'Complies with Minimum Ceiling Insulation Standard',
'NBN Connection Type',
'Solar',
'Aircon',
'Heating',
'Hot Water',
'Stove',
'Dishwasher',
'Garage'
],
'TODO'
);
applyDefaultsForKeys(
[
'Status',
'Decision Based On Comments'
],
'Waiting on comments from housemates'
);
// Write back the cache if updated
if (USE_CACHE && cacheNeedsUpdate && headerMap) {
try {
cache.put(cacheKey, JSON.stringify(headerMap), 24 * 3600); // expire after 24 hours
} catch {
// ignore cache write failures
}
}
}v2:
// // v2 — Uses RangeList + .setValue to theoretically batch update, but still maps header columns each time (no cache)
// // https://developers.google.com/apps-script/reference/spreadsheet/range-list#setValue(Object)
// function onEdit(e) {
// const sheet = e.range.getSheet();
// const row = e.range.getRow();
// const col = e.range.getColumn();
// // Only trigger on column A edits in the 'Listings' sheet
// if (sheet.getName() !== 'Listings' || col !== 1) return;
// const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// const rowValues = sheet.getRange(row, 1, 1, headers.length).getValues()[0];
// /**
// * Helper: resolve a fuzzy header key (substring) to a 0-based column index,
// * scanning headers each time (no caching).
// *
// * @param {string} key Substring to match within the header (case-insensitive)
// * @returns {number|null} 0-based index of matching header, or null if none.
// */
// function resolveKeyToIndex(key) {
// const lowerKey = key.toLowerCase();
// for (let i = 0; i < headers.length; i++) {
// const h = headers[i];
// if (h && h.toLowerCase().includes(lowerKey)) {
// // Found matching header at column i (0-based)
// return i;
// }
// }
// return null;
// }
// /**
// * Helper: for a group of fuzzy header keys and one default value,
// * finds which columns match those keys, picks only those blank in this row,
// * collects their A1 notations, and calls RangeList.setValue on them.
// *
// * @param {string[]} keys Array of fuzzy header substrings (case-insensitive match)
// * @param {string} defaultVal Value to set into all matching blank cells
// */
// function applyDefaultsForKeys(keys, defaultVal) {
// const a1List = [];
// keys.forEach(key => {
// const idx = resolveKeyToIndex(key);
// if (idx != null && !rowValues[idx]) {
// // Found matching header at column idx (0-based) AND it's blank → push its A1 notation
// a1List.push(sheet.getRange(row, idx + 1).getA1Notation());
// }
// });
// if (a1List.length > 0) {
// sheet.getRangeList(a1List).setValue(defaultVal);
// }
// }
// // Now call the helper once for each default group:
// applyDefaultsForKeys(
// [
// 'Property Type',
// 'Bedrooms / Study',
// 'Living Area Space',
// 'Bathrooms',
// 'Floorplan',
// 'Complies with Minimum Ceiling Insulation Standard',
// 'NBN Connection Type',
// 'Solar',
// 'Aircon',
// 'Heating',
// 'Hot Water',
// 'Stove',
// 'Dishwasher',
// 'Garage'
// ],
// 'TODO'
// );
// applyDefaultsForKeys(
// ['Status', 'Decision Based On Comments'],
// 'Waiting on comments from housemates'
// );
// }v1:
// // v1 - Naive, slow, updates columns individually
//
// function onEdit(e) {
// const sheet = e.range.getSheet();
// const row = e.range.getRow();
// const col = e.range.getColumn();
// // Only run when editing column A (1) on the 'Listings' sheet
// if (sheet.getName() !== 'Listings' || col !== 1) return;
// // Read the header row (assumed to be row 1)
// const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// // Helper: find column by partial header name (case-insensitive)
// function findHeader(substring) {
// const lower = substring.toLowerCase();
// const index = headers.findIndex(h => h && h.toLowerCase().includes(lower));
// return index >= 0 ? index + 1 : null;
// }
// // Helper: set default value if blank
// function setDefaultIfBlank(headerSubstring, value) {
// const colIndex = findHeader(headerSubstring);
// if (!colIndex) return; // skip if header not found
// const cell = sheet.getRange(row, colIndex);
// if (cell.isBlank()) cell.setValue(typeof value === 'function' ? value() : value);
// }
// // -------------------------
// // 🧩 Default Values Section
// // -------------------------
// // Simple "TODO" defaults
// [
// 'Property Type',
// 'Bedrooms',
// 'Living Area Space',
// 'Bathrooms',
// 'Floorplan',
// 'Complies with Minimum Ceiling Insulation Standard',
// 'NBN Connection Type',
// 'Solar',
// 'Aircon',
// 'Heating',
// 'Hot Water',
// 'Stove',
// 'Dishwasher',
// 'Garage'
// ].forEach(h => setDefaultIfBlank(h, 'TODO'));
// // Complex defaults
// [
// 'Status',
// 'Decision Based On Comments'
// ].forEach(h => setDefaultIfBlank(h, 'Waiting on comments from housemates'));
// }