title: Cross lookup in Google sheets using appscript
body: <p>Firstly, I'm pretty new to appscript.</p>
  <p>I'm trying to extract data based on color cells &amp; map with respective cell data.</p>
  <p><a href="https://docs.google.com/spreadsheets/d/12spiCq7bKaqfzTZ4tYrFFAfPyo8l4wZFtyCsZBhns7E/edit#gid=0" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/12spiCq7bKaqfzTZ4tYrFFAfPyo8l4wZFtyCsZBhns7E/edit#gid=0</a></p>
  <p>My Column A has all the dates.
  Row 1 with respective names.  Range of B2:E has other data with cells highlighted.</p>
  <p>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</p>
  <p>Lets say if the cell B4 with a message, then my output sheet should consist of B4,A4,B1 cells</p>
  <p>Here is the script i'm trying out:</p>
  <pre><code>function myFunction() {
    const srcSheetName = &quot;Source&quot;; 
    const dstSheetName = &quot;Calendar&quot;; 
    const checkColor = &quot;#ffff00&quot;; 
  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const [src, dst] = [srcSheetName, dstSheetName].map(s =&gt; ss.getSheetByName(s));
  
    const srcRange = src.getDataRange();
    const backgrounds = srcRange.getBackgrounds();
  
    const values = srcRange.getValues().reduce((ar, r, i) =&gt; {
      r.forEach((c, j) =&gt; {
        if (backgrounds[i][j] == checkColor) {
          ar.push([c]);
        }
      });
      return ar;
    }, []);
    
    dst.getRange(2, 1, values.length).setValues(values).setBackground(&quot;#FFFFFF&quot;);
  }
  </code></pre>
  <p>Please see if you can help me with this.</p>
  <p>Thanks in advance.</p>