Find matches to a value in a range and get first cell and first column values for each match google app script

Question

Currently working on code, but cant make it work.

terms: have a table where y: names, x:dates need: if there is value 'yes' in the cell, then take name and date at intersection and write it down somewhere as list (the Result below)

// -- Table:

      1 Sep 2022      2 Sep 2022    3 Sep 2022  4 Sep 2022  5 Sep 2022  6 Sep 
Name 1  yes                       yes   
Name 2                yes                       yes
Name 3                             yes                  
Name 4                                                      yes             
Name 5                                                              yes         
Name 6                     yes      
                                
// -- Result:                               
Name 1  1 Sep 2022                          
Name 1  7 Sep 2022                          
Name 2  ..                          
Name 4                              
Name 5                              
Name 6

// -- My code:

function nameAndDate () {
  var sheetTable = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('table');
  var lastRow = sheetTable.getLastRow();
  var range = sheetTable.getRange('A1:AF'+ lastRow);
  var match = 'yes'
  var values = range.getValues();
  values.forEach(function(value){
    if (value.includes(match)){
      var cellRow = sheetTable.getRange.getRow;
      var cellColumn = sheetTable.getRange.getColumn;
      var date = sheetTable.getRange(1,cellColumn).getValue();
      var name = sheetTable.getRange(cellRow,1).getValue();

      sheetTable.getRange('A'+ lastRow).setValue(name);
      sheetTable.getRange('B' + lastRow).setValue(date);
    
    }  
  })
}

will appreciate any help please.

TheMaster · Answer

Use loops with Array.map and .filter to create a new 2D array and setValues() the modified array.

const values = range.getValues(),
  out = ((i) =>
    ((data) => {
      while (++i < values.length) {
        const row = values[i];
        data.push(
          values[0]
            .filter((h, j) => row[j] === 'yes')
            .map((header) => [row[0], header])
        );
      }
      return data;
    })([]))(0).flat();
console.log(out);
sheetTable.getRange(20, 1, out.length, 2).setValues(out)

/**/console.config({maximize:true,timeStamps:false,autoScroll:false});/**/ 
const values = [
['', 'Sep 1', 'Sep 2', 'Sep 3'],
['Name 1', 'yes', '', 'yes'],
['Name 2', '', 'yes', ''],
  ],
  out = ((i) =>
((data) => {
  while (++i < values.length) {
    const row = values[i];
    data.push(
      values[0]
        .filter((h, j) => row[j] === 'yes')
        .map((header) => [row[0], header])
    );
  }
  return data;
})([]))(0).flat();
console.log(out);

Find matches to a value in a range and get first cell and first column values for each match google app script

1 Answers