Bwrgallagher Bwrgallagher - 1 month ago 5
Javascript Question

How can I change multiple ranges of cells background color with a script?

First I am fairly new to JS. I have learned a bunch of it back in college and now I am brushing the dust off to get back at it.

In a previous formula I was able to clear multiple ranges of cells back to nothing with the push of a button. I now want to take a derivative of that formula and change the cells that are within the aforementioned ranges to a different background color.

Here is the "clear" formula:



function clear1() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Main Board');
var rangesToClear = ['D25:I58','D62:I114','D118:I123','D127:I142','D146:I157','D161:I173','D179:I237','D241:I253','D257:I272','D276:I289','D295:I302','D306:I328','D332:I345','D349:I356','D362:I371','D375:I397','D401:I402','D409:I424','D428:I428','D434:I448','D452:I471','D475:I514','D518:I536','D540:I582','D586:I593','D597:I605','D611:I628','D632:I641','D645:I653','D657:I666','D672:I681','D685:I707','D711:I721','D727:I752','D756:I762','D766:I772','D778:I793','D797:I805','D809:I817','D823:I840','D844:I851','D857:I908','D912:I912','D916:I944','D948:I956','D960:I981','D985:I1004','D1010:I1031','D1035:I1051','D1056:I1064','D1069:I1079','D1083:I1094','D1098:I1110'];
for (var i=0; i<rangesToClear.length; i++) {
sheet.getRange(rangesToClear[i]).clearContent();
}
}





I do not completely understand this formula as I was helped to make it. Here is my newer rendition to change the background of the cells. What I did was pare back the ranges in which I want the variables to hit.



function colorup() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Main Board');
var rangesMon = ['D25:D58','D62:D114','D118:D123','D127:D142','D146:D157','D161:D173','D179:D237','D241:D253','D257:D272','D276:D289','D295:D302','D306:D328','D332:D345','D349:D356','D362:D371','D375:D397','D401:D402','D409:D424','D428:D428','D434:D448','D452:D471','D475:D514','D518:D536','D540:D582','D586:D593','D597:D605','D611:D628','D632:D641','D645:D653','D657:D666','D672:D681','D685:D707','D711:D721','D727:D752','D756:D762','D766:D772','D778:D793','D797:D805','D809:D817','D823:D840','D844:D851','D857:D908','D912:D912','D916:D944','D948:I956','D960:D981','D985:D1004','D1010:D1031','D1035:D1051','D1056:D1064','D1069:D1079','D1083:D1094','D1098:D1110'];
var rangesTues = ['E25:E58','E62:E114','E118:E123','E127:E142','E146:E157','E161:E173','E179:E237','E241:E253','E257:E272','E276:E289','E295:E302','E306:E328','E332:E345','E349:E356','E362:E371','E375:E397','E401:E402','E409:E424','E428:E428','E434:E448','E452:E471','E475:E514','E518:E536','E540:E582','E586:E593','E597:E605','E611:E628','E632:E641','E645:E653','E657:E666','E672:E681','E685:E707','E711:E721','E727:E752','E756:E762','E766:E772','E778:E793','E797:E805','E809:E817','E823:E840','E844:E851','E857:E908','E912:E912','E916:E944','E948:E956','E960:E981','E985:E1004','E1010:E1031','E1035:E1051','E1056:E1064','E1069:E1079','E1083:E1094','E1098:E1110']
var rangesWed =['F25:F58','F62:F114','F118:F123','F127:F142','F146:F157','F161:F173','F179:F237','F241:F253','F257:F272','F276:F289','F295:F302','F306:F328','F332:F345','F349:F356','F362:F371','F375:F397','F401:F402','F409:F424','F428:F428','F434:F448','F452:F471','F475:F514','F518:F536','F540:F582','F586:F593','F597:F605','F611:F628','F632:F641','F645:F653','F657:F666','F672:F681','F685:F707','F711:F721','F727:F752','F756:F762','F766:F772','F778:F793','F797:F805','F809:F817','F823:F840','F844:F851','F857:F908','F912:F912','F916:F944','F948:F956','F960:F981','F985:F1004','F1010:F1031','F1035:F1051','F1056:F1064','F1069:F1079','F1083:F1094','F1098:F1110']
var rangesThurs =['G25:G58','G62:G114','G118:G123','G127:G142','G146:G157','G161:G173','G179:G237','G241:G253','G257:G272','G276:G289','G295:G302','G306:G328','G332:G345','G349:G356','G362:G371','G375:G397','G401:G402','G409:G424','G428:G428','G434:G448','G452:G471','G475:G514','G518:G536','G540:G582','G586:G593','G597:G605','G611:G628','G632:G641','G645:G653','G657:G666','G672:G681','G685:G707','G711:G721','G727:G752','G756:G762','G766:G772','G778:G793','G797:G805','G809:G817','G823:G840','G844:G851','G857:G908','G912:G912','G916:G944','G948:G956','G960:G981','G985:G1004','G1010:G1031','G1035:G1051','G1056:G1064','G1069:G1079','G1083:G1094','G1098:G1110']
var rangesFri = ['H25:H58','H62:H114','H118:H123','H127:H142','H146:H157','H161:H173','H179:H237','H241:H253','H257:H272','H276:H289','H295:H302','H306:H328','H332:H345','H349:H356','H362:H371','H375:H397','H401:H402','H409:H424','H428:H428','H434:H448','H452:H471','H475:H514','H518:H536','H540:H582','H586:H593','H597:H605','H611:H628','H632:H641','H645:H653','H657:H666','H672:H681','H685:H707','H711:H721','H727:H752','H76:H762','H766:H772','H778:H793','H797:H805','H809:H817','H823:H840','H844:H851','H857:H908','H912:H912','H916:H944','H948:H956','H960:H981','H985:H1004','H1010:H1031','H1035:H1051','H1056:H1064','H1069:H1079','H1083:H1094','H1098:H1110']
var rangesSat = ['I25:I58','I62:I114','I118:I123','I127:I142','I146:I157','I161:I173','I179:I237','I241:I253','I257:I272','I276:I289','I295:I302','I306:I328','I332:I345','I349:I356','I362:I371','I375:I397','I401:I402','I409:I424','I428:I428','I434:I448','I452:I471','I475:I514','I518:I536','I540:I582','I586:I593','I597:I605','I611:I628','I632:I641','I645:I653','I657:I666','I672:I681','I685:I707','I711:I721','I727:I752','I756:I762','I766:I772','I778:I793','I797:I805','I809:I817','I823:I840','I844:I851','I857:I908','I912:I912','I916:I944','I948:I956','I960:I981','I985:I1004','I1010:I1031','I1035:I1051','I1056:I1064','I1069:I1079','I1083:I1094','I1098:I1110']
for (var i=0; i<rangesMon.length; i++){
sheet.getRange(rangesMon[i]).setBackground(color)
}





As you can see I added more vars, one for each day of the week since each day needs to be a different color. Towards the end I fall short as I am unsure of exactly what needs to be added to make the color change. One other thing that I am having trouble finding is the color value in Google Sheets. Can I just point it to another cell to find the value and insert it or do I actually need to find the value?

Thanks for the help and any sort of education would be amazing in this sort because I am trying to learn a little more to stop these questions before they start.

UPDATE:Here is where I have gotten to know and it says when running colorUp that it cannot find the range on line 20 which is
sheet.getRange(rangesMon).setBackground("#f4cccc");

What I don't understand is why it's not finding the range, it's a variable at the global level.

Thanks again for the help.



function colorUp() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Main Board');
var rangesMon = ['D25:D58','D62:D114','D118:D123','D127:D142','D146:D157','D161:D173','D179:D237','D241:D253','D257:D272','D276:D289','D295:D302','D306:D328','D332:D345','D349:D356','D362:D371','D375:D397','D401:D402','D409:D424','D428:D428','D434:D448','D452:D471','D475:D514','D518:D536','D540:D582','D586:D593','D597:D605','D611:D628','D632:D641','D645:D653','D657:D666','D672:D681','D685:D707','D711:D721','D727:D752','D756:D762','D766:D772','D778:D793','D797:D805','D809:D817','D823:D840','D844:D851','D857:D908','D912:D912','D916:D944','D948:I956','D960:D981','D985:D1004','D1010:D1031','D1035:D1051','D1056:D1064','D1069:D1079','D1083:D1094','D1098:D1110'];
var rangesTues = ['E25:E58','E62:E114','E118:E123','E127:E142','E146:E157','E161:E173','E179:E237','E241:E253','E257:E272','E276:E289','E295:E302','E306:E328','E332:E345','E349:E356','E362:E371','E375:E397','E401:E402','E409:E424','E428:E428','E434:E448','E452:E471','E475:E514','E518:E536','E540:E582','E586:E593','E597:E605','E611:E628','E632:E641','E645:E653','E657:E666','E672:E681','E685:E707','E711:E721','E727:E752','E756:E762','E766:E772','E778:E793','E797:E805','E809:E817','E823:E840','E844:E851','E857:E908','E912:E912','E916:E944','E948:E956','E960:E981','E985:E1004','E1010:E1031','E1035:E1051','E1056:E1064','E1069:E1079','E1083:E1094','E1098:E1110']
var rangesWed =['F25:F58','F62:F114','F118:F123','F127:F142','F146:F157','F161:F173','F179:F237','F241:F253','F257:F272','F276:F289','F295:F302','F306:F328','F332:F345','F349:F356','F362:F371','F375:F397','F401:F402','F409:F424','F428:F428','F434:F448','F452:F471','F475:F514','F518:F536','F540:F582','F586:F593','F597:F605','F611:F628','F632:F641','F645:F653','F657:F666','F672:F681','F685:F707','F711:F721','F727:F752','F756:F762','F766:F772','F778:F793','F797:F805','F809:F817','F823:F840','F844:F851','F857:F908','F912:F912','F916:F944','F948:F956','F960:F981','F985:F1004','F1010:F1031','F1035:F1051','F1056:F1064','F1069:F1079','F1083:F1094','F1098:F1110']
var rangesThurs =['G25:G58','G62:G114','G118:G123','G127:G142','G146:G157','G161:G173','G179:G237','G241:G253','G257:G272','G276:G289','G295:G302','G306:G328','G332:G345','G349:G356','G362:G371','G375:G397','G401:G402','G409:G424','G428:G428','G434:G448','G452:G471','G475:G514','G518:G536','G540:G582','G586:G593','G597:G605','G611:G628','G632:G641','G645:G653','G657:G666','G672:G681','G685:G707','G711:G721','G727:G752','G756:G762','G766:G772','G778:G793','G797:G805','G809:G817','G823:G840','G844:G851','G857:G908','G912:G912','G916:G944','G948:G956','G960:G981','G985:G1004','G1010:G1031','G1035:G1051','G1056:G1064','G1069:G1079','G1083:G1094','G1098:G1110']
var rangesFri = ['H25:H58','H62:H114','H118:H123','H127:H142','H146:H157','H161:H173','H179:H237','H241:H253','H257:H272','H276:H289','H295:H302','H306:H328','H332:H345','H349:H356','H362:H371','H375:H397','H401:H402','H409:H424','H428:H428','H434:H448','H452:H471','H475:H514','H518:H536','H540:H582','H586:H593','H597:H605','H611:H628','H632:H641','H645:H653','H657:H666','H672:H681','H685:H707','H711:H721','H727:H752','H76:H762','H766:H772','H778:H793','H797:H805','H809:H817','H823:H840','H844:H851','H857:H908','H912:H912','H916:H944','H948:H956','H960:H981','H985:H1004','H1010:H1031','H1035:H1051','H1056:H1064','H1069:H1079','H1083:H1094','H1098:H1110']
var rangesSat = ['I25:I58','I62:I114','I118:I123','I127:I142','I146:I157','I161:I173','I179:I237','I241:I253','I257:I272','I276:I289','I295:I302','I306:I328','I332:I345','I349:I356','I362:I371','I375:I397','I401:I402','I409:I424','I428:I428','I434:I448','I452:I471','I475:I514','I518:I536','I540:I582','I586:I593','I597:I605','I611:I628','I632:I641','I645:I653','I657:I666','I672:I681','I685:I707','I711:I721','I727:I752','I756:I762','I766:I772','I778:I793','I797:I805','I809:I817','I823:I840','I844:I851','I857:I908','I912:I912','I916:I944','I948:I956','I960:I981','I985:I1004','I1010:I1031','I1035:I1051','I1056:I1064','I1069:I1079','I1083:I1094','I1098:I1110']
for (var i=0; i<rangesMon.length; i++){
sheet.getRange(rangesMon[i]).setBackground("#f4cccc")
for (var i=0; i<rangesTues.length; i++){
sheet.getRange(rangesTues[i]).setBackground("#d9ead3")
for (var i=0; i<rangesWed.length; i++){
sheet.getRange(rangesWed[i]).setBackground("#cfe2f3")
for (var i=0; i<rangesThurs.length; i++){
sheet.getRange(rangesThurs[i]).setBackground("#d5a6bd")
for (var i=0; i<rangesFri.length; i++){
sheet.getRange(rangesFri[i]).setBackground("#fff2cc")
for (var i=0; i<rangesSat.length; i++){
sheet.getRange(rangesSat[i]).setBackground("#d9d2e9")
}
}}}}}}




Answer

As someone that never used JS functions in Drive before, I found very simple to understand how it works from this question, so thank you for that :D.

When writing any function, you can use CTRL+space keys to see what commands you have available. setBackground() works with any written color like "orange", "red", "white" ... Or a color code like '#000000' to '#ffffff'.

Here is a simple example to color cells A1 to A10.

function test(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  sheet.getRange("A1:A10").setBackground('#ffff00');
}

Be sure to keep the same name in your Spreadsheet and in the Script when you call it.

Taking your example, here another way to do it:

var sheet = SpreadsheetApp.getActive().getSheetByName('Main Board');
var rangesMon = ['D25:D58','D62:D114','D118:D123','D127:D142','D146:D157','D161:D173','D179:D237','D241:D253','D257:D272','D276:D289','D295:D302','D306:D328','D332:D345','D349:D356','D362:D371','D375:D397','D401:D402','D409:D424','D428:D428','D434:D448','D452:D471','D475:D514','D518:D536','D540:D582','D586:D593','D597:D605','D611:D628','D632:D641','D645:D653','D657:D666','D672:D681','D685:D707','D711:D721','D727:D752','D756:D762','D766:D772','D778:D793','D797:D805','D809:D817','D823:D840','D844:D851','D857:D908','D912:D912','D916:D944','D948:I956','D960:D981','D985:D1004','D1010:D1031','D1035:D1051','D1056:D1064','D1069:D1079','D1083:D1094','D1098:D1110'];
var rangesTues = ['E25:E58','E62:E114','E118:E123','E127:E142','E146:E157','E161:E173','E179:E237','E241:E253','E257:E272','E276:E289','E295:E302','E306:E328','E332:E345','E349:E356','E362:E371','E375:E397','E401:E402','E409:E424','E428:E428','E434:E448','E452:E471','E475:E514','E518:E536','E540:E582','E586:E593','E597:E605','E611:E628','E632:E641','E645:E653','E657:E666','E672:E681','E685:E707','E711:E721','E727:E752','E756:E762','E766:E772','E778:E793','E797:E805','E809:E817','E823:E840','E844:E851','E857:E908','E912:E912','E916:E944','E948:E956','E960:E981','E985:E1004','E1010:E1031','E1035:E1051','E1056:E1064','E1069:E1079','E1083:E1094','E1098:E1110']
var rangesWed =['F25:F58','F62:F114','F118:F123','F127:F142','F146:F157','F161:F173','F179:F237','F241:F253','F257:F272','F276:F289','F295:F302','F306:F328','F332:F345','F349:F356','F362:F371','F375:F397','F401:F402','F409:F424','F428:F428','F434:F448','F452:F471','F475:F514','F518:F536','F540:F582','F586:F593','F597:F605','F611:F628','F632:F641','F645:F653','F657:F666','F672:F681','F685:F707','F711:F721','F727:F752','F756:F762','F766:F772','F778:F793','F797:F805','F809:F817','F823:F840','F844:F851','F857:F908','F912:F912','F916:F944','F948:F956','F960:F981','F985:F1004','F1010:F1031','F1035:F1051','F1056:F1064','F1069:F1079','F1083:F1094','F1098:F1110']
var rangesThurs =['G25:G58','G62:G114','G118:G123','G127:G142','G146:G157','G161:G173','G179:G237','G241:G253','G257:G272','G276:G289','G295:G302','G306:G328','G332:G345','G349:G356','G362:G371','G375:G397','G401:G402','G409:G424','G428:G428','G434:G448','G452:G471','G475:G514','G518:G536','G540:G582','G586:G593','G597:G605','G611:G628','G632:G641','G645:G653','G657:G666','G672:G681','G685:G707','G711:G721','G727:G752','G756:G762','G766:G772','G778:G793','G797:G805','G809:G817','G823:G840','G844:G851','G857:G908','G912:G912','G916:G944','G948:G956','G960:G981','G985:G1004','G1010:G1031','G1035:G1051','G1056:G1064','G1069:G1079','G1083:G1094','G1098:G1110'] 
var rangesFri = ['H25:H58','H62:H114','H118:H123','H127:H142','H146:H157','H161:H173','H179:H237','H241:H253','H257:H272','H276:H289','H295:H302','H306:H328','H332:H345','H349:H356','H362:H371','H375:H397','H401:H402','H409:H424','H428:H428','H434:H448','H452:H471','H475:H514','H518:H536','H540:H582','H586:H593','H597:H605','H611:H628','H632:H641','H645:H653','H657:H666','H672:H681','H685:H707','H711:H721','H727:H752','H76:H762','H766:H772','H778:H793','H797:H805','H809:H817','H823:H840','H844:H851','H857:H908','H912:H912','H916:H944','H948:H956','H960:H981','H985:H1004','H1010:H1031','H1035:H1051','H1056:H1064','H1069:H1079','H1083:H1094','H1098:H1110']
var rangesSat = ['I25:I58','I62:I114','I118:I123','I127:I142','I146:I157','I161:I173','I179:I237','I241:I253','I257:I272','I276:I289','I295:I302','I306:I328','I332:I345','I349:I356','I362:I371','I375:I397','I401:I402','I409:I424','I428:I428','I434:I448','I452:I471','I475:I514','I518:I536','I540:I582','I586:I593','I597:I605','I611:I628','I632:I641','I645:I653','I657:I666','I672:I681','I685:I707','I711:I721','I727:I752','I756:I762','I766:I772','I778:I793','I797:I805','I809:I817','I823:I840','I844:I851','I857:I908','I912:I912','I916:I944','I948:I956','I960:I981','I985:I1004','I1010:I1031','I1035:I1051','I1056:I1064','I1069:I1079','I1083:I1094','I1098:I1110']
var ranges= [rangesMon,rangesTues,rangesWed,rangesThurs,rangesFri,rangesSat];

function clear1() {
  sheet.clearFormats();
}

function colorup() {
  for(var j =0; j<ranges.length; j++){
      for (var i=0; i<ranges[j].length; i++){
        sheet.getRange(ranges[j][i]).setBackground('#ffff00');
        //if empty, print "1";
        if(sheet.getRange(ranges[j][i]).getValue() === "" ){
          sheet.getRange(ranges[j][i]).setValue(1);
        }
      }
  }
}

Added a new array called range to explain one possible way to use all ranges with the help of arrays. For every value inside the array, I check if the cell in that range is "empty", if so, the function prints a "1". I hope this helps you.