Google sheet cell is only writen once instead of multiple times-CodePudding

I would like to make a visual random number genarator. That means in Cell F4 should random numbers shuffle trough until it stops with a random number. My Problem is now, that i can only see the last number generated and not the shuffeling.

function Wuerfeln() {

  var Upperbound = 60;
  var Lowerbound = 1;
  var i = Math.floor(Math.random() * (50 - 10   1)   10);
  var RandomNummber = 0;
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange("F4");

  while(i>0)
  {
    RandomNummber = Math.floor(Math.random() * (Upperbound - Lowerbound   1)   Lowerbound);
    range.setValue(RandomNummber);
    i--;
    Utilities.sleep(200);
  };
};

CodePudding user response：

I would suggest you define a few things and then try it out until it looks like you want, or what you could do is create a pop up which shows a .gif of a dice.

The first approach would look like this

function wurfel(){
   const timeDelay = 200
   const numbersToShowUntilFinal = 20 // or do a random num

   // then your code here, 
   // but using the variables above to test around until you found a cool 

}

CodePudding user response：

Google Sheets has a minimum refersh time, you're not going to make it change super fast by design.

Many requests from a script get batched together and run at the same time, so when the edits are all in the same cell you do not see them change and only the last one is displayed.

The only way to force a cell to change is using SpreadsheetApp.flush() as suggested by Sergey. This forces all pending sheet changes to be pushed. As you have noted however, this is slow.

To do this at all you would need to have an HTML dialog or sidebar which can run JavaScript that directly changes the HTML elements rather than a cell in the spreadsheet.

I know this is generally bad news, but I hope this is helpful to you!