Google Script Count based on value and color-CodePudding

In my data there are two columns, one has names and the other has actions which are color coded. I need help in generating a summary that shows for each name what is the count of colors.

Following blogpost is helpful in counting the colored cells but also need to add count based on values/names to it:

Output:

CodePudding user response：

I believe your goal is as follows.

You want to achieve your showing sample input and output situations of Google Spreadsheet.
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please set your source and destination sheet name.

function myFunction() {
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));
  const lastRow = srcSheet.getLastRow();
  const srcValues = srcSheet.getRange("A2:A"   lastRow).getValues();
  const srcColors = srcSheet.getRange("B2:B"   lastRow).getBackgrounds();
  const obj = srcValues.reduce((o, [a], i) => {
    if (o[a]) {
      o[a][srcColors[i][0]] = o[a][srcColors[i][0]] ? o[a][srcColors[i][0]]   1 : 1;
    } else {
      o[a] = { [srcColors[i][0]]: 1 };
    }
    return o;
  }, {});
  const dstValues = dstSheet.getRange("A2:A"   dstSheet.getLastRow()).getValues();
  const dstColors = [...new Set(dstSheet.getRange(1, 2, 1, dstSheet.getMaxColumns() - 1).getBackgrounds()[0])];
  const values = dstValues.map(([a]) => obj[a] ? dstColors.map(c => obj[a][c] || null) : Array(dstColors.length).fill(null));
  dstSheet.getRange(2, 2, values.length, values[0].length).setValues(values);
}

When this script is run, the values and background colors are retrieved from columns "A" and "B" of the source sheet, respectively. And, the values and background colors are also retrieved from column "A" and row 1 of the destination sheet, respectively. Using these values and background colors, the destination values are calculated. And, the destination values are put from cell "B2" of the destination sheet.

Note:

This sample script supposes your sample input and output images of the Spreadsheet. So, if your actual Spreadsheet is different from your showing sample input and output situations, this script might not be able to be used. Please be careful about this.

Sample script:

Note:

References: