使用字符串或整数在Google表格中查找单元格的位置 [英] Locating a cell's position in google sheets using a string or an integer

问题描述

这个问题似乎很简单，但是我已经搜索/尝试了几个小时才能找到解决方案.我基本上有一个很大的电子表格(ISBN的3k行)，每个电子表格都有相应的作者，标题，流派等.

This problem seems so simple but I've been searching/trying for several hours to find a solution. I basically have a large spreadsheet (3k rows of ISBN's) which each have a corresponding author, title, genre etc.

我的计划是创建一个函数，使我可以简单地生成一个小的UI，该UI包含一个Isbn和一个金额，具有一个Submit按钮(全部完成)，然后找到ISBN并在该行中增加一个值.最大的绊脚石是能够使用已经存储为变量的isbn并在表中找到匹配项，因为我根本无法在Google表格中找到某种"getValue()"函数.

My plan was to make a function that allows me to simply produce a small UI, which takes in an Isbn and an amount, has a submit button (all done) and then finds that ISBN and increments a value in that row. The big stumbling block is being able to use the isbn which has been stored as a variable and finding the match in the table, as I simply cannot find some sort of "getValue()" function in google sheets.

我看过几个例子，人们将整个数据加载到一个数组中，然后将所需的值与每个单独的条目进行比较，但这对我来说似乎效率低下又荒谬吗?当然，我必须忽略一种简单有效的方法.我所要做的只是一种简单的搜索值，在工作表中找到该值并返回该行的方法.

I've seen several examples of people loading their entire data into an array and comparing the desired value to each individual entry, but that seems ridiculously inefficient and slow to me? Surely there must be a simple, efficient way that I'm just overlooking. All I'm after is a simple way of searching for a value, finding that value in the sheet and returning that row.

提前谢谢！

推荐答案

您认为必须有一种更有效的方法来在电子表格中查找项目的假设是不正确的.电子表格服务中没有查找"方法，但数组迭代速度很快，批量数据读取也非常快，因此即使对于很长的列表，也不要因为速度参数而烦恼.

Your assumption that there must be a more efficient way to find an item in a spreadsheet is not true. There is no "find" method in spreadsheet service but array iteration is fast and bulk data reading is also very fast so you shouldn't be annoyed with the speed parameter even for very long lists.

这里有2个代码段，可在第1列中找到字符串的行号.

Here are 2 code snippets that find the row number of a string in column 1.

在第一个数组中，我对数组进行了转置，以便可以在简单数组中的单列中进行搜索.

In the first one I transposed the array so that I could search in a single column in a simple array.

两者都显示花费的时间(以毫秒为单位).

Both show the time it takes in milliseconds.

function testISBN1(){
  var time = new Date().getTime();
  var isbn = 'test1990'; // just as a test... create your own Ui to enter the value to search for
  Logger.log(findISBN(isbn)+'  in '+(new Date().getTime()-time)+' mSec');// view result... false if no occurrence (do something with that value)
}

function findISBN(isbn){
  var sh = SpreadsheetApp.getActive().getSheetByName('Sheet1');// select your sheet
  var data = sh.getDataRange().getValues(); // get all data at once
  var isbnColumn = transpose(data)[0];// if isbn is column 1 (it became row1 because of transpose
  for(var n in isbnColumn){
    if(isbnColumn[n] == isbn){ n++ ; return n }; // if found, increment to get the sheet row number
  }
  return false;// not found
}


function transpose(a) { // classical Array transpose JS function
  return Object.keys(a[0]).map(
    function (c) { return a.map(function (r) { return r[c]; }); }
  )
}

第二个没有转置的代码执行大约需要相同的时间(在2000行工作表中约为230 mS)

And the second one, without transpose takes about the same time to execute (about 230 mS in a 2000 rows sheet)

function testISBN2(){
  var time = new Date().getTime();
  var isbn = 'test1990'; // just as a test... create your own Ui to enter the value to search for
  Logger.log(findISBN2(isbn)+'  in '+(new Date().getTime()-time)+' mSec');// view result... false if no occurrence (do something with that value)
}

function findISBN2(isbn){
  var sh = SpreadsheetApp.getActive().getSheetByName('Sheet1');// select your sheet
  var data = sh.getDataRange().getValues(); // get all data at once
  for(var n in data){
    if(data[n][0] == isbn){ n++ ; return n }; // if found (in column 1), increment to get the sheet row number
  }
  return false;// not found
}

下面是执行记录，显示了每一行代码所花费的时间.如您所见，总执行时间主要用于获取工作表并获取其值.

Below is the execution transcript that shows the time taken by every line of code. As you can see, the total execution time is mainly used to get the sheet and get its values.

这篇关于使用字符串或整数在Google表格中查找单元格的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！