如何在Google表格中自动刷新自定义公式的结果? [英] How to refresh the results of custom formulas automatically in Google Sheets?

问题描述

我确实在Google表格中使用了简单的自定义功能margin()(使用Apps脚本)，该功能从名为汇率" 的表格的单元格H55 中获取一个值.并只返回单元格中相同的值.

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;
};

如果我将 = margin()放到任何单元格(例如H56)中，它将起作用.但是，当我更新单元格H55 中的值时，说...从数字 1,05 更改为 1,55 - 单元格H56 的值. (其中公式等于margin()函数).

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).

当我将公式=margin()放在任何单元格中时，它会在第一次正确计算时返回 1,55 . (再次-当我将单元格H55 的值更改为另一个数字时，公式为 的单元格不会更新 .)

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!

推荐答案

---

好的，有几个有用的答案 和此处.

但是，基本上，如果我理解正确，那么您希望自定义函数margin()获得给定单元格的值(代码中的H55)，最好对其进行处理并返回结果. (按现状，您只是返回H55的值.)

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).

为什么您的自定义函数不使用H55作为参数?这样，当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.

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

这篇关于如何在Google表格中自动刷新自定义公式的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！