CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
user7935276
user7935276

Reputation: 239

How to retain cell background color when importing data into another sheet using GAS?

I'd like to ask if there is a way to retain the color (column J) of cell column if it is colored into another sheet. The code seems to work but I lost the color format in column J (red).

Here's the code I am using:

function Extract(){ 
  var ss = SpreadsheetApp.openById('SHEETID');
  var sheet = ss.getSheetByName('NAME');
  var lr = sheet.getLastRow();
  var lc = sheet.getLastColumn();
  var sheet1 = sheet.getRange(2,1,lr,lc).getValues();
  var sheetBG = sheet.getRange(2,10,lr,1).getBackgrounds();
  
  var newSheet = ss.getSheetByName('NEWSHEET');
 
  var columnJ = "10";
  var red = '#f4cccc';  
  var data = [];
  
  for (var i = 0; i <= lr - 1; i++) {
    if (sheetBG[i] == red ) {  
       data.push(sheet1[i]); 
  }}
  newSheet.getRange(2,1,data.length,17).setValues(data);  
}

Upvotes: 0

Views: 81

Answers (2)

user7935276
user7935276

Reputation: 239

function Extract(){ 
  var ss = SpreadsheetApp.openById('SHEETID');
  var sheet = ss.getSheetByName('NAME');
  var lr = sheet.getLastRow();
  var lc = sheet.getLastColumn();
  var sheet1 = sheet.getRange(2,1,lr,lc).getValues();
  var sheetBG = sheet.getRange(2,10,lr,2).getBackgrounds();
  
  var newSheet = ss.getSheetByName('NEWSHEET');
 
  var red = '#f4cccc';  
  var data = [];
  var color = [];
  for (var i = 0; i <= lr - 1; i++) {
   var columnJ = sheetBG10[i][0];
    var columnK = sheetBG10[i][1]
    if (columnJ == red && columnK == red) {
      data.push(sheetAR[i]);
      color.push(sheetBG10[i]);
    }
    if (columnJ != red && columnK == red) {
      data.push(sheet[i]);
      color.push(sheetBG[i]);
    }
    if (columnJ != red && columnK == red) {
      data.push(sheet[i]);
      color.push(sheetBG[i]);
     }
    }
   newSheet.getRange(2,1,data.length,data[0].length).setValues(data);
   newSheet.getRange(2,10,color.length,color[0].length).setBackgrounds(color);
}

Upvotes: 0

Marios
Marios

Reputation: 27348

In the same way you got the background colors you can set them in the new sheet by using setBackgrounds

newSheet.getRange(2,1,data.length,17).setValues(data);
newSheet.getRange(2,10,sheetBG.length,sheetBG[0].length).setBackgrounds(sheetBG); // new code

In case you want to copy only red values then do something like that:

var new_sheetBG=sheetBG.map(r=>r[0]==red?red:"#ffffff") // keep only red and make white the others
newSheet.getRange(2,1,data.length,17).setValues(data);
newSheet.getRange(2,10,new_sheetBG.length,new_sheetBG[0].length).setBackgrounds(new_sheetBG); // new code

Upvotes: 2

Related Questions