Cross lookup in Google sheets using appscript

Question

Firstly, I'm pretty new to appscript.

I'm trying to extract data based on color cells & map with respective cell data.

https://docs.google.com/spreadsheets/d/12spiCq7bKaqfzTZ4tYrFFAfPyo8l4wZFtyCsZBhns7E/edit#gid=0

My Column A has all the dates. Row 1 with respective names. Range of B2:E has other data with cells highlighted.

I'm able to run appscript to get the coloured cell value in my destination sheet. However, I'm trying for other information the associated highlighted cell

Lets say if the cell B4 with a message, then my output sheet should consist of B4,A4,B1 cells

Here is the script i'm trying out:

function myFunction() {
  const srcSheetName = "Source"; 
  const dstSheetName = "Calendar"; 
  const checkColor = "#ffff00"; 

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));

  const srcRange = src.getDataRange();
  const backgrounds = srcRange.getBackgrounds();

  const values = srcRange.getValues().reduce((ar, r, i) => {
    r.forEach((c, j) => {
      if (backgrounds[i][j] == checkColor) {
        ar.push([c]);
      }
    });
    return ar;
  }, []);
  
  dst.getRange(2, 1, values.length).setValues(values).setBackground("#FFFFFF");
}

Please see if you can help me with this.

Thanks in advance.

Answer 1

Try

function myFunction() {
  const srcSheetName = "Source";
  const dstSheetName = "Calendar";
  const checkColor = "#ffff00";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = [srcSheetName, dstSheetName].map(s => ss.getSheetByName(s));

  const srcRange = src.getDataRange();
  const backgrounds = srcRange.getBackgrounds();
  const data = srcRange.getValues()

  const result = data.reduce((ar, r, i) => {
    r.forEach((c, j) => {
      if (backgrounds[i][j] == checkColor) {
        ar.push([data[i][0], c, data[0][j]]);
      }
    });
    return ar;
  }, []);

  dst.getRange(2, 1, result.length, result[0].length).setValues(result)
}

input

output