Return to Revisions

I was able to find a way to solve my problem using an Apps Script with a custom function.

If you replace =IMPORTRANGE(spreadsheet_url, range_string) in your spreadsheet with =DynamicImportRange(spreadsheet_url, sheet_name, range) and paste the code below into Tools --> Script Editor --> Blank Project, it should work (see this for more information on writing apps scripts).

/**
Usage: =DynamicImportRange(spreadsheet_url, sheet_name, range)
Compare to: =IMPORTRANGE(spreadsheet_url, range_string)
 */
function DynamicImportRange(sheet_url, sheet_name, sheet_range) {
  var values = SpreadsheetApp.openByUrl(sheet_url).getSheetByName(sheet_name).getRange(sheet_range).getValues();
  return values
};

/**
*/
function RefreshSheet() {
  // Make sure you replace "sheet_name" with the appropriate string for the sheet you want refreshed
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  var formula1 = "Loading...";
  var formula2 = sheet.getRange("A1").getFormula().toString().replace('"', '\"');
  sheet.clear();
  sheet.getRange("A1").setValue(formula1);
  Utilities.sleep(245);
  sheet.getRange("A1").setValue(formula2);
};

If you want the sheet to be updated on a regular basis, you can set up a trigger by going to Resources --> Current project's triggers in the Apps Script window.