0

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.

1

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

  • Thanks I'm a lot closer now, the script I am using now looks like this: function copyValuesAndFormatting() { var sheet = SpreadsheetApp.getActiveSpreadsheet(); var fromRangeGI14 = sheet.getRange("GI14"); var toRangeGI14 = sheet.getRangeList(['N9','N10','N11']); var backgroundsGI14 = fromRangeGI14.getBackgrounds(); toRangeGI14.setBackgrounds(backgroundsGI14); } <br/> I'm receiving this error though: TypeError: Cannot find function setBackgrounds in object RangeList. (line 6, file "Code") – Joshua Shenton Sep 13 at 15:50
  • @JoshuaShenton I'm sorry, the solution is simpler than using getRangeList. Instead of "N9,N10,N11" use "N9:N11" – Rubén Sep 13 at 15:56
  • Sorry I am aware of putting ranges like this, I should have clarified, that I would like to be able to reference odd cells, for example "N9, O10, P11" – Joshua Shenton Sep 13 at 15:57
  • @JoshuaShenton Please edit the question to add to it the clarification and the code that you tried. – Rubén Sep 13 at 15:58
  • That's updated now – Joshua Shenton Sep 13 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.