CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
DiscoSoup
DiscoSoup

Reputation: 1

In a Google Sheets script can I have a function check for a random number, and then activate a specific function based on that number?

Here's the script that I'm working with:

function ExampleFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('d8').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getCurrentCell().setFormula('=rbetween(1,A22)');

In this function rbetween is a custom formula that is basically randbetween, but it's a one-time formula that creates a persistent number in its target cell. In this case it's going to create a random number between 1 and the value of a22 (which varies between 2 and 20.

I would like to have a different function activate depending on the result of the rbetween. I have functions set up to add +1 to the value of a specific cell in column B.

For instance,

function IncreaseB2() {
  SpreadsheetApp.getActiveSheet().getRange('B2').setValue(SpreadsheetApp.getActiveSheet().getRange('B2').getValue() + 1);

What do I add to ExampleFunction to have it trigger IncreaseB2 when the RBETWEEN returns a value of 1? I have 19 other functions for each cell from B3:B21, and I want to assign each formula to one value of the RBETWEEN. Thanks.

Upvotes: 0

Views: 82

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

It can be something like this:

function ExampleFunction() {
  var range = SpreadsheetApp.getActive().getRange('d8');
  range.clear().setFormula('=rbetween(1,A22)');

  SpreadsheetApp.flush(); // it supposes to refresh the value, but it could not work sometimes
  var value = range.getValue();
  console.log(value);

  switch (value) {
    case(1): IncreaseB2(); break;
    case(2): func2(); break;
    case(3): func3(); break;
    case(4): func4(); break;
    case(5): func5(); break;

    // ...etc
  }
}

function IncreaseB2() { 
  var range = SpreadsheetApp.getActive().getRange('b2');
  var value = +range.getValue(); // '+' means to convert to number, just in case
  range.setValue(++value);
}

function func2() { console.log('func2')}
function func3() { console.log('func3')}
function func4() { console.log('func4')}
function func5() { console.log('func5')}

// ...etc

But there can be the trouble with refreshing of the value inside the cell D8 due the cashing mechanism for custom functions in Google Sheets. I'd advice to populate this cell with a script, not with a custom formula. It can be done like this:

function ExampleFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var a22 = sheet.getRange('a22').getValue();
  var value = Math.ceil(Math.random() * a22);
  sheet.getRange('d8').clearContent().setValue(value);

  switch (value) {
    case(1): IncreaseB2(); break;
    case(2): func2(); break;
    case(3): func3(); break;
    case(4): func4(); break;
    case(5): func5(); break;

    // ...etc
  }
}

Upvotes: 1

Related Questions