0

I'm looking for a script for my Google Sheets sheet to change the background color of a specific cell to another color if the value of this cell changes.

With changing the color, a timer starts and changes the color back to the former color after 5 seconds.

Example

As you can see, A1 = B1.

If B1's value changes, A1 gets updated, so its "value" changes. A1 should get a red background color for 5 seconds and after that change back to the former color (or another specified color). The same should happen to A2...A5.

Is this possible? This is my code so far:

    function onEdit(e){
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getRange(10,2,12,4);
  var rangee = e.range;
  rangee.setBackgroundColor("#FF6633");
  Utilities.sleep(5000);    
  rangee.setBackgroundColor("#434343");   
}

It changes the cell to FF6633 (if I comment the last line out), but doesn't switch back. If I comment the last line in, it does nothing.

Side question: does onEdit() work just on really "editing" the cell value, or does it also work with a formula in this cell, where just the "result value" changes.

If you need further information, please let me know. :)

schmidt206
  • 1
  • 1
  • Welcome to [so]. Questions seeking for recommendations, including scripts, are off-topic on this site. Suggestions start reading https://developers.google.com/apps-script/guides/sheets. If you need further help, before posting a question please read [ask]. – Rubén Jul 27 '20 at 16:38
  • Hi.. Please learn js fundamentals(Takes less than 5 hours). You can start by doing codelabs: See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. – TheMaster Jul 30 '20 at 08:06
  • The commented line should be `rangee` not `range`. `range` is not needed at all. – TheMaster Jul 30 '20 at 09:49
  • See https://stackoverflow.com/a/48832585/ – TheMaster Jul 30 '20 at 09:50
  • Does this answer your question? [Why do we use SpreadsheetApp.flush();?](https://stackoverflow.com/questions/41175326/why-do-we-use-spreadsheetapp-flush) – TheMaster Jul 30 '20 at 09:50
  • Thanks for you help @TheMaster, But I still got problems, to get the script running for me (see original post). Could you help me? Thank you very much! – schmidt206 Jul 30 '20 at 10:19
  • That question is irrelevant. I just wanted you to see my answer there: Human edits don't trigger `onEdit`. Nothing else. I rollbacked your question. – TheMaster Jul 30 '20 at 10:43
  • Yeah, I already found that about onEdit out :) Why did you rollback my whole other editing? – schmidt206 Jul 30 '20 at 10:45
  • Feel free to revert it. But the current version is much closer to solution than all that checkvalue and propertiesService, IMO. – TheMaster Jul 30 '20 at 10:49
  • Yeah, but you said "Human edits don't trigger onEdit" (I think "don't" is wrong here, because it's the only way to trigger it), so I can't use it here, because I need the function to be executed, when the cell value (result of formula) changes. – schmidt206 Jul 30 '20 at 10:51
  • Why would the result of formula change? – TheMaster Jul 30 '20 at 10:58
  • Because it gets updated on another spreadsheet. Or don't I understand your question? Like in my example, A1 = B1. If B1 changes, the result of formula in A1 (=value?) changes. – schmidt206 Jul 30 '20 at 11:01
  • So the formula changes because there was a edit by a human- just not in the cell we want. But a edit is made by a human and onEdit will trigger regardless. Right? – TheMaster Jul 30 '20 at 11:11
  • Okay - and how do I tell the function that just the cell that changes its (formula) value has to change the background color? – schmidt206 Jul 30 '20 at 12:13
  • *If I comment the last line in, it does nothing.* You need to add flush() in between. – TheMaster Jul 30 '20 at 13:11
  • *how do I tell the function that just the cell that changes its (formula) value has to change the background color* Depends. In your case, b1=a1, you just need to `.offset` the edited range by 1 column – TheMaster Jul 30 '20 at 13:13
  • Thanks! flush() really helped. I tested it with one particular cell and that worked. But I have the problem that it works on every cell now, not even in my given spreadsheet. What is wrong? I can't use offset - the cells on which A1:A5 are relating are in another spreadsheet, not like mentioned in the sample in the next column. Didn't know the relevance when posting. – schmidt206 Jul 30 '20 at 13:29
  • Ask different questions in different posts. Related: https://stackoverflow.com/a/63181442/ – TheMaster Jul 30 '20 at 21:11

0 Answers0