在不同工作表中找到多个搜索词的运行脚本 [英] Running script for multiple search terms found on different sheet

问题描述

我正在尝试使用 REFERENCESHEET 整理搜索词和目标单元格，以从 REPORTSHEET 填充 SCORESHEET ./p>

我正在运行的脚本如下.想法是该脚本在 REFERENCESHEET 中找到 searchDate ，然后使用它们在 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 =总费用"；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]);}}}}功能列查找器(查找){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  

从广义上讲，该代码有效，就好像我将 searchDate 定义为我要查找的术语之一(例如Jan-21)一样，一切正常.问题在于，按照以下条件查找多个搜索词时似乎并没有这样做-因此填充了多个行:

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

该日志告诉我，它在REFERENCESHEET中找到了 searchDate ，但是无法通过 function columnfinder 运行它们(第二个记录器没有日志).

我怀疑答案是在此构想的较早版本中收到的较早答案中的某个地方-解决方案

该日志告诉我，它正在REFERENCESHEET中找到searchDate，但是无法通过功能列查找器运行它们(我没有第二个记录器的日志)

您不要在 for 循环内执行 columnfinder .

尝试一下:

  for(n = 0; n< referenceData.length; ++ n){var searchDate = referenceData [n] [0];Logger.log(searchDate);列查找器(searchDate);//修改后的代码} 

，您将同时获得两个 logs .

对不起，如果我误解了你的问题.

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:

解决方案

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.

这篇关于在不同工作表中找到多个搜索词的运行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！