0

I run a fitness challenge for my company out of google sheets. Each team gets their own spread sheet and I have one that aggregates all of the data. One of my sheets is driven by this line :

={IMPORTRANGE(Constants!B11,"TeamStats!A17:N"&(16+Constants!D11));
  IMPORTRANGE(Constants!B12,"TeamStats!A17:N"&(16+Constants!D12));
  IMPORTRANGE(Constants!B13,"TeamStats!A17:N"&(16+Constants!D13));
  IMPORTRANGE(Constants!B14,"TeamStats!A17:N"&(16+Constants!D14)); 
  IMPORTRANGE(Constants!B15,"TeamStats!A17:N"&(16+Constants!D15));
  IMPORTRANGE(Constants!B16,"TeamStats!A17:N"&(16+Constants!D16))}

This feels really inelegant and clunky as each time I add a team I need to copy+paste to the end of the line and change to the next number. Is there a better way for me to import these ranges?

NightEye
  • 10,634
  • 2
  • 5
  • 24
Hovestar
  • 1,544
  • 4
  • 18
  • 26
  • As far as I know the only way is to use scripts for this as IMPORTRANGE does not work with arrayformula. See here: https://stackoverflow.com/questions/38924637/importrange-from-multiple-google-spread-sheets – Krzysztof Dołęgowski Mar 08 '21 at 15:03
  • Could you paste/share what a sample team sheet looks like in columns A through N? I think I can demo an elegant way I've come up with to handle these probelms for relatively small datasets which it sounds like you have. Here's a blank sheet. https://docs.google.com/spreadsheets/d/1X3ZvDR3-zbvQIM2VhwJmgGd73Hb7Z5cn0CbCZ9B4GVk/edit – MattKing Mar 08 '21 at 23:25

1 Answers1

1

As per Krysztof's comment, IMPORTRANGE isn't allowed ARRAYFORMULA (which could actually optimize your formula) due to possible restriction on each sheet.

I created an Apps Script that can actually update the formula for you automatically if you are open for it as a solution:

Code:

function onEdit(e) {
  if(e.range.getColumn() == 2 && e.range.getRow() >= 11){
    // URL of spreadsheet containing Constants Sheet 
    var constantsURL = '<constants spreadsheet url>';
    // Access "Constants" sheet specifically
    var constantsSheet = SpreadsheetApp.openByUrl(constantsURL).getSheetByName("Constants");
    // Get last row of whole sheet
    var lastRow = constantsSheet.getLastRow();
    // get all urls in Constants sheet from range column B row 11 until last row of the sheet
    var sheetURLs = constantsSheet.getRange("B11:B" + lastRow).getValues();
    var data = [];

    sheetURLs.forEach(function(url, index) {
      // Per data found in column B, we add the formula IMPORTRANGE(Constants!B11,"TeamStats!A17:N"&(16+Constants!D11))
      // Incrementing the numbers 11  one at a time per row
      data.push("IMPORTRANGE(Constants!B" + (11 + index) + ",\"TeamStats!A17:N\"&(16+Constants!D" + (11 + index) + "))");
    });

    // Combine all data and make into formula 
    var formula = "={" + data.join(";\n") + "}";
    // Apply the formula to a specific cell you want to
    // Below is A1 for example
    SpreadsheetApp.getActiveSheet().getRange("A1").setFormula(formula);
  }
}

Sample Output:

output

Note:

  • Script is triggered every time you edit a value on column B11 and below (add, remove or edit value)
  • Make sure to install the onEdit under Triggers as a simple trigger will fail due to permission
  • Removing the last row will also remove the last IMPORTRANGE in the dynamic formula
  • You can further optimize the code for your own conditions but this for now will work on your case (in which you append rows most of the time I assume)
NightEye
  • 10,634
  • 2
  • 5
  • 24