Google-sheets – Use the order of sheets in which they appear in a Google spreadsheet

google sheetsgoogle-apps-scriptworksheet-function

Goal

What I would like to accomplish is to have my aggregation-master-schedule-sheet (see context) put schedules in the same order that the sheets are. I.e. the left most sheet is 1st, the 2nd leftmost sheet is 2nd… from left-to-right ordered. It would be nice to drag the sheets into different orders and have the order reflected in the master-schedule.

Is there a way to either with code/gscript, or with a function/formula to return an integer value for a sheet? Or a another way to resolve this problem?

Context

I created a google-workbook for sending schedules, such that per person receiving a schedule there is a sheet dedicated to that person's schedule only. THEN, there is a sheet that aggregates all of that information into a master-schedule that my supervisors use "out in the field" from their smartphones.

Best Answer

This is possible with a custom function or a script. Here's a custom function =sheets() which returns a column of sheet names, ordered as they are currently in the spreadsheet. You can use the output of this function as an input elsewhere, e.g., in =indirect, thus forming the master sheet according to the order of the other sheets.

function sheets() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var names = [];
  for (var i=0; i<sheets.length; i++) {
    names.push(sheets[i].getName());
  } 
  return names;
}

Note that the function is not automatically recalculated as the sheets are rearranged. If this is a problem, consider a script that is invoked either from a menu, or onOpen, or onEdit, etc. It could look like this: 

function sheets(time) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var names = [];
  for (var i=0; i<sheets.length; i++) {
    if (sheets[i].getName() !== 'master') {
      names.push([sheets[i].getName()]);
    }
  } 
  var master = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('master');
  master.getRange(1, 1, names.length, 1).setValues(names);
}

Here 'master' is the name of the master sheet; note that this name is omitted from the list of sheet names. The list is placed in the first column of master sheet.

Related Topic