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