Indirectly join multiple tables into one master table

Question

I have one string that is dynamically built with existing ranges across multiple sheets. All these ranges are exactly the same structurally but with different data. I want to join them all into one master table for further processing. One example of such a string:

{04.06.2016!B8:H;18.06.2016!B8:H;09.07.2016!B8:H;15.07.2016!B8:H;24.07.2016!B8:H;30.07.2016!B8:H;06.08.2016!B8:H}

The path above is a result of the following formula:

=CONCATENATE("{";JOIN(";";FILTER(S3:S;NOT(ISBLANK(S3:S))));"}")

I want to use the result of this formula with some function like INDIRECT. The problem is that INDIRECT does not support arrays.

Is that possible?

Answer 1

I managed to solve the problem by writing the script below. One change I did is that the value no longer contains "{}" characters at the start/end of the string.

function TEST_INDIRECTS() {
  INDIRECTS("04.06.2016!B8:H;18.06.2016!B8:H;09.07.2016!B8:H;15.07.2016!B8:H;24.07.2016!B8:H;30.07.2016!B8:H;06.08.2016!B8:H");
}

function INDIRECTS(string_array) {
  ret = [];

  var sheet = SpreadsheetApp.getActiveSheet();

  items = string_array.split(";");

  for (var idx in items) {
    var item = items[idx];
    var range = sheet.getRange(item);
    var range_array = range.getValues();

    MERGE_ARRAYS(ret, range_array);
  }

  return ret;
}

function MERGE_ARRAYS(a, b) {
  for (var idx in b) {
    var item = b[idx];

    if (!IS_EMPTY_ARRAY(item)) {
      a.push(item);
    }
  }
}

function IS_EMPTY_ARRAY(item) {
  for (idx in item) {
    if(item[idx] != "") {
      return false;
    }
  }

  return true;
}