Home / Questions / 410769
Accepted
HeckDan
Asked: 2021-11-29 17:06:33 +0800 CST 2021-11-29 17:06:33 +0800 CST

Button to Generate a Random Number in a cell of a Google Spreadsheet

  • 772

Make the button with an image and assign a script to this image, ok everything is correct up to here.

I need help to generate the Script that adds the random value that is generated between a certain range to a certain cell, something like the function =ALEATORIO.ENTRE(celda1;celda2);... but this function is executed every time the page is updated, so I want to make a button so that The random number will only be generated when I hit this button.

I've been studying the Google Apps Script, but I'm still not very skilled if you can help me that would be great. So far what I am understanding is that I must proceed, I must create the function, I must indicate in the sheet that I am in this case I have placed .getActiveSpreadsheet();so that it takes the active sheet without having to put the Id of the sheet. Now I must proceed to recognize the two cells where the minimum and maximum range for which the random will be generated is; in this case cell B2 and cell B3 indicate the corresponding ones. From what I have seen, I must specify the helpers to be able to handle the sheet and its data, the sheet variable refers to the first sheet of the document (the sheet or book with index 0), now I need to place the result of the random numbers in the cell B6

function Sorteo(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Sorteo");
 var cell = sheet.getRange("B6");
 var rango_min = sheet.getRange("B2");
 var rango_max = sheet.getRange("B3");
 cell.setValue(Math.floor((Math.random()*(rango_max - rango_min)) + rango_min));
}

If so far I am doing well, how can I make this happen when I press the button?

// Edit // I ran the code without further ado, I didn't add anything to onClick()it that I thought it would need, apparently, it ran straight, but...

It has given me the following message as a result in the correct cell:#¡NÚM!

// I return to Edit // The error has been corrected after the suggestion of the user Ruben. I have given it many times to draw and it has not gone out of the limits, if someone else can verify that the draw formula is "correct" I would appreciate it. Add the function to the button and it's very nice!

google-apps-script

1 Answers

  1. Best Answer

    of the question

    So far so good...

    It is not like this. You must change

    var rango_min = sheet.getRange("B2");
var rango_max = sheet.getRange("B3");

    a

    var rango_min = sheet.getRange("B2").getValue();
var rango_max = sheet.getRange("B3").getValue();

    otherwise there will be an error on the next line

    cell.setValue(Math.floor((Math.random()*(rango_max - rango_min) + rango_min));

    because you can't do arithmetic operations having Range objects as operands

    Tip to make your scripts easier to debug: Try to avoid full expressions as method parameters like setValue, it is better to assign the complex expression to a variable and then use that variable as the above argument, for example, you could put a console.logbefore the line with the setValuefor have in the execution page the result of your complex expression.

    • 1