Automate range name creation in Google Sheets

Question

I am tired of meaningless spreadsheets. I want to use range names more often. It's a pain to make them by hand. My thought if each column on each sheet is given a range, then most of the pain of range creation is done. In the bulk of cases range would be defined as the rest of the column. E.g.

|  C   |
  Cost
   2.75
   3.10

Then the script/add-on would create

Rangename Cost Range C2:C

I would like a script or Google Sheet addon that would in essence

Foreach sheet {
   Foreach Column
       Create Named-Range Column-Name using Column:1 for name
  }
}

Ranges have to be globally unique. So if Cost is a column name on multiple sheets, a mechanism to make it unique needs to be devised.

• User is responsible for making unique column names. Script behaviour undefined if a duplicate is found.

• Script prepends sheetname to column name. This produces long names.

• Script prepends some abstraction of sheet name. Removal of all lower case letters comes to mind. E.g. Inv_Summary becomes I_S- This still might produce collisions.

The best option I think would be to require the script to point out collisions, and exit.

Need a way to maintain them. You add columns, rename columns. Rerun the script updates the names AND their use in formulas throughout the sheet. Internally Sheets is capable of handling a range rename. I don't know if there is an API for this.

Like a way to substitute range names for C:R references in existing sheets. I think this is a separate issue.

---

What have I tried:

A: search on Google product groups. This provided no joy.

B: google: Automatically create range names. http://www.k2e.com/tech-update/tips/418-tip-fastest-way-to-create-defined-names-in-excel This article shows how it works in excel.

Answer 1

Partial answer

See Extending Google Sheets using Apps Script and Class namedRange

See also

Named Ranges

• Google spreadsheet: named range built upon formula

Scripts

• how do I get named ranges in Google Spreadsheets to be more reliable/current?
• https://stackoverflow.com/questions/17723221/clear-not-delete-all-namedranges-in-google-spreadsheet
• https://stackoverflow.com/questions/12323961/setnamedrange-outside-of-the-spreadsheet-container

Answer 2

This is not a complete answer, just the start of my search, but I recorded a Macro of what I usually do to create a named range, and modified it to set the named range name to the cell I have selected.

PS: This macro assumes the sheet has 3234 rows.

function Createnamedrange() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getCurrentCell().activate();
  Logger.log(spreadsheet.getCurrentCell().getValue())
  spreadsheet.setNamedRange(spreadsheet.getCurrentCell().getValue(), spreadsheet.getCurrentCell().offset(1, 0, 3234, 1));
};

The important bit seems to be "spreadsheet.setNamedRange('Name_Of_Range', 'Range_Address'), that's the basis of how I would start to write a script to do this automatically for all columns that have a name in a given header row.

Here is the script that I started using this as a basis. There are a lot of unknowns that I need to research before it will work, but I think I have the basic structure commented out and the variables set.

function CreateNamedRanges() {

    var spreadsheet = SpreadsheetApp.getActive();
    var rowheader = "2" // Identify row of column headers
    var colstart // Optional: Identify start column
    var rowstart = "3" // Optional: identify row of data start
    var total_range = "?"  // Gives full range from beginning of data to last row of data in sheet (example: A2:Z)
                           // Upgrade: Set the range to only include columns where the row name is not blank. 

    for (var i=0, len=range.width; i<len; i++) {    // not sure if "width" is correct . . .

      // IF rowstart is blank, assume the rowstart is the first row after the rowheader.  Google: "How to build if statements in javascript"...
      var range = sheet.getRange(Concatenate([colstart+i],[rowstart],":",[colstart+i])); // Generate range address based on rowstart (for example, "A3:A").  Google: "How to build if statements in javascript"...
     Logger.log('Range: ' + range)
                                 var colname = "?" // Set variable of name of column header for column A to the apppropriate cell (for example, A2)
    spreadsheet.setNamedRange(colname, range);
  }
 }

Answer 3

Give this a whirl. It takes the DataRegion of your current selection (basically the contiguous used area you're in) and makes a Named Range for each column in the format sheetName_header.

function createStructuredReferences() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SpreadsheetApp.getActiveSheet();
  const re = new RegExp( '[^A-Za-z0-9]+', 'g' );
  const sheet_name = sheet.getName().replace(re,'');
  
  const rng = sheet.getActiveRange().getDataRegion();
  const dr_height = rng.getNumRows();
  const dr_width = rng.getNumColumns();
  const dr_row1 = rng.getRow();
  const dr_col1 = rng.getColumn(); 

  const headers = sheet.getRange( dr_row1, dr_col1, 1, dr_width )
  const header_names = rng.getValues()[0].map( (h) => h.replace(re,'') );

  const namedRanges = sheet.getNamedRanges();

  for ( let i = 0; i < dr_width; i++ ) {
    let name = sheet_name + "_" + header_names[i];
    let range = sheet.getRange( dr_row1 + 1, dr_col1 + i, dr_height - 1, 1 )
    
    ss.setNamedRange( name, range );
  }
};

Once you've got that, create a Named Function called AT with the argument range and the formula =INDEX( range, ROW() - ROW( range ) + 1 ). That will let you pull the current row's value for the named range you specify.

Near as I can tell, the only drawback to this system is that you have to reference the "table" name each time. Unless you want to modify the script to omit the sheet name and just avoid reusing column names.