-1

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!

Aerials
  • 4,231
  • 1
  • 16
  • 20
chudst
  • 139
  • 3
  • 12
  • 2
    see https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet – Jakob F Feb 11 '20 at 15:57
  • 3
    Does this answer your question? [Refresh data retrieved by a custom function in Google Sheet](https://stackoverflow.com/questions/17341399/refresh-data-retrieved-by-a-custom-function-in-google-sheet) – ZektorH Feb 11 '20 at 16:06

1 Answers1

0

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;
};
Community
  • 1
  • 1
Aerials
  • 4,231
  • 1
  • 16
  • 20
  • Hi Aerials, thanks, but this dosen´t help. I need it within the script, this was the easiest script I have. Then I have eg." if (number.search("-CZ-")>=0) {price = SpreadsheetApp.getActiveSpreadsheet().getRange("Exchange Rates!H43").getValue();} " and have the same problem. And why I don´t want to put the margin parametr, because I´m working with 80 sheets and I have no idea how many cells. I don´t want to correct everything, if I´ll need to move it with the position. I´d like to have directly in the script. – chudst Feb 13 '20 at 17:56
  • And I do caluclate with this more in the script: eg: with number search above: return price*margin; Its working great, but its not refreshing, when I use within the script: SpreadsheetApp.getActiveSpreadsheet().getRange("Exchange Rates!H43").getValue(); . Its working for the first time exactly as I need, but when I update the source, the script doesn´t react and doesn´t recalculate itself. – chudst Feb 13 '20 at 18:02
  • Ok: I do have part of the solution. function onEdit(e){SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());}. And I put there =margin($A$1). Now its working, but only in active list. Not in the all of the lists. – chudst Feb 13 '20 at 18:23
  • 1
    Try adding the info in your comments to your question. After all, it is your question that people are trying to answer. Anyways, it doesn't matter how many sheets or formulas you have, you can write a loop to change them all at once. – Aerials Feb 13 '20 at 21:37