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 missingUpdate the values as they change in their parent spreadsheetsSo 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.
Array formula and nested IMPORTRANGE
google spreadsheets;google apps script