google sheets =indirect() on range of cells

问题:

I'm wondering if there is a standard function to loop over a range of cells and apply the indirect function on it. I'm trying to build a sheet to cover my expenses by store, month, and category, but as of yet it is hard to expand because I have a function that looks like

=INDIRECT(""&$B$3&"!"&concat(A12,$C$1))+INDIRECT(""&$B$4&"!"&concat(A12,$C$1))+INDIRECT(""&$B$5&"!"&concat(A12,$C$1))+INDIRECT(""&$B$6&"!"&concat(A12,$C$1))

where B3:B7 are my stores, and it would be a lot easier to read as:

=forEach(B3:B7, INDIRECT(""& B3:B7 &"!"&concat(A12,$C$1)))

or something of the like.

I've tried with arrayFormula, but that does not work in this case, as I'm not trying to apply a function on 2 same-sized ranges, but on a single range of values to find

concise question: is there an existing function, or will I have to try to code my own?

回答1:

I have found a very use-case specific solution for my specific problem. Posting for completeness, but still wondering if some else has something better.

function indirectLoop(range, cellReference) {
  var currentCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var numberOfStores = range.length;

  var total = 0;

  var debugString = "";
  var currentStore;
  var storeSheet;
  var storeCell;
  for (var i = 0; i < numberOfStores; i++) {
    currentStore = range[i];
    storeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentStore);
    storeCell = storeSheet.getRange(cellReference);

    total = total + storeCell.getValue();
    //debugString += currentStore + ":" + storeCell.getValue() + "- ";

  }
  return(total);
}