Search code examples
google-apps-scriptgoogle-sheetssetbackground

Is there a better way to copy backgrounds from one column and set them to multiple other columns?

I have written a script for a Google spreadsheet that copies the backgrounds in one column and sets the same backgrounds to columns Q3:AB. However, the background is set to each column individually. See the script below:

function setBackgrounds(event) {
  var sheet = event.source.getActiveSheet();
  var range = event.range;

  // Check if the changed range is in Column B
  if (range.getColumn() == 2 && range.getSheet().getName() == "Sheet1") {
    var sourceColumn = sheet.getRange("B3:B");

    // Copy backrounds in Column B to target ranges

    var sourceBackgrounds = sourceColumn.getBackgrounds();
   
    sheet.getRange("Q3:Q").setBackgrounds(sourceBackgrounds);
    sheet.getRange("R3:R").setBackgrounds(sourceBackgrounds);
    sheet.getRange("S3:S").setBackgrounds(sourceBackgrounds);
    sheet.getRange("T3:T").setBackgrounds(sourceBackgrounds);
    sheet.getRange("U3:U").setBackgrounds(sourceBackgrounds);
    sheet.getRange("V3:V").setBackgrounds(sourceBackgrounds);
    sheet.getRange("W3:W").setBackgrounds(sourceBackgrounds);
    sheet.getRange("X3:X").setBackgrounds(sourceBackgrounds);
    sheet.getRange("Y3:Y").setBackgrounds(sourceBackgrounds);
    sheet.getRange("Z3:Z").setBackgrounds(sourceBackgrounds);
    sheet.getRange("AA3:AA").setBackgrounds(sourceBackgrounds);
    sheet.getRange("AB3:AB").setBackgrounds(sourceBackgrounds);
  }
}

This script works and does exactly what is needed. However, in the interests of optimization, I had tried to achieve this by copying the background from range B3:3 and setting it to range Q3:AB (instead of to each range separately) with the snippet sheet.getRange("Q3:AB").setBackgrounds(sourceBackgrounds);, but I got the following error:

Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 12. at setBackgrounds(Testing:13:29)

Surely there is a way to optimize this script so that the backgrounds are set to the whole range at once?

Solution

  • The backgrounds from B3:B is a 2 Dimensional array of 1 column [[#somecolor],[#somecolor]....].

    But since the range Q3:AB is 12 columns you need to construct an array of 12 columns per row. Here is an example of expanding the 1 column array to 12.

    Code.gs

    function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Test");
    setBackGrounds(sheet,"B3:B","Q3:AB");
  }
  catch(err) {
    console.log("Error in test: "+err)
  }
}

function setBackGrounds(sheet,fromRange,toRange) {
  try {
    let fromBackgrounds = sheet.getRange(fromRange).getBackgrounds();
    let setBackgrounds = sheet.getRange(toRange);
    let numColumns = setBackgrounds.getNumColumns();
    console.log(numColumns);
    fromBackgrounds.forEach( row => {  // row is an array of one
        row.length = numColumns;
        row.fill(row[0]);  // make row numColumns long
      }
    )
    setBackgrounds.setBackgrounds(fromBackgrounds);
  }
  catch(err) {
    console.log("Error in setBackGround: "+err);
  }
}

    Reference