Running script for multiple search terms found on different sheet

Question

I'm trying to get a SCORESHEET to populate from a REPORTSHEET, using a REFERENCESHEET to collate search terms and destination cells.

The script I'm running is as below. The idea is that the script finds searchDate's in the REFERENCESHEET and uses them to locate data columns in the REPORTSHEET:

function superAuto() {
  var report = SpreadsheetApp.openById('REPORTSHEET');
  var reportData = report.getDataRange().getValues();

  var reference = SpreadsheetApp.openById('REFERENCESHEET');
  var referenceData = reference.getDataRange().getValues();

  var scorecard = SpreadsheetApp.openById('SCORESHEET');
  var scorecardData = scorecard.getDataRange().getValues();

  var tExpenses = "Total Expenses";

  for(n=0;n<referenceData.length;++n){
    var searchDate = referenceData[n][0] ;
    Logger.log (searchDate)
  }

  var column = columnfinder(searchDate);

  for (var a = 0; a < referenceData.length; a++) {
    var refRow = referenceData[a];
   for (var i = 0; i < reportData.length; i++) {
    var row = reportData[i];
    if (row[0] == tExpenses && refRow[0] == searchDate) {
      scorecard.getRange(refRow[5]).setValue(row[column]);
    }
   }
  }
}

function columnfinder(find) {
  var report = SpreadsheetApp.openById('REPORTSHEET');
  var reportData = report.getDataRange().getValues();

  var reference = SpreadsheetApp.openById('REFERENCESHEET');
  var referenceData = reference.getDataRange().getValues();

  
    for(var j=0, jLen=reportData.length; j<jLen; j++) {
      for(var k=0, kLen=reportData[0].length; k<kLen; k++) {
        if(find == reportData[j][k]) {
          Logger.log(k);
          return (k);}
      }
    }
  }

Broadly speaking, the code works, as if I define searchDate as one of the terms I'm looking for (e.g. Jan-21) it all works fine. The issue is that it doesn't seem to be doing so when finding multiple search terms - and therefore populating multiple rows - as per:

for(n=0;n<referenceData.length;++n){
    var searchDate = referenceData[n][0] ;
    Logger.log (searchDate)
  }

The log tells me that it's finding searchDate's in the REFERENCESHEET, but it's not able to run them through function columnfinder (I get no logs for the second logger).

I suspect the answer lay somewhere in an earlier great answer I received to an earlier version of this idea - How to return multiple column values for setValue - but I've not been able to make it fit. Any thoughts?

EDIT: Please find a sample REFERENCESHEET & REPORTSHEET for more info:

question from:https://stackoverflow.com/questions/65849476/running-script-for-multiple-search-terms-found-on-different-sheet

Answer 1

The log tells me that it's finding searchDate's in the REFERENCESHEET, but it's not able to run them through function columnfinder (I get no logs for the second logger)

You don't execute columnfinder inside the for loop.

Try this:

for(n=0;n<referenceData.length;++n){
    var searchDate = referenceData[n][0] ;
    Logger.log(searchDate);
    columnfinder(searchDate); // modified code
  }

and you will get both logs.

Sorry if I misunderstood your question.