Is this possible, like you can in other spreadsheet software to do something like this? (color scales)
Try conditional highlighting - this doesn't provide quite that high level of automation, as you have to add rules one-by-one - but you can automate values with all sorts of controls. Find the main menu in Format -> Cell -> Conditional Highlighting...
Here is the main menu:
You have options such as: Numbers | Text | Date | Duration | Blank
Hope this helps :-)