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, tryN9:N11
.Another option is to use
getRangeList(A1Notations)
whereA1Notations
is an Array ofA1
notation strings, in this case['N9','N10','N11']
but asgetRangeList
returns a Class RangeList object more changes should be made to the Vidar's code, specifically instead ofsetBackgrounds
, if the you want the same background to all the ranges you could usesetBackground(color)
(without the endings
).References
Related
Related Q/A from SO