Google-sheets – Copying a cell’s background colour to other multiple cells via script

google sheetsgoogle-apps-script

Update (01/10/2019); code is now:

function copyFormatting() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var fromRangeGJ14 = sheet.getRange("GJ14");
    var toRangeGJ14 = sheet.getRangeList(['N9','O10','P11']);
    var backgroundsGJ14 = fromRangeGJ14.getBackgrounds();
    toRangeGJ14.setBackground(backgroundsGJ14);
} 

This works very well, but now I want to create many iterations of it in the same sheet, what would be the best way to do this? As an example, if I were to paste the script twice without any optimisation it would look like this:

function copyFormattingGJ14() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var fromRangeGJ14 = sheet.getRange("GJ14");
    var toRangeGJ14 = sheet.getRangeList(['N9','O10','P11']);
    var backgroundsGJ14 = fromRangeGJ14.getBackgrounds();
    toRangeGJ14.setBackground(backgroundsGJ14);
} 
function copyFormattingGJ15() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var fromRangeGJ15 = sheet.getRange("GJ15");
    var toRangeGJ15 = sheet.getRangeList(['N22','O23','P24']);
    var backgroundsGJ15 = fromRangeGJ15.getBackgrounds();
    toRangeGJ15.setBackground(backgroundsGJ15);
} 

As you can see the second block differs slightly to the first; is there a tidier way of organising the code as I want to essentially run this probably daily, and I don't mind typing it in manually?

Update (13/09/2019); code is now:

function copyFormatting() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var fromRangeGI14 = sheet.getRange("GI14");
    var toRangeGI14 = sheet.getRangeList(['N9','O10','P11']);
    var backgroundsGI14 = fromRangeGI14.getBackgrounds();
    toRangeGI14.setBackgrounds(backgroundsGI14);
}

However I am getting this error:

TypeError: Cannot find function setBackgrounds in object RangeList. (line 6, file "Code"

How do I alter this so that the setbackgrounds function applies the background colour from cell GI14 to cells N9, O10, and P11?

Original Post:
Going off this thread here Cell reference with colour formatting

I have modified the script from Vidar to this:

function copyValuesAndFormatting() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var fromRangeGI13 = sheet.getRange("GI14");
    var toRangeGI13 = sheet.getRange("N9,N10,N11");
    var backgroundsGI13 = fromRangeGI13.getBackgrounds();
    toRangeGI13.setBackgrounds(backgroundsGI13);
} 

I understand that line 4 here does not work (I am trying to reference a range, but I have instead put multiple cells instead of a range – this is intentional as I want to target multiple cells that may not be in a row or in order).

The goal here is copying the background's colour from Cell GI13 to Cells N9, N10, and N11; what do I have to do to the existing script here in order to achieve this effect?

My eventual goal is copying backgrounds from around a hundred cells, to thousands of individual cells, and not necessarily ranges, but individual odd cells.

Best Answer

getRange(A1Notation) requires that A1Notation be a single A1 notation reference. As the cells are contiguous, try N9:N11.

Another option is to use getRangeList(A1Notations) where A1Notations is an Array of A1 notation strings, in this case ['N9','N10','N11'] but as getRangeList returns a Class RangeList object more changes should be made to the Vidar's code, specifically instead of setBackgrounds, if the you want the same background to all the ranges you could use setBackground(color) (without the ending s).

References

Related

Related Q/A from SO