Replace ISEMPTY
with ISBLANK
Your formula should be as follows:-
=SUM(FILTER(A:A, ISBLANK(B:B)))
Now you will get the result as 40
Short answer
Instead of using IMPORTRANGE
use Google Apps Script
Explanation
According to Change a spreadsheet's locale, time zone, recalculation, and language the recalculation interval for IMPORTRANGE
is 30 mins. If shorter recalculation interval is needed then somehow the formula parameters should be changed.
Unfortunately, in the new Google Sheets is not possible to use NOW
, RAND
or RANDBETWEEN
functions, so the alternative is to use a Google Apps Script.
Explanation about the use of non-deterministic functions
In the thread provided in the comment by Jacob Jan Tuinstra it's mentioned the use of an extra URL parameter that it's updated at certain intervals. That thread is from 2011.
The core idea was to add to the URL something like "&workaround="&NOW()
and set the recalculation setting to on change and every minute
but this could be too frequent as the import task could take more than one minute, so instead of NOW()
you should round it to a good enough interval, let's say 10 minutes, then instead of NOW()
use
INT(NOW()/TIME(0;10;0))
Let name this formulas time_interval
.
The cell formula containing IMPORTRANGE
will look like this
=IMPORTRANGE(
"https://docs.google.com/spreadsheets/d/spredsheet_key&workaround="&time_interval,
"Sheet1!A1:AB"
)
Unfortunately the above formula returns the following error
Error
This function is not allowed to reference a cell with NOW(),
RAND(), or RANDBETWEEN()
The same happens with the other import functions (IMPORTDATA
, IMPORTHTML
, IMPORTFEED
, IMPORTXML
)
References
Best Answer
The bot that fetches content for
importxml
only gets the HTML source of the page, not any dynamic elements inserted in it with JavaScript. You can see what it sees by opening the page and choosing view-source (typically available with right-click). This is the relevant HTML element:There is no data here. The attributes like
dataset={parent.state.dataset}
indicate that the data will be inserted in this element with a script.importxml
cannot access it.