Google表格单元格重新计算 [英] Google sheet cell recalculation

查看：97
本文介绍了Google表格单元格重新计算的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在创建一个棋盘游戏，因此我决定选择Google床单.我已将问题简化为一个包含一个图纸和一个脚本的最小示例.

情况

以下几点是我的骰子表:

  • 单元格 B2:C5 包含可用的骰子.第一列包含骰子小精灵，第二个逗号分隔的骰子面编号.
    • 游戏设计视角:艺术家可以更改骰子图像.设计者可以更改骰子的面数.两种类型的更改都会自动传播到引用骰子的单元格.
  • 单元格 E2:I2 和 E10:I10 包含特定的 throw .在每个投掷中， B 列中有 1至5 个骰子引用.
    • 游戏设计视角:存在许多不同的游戏项目，每个项目可能具有不同的骰子来确定动作的结果.设计人员可以添加或删除对骰子的引用，它将触发特定单元格的自动重新计算(在我们的示例中，这些单元格为 K2 和 K10 ).
  • 以 K2 和 K10 开头的单元格将DICEFACES函数的结果应用于范围 E2:I2 和 E10:I10 .
    • 游戏设计视角:骰子面孔矩阵将进一步用于计算骰子概率.为了简化示例，我将矩阵本身视为最终结果.

  • DICEFACES是我在Script Editor中创建的自定义函数 与样式表关联的文件Code.gs.它返回一个矩阵 所提供范围内的骰子所对应的骰子面.它是 正文如下:

    function DICEFACES(unused_ref_to_range_containing_dices)
{
  var app  = SpreadsheetApp;
  var spr  = app.getActiveSheet();

  // In the end this array will hold the dice faces. For example two
  // 1d6 dices would result in [[1,2,3,4,5,6],[1,2,3,4,5,6]].
  //
  var Dices = [];

  // The the formula inside the active cell (i.e. the cell on which
  // we are calling this function). This is a string like:
  //
  // "=DICEFACES(E2:I2)"
  //
  var active_formula = spr.getActiveRange().getFormula();

  // Set item_range to the one pointed to by the formula. This could
  // be a range like E2:I2.
  //
  var item_range = spr.getRange(active_formula.match(/=\w+\((.*)\)/i)[1]);

  // Loop over dice cells in the item_range.
  //
  for (var i = 1; i <= item_range.getNumColumns(); i++)
  {
    // "=B2", "=B3", ...
    //
    var dice_formula = item_range.getCell(1, i).getFormula();

    // As soon as we encounter an empty formula, we skip (i.e. there are
    // no more dices).
    //
    if (dice_formula == "")
    {
      break;
    }

    // A reference to the cell containing the dice image. We don't really
    // need the image, the dice faces are of greater importance to us.
    //
    var dice_cell = spr.getRange(dice_formula.substr(1));

    // Move one column to the right prior to the dice_cell and retreive
    // the value of the cell. This is a string like "1,2,3,4,5,6".
    //
    var dice_csv = dice_cell.offset(0, 1).getValue();

    // Convert the CSV string to a javascript array like [1,2,3,4,5,6]
    // and push it to Dices.
    //
    Dices.push(dice_csv.split(",").map(Number));
  }
  return Dices;
}

问题

问题在于，当我更改列 C 中的骰子面时，不会重新计算DICEFACE公式.在创建截图之前，我在单元格,4后缀. > C2 ，如您所见，单元格 N2 中没有4.但是，如果我或者重新保存Code.gs脚本文件或更改 E2:I2 中的骰子，则会立即进行重新计算. /p>

我很确定我知道问题出在哪里:因为我正在遍历脚本中的单元格，所以工作表应用本身看不到 C 列和 K2 和 K10 中的公式.查看我的工作表，单元格引用可能类似于:

K4  <-- E2:I2   <-- B2, B3 (C is not here)
K10 <-- E10:I10 <-- B4, B5 (C is not here)

我的符号A <-- B的含义是If there's a change in range B, update cell A.

问题

修改骰子面后，应如何更改以立即进行自动重新计算?如果不可能，那么完成我任务的最佳方法是什么?

解决方案

问题在于，当我更改列 C 中的骰子面时，不会重新计算DICEFACE公式.

DIFACE是自定义函数，当打开电子表格并且自定义函数参数值更改时，将重新计算自定义函数.

考虑到上述情况，为了最大程度地减少对自定义函数的更改，请添加第二个参数作为触发器.

更改正则表达式

/=\w+\((.*)\)/i

到

/=\w+\((.*),.*\)/i

然后通过以下方式调用您的自定义函数

=DICEFACES(E2:I2,C2)

或

=DICEFACES(E2:I2,C2:C5)

我使用了一个逗号，假设使用的是Google Sheets参数分隔符，但某些电子表格可以使用分号.

OP自定义功能的修改版本

/**
 * Returns a matrix of dice faces corresponding to the dices in the provided range.
 *
 * @param {Array} unused_ref_to_range_containing_dices Reference to range. i.e. E2:I2
 * @param {String|Number|Date|Array} ref_as_trigger Reference to a range used as trigger. i.e. C2 or C2:C5
 * @return array
 * @customfunction
 */
function DICEFACES(unused_ref_to_range_containing_dices,ref_as_trigger)
{
  var app  = SpreadsheetApp;
  var spr  = app.getActiveSheet();

  // In the end this array will hold the dice faces. For example two
  // 1d6 dices would result in [[1,2,3,4,5,6],[1,2,3,4,5,6]].
  //
  var Dices = [];

  // The the formula inside the active cell (i.e. the cell on which
  // we are calling this function). This is a string like:
  //
  // "=DICEFACES(E2:I2)"
  //
  var active_formula = spr.getActiveRange().getFormula();

  // Set item_range to the one pointed to by the formula. This could
  // be a range like E2:I2.
  //
  var item_range = spr.getRange(active_formula.match(/=\w+\((.*),.*\)/i)[1]); // CHANGED

  // Loop over dice cells in the item_range.
  //
  for (var i = 1; i <= item_range.getNumColumns(); i++)
  {
    // "=B2", "=B3", ...
    //
    var dice_formula = item_range.getCell(1, i).getFormula();

    // As soon as we encounter an empty formula, we skip (i.e. there are
    // no more dices).
    //
    if (dice_formula == "")
    {
      break;
    }

    // A reference to the cell containing the dice image. We don't really
    // need the image, the dice faces are of greater importance to us.
    //
    var dice_cell = spr.getRange(dice_formula.substr(1));

    // Move one column to the right prior to the dice_cell and retreive
    // the value of the cell. This is a string like "1,2,3,4,5,6".
    //
    var dice_csv = dice_cell.offset(0, 1).getValue();

    // Convert the CSV string to a javascript array like [1,2,3,4,5,6]
    // and push it to Dices.
    //
    Dices.push(dice_csv.split(",").map(Number));
  }
  return Dices;
}

I'm creating a board game and I have decided to choose Google sheets for that purpose. I have reduced my problem to a minimal example consisting of one sheet and one script.

Situation

Following points refer to my dice sheet:

  • Cells B2:C5 contain available dices. First column contains dice sprites, second comma-separated dice face numbers.
    • Game design perspective: The artist can change the dice images. The designer can change the dice face numbers. Both types of changes will automatically propagate to the cells which reference the dices.
  • Cells E2:I2 and E10:I10 contain particular throw. In each throw there's 1 to 5 references to dices in column B.
    • Game design perspective: There are many different game items each with possibly different dices to determine an outcome of an action. The designer can add or remove references to dices and it will trigger an automatic recalculation in particular cells (in our case these cells are K2 and K10).
  • Cells beginning at K2 and K10 hold the result of the DICEFACES function applied to the ranges E2:I2 and E10:I10.
    • Game design perspective: The dice faces matrix will further be used for calculation of dice probabilities. For simplicity of my example I'm considering the matrix itself as a final result.

  • DICEFACES is a custom function I have created in Script editor in file Code.gs associated with the stylesheet. It returns a matrix of dice faces corresponding to the dices in the provided range. It's body is following:

    function DICEFACES(unused_ref_to_range_containing_dices)
{
  var app  = SpreadsheetApp;
  var spr  = app.getActiveSheet();

  // In the end this array will hold the dice faces. For example two
  // 1d6 dices would result in [[1,2,3,4,5,6],[1,2,3,4,5,6]].
  //
  var Dices = [];

  // The the formula inside the active cell (i.e. the cell on which
  // we are calling this function). This is a string like:
  //
  // "=DICEFACES(E2:I2)"
  //
  var active_formula = spr.getActiveRange().getFormula();

  // Set item_range to the one pointed to by the formula. This could
  // be a range like E2:I2.
  //
  var item_range = spr.getRange(active_formula.match(/=\w+\((.*)\)/i)[1]);

  // Loop over dice cells in the item_range.
  //
  for (var i = 1; i <= item_range.getNumColumns(); i++)
  {
    // "=B2", "=B3", ...
    //
    var dice_formula = item_range.getCell(1, i).getFormula();

    // As soon as we encounter an empty formula, we skip (i.e. there are
    // no more dices).
    //
    if (dice_formula == "")
    {
      break;
    }

    // A reference to the cell containing the dice image. We don't really
    // need the image, the dice faces are of greater importance to us.
    //
    var dice_cell = spr.getRange(dice_formula.substr(1));

    // Move one column to the right prior to the dice_cell and retreive
    // the value of the cell. This is a string like "1,2,3,4,5,6".
    //
    var dice_csv = dice_cell.offset(0, 1).getValue();

    // Convert the CSV string to a javascript array like [1,2,3,4,5,6]
    // and push it to Dices.
    //
    Dices.push(dice_csv.split(",").map(Number));
  }
  return Dices;
}

Problem

The problem is that when I change the dice faces in column C, the DICEFACE formulas are not being recalculated. Just before I had created the screenshot I added ,4 suffix to the cell C2 and as you can see there's no 4 in cell N2. However, if I either re-save theCode.gs script file or change the dices in E2:I2, the recalculation occurs immediately.

I'm pretty sure I know where the problem is: Because I'm traversing the cells in a script, the sheet app itself does not see a reference linkage between the cells in column C and the formulas in K2 and K10. Looking at my sheet, the cell referencing is probably something like:

K4  <-- E2:I2   <-- B2, B3 (C is not here)
K10 <-- E10:I10 <-- B4, B5 (C is not here)

The meaning of my notation A <-- B is If there's a change in range B, update cell A.

Question

What should I change to make the automatic recalculation happen immediately after I modify the dice faces? And if this is not possible, what would be the best approach to accomplish my task?

解决方案

The problem is that when I change the dice faces in column C, the DICEFACE formulas are not being recalculated.

DIFACE is a custom function and custom functions are recalculated when the spreadsheet is opened and custom function arguments values change.

Considering the above, in order to minimize the changes to your custom function add a second argument to work as a trigger.

Change the regular expression

/=\w+\((.*)\)/i

to 

/=\w+\((.*),.*\)/i

Then call your custom function in the following way

=DICEFACES(E2:I2,C2)

or

=DICEFACES(E2:I2,C2:C5)

I used a comma assuming that it's the Google Sheets argument separator being used but some spreadsheets instead could use a semicolon.

Modified version of the OP custom function

/**
 * Returns a matrix of dice faces corresponding to the dices in the provided range.
 *
 * @param {Array} unused_ref_to_range_containing_dices Reference to range. i.e. E2:I2
 * @param {String|Number|Date|Array} ref_as_trigger Reference to a range used as trigger. i.e. C2 or C2:C5
 * @return array
 * @customfunction
 */
function DICEFACES(unused_ref_to_range_containing_dices,ref_as_trigger)
{
  var app  = SpreadsheetApp;
  var spr  = app.getActiveSheet();

  // In the end this array will hold the dice faces. For example two
  // 1d6 dices would result in [[1,2,3,4,5,6],[1,2,3,4,5,6]].
  //
  var Dices = [];

  // The the formula inside the active cell (i.e. the cell on which
  // we are calling this function). This is a string like:
  //
  // "=DICEFACES(E2:I2)"
  //
  var active_formula = spr.getActiveRange().getFormula();

  // Set item_range to the one pointed to by the formula. This could
  // be a range like E2:I2.
  //
  var item_range = spr.getRange(active_formula.match(/=\w+\((.*),.*\)/i)[1]); // CHANGED

  // Loop over dice cells in the item_range.
  //
  for (var i = 1; i <= item_range.getNumColumns(); i++)
  {
    // "=B2", "=B3", ...
    //
    var dice_formula = item_range.getCell(1, i).getFormula();

    // As soon as we encounter an empty formula, we skip (i.e. there are
    // no more dices).
    //
    if (dice_formula == "")
    {
      break;
    }

    // A reference to the cell containing the dice image. We don't really
    // need the image, the dice faces are of greater importance to us.
    //
    var dice_cell = spr.getRange(dice_formula.substr(1));

    // Move one column to the right prior to the dice_cell and retreive
    // the value of the cell. This is a string like "1,2,3,4,5,6".
    //
    var dice_csv = dice_cell.offset(0, 1).getValue();

    // Convert the CSV string to a javascript array like [1,2,3,4,5,6]
    // and push it to Dices.
    //
    Dices.push(dice_csv.split(",").map(Number));
  }
  return Dices;
}

这篇关于Google表格单元格重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆