How to refresh the results of custom formulas automatically in Google Sheets?

0
votes

I do have simple custom function margin() in Google Sheet (using Apps Script), which takes a value from cell H55 in the sheet named "Exchange Rates" and simply returns the same value that is in the cell.

function margin()
{     
  var price = 0;
  price = SpreadsheetApp.getActiveSpreadsheet().getRange("Exchange Rates!H55").getValue();
  return price;
};

If I put =margin() to any cell (eg. H56) - it works. But when I update the value in cell H55, say... from the number 1,05 to 1,55 - nothing happens to the value of cell H56. (Where the formula equals margin() function).

When I put the formula =margin() in any cell it calculates correctly the first time, returning 1,55. (Again - when I change the value of cell H55 to another number, the cells where my formula is are not updated).

Please, do I have to somehow run the script again? Or what can I do? (I simply tried to refresh the page, but nothing happened and I definitely don´t want to rewrite every cell with my function.) Thank you!

1
javascriptgoogle-apps-scriptgoogle-sheets
see stackoverflow.com/questions/17341399/… – Jakob F
Does this answer your question? Refresh data retrieved by a custom function in Google Sheet – ZektorH

1 Answers

0
votes

Alright, there are a couple of helpful answers here and here as per the comments.

But basically, if I understand correctly you desire your custom function margin() to get the value of a given cell (H55 in your code), ideally do something with it and return the result. (As it stands you are just returning the value of H55).

Why not have your custom function use H55 as parameter? This way when the value of H55 changes, all your occurrences of your custom function will be updated.

Example:

enter image description here

Sample script:

function margin(price){
  
  // Return the margin (10%)
  var margin = price*0.1;
  return margin;
};