VBA to GAS Conversion

[Matt Semple]

Hi there,

I am new to GAS and am trying to convert this bit of VBA code into GAS to apply as a custom function in sheets.  The code works wonderfully in VBA but isn't working in GAS as you could probably tell from looking at it below.  

My desired outcome is for the function to simulate a RANDBETWEEN with exclusions based on a defined array.  

eg) I have a column that includes the numbers: 5,17,19,23,45.  The function will choose a random integer between 1 and 50 but will exclude 5,17,19,23,45.  

Here is the bit of VBA.  Any tips would be greatly appreciated!

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude As Range) As Long

Dim R As Long

Dim C As Range

Do

R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))

For Each C In Exclude

If R = C Then Exit For

Next C

Loop Until C Is Nothing

RandBetweenExcl = R

Application.Volatile

End Function

[Ludovic Wagner]

Honestly, I've been struggling a lot with this kind of custom functions recently. I think that Google Sheets is quite buggy when it comes to use them (I'm currently reworking the architecture of my script to avoid this kind of functions). I use data abstraction to avoid this kind of errors.

I'm not sure if it actually comes from custom formulae, but the formulae get stuck in "Loading..." state about 1 / 4 of the time.

I give you a clue : you can't pass Excel.Range or its equivalent Google Sheet Range object to custom formulae. You need and only can work from the range values. Be careful, VBA silently calls the Value member of the Excel.Range class (default property which does not exist in JavaScript).

I would go for something like that (probably rework the break; a bit). There are also sorter syntaxes with array "extension methods" .map(), .filter(),...

function RANDBETWEENINT(lowest, highest, excludes) {
   
    var r = lowest + Math.floor(highest + 1 - lowest);
   
    for (var i in excludes) {


        for (var j in excludes[i]) {
           
            r = excludes[i][j]; // excludes corresponds the values of the range, but not the range itself
           
            break;
        }
    }
   
    return r;
}

[Martin Molloy]

This works

function getRandom(){
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
var sheet = spreadsheet.getActiveSheet()
var rows= sheet.getLastRow()
var low = sheet.getRange('B1').getValue()
var high = sheet.getRange('B2').getValue()
var exclude = sheet.getRange(1, 3, rows, 1).getValues() //array of excluded values - assumes that they are in column C starting at row 1 - values are in a 2d array
var number = randBetweenFunc(low, high, exclude)
}

function randBetweenFunc(low, high, exclude) {
  var upper = high-low
  var len = exclude.length
  var test = false  
  while (test == false) {
    var value = parseInt(Math.random()*upper+low)  //generate a number between low and high
    test = true
      // check that the random value is not in the exclusion array
      for(var i=0; i<len; i++){
        var excludeVal = exclude[i][0] // if values are in a single row, ie multiple columns then  [i]&[0] should be reversed
        if(excludeVal==value){
          test = false;  //if the random value is in the exclusion array set test to false to make the while loop run again
          break;
        }
      }//end of for loop
  } //end of while loop
  return value
}

martin

           
            r = excludes[i][j]; // excludes correspond the values of the range, but not the range itself
           
            break;
        }
    }
   
    return r;
}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3ef2e1b6-b853-4d2d-b953-3692d183d349o%40googlegroups.com.