Random math with google sheets

3 minute read

Recently I played my first session of Dungeons & Dragons. I enjoy RPG video games and decided to give the analog alternative tabletop a try. After learning some of the basic rules it wasn’t long before I wanted to develop something to help run gold exchanges in my fictional realm. I opted for a simple spreadsheet, but like most personal projects the scope soon expanded into making something more complex.

My goal was to create a D&D shop that could sell players potions, weapons, etc. I was using the base prices from the rules but I also wanted some random in there - to differentiate between expensive shops and your cheap traveling peddler. Normally in Google spreadsheets this would involve =RAND() or =RANDBETWEEN(). While this works beautifully, it annoyingly updates any time a new entry is added onto the spreadsheet. This wasn’t an option because tools on other sheets change frequently. Surprisingly, there is no other option from what I can tell which doesn’t update all the time.

Custom JS functions in Google sheets are fairly straightforward to produce, so I went about my initial attempt at solving the problem.

1
2
3
  function mathRandom(){
    return Math.random();
  }

Problem solved right?

Well… sort of. Calling this function does indeed return a random number. The issue lies with the fact is it now entirely static. Once a number is made it will stay there until the cache expires, whenever that is.

My next discovery were triggers. This looked very promising, one could just close and reopen the spreadsheet with the correct implementation of onOpen(e). Despite my attempts however I couldn’t get my custom function working while using the trigger. I ran into namespace issues and other problems, not fully understanding the workings of the JS integration of Google sheets and how things are called.

But where there’s a will, there’s a way.

The magic bit

If the cell containing my custom function updates somehow, then Math.random() is recalculated. As daft as it sounds, if I have my custom function in cell A1 cell like so =mathRandom(B1). When B1 updates A1 is recalculated. Even though the information in B1 is not used, the update of information is enough to trigger the function again. Every time I wanted to recalculate my random figures I could just type into the designated cell.

Neat.

One last idea was to create a GUI element which would capture the current datetime and paste it into my designated cell. This would trigger an update without having to type something in there manually.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
  // Using the onOpen trigger
  function onOpen(){
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var entries = [{
      name : "Regenerate Shop",
      functionName : "regenerateShop"
    }];

    // Add the GUI menu option
    sheet.addMenu("Regenerate Shop", entries);
  }

  function regenerateShop() {
    // paste the date into cell
    SpreadsheetApp.getActiveSpreadsheet().getRange('E1').setValue(new Date().toTimeString());
  }

This does the trick, however with a huge number of calls at once sometimes Google doesn’t like that and throws an error. A minor issue for me in this case because I simply do not need to make so many requests.

In conclusion

Don’t expect simple projects to go smoothly even though they can be small. Also don’t lose two days of free time making D&D item shops.