1

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.

  • Questions about the right SE site for a question should be made on Meta Stack Exchange and regarding if a question is on topic on a specific site and how to ask should be made on the related meta site, in this case Web Applications Meta. Anyway, this questions about Google Sheets could be made on this site as it's a web application but also could be make on Stack Overflow when the question is about programming (writing formulas is also programming). As the question is written it could be slightly too broad and could be improved by adding more details about what your research efforts like searching the add-ons gallery. – Rubén Nov 26 '16 at 22:01
  • Th question looks better, so I upvoted it, but it's too broad to post an specific answer, so I posted a partial answer that could help you to refine your question. I think that it will help you to start writing your first (?) script, learn the Google Sheets - Google Apps Scripts argot and to find the specific class that it's required. – Rubén Nov 27 '16 at 16:31
1

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);
  }
 }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.