There are multiple ways of achieving this goal.
Custom Menu
As mentioned by @Tanaike, you can avoid the recalculation and the formula dependency by using a Custom Menu:
// @OnlyCurrentDoc
// Create a function that binds the "simple trigger" for the open event:
function onOpen(e) {
// Add a menu to the UI with the function we want to be able to invoke.
const ui = SpreadsheetApp.getUi();
ui.createMenu("Randomizer")
.addItem("Sample from 'AllValues' sheet", "sampleAllValues")
.addToUi();
}
You then need a function definition matching this name sampleAllValues, and when the user selects the associated menu option, it will be invoked with the permissions of the clicking user (the user will be prompted first to provide consent for access per the script's OAuth scopes).
function sampleAllValues() {
const wb = SpreadsheetApp.getActive();
const destination = wb.getSheetByName("RandomValues");
const source = wb.getSheetByName("AllValues");
if (!source || !destination)
throw new Error("Missing required sheets 'RandomValues' and 'AllValues'");
// Create a flat array of all non-empty values in all rows and columns of the source sheet.
const data = source.getDataRange().getValues().reduce(function (compiled, row) {
var vals = row.filter(function (val) { return val !== ""; });
if (vals.length)
Array.prototype.push.apply(compiled, vals);
return compiled;
}, []);
// Sample the smaller of 50 elements or 10% of the data, without replacement.
const sample = [];
var sampleSize = Math.min(50, Math.floor(data.length * .1));
while (sampleSize-- > 0)
{
var choice = Math.floor(Math.random() * data.length);
Array.prototype.push.apply(sample, data.splice(choice, 1));
}
// If we have any samples collected, write them to the destination sheet.
if (sample.length)
{
destination.getDataRange().clearContent();
// Write a 2D column array.
destination.getRange(1, 1, sample.length, 1)
.setValues(sample.map(function (element) { return [ element ]; }));
// Write a 2D row array
// destination.getRange(1, 1, 1, sample.length)
// .setValues( [sample] );
}
}
Custom Function
If you still wanted to use a custom function from the RandomValues sheet, e.g.
RandomValues!A1: =sampleAllValues(50, AllValues!A1:A)
then you would need to return sample instead of write to a specific sheet. Note that custom functions are treated deterministically--they are computed at the time of entry and then only recalculated when the values of their arguments change. Custom functions run with very limited scope, so be sure to review their restrictions.
The above usage hints that you might find it useful to allow passing in the number of desired samples, and the values to sample from:
function sampleAllValues(sampleSize, value2Darray) {
const data = value2Darray.reduce(function (compiled, row) {
/* as above */
}, []);
/* sample as above */
return sample; // Must be 2D row or 2D column array, or a single primitive e.g. `1`
}
No matter which route you take, be sure to review your script's error logging by viewing your script's Stackdriver logs. (View -> Stackdriver Logging)
References:
