Preventing Google Sheets from refreshing a custom formula when opening the spreadsheet?

Question

I'll begin with the specific question: Is it possible to stop function-containing cell from autorefreshing on open?

Or perhaps is it possible to apply a function (which itself calls other functions who call other functions before the actual output is produced) from Apps Script onto a cell without having a formula calling the function written onto the cell? I suppose that would update the JSON table only when the function runs from the script, without anything from the sheet being able to trigger it.

MORE INFORMATION: I have a spreadsheet that has two sheets: one pulls data from an API using ImportJSON and the other runs calculations on some of the values from the data.

The ImportJSON function is called automatically from a time-triggered function which writes the =ImportJSON formula into a cell in the first sheet. It also automatically copies the calculations in the other sheet one row below, so the calculated values are archived right before new data is pulled from the API.

The issue is that sometimes (but not always?) the =ImportJSON function, which is written into a cell, gets refreshed when I check the spreadsheet. This updates the data at an unintended interval before the appropriate data was archived.

Thank you in advance.

Answer 1

There is no way to prevent spreadsheet formulas that use a custom function from recalculating from time to time.

To work around the issue, set up a time-driven trigger to run a function like this:

function importJsonToRange() {
  const rangeToUpdate = SpreadsheetApp.getActive().getRange('Sheet1!A1:Z1');
  archiveRange(rangeToUpdate); // call the function that archives data
  const values = ImportJSON(/* many parameters here */);
  rangeToUpdate.offset(0, 0, values.length, values[0].length).setValues(values);
}

Use the time-driven trigger to control when the values get updated.

With this solution, you do not need to call ImportJSON() from a spreadsheet formula in the first place.

Answer 2

1. There is no way to stop Google Sheets / Google Apps Script to stop recalculating a custom function from recalculating it on-open on an online spreadsheet.

2. The basic alternative to custom functions to write into a single cell is to use are the Spreadsheet Service methods getRange and setValue.

3. The code on the https://github.com/bradjasper/ImportJSON besides the custom function IMPORTJSON includes other functions that are documented on the code. You might helpful to read https://blog.fastfedora.com/projects/import-json that explains some of the basics but if you found it too complex, you might have to spend some to to learn the basics about UrlFetchApp, JSON.parse and about writing into a Google Sheets spreadsheet by using the Google Apps Script Spreadsheet Service.

By the other hand, if your needs regarding importing JSON into a spreadsheet are very specific, in the short term it might be better to forget about using IMPORTJSON or and focus on learning to use UrlFetchApp to read a JSON file and to use Google Apps Script to parse a JSON. In the future project like IMPORTJSON might save you a lot of time but you should be able to understand how to use to different scenarios.