0

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.

9
  • 1
    Instead of calling ImportJSON() as a custom function in a spreadsheet formula, call it directly from your time-driven function code, and store the 2D array you get as the return value in the spreadsheet using Range.setValues(). Nov 2, 2021 at 13:54
  • @doubleunary I thought about that, but I don't know where is the final output code written in the ImportJSON script. It has many functions calling each other and that Range.setValues() would need to sit at the very end. Also, this might require me to add additional parameters to all the functions in the chain in order to be able to call it either from a triggered auto-update function or directly from a formula written into a cell.
    – TLSO
    Nov 2, 2021 at 14:13
  • @TLSO Please read github.com/bradjasper/ImportJSON/blob/master/README.md
    – Rubén
    Nov 2, 2021 at 15:45
  • @Rubén I don't think these explanations are related to my question. Is there a way to change a cell's value within the same sheet from a custom function? I thought about making the triggered function also give some cell on the JSON sheet a value ("update", for example) and then after the ImportJSON function is called from the cell its written onto, have the ImportJSON function first check whether that cell is != "update" and return; if it is. If it is == then it will change value to "stop" and continue the function. The problem is SpreadsheetApp doesn't work from the cell-written function.
    – TLSO
    Nov 2, 2021 at 16:06
  • Please edit the question to clarify what you are asking.
    – Rubén
    Nov 2, 2021 at 16:15

2 Answers 2

3

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.

6
  • So this basically stores the values that would be received from running the ImportJSON function in the const type, and then you just post the stored values rather than the function itself into a 2D range the length of the ImportJSON data, starting from A1? And what does writing archiveRange or archiveValues do?
    – TLSO
    Nov 2, 2021 at 21:24
  • ImportJSON() is a function that returns a 2D array of values. That array can be stored in a variable. Here, we store those values in the values variable. The values variable is declared with the const keyword, because we do not intend to re-assign the variable to something else later. archiveRange() is a placeholder for your code whose purpose is that "the calculated values are archived right before new data is pulled from the API." See Apps Script at Stack Overflow. Nov 3, 2021 at 5:56
  • Understood. The archiving I did is actually just copying the existing numbers in the calculation sheet a row down, and that is done by the ImportJSONUpdate function called through the time trigger right before it also pastes the formula calling an updated ImportJSON into a cell on the first sheet. Anyway, I did find a solution for this specific situation as I added an additional row on the calculations sheet right below the row calculating current values, and made it copy right after it pastes the ImportJSON formula. I didn't realize it actually calculates the pasted formula before continuing.
    – TLSO
    Nov 3, 2021 at 7:48
  • I understand, I simply for this specific situation found the solution I mentioned earlier to be even more appropriate (copying the resulting API value-update from within the same time-triggered function right after it sends the updated =ImportJSON formula onto the cell; that way I both have a "-00:00:00" value row and the very current value row that might show real-time data updates in-between the stamped rows), so I didn't check the method from your answer to verify it. I'm giving you a vote up and if I'll use it later and see it's working well I'll give the checkmark as well. Thank you!
    – TLSO
    Nov 3, 2021 at 9:48
  • 1
    archiveRange() is a placeholder for your code ... I read the script and thought - "wow, a new AppsScript command!" and I looked at the Apps Script docs and Googled that command for about five minutes before I happened to read the comment. ;) I don't suppose it would be worth putting that explanation into the code as a remark so that others don't fall for the same trick.
    – Tedinoz
    Nov 3, 2021 at 10:55
1
  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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.