Google-sheets – way to use IMPORTRANGE within an array formula and for this to be dynamic

google sheetsgoogle-apps-scriptimportrange

I'm trying to pull spreadsheet IDs from another sheet's range with equal dimensions, and then using those to pull data from spreadsheets with those IDs. I want to use an array formula to auto fill a column with the data I need with the following criteria:

  • If the spreadsheet ID is empty, return "ID missing"
  • Update the values as they change in their parent spreadsheets

So let's say I have a spreadsheet like this:

  \ __A__|__B__|__C__
_1_|_ID1_|_____|_____
_2_|_ID2_|_____|_____
_3_|_ID3_|_____|_____

Each of those IDs lead to a spreadsheet like this:

  \ __A__|__B__|__C__
_1_|DATE1|_____|_____
_2_|DATE2|_____|_____
_3_|DATE3|_____|_____

I will be importing the ID spreadsheet's column A from another sheet, and then running IMPORTRANGE on the imported range to get the dates. I tried the following formula (filler arguments for IMPORTRANGE):

    =ARRAYFORMULA(IF(ISBLANK(IMPORTRANGE(ABC, DEF)), "ID missing", IMPORTRANGE(IMPORTRANGE(ABC, DEF), GHI)))

All the fields with missing IDs are filled properly. However, the ones with IDs all populate with the same value (the first value returned by IMPORTRANGE, to be exact). It's as if they are only being applied to the very first cell over and over again. I have tried INDEX, VLOOKUP, OFFSET, QUERY, but it seems that none of them work with ARRAYFORMULA.

At the moment, I have resorted to using a custom function and a rather odd combination of arguments to pull the relevant data (I need to find the most recent date in each of the ranges). The custom function in Google Apps Script is as follows:

/**
 * Imports a range from each spreadsheet given an array of spreadsheet IDs
 *
 * @param {range} IDs An array of spreadsheet IDs
 * @param {string} Range The range to import from each spreadsheet
 * @param {[string]} JavaScript Optional JavaScript code to run on the returned values
 * @return The desired range
 * @customfunction
 */
function IMPORTRANGES(IDs, Range, JavaScript) {
  return IDs.map(function (ID) {
    if (ID[0]) {
      var Values = SpreadsheetApp.openById(ID[0]).getRange(Range).getValues();
      return JavaScript ? function () { return eval(JavaScript); }.call(Values) : Values;
    }
  });
}

And my formula changes to:

=ARRAYFORMULA(IF(ISBLANK(IMPORTRANGE(ABC, DEF)), "Log missing", IMPORTRANGES(IMPORTRANGE(ABC, DEF), "Dates", "new Date(Math.max.apply(null, this.filter(function () { return arguments[0][0] instanceof Date; }).map(function () { return arguments[0][0]; })))")))

It works perfectly fine now, but the problem is that since all the processing is done via a custom function, the results are cached and won't update automatically to reflect changes in their respective spreadsheets, which was one of the criteria I had.

Is there really no way to use IMPORTRANGE with an array formula?

I would really like to avoid using an installable GAS trigger, if possible.

Best Answer

There is really no way to use IMPORTRANGE within an array formula as well there isn't for IMPORTDATA and IMPORTXML. See my answer to Google Sheets: How can I combine multiple ImportXML results for multiple URLs into a single column?.

Another related question:

Related Topic