CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
TipVisor
TipVisor

Reputation: 1092

how to get cell range values with background colors in to single array?

I tried to write the script as shown below. It is necessary to combine the values and background colors in this cell range into a single array. But it does not come as needed. cell range

    function newsheet(){
  var app = SpreadsheetApp
  var ss = app.getActiveSpreadsheet().getSheetByName("AddData");

 //get category if checked
  var catList = ss.getRange("I5:J31").getValues();
  var catClrs = ss.getRange("J5:J31").getBackgrounds();
  var c = catList.map((key,index) => ({[key]:catClrs[index]}));
  Logger.log(c);
}

The answer comes this way Log result

But I want an outcome like this.

    [{Extra,true,#b6d7a8}, {Extra,false,#ffffff}, {Extra,false,#ffffff}, {Ldrain ,false,#ffffff}, {Dish Drain,true,#0c343d}, {U drain,true,#274e13}, {Hard Sholder,true,#00ffff}, {Soft Sholder,true,#cc0000}, {As. wearing,true,#ff9900}, {Tack coat Wearing,true,#fce5cd}, {As.Binder,true,#9900ff}, {Tack Coat binder,true,#46bdc6}, {Prime coat,true,#000000}, {ABC overlay,false,#ffffff}, {Subbase overlay,true,#980000}, {Edgewidning ABC,true,#999999}, {Edgewidning Subbase,false,#ffffff}, {SubGrade Ew,true,#0000ff}, {Embankment 5,true,#9fc5e8}, {Embankment 4,true,#6fa8dc}, {Embankment 3,true,#3d85c6}, {Embankment 2,true,#0b5394}, {Embankment 1,true,#1c4587}, {Subgrade Emb,true,#8e7cc3}, {Soft Ground tratment 1,true,#c27ba0}, {Soft Ground tratment 2,true,#a64d79}, {Clearing & grabbing,true,#f1c232}

Upvotes: 0

Views: 155

Answers (4)

Marios
Marios

Reputation: 27348

Nothing new compared to the accepted answer, but just shortening your code and a slighty better performance:

function newsheet(){
  const ss = SpreadsheetApp.getActive().getSheetByName("AddData");
  const range = ss.getRange("I5:J31");
  const [catList,catClrs] = [range.getValues(),range.getBackgrounds()];
  const c = catList.map((row,i) => [row[0],row[1],catClrs[0][i]]);
  Logger.log(c);
}

If you want to filter on the true values:

function newsheet(){
  const ss = SpreadsheetApp.getActive().getSheetByName("AddData");
  const range = ss.getRange("I5:J31");
  const [catList,catClrs] = [range.getValues(),range.getBackgrounds()];
  const c = catList.map((row,i) => [row[0],row[1],catClrs[0][i]]).filter(r=>r[1]);
  Logger.log(c);
}

Upvotes: 1

TipVisor
TipVisor

Reputation: 1092

Here is the code that worked correctly. In addition to what is mentioned in the question, the method of filtering is also included.

function newsheet(){
  var app = SpreadsheetApp
  var ss = app.getActiveSpreadsheet().getSheetByName("AddData");
  
//get category
  var catList = ss.getRange("I5:J31").getValues();
  var catClrs = ss.getRange("J5:J31").getBackgrounds().flat();
//merge arrays
  var c = catList.map((row,i) => [row[0],row[1],catClrs[i]]);
//filter "checked" boxes values
var fillterC = c.filter(filterLogic);    
}    

//create filter logic function
var filterLogic =function(item){
  if(item[1] === true){
    return true;
  }else{
    return false;
  }
}

Thanks to everyone who helped. I learned a lot.

Upvotes: 0

Cooper
Cooper

Reputation: 64042

I'd do it like this because it will be easier to read the values as you iterate through the array by having property labels for the object

function myobject(){
      const ss=SpreadsheetApp.getActive()
      var sh = ss.getSheetByName("AddData");
      var catList = sh.getRange("I5:J31").getValues();
      var catClrs = sh.getRange("J5:J31").getBackgrounds().flat();
      let c=[];
      catList.forEach((row,i) => {c.push({label:row[0],value:row[1],color:catClrs[i]})});
      Logger.log(JSON.stringify(c));
    }

With my fake data:

[{"label":3,"value":21,"color":"#ffffff"},{"label":0,"value":17,"color":"#ffffff"},{"label":5,"value":29,"color":"#ffffff"},{"label":29,"value":25,"color":"#ffffff"},{"label":16,"value":24,"color":"#ffffff"},{"label":17,"value":15,"color":"#ffffff"},{"label":22,"value":4,"color":"#ffffff"},{"label":6,"value":1,"color":"#ffffff"},{"label":4,"value":28,"color":"#ffffff"},{"label":14,"value":28,"color":"#ffffff"},{"label":13,"value":1,"color":"#ffffff"},{"label":2,"value":15,"color":"#ffffff"},{"label":23,"value":6,"color":"#ffffff"},{"label":14,"value":24,"color":"#ffffff"},{"label":21,"value":6,"color":"#ffffff"},{"label":12,"value":18,"color":"#ffffff"},{"label":12,"value":7,"color":"#ffffff"},{"label":5,"value":5,"color":"#ffffff"},{"label":2,"value":0,"color":"#ffffff"},{"label":16,"value":16,"color":"#ffffff"},{"label":15,"value":21,"color":"#ffffff"},{"label":0,"value":25,"color":"#ffffff"},{"label":20,"value":6,"color":"#ffffff"},{"label":24,"value":23,"color":"#ffffff"},{"label":22,"value":0,"color":"#ffffff"},{"label":14,"value":15,"color":"#ffffff"},{"label":24,"value":6,"color":"#ffffff"}]

Upvotes: 1

Wicket
Wicket

Reputation: 38130

  1. Added flat()
  2. Replaced key by row, index by i and ({[key]:catClrs[index]}) by [row[0],row[1],catClrs[i]]
function newsheet(){
  var app = SpreadsheetApp
  var ss = app.getActiveSpreadsheet().getSheetByName("AddData");

 //get category if checked
  var catList = ss.getRange("I5:J31").getValues();
  var catClrs = ss.getRange("J5:J31").getBackgrounds().flat();
  var c = catList.map((row,i) => [row[0],row[1],catClrs[i]]);
  Logger.log(c);
}

The result will be slightly different because {} is used for objects and objects requires a key and a value.

I changed key by row to make the variable name closer to what it's holding as usually a "key" is a string but in this case it's an Array holding the values of two cells in the same row. i is shorter than index.

Upvotes: 1

Related Questions