Google-sheets – Automate range name creation in Google Sheets

google sheetsgoogle-apps-script

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.

Best Answer

Partial answer

See Extending Google Sheets using Apps Script and Class namedRange

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add Student one by one", functionName: "addSheet"},
    {name: "Add Student's at once (FIFO)", functionName: "addSheets"},
    {name: "Add Student's at once (LIFO)", functionName: "addSheetsLIFO"},
    {name: "Delete Sheets", functionName: "delSheets"}
  ];
  ss.addMenu("Maintenance", menuEntries);
}

The next piece of code will create a new sheet, only if all information is entered properly. Once the information is entered, select the student that needs to be added and press Add Student:

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("This is the header");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][2]);
      ss.setActiveSheet(ss.getSheets()[0]);
      sh.getRange("D"+(row)).setValue(new Date());
    } catch(e) {
      throw 'This student allready has a sheet. Try another sheet name.';      
    }
  }
}

The third piece of code will create new sheets, based on the sheet names, press Add Student's at once (FIFO):

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [];    
  for(var i=1, len=rData.length; i<len; i++) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The fourth piece of code will create new sheets, based on the sheet names, but by itterating backwards, press Add Student's at once (LIFO):

function addSheetsLIFO() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [], i=rData.length; 
  while(i--) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The last script is a bonus to delete all the created sheets, execpt the first sheet, press Delete Sheets:

function delSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getNumSheets();

  for(var i=shs-1;i>0;i--){
    ss.setActiveSheet(ss.getSheets()[i]);
    ss.deleteActiveSheet();
  }
  ss.setActiveSheet(ss.getSheets()[0]);
  ss.getRange("D2:D").clear();
}

I've prepared an example file, to see things happening: Add Student to New Sheet.

Google-sheets – Return name of range as string in Google Sheets

That's currently not possible in Google Apps Script. See this enhancement request in the Google Apps Script issue tracker, to make that possible:

issue 4245

As mentioned in the issue itself:

To subsequent readers: If you are also interested in this requested feature, please click the star next to the issue number.

Google-sheets – Automate range name creation in Google Sheets

Best Answer

Partial answer

See also

Named Ranges

Scripts

Related Topic

Best Answer

Partial answer

See also

Named Ranges

Scripts

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

Google-sheets – Return name of range as string in Google Sheets

Related Topic