0

I have a problem with my spreadsheet not refreshing an =IMPORTRANGE formula. I've tried all the tricks with sharing permissions and spreadsheet settings, but they don't work for me.

What I'm trying to do instead is create a simple macro that wipes the formula and re-enters it to refresh the import. When I run my macro, my formulas turns into #REF.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var startingSheet = ss.getActiveSheet();

var importRange = ss.getRangeByName("MasterImport");
var importFormulas = importRange.getFormulas();
importRange.clearContent();
importRange.setFormulas(importFormulas);

My formula in named range "MasterImport" looks like this afterwards:

=IMPORTRANGE(#REF!, index(indirect(index(#REF!, match("asset_tab", #REF!, 0))),
    match(#REF!, indirect(index(#REF!, match("series_id", #REF!, 0))), 0)))
tehhowch
  • 9,645
  • 4
  • 24
  • 42
Tommy Wu
  • 61
  • 9
  • Give an example of what the formulas should be, and also the values that are read into memory: set a breakpoint on the `importRange.clearContent()` line, debug-execute the function in the Script Editor manually, and review the variables in the "watch" section at the bottom of the page. (Edit these bits of info into your question) – tehhowch Jan 04 '19 at 16:22
  • 1
    Add a `SpreadsheetApp.flush()` after clearing content. – TheMaster Jan 04 '19 at 16:58
  • thank you @TheMaster! Worked for me. I'll add it as a self-answer for more visibility in case others are curious – Tommy Wu Jan 04 '19 at 17:15

1 Answers1

0

@TheMaster's suggestion worked perfectly. Here is the final code:

var importRange = ss.getRangeByName("MasterImport");
var importFormulas = importRange.getFormulas();
importRange.clearContent();
SpreadsheetApp.flush();
importRange.setFormulas(importFormulas);
Tommy Wu
  • 61
  • 9