如何为setValue返回多个列值 [英] How to return multiple column values for setValue

问题描述

我试图运行一些脚本来查找各种单元格值，返回包含搜索值的单元格的每个列号，然后根据每次返回值 setValue .我已经得到以下示例:

  function superAuto(){var report = SpreadsheetApp.openById('REPORTSHEET');var reportData = report.getDataRange().getValues();var scorecard = SpreadsheetApp.openById('SCORESHEET');var scorecardData = scorecard.getDataRange().getValues();var column = columnfinder();scorecard.getRange(18，[column]).setValue(示例输入")；}函数columnfinder(){var report = SpreadsheetApp.openById('REPORTSHEET');var reportData = report.getDataRange().getValues();for(var j = 0，jLen = reportData.length; j< jLen; j ++){for(var k = 0，kLen = reportData [0] .length; k  

我的代码存在的问题是，对于一个搜索词(第一个搜索词，"Nov-20" )，它仅返回 k ，因此，基于此， SCORESHEET 中只有 setValue 个单元格，而在本例中，我真的希望在 REPORTSHEET 中找到3个单元格.code>(可以找到所有搜索词)，要返回3个 k 值，因此要在 SCORESHEET 中填充3个单元格.

有什么想法吗?

解决方案

如果要 columnfinder()返回列索引列表，则需要定义一个数组变量，该变量将保存匹配的列索引，然后返回其值.您可以使用

得分表:

Am trying to run a bit of script to find various cell values, return each of the column numbers of the cells that contain the searched-for values, and then setValue according to each return. I've got as far as the below example:

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

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

var column = columnfinder ();

scorecard.getRange(18, [column]).setValue("Example input");
}


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

    for(var j=0, jLen=reportData.length; j<jLen; j++) {
      for(var k=0, kLen=reportData[0].length; k<kLen; k++) {
        var find = "Nov-20" || "Dec-20" || "Jan-21";
        if(find == reportData[j][k]) {
          Logger.log(k);
          return (k);}
      }
    }
  }

The problem with my code as it stands is that it only returns k for one of the search terms (the first one, "Nov-20"), and therefore only setValue's one cell in SCORESHEET based on that, whereas as by this example I'd really like 3 cells to be found in REPORTSHEET (all the search terms are there to be found), 3 values of k to be returned, and therefore 3 cells to be populated in SCORESHEET.

Any thoughts?

解决方案

If you want columnfinder() to return a list of column indexes, you need to define an array variable that will hold the matched column indexes before returning its value. You can use array.push() to append values to your array.

Your code:

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

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

  var columns = columnfinder();
  Logger.log(columns);

  columns.forEach(col=>{
    scorecardSheet.getRange(18, col).setValue("Example input");
  })

}


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

  for(var j=0, jLen=reportData.length; j<jLen; j++) {
    for(var k=0, kLen=reportData[0].length; k<kLen; k++) {
      var find = ["Nov-20", "Dec-20", "Jan-21"];

      if(find.includes(reportData[j][k])) {
        //return (k);

        //Array is in zero-based, but column index for sheet range is in one-based.
        result.push(k+1);
      }
    }
  }

  return result;
}

---

Modifications Done:

1. I declared a result array in columnfinder() that will hold column indexes and push found indexes using in result.push(k+1);

2. Replaced the condition in matching strings in columnfinder(), I declared an array find and used array.includes() and check if find array contains the current cell being checked.

3. Return the result after finishing the loop in columnfinder()

4. Added a scorecardSheet variable in superAuto(). Initially you are trying to use getRange(row, column) in the spreadsheet scorecard which is not possible. (See Spreadsheet methods). getRange(row, column) is available in Sheet Class

---

Output:

Report Sheet:

Score Sheet:

这篇关于如何为setValue返回多个列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！