Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Automatically Create named ranges for each column (First row assumed to be header)
/**
* Automatically Create named ranges for each column
*
* NOTE THAT IT DESTORYS ALL OTHER NAMED RANGES ON EVERY EDIT IN EVERY SHEET
*
* Public Domain by Ori Pekelman (2017)
**/
function addNamedRanges(SS, s, number_of_sheets){
var sheet_name = s.getName();
var range = s.getRange(sheet_name+'!1:1'); // First row of current sheet
var numRows = s.getLastRow();
var numCols = range.getNumColumns();
var first_row =1;
for (var col = 1; col <= numCols; col++) {
var cell = range.getCell(first_row,col);
var columnName = cell.getValue();
var column = columnToLetter(cell.getColumn());
if (number_of_sheets > 0 ){ var prefix = sheet_name.replace(/\W/g, '') + "_"} else { var prefix = "";} // Add sheet name prefix if more than one
var named_range = prefix + columnName.replace(/\W/g, ''); // Alpha Numeric Only
if (columnName) {
var range_a1notation = column + "2:" + column + numRows;
var range_to_name = s.getRange(range_a1notation);
SS.setNamedRange(named_range, range_to_name); // update
}
}
};
function columnToLetter(column)
{
var temp, letter = '';
while (column > 0)
{
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}
function refreshNamedRanges(){
var SS = SpreadsheetApp.getActive();
var sheets = SS.getSheets();
deleteNamedRanges(SS, sheet);
for (var s in sheets){
var sheet=sheets[s];
addNamedRanges(SS, sheet, sheets.length);
}
}
function deleteNamedRanges(SS){
// The code below will delete all the named ranges in the spreadsheet.
var namedRanges = SS.getNamedRanges();
for (var i = 0; i < namedRanges.length; i++) {
namedRanges[i].remove();
}
}
function onEdit() {
var entries = [{
name : "Refresh Named Ranged",
functionName : "refreshNamedRanges"
}];
SS.addMenu("Script", entries);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.