Google Sheets Script: Make Named Ranges based on Header Title for all columns-CodePudding

I am quite new to playing around in Google Apps Script. I need a bit of help in making a dynamic formula that creates Named Ranges based on the name of the Header of the column for all columns. The range of the Named Range will be [column]2:[column] - that is, all values in that column except in Row 1. I need named ranges in order to make dependent data validation lists.

For some context, this script starts with an importrange that I copy/paste without formatting so I can set up these named ranges.

//this function pulls header values for other scripts below
function getHeaderValues() {
  return SpreadsheetApp.getActiveSheet().getRange('1:1').getValues();
}

...

 function groupnamedranges() {
  const spreadsheet = SpreadsheetApp.getActive();
  const col = spreadsheet.getRange('A:N').getColumn();

//this is to convert importrange
      spreadsheet.getRange('A:N').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); 

//this is to make named ranges for all columns based on header name
      spreadsheet.setNamedRange([getHeaderValues()], spreadsheet.getRange(2, col));
        };

How far off am I?

(P.S. All of my headers are acceptable names for named ranges - already checked for this).

CodePudding user response：

Makes named ranges from column headers

function headerNamedRanges() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getDisplayValues()[0];
  hA.forEach((h, i) => {
    let rg = sh.getRange(2, i   1, sh.getLastRow() - 1, 1).activate();
    Logger.log(h);
    ss.setNamedRange(h, ss.getActiveRange());
  });
}