Extracting data by cell color

user_2578960 0
Viewed 102

The sheet cells contain numbers. The cells are filled with different colors. I want to sum in columns those values ​​that are colored with the same color. Here's the code that I started but didn't finish because it seemed cumbersome to me.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var bg_1 = sheet.getRange("B3").getBackground();
  var bg_2 = sheet.getRange("B4").getBackground();
  var bg_3 = sheet.getRange("B5").getBackground();
  Logger.log(bg_1); Logger.log(bg_2); Logger.log(bg_3);
  
  var ROW = 7;
  var lr = sheet.getLastRow();
  var d = lr - ROW + 1;
  
  var step_1 = sheet.getRange(ROW, 3, d).getValues().filter(String);
  var step_2 = sheet.getRange(ROW, 4, d).getValues().filter(String);
  var step_3 = sheet.getRange(ROW, 5, d).getValues().filter(String);
  var step_4 = sheet.getRange(ROW, 6, d).getValues().filter(String);
  var step_5 = sheet.getRange(ROW, 7, d).getValues().filter(String);

  var vals = sheet.getRange(ROW, 3, d, 5).getValues();
  var bgs = sheet.getRange(ROW, 3, d, 5).getBackgrounds();
  Logger.log("pause");
}

It seems to me that the MAP function can be used here. First get arrays of values ​​and colors. Then, from the array of colors, get 3 arrays for each of the three colors (with zeros and ones). Then multiply these arrays by an array of values, add the elements and insert them into 5 cells of each of the 3 lines. In theory, I understand, but in practice ... I ask for help.

The figure shows the desired result. enter image description here

Here is a link to the sheet.

google-apps-script google-sheets
1 Answers

See the solution below. I traversed each column, then used a 2D array to store the value per column, add them when background color match, then set the column color summation. After all that, proceed to next column.

Code:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  
  var bg_1 = sheet.getRange("B3").getBackground();
  var bg_2 = sheet.getRange("B4").getBackground();
  var bg_3 = sheet.getRange("B5").getBackground();
  
  // how many colors
  colors = 3
  // C3 (where the data starts)
  startingRow = 7;
  startingColumn = 3;
  // how many rows and columns is the data
  rows = 6;
  columns = 5;

  // get all data
  var data = sheet.getRange(startingRow, startingColumn, rows, columns).getValues();

  // loop the data, but we start from columns then row instead
  for(j=0;j<columns;j++){
    // reset values per column
    values = [[0], [0], [0]];
    for(i=0;i<rows;i++){
      switch(sheet.getRange(i + startingRow, j + startingColumn).getBackground()) {
        // if yellow, add to 1st index
        case bg_1:
          values[0][0] += data[i][j];
          break;
        // if green, add to 2nd index
        case bg_2:
          values[1][0] += data[i][j];
          break;
        // if blue, add to 3rd index
        case bg_3:
          values[2][0] += data[i][j];
          break;
        default:
          break;
      }
    }
    // after processing all values in column, set value of the column per color 
    sheet.getRange(startingRow - 1 - colors, j + startingColumn, colors, 1).setValues(values);
  }
}

Output:

output

user_161186860
Related