Google Sheets – Storing Number Produced by RAND Function

google sheetsgoogle-apps-script

In cell B2 I have the formula:

=INT(RAND()*100+1)

Using a script, is there any way I can copy the current results of this RAND function into another cell on the sheet? I want to copy the absolute value, not just mirror the current value.

All attempts I've come up with cause the function to generate a new random number, store that value, then generate yet another random value in cell B2 due to the page being updated.

Ultimately I'd like to click a button that runs a script to store the current value of B2 into another cell.

Best Answer

Using a script, is there any way I can copy the current results of this RAND function into another cell on the sheet?

No, there isn't.

The following script should add the value of B2 to C2, but instead adds a new calculated value.

function freeze1(){
  var s = SpreadsheetApp.getActiveSheet();
  var v = s.getRange("B2").getValue();
  s.getRange("C2").setValue(v);
}

The following script should add the value of B2 to the active range, but it adds a new calculated value

function freeze2(){
  var s = SpreadsheetApp.getActiveSheet();
  var v = s.getRange("B2").getValue();
  var t = SpreadsheetApp.getActiveRange();
  t.setValue(v);
}

On both cases, a third value is calculated "after" the script runs.

An alternative could be the use of a script to calculate the random number instead of a built-in RANDOM() spreadsheet function. The JavaScript command is Math.random().

Related Solutions

Google-sheets – Entire sheet as a function

Yes, it's possible. From Custom Functions in Google Sheets>Guidelines for custom functions>Arguments

If you call your function with a reference to a range of cells as an argument (like =DOUBLE(A1:B10)), the argument will be a two-dimensional array of the cells' values. For example, in the screenshot below, the arguments in =DOUBLE(A1:B2) are interpreted by Apps Script as double([[1,3],[2,4]]). Note that the sample code for DOUBLE from above would need to be modified to accept an array as input.

Google Sheets – Organize Employee Time Log into a Table

This is probably best handled by a custom function. The function I wrote takes the data range (including the header row) as an argument and outputs the new table, with Ids and its header. Usage example: =inOut(A1:C20). It also checks data for consistency: the times must begin with In and then alternate between In and Out.

function inOut(arr) {
  var data = {};
  var maxLen = 0;
  for (var i = 1; i < arr.length; i++) {
    var id = arr[i][0];
    if (!data[id]) {
      data[id] = [];
    }
    var expectedType = (data[id].length % 2 ? 'Out' : 'In');
    if (arr[i][1] != expectedType) {
      throw new Error('Inconsistent data on line ' + i + ': expected ' + expectedType + ' and got ' + arr[i][1]);
    }
    data[id].push(arr[i][2]);
    if (data[id].length > maxLen) {
      maxLen = data[id].length;
    }
  }

  // End of data collection; proceed to form output

  var keys = Object.keys(data).sort();
  var headerRow = ['EmpId'];
  for (i = 0; i < maxLen; i++) {
    headerRow.push(i % 2 ? 'Out' : 'In');
  }
  var output = [headerRow];
  for (i = 0; i < keys.length; i++) {
    id = keys[i];
    var row = [id].concat(data[id]);
    while (row.length < maxLen + 1) {
      row.push('');
    }
    output.push(row);
  }
  return output;
}

Best Answer

Related Solutions

Google-sheets – Entire sheet as a function

Google Sheets – Organize Employee Time Log into a Table

Related Topic