Only run ImportData when Google Sheet is open

Question

I have a number of Google Sheets that make heavy use of ImportData to call a REST service.

I was initially under the impression that ImportData would only call the REST service when the sheet is open and in use by a person.

It turns out, this is not the case, I can see many calls going to the REST service, even when the sheets are not open.

What could be making matters worse, is the fact that a number of these sheets were also accidentally made available offline in the Google Sheets Android app (by default, the app will make recently used sheets available offline). I have a feeling that may have made matters worse.

So my questions:

• Is there somewhere an explanation how the ImportData refreshes work?
• Is it possible to control this refresh mechanism?
• Does the Android app's "offline" functionality interfere with this mechanism?

Ideally, I would like to:

1. Put a "refresh now" button on the sheet.
2. Click the button to refresh all importdata fields.
3. As long as the button is not pressed, the fields keep displaying the old data (even when the sheet is re-opened).
4. Optionally, this should also behave well in the Android app, although this is not a "must have".

Note that this is related to this question, but the other way around. I do not want ImportData updates when the sheet is not open in some one's browser.

Answer 1

You can use Google Apps Script to

1. Create a menu with an item that runs a script to import your data

2. The script will not run in Android

P.S. Also there is the possibility for Triggers, naming a function in GAS 'onOpen' that actually only runs the function when open.

The code to import data should be 2 or 3 lines, if you are not familiar with GAS take a look here:

https://developers.google.com/apps-script/reference/spreadsheet/range#getValues() https://developers.google.com/apps-script/reference/spreadsheet/range#setValues()