CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Lle.4
Lle.4

Reputation: 606

Apps Script Google Sheet Change Cell Colors With Sleep

I would like to be able to show users a form of "animation" within a spreadsheet while running a script, by changing the cell colors as different actions occur within a script. For a simple example, open a blank spreadsheet and run the following:

function test()
{
  let sheet = SpreadsheetApp.getActiveSheet();
  let cell = sheet.getRange('A1');
  cell.setBackground('red');
  // Perform some action here; e.g. increment cell value
  sleep(1000);
  cell.setBackground('green');
  // Perform some other action here; e.g. multiple cell value by 2
  sleep(1000);
  cell.setBackground('white');
}


function sleep(milliseconds)
{
  const date = Date.now();
  let currentDate = null;
  do {
    currentDate = Date.now();
  } while (currentDate - date < milliseconds);
}

(I got the suggestion for the sleep function on StackOverflow.) However, when you run this, if you watch the spreadsheet while it's running, the colors don't actually change--it just sets it at the end to the final color. Is there a way to show the color flickering with these sleep functions? Is there a different sleep function I should be using? Any help would be greatly appreciated; thank you!

Upvotes: 1

Views: 236

Answers (1)

John Wells
John Wells

Reputation: 86

Google is trying to be smart and skip unnecessary read/writes. You need to tell google to update the sheet between each update.

    SpreadsheetApp.flush();
    sleep(1000)
    SpreadsheetApp.flush();

This should get your colors working.

Upvotes: 6

Related Questions