Hard-coded emails, folder IDs, and flags in a script are a pain to change. readNamedRangeConfig(prefix) reads every named range whose name starts with a prefix like cfg_ and returns a {key: value} object, so a Config tab (and Data > Named ranges) can hold the settings instead.
When to use
- A bound script that should not be edited just to change a recipient or flag
- Shared workbooks where operators own the settings
- Any project with a small, named set of config cells
How to use this snippet
/**
* Read named ranges starting with prefix (e.g. "cfg_") into a key/value object.
* Keys are the name with the prefix stripped (cfg_owner -> owner).
* Single-cell ranges become that cell's value.
* A single row or single column becomes a 1D array; any other block stays 2D.
*
* @param {string} prefix
* @param {{spreadsheet?: GoogleAppsScript.Spreadsheet.Spreadsheet}} [opts]
* @return {Object<string, *>}
*/
function readNamedRangeConfig(prefix, opts) {
opts = opts || {};
var ss = opts.spreadsheet || SpreadsheetApp.getActive();
var out = {};
ss.getNamedRanges().forEach(function (nr) {
var name = nr.getName();
if (prefix && name.indexOf(prefix) !== 0) return;
var key = prefix ? name.slice(prefix.length) : name;
var values = nr.getRange().getValues();
if (values.length === 1 && values[0].length === 1) {
out[key] = values[0][0];
} else if (values.length === 1) {
out[key] = values[0]; // one row
} else if (values[0].length === 1) {
out[key] = values.map(function (r) { return r[0]; }); // one column
} else {
out[key] = values; // 2D block
}
});
return out;
}
Example
function getConfig() {
// Named ranges: cfg_owner, cfg_folderId, cfg_notify (a column of emails)
return readNamedRangeConfig('cfg_');
}
Notes
- Name the ranges in Data > Named ranges, not just cells with a label beside them.
- Multi-cell ranges: one row becomes a row array, one column becomes a column array, a block stays a 2D array. A 1x1 range is the raw cell value, not
[value]. - If two ranges collapse to the same key, the later one in
getNamedRanges()wins.
Happy Coding!
