Google App script to rename multiple name ranges according to cell values

Question

I am kinda new in GAS programming and I need help in making a script to rename multiple name ranges according to cell values

I am currently using a simple script in which I am creating a name range for a selected column, and naming the range according to a cell value.

function Group_A() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var first = ss.getSheetByName("DATA VALIDATION");
  var range = first.getRange("A1");
  var cell = range.getCell(1,1);
  var sheet = cell.getValue();

  ss.setNamedRange(sheet, ss.getRange('A2:A'));

}

The name of the range will be from the first cell of the given column. The range of the name range will be from the 2nd row to the end row for the given columns.

I need help in running this code on a loop for nNum of columns as there are more than 20 name ranges to make.

Thanks in advance

Answer 1

I believe your goal as follows.

• The 1st row is the name for using the named range.
• You want to rename the named range with the new name. The range is after the row 2 in the column.
• You want to select the columns on the sheet DATA VALIDATION.
• You want to rename the named ranges to each column of the selected columns by giving the name retrieved from the 1st row.

For this, how about this answer?

Flow:

The flow of this sample script is as follows.

1. Retrieve sheet.
2. Retrieve the 1st row values.
3. Retrieve the named ranges in the sheet and create an object.
4. Retrieve the selection.
5. Retrieve each range and rename the existing named range using the name.

Sample script 1:

In this sample script, the existing named range is renamed for the selected columns. Before you run the script, please select columns in the sheet DATA VALIDATION. And then, please run the script. By this, the named range is set for each column using the name retrieved by the 1st row.

function Group_A() {
  // 1. Retrueve sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("DATA VALIDATION");

  // 2. Retrieve the 1st row values.
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  // 3. Retrieve the named ranges in the sheet and create an object.
  const namedRangesObj = sheet.getNamedRanges().reduce((o, e) => Object.assign(o, {[e.getRange().getColumn()]: e}), {});

  // 4. Retrieve the selection.
  const selection = sheet.getSelection();

  // 5. Retrieve each range and rename the existing named range using the name.
  selection
    .getActiveRangeList()
    .getRanges()
    .forEach(r => {
      const col = r.getColumn();
      const name = headerRow[col - 1];
      if (!name) throw new Error("No headef value.");
      if (col in namedRangesObj) {
        namedRangesObj[col].setName(name);
      }
    });
}

Sample script 2:

In this sample script, the existing named range is renamed for the selected columns. And also, when the selected column is not the named range, it is set as new named range using the name retrieved from the 1st row. Before you run the script, please select columns in the sheet DATA VALIDATION. And then, please run the script. By this, the named range is set for each column using the name retrieved by the 1st row.

function Group_A() {
  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  // 1. Retrueve sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("DATA VALIDATION");

  // 2. Retrieve the 1st row values.
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  // 3. Retrieve the named ranges in the sheet and create an object.
  const namedRangesObj = sheet.getNamedRanges().reduce((o, e) => Object.assign(o, {[e.getRange().getColumn()]: e}), {});

  // 4. Retrieve the selection.
  const selection = sheet.getSelection();

  // 5. Retrieve each range and rename and set the named range using the name.
  selection
    .getActiveRangeList()
    .getRanges()
    .forEach(r => {
      const col = r.getColumn();
      const name = headerRow[col - 1];
      if (!name) throw new Error("No headef value.");
      if (col in namedRangesObj) {
        namedRangesObj[col].setName(name);
      } else {
        const colLetter = columnToLetter(col);
        ss.setNamedRange(name, sheet.getRange(`${colLetter}2:${colLetter}`));
      }
    });
}

Note:

• In these sample scripts, it supposes that each named range is one column. Please be careful this.
• Please use this script with enabling V8.

References:

• getSelection()
• Class Selection