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.

Ed Nelson · Answer 1 · 2016-12-30T22:50:58.767

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()).

score 2 · Answer 2 · answered Dec 29 '16 at 21:50

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

I wonder if you can do it as a result of an event hook by overriding onEdit perchance? That will give you more permissions according to the [Auth-Mode documentation](https://developers.google.com/apps-script/reference/script/auth-mode), but it might include the appropriate one. — Atreys, Dec 30 '16 at 01:04
@Atreys I was looking into this as well, but I wasn't able to have any luck doing so. I'm sure there is a way, but the complexity of doing so might not be worth the effort. — CodeLikeBeaker, Dec 30 '16 at 15:09
Scope restrictions can prevent code in a cell from affecting another cell, but if you move the script outside the cell you may be able to bypass the security: https://stackoverflow.com/questions/24424977/cell-coloring-in-google-spreadsheet-fails-if-it-is-called-from-a-cell-but-works — hanmari, Jan 07 '22 at 15:54

Match background color of another cell

2 Answers2

Linked