Match background color of another cell

Question

I am using Google Spreadsheets and I am trying to do the following:

How can I get a range of cells (R2:V2) to match the background color of D2 when I change the color of cell D2.

For example: If I change the background color of D2 to red. I want cells R2 through V2 to format to the same color red.

If I change the background color of D3 to green. I want cells R3 through V3 to format to the same color green.

Answer 1

You can do this with onChange:

function onChange(e) {
   var ss=SpreadsheetApp.getActiveSpreadsheet()
   var s=ss.getActiveSheet()
   Logger.log(e.changeType);//e.changeType will equal FORMAT
   var clr1=s.getRange("D2").getBackground()
   var clr2=s.getRange("R2").getBackground()
   if(clr1 != clr2){
       var clr3=s.getRange("R2:V2").setBackground(clr1)
   }}

This well also work when D2 is changed by conditional formatting or the background color from another function (the function needs to call onChange()).

Answer 2

The short answer is you can't.

The long answer:

If you take a look at the Google Apps Script documentation for custom functions, you will see a list of all the functions that you can do.

The problem is you can only set the value, not the color of the cell. You will get a permission denied.

To show you this, look at the following script:

function colorChanger(text, cellref, colorref) {

    var sheet  = SpreadsheetApp.getActiveSpreadsheet();
    var color = sheet.getRange(colorref).getBackground();
    var cell = sheet.getRange(cellref);

    cell.setBackground(color);


    return text;

}

By theory, it should work, however the scripting doesn't support it.

When running in google-sheets you get the following:

According to their documentation they say this:

If your custom function throws the error message You do not have permission to call X service., the service requires user authorization and thus cannot be used in a custom function.

Reference: https://developers.google.com/apps-script/guides/sheets/functions