Google sheet cell recalculation

Question

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?

Answer 1

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