遍历范围以查找匹配的字符串谷歌脚本 [英] Loop through range to find matching string google scripts

问题描述

我正在尝试遍历电子表格的顶部标题行，以根据标题名称查找列的索引号，这样如果有人插入一列，我的代码就不会中断.到目前为止，这是我所拥有的:

I'm trying to loop through the top title row of my spreadsheet to find the index number of a column based on the title name so that if someone inserts a column my code won't break. Here's what I have so far:

  var sheet = SpreadsheetApp.getActive().getSheetByName('RawData');
  var values = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).getValue(); //line that's breaking
  range.setNote("inside function");
  var i = 1;
  while(values[1][i] != name) {
    i++;
  }return i;
}

代码似乎在我设置值"的那一行中断，我不知道如何访问我创建的数组以检查哪个数组包含与名称"参数相同的字符串.setNote 行仅用于测试目的，您可以忽略它.提前致谢.

The code appears to break on the line where I set 'values,' and I can't figure out how to access the array I created in order to check which one contains the same string as the 'name' parameter. The setNote line is just for testing purposes, you can ignore it. Thanks in advance.

编辑

function getColumnByName() {
  var name = "Ready For Testing?";
  var ss=SpreadsheetApp.getActive().getSheetByName('RawData');
  var vA=ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();
  var hA=vA.shift();//returns header array vA still contains the data
  var idx={};
  var index = hA.forEach(function(name,i){idx[name]=i});//idx['header name'] returns index  
  return index;
}

我设置名称只是为了测试目的，当我使用实际函数时它会作为参数传递

I set name just for testing purposes, it will be passed as a parameter when I use the actual function

推荐答案

试试

function getColumnByName() {
  var name = "Ready For Testing?";
  var ss=SpreadsheetApp.getActive().getSheetByName('RawData');
  var vA=ss.getDataRange().getValues();
  var hA=vA.shift();//returns header array vA still contains the data
  var idx= hA.indexOf(name);
  if(idx === -1 ) throw new Error('Name ' + name + ' was not found');
  return idx + 1; // Returns the name position 
}

<小时>

代替

var values = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).getValue();

而不是

var vA=ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();

使用

var values = sheet.getDataRange().getValues();

1. getValue() 只返回左上角单元格的值
2. 使用 sheet.getMaxRows()/sheet.getMaxColumns() 返回工作表的最后一行/列，这可能会导致获得大量空白行/列.
3. 与第二行代码 (getLastRow()/getLastColumn()) 相比，建议的代码行更便宜"；(一次调用电子表格服务而不是三个(1. getRange, 2. getLastRow, 3 getLastColumn Vs. getDataRange) 来获取数据范围，通常也更快.

1. getValue() only returns the value of top-left cell
2. using sheet.getMaxRows() / sheet.getMaxColumns() returns the sheet last row / column which could cause getting a lot of blank rows / columns.
3. compared with the second line code (getLastRow() / getLastColumn()) the proposed code line is "cheaper" (one call to the Spreadsheet Service instead of three (1. getRange, 2. getLastRow, 3 getLastColumn Vs. getDataRange) to get the data range and usually is faster too.

<小时>

关于

var index = hA.forEach(function(name,i){idx[name]=i});

forEach 返回 undefined 参考.Array.prototype.forEach

这篇关于遍历范围以查找匹配的字符串谷歌脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！