1

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.

1

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.

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.