Return to Revisions

I was able to find a way to solve my problem (detailed here) 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() {
  // Update the following two variables to suit your particular situation
  var sheet_name = "sheet1"
  var range = "A1"
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  var formula1 = "Loading...";
  var formula2 = sheet.getRange("range").getFormula().toString().replace('"', '\"');
  // Note that the following command will delete everything on the sheet.
  // If you do not want to do this, you may need to get creative
  // with how you specify what gets deleted.  You could potentially split
  // out the last argument of "formula2" to find the range that needs cleared.
  sheet.clear();
  sheet.getRange("range").setValue(formula1);
  Utilities.sleep(245);
  sheet.getRange("range").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.