Google-sheets – Dynamically connect Google Sheets to Excel with protection of privacy of information

When you email an Excel file you are actually sending someone a copy of the file.

To duplicate this, you would create multiple copies of the Google Spreadsheet and then share each copy.

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.