使用谷歌脚本在电子表格中查找值 [英] Find value in spreadsheet using google script

问题描述

情况:

1. 1 个电子表格
2. 多张纸
3. 已选择 1 个单元格(可能会有所不同)

我想做的是在单击电子表格中类似按钮的图像时，在任何与所选单元格(不区分大小写)匹配的工作表中查找并设置焦点到下一个单元格.有点像 MS Word 可以为您创建的自定义索引.

What I'd like to do is to find and set focus to the next cell in any sheet that matches the selected cell (case insensitive) upon clicking a button-like image in the spreadsheet. Sort of like a custom index MS Word can create for you.

我的做法是:- 将所选单元格的值设置为变量(成功)- 找到与该变量匹配的第一个单元格(不是所选单元格)(不成功)- 将找到的单元格的值设置为变量 2(不成功)- 将电子表格的焦点设置为 variable2(不成功)

My approach is: - set value of the selected cell as the variable (succeeded) - find the first cell that matches that variable (not the selected cell) (no success) - set value of found cell as variable2 (no success) - set the focus of spreadsheet to variable2 (no success)

function FindSetFocus() 

{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var activecell = sheet.getActiveCell();
var valueactivecell = activecell.getValue();

//here comes the code :)

}

我在以下主题中找到了此代码段，但在设置输入和处理输出时遇到了一些问题:如何搜索 Google 电子表格?

I have found this snippet in the following topic, but I'm having a little trouble setting the input and doing something with the output: How do I search Google Spreadsheets?

我想我可以用valueactivecell"替换value"，但我不知道如何设置范围以搜索电子表格中的所有工作表.另外，我希望输出是我可以将焦点设置为使用类似 'ss.setActiveSheet(sheet).setActiveSelection("D5");'

I think I can replace 'value' with 'valueactivecell', but I don't know how to set the range to search through all sheets in the spreadsheet. Also, I'd like the output to be something I can set focus to using something like 'ss.setActiveSheet(sheet).setActiveSelection("D5");'

/**
 * Finds a value within a given range. 
 * @param value The value to find.
 * @param range The range to search in.
 * @return A range pointing to the first cell containing the value, 
 *     or null if not found.
 */
function find(value, range) {
  var data = range.getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == value) {
        return range.getCell(i + 1, j + 1);
      }
    }
  }
  return null;
}

也发现了这个，但没有运气让它在选定的单元格上工作并设置焦点:如何在 Google 电子表格中搜索和查找行的坐标 最佳答案，第一个代码.

also found this but no luck on getting it to work on the selected cell and setting focus: How do I search for and find the coordinates of a row in Google Spreadsheets best answer, first code.

请记住，我不是专业编码员 :) 如果提供了代码示例，请内联评论.呵呵.

Please bear in mind that I'm not a pro coder :) If code samples are provided, please comment inline hehe.

提前感谢您的帮助.

24/10 使用下面答案中的代码并对其进行了一些编辑.现在可以查看电子表格中的多个工作表以查找值.此位的唯一问题是:我的单元格以黄色突出显示，但未选择具有找到值的单元格.请参阅下面的代码以浏览工作表.我无法理解这个:)

Edit 24/10: Used the code from the answer below and edited it a bit. Now able to look through multiple sheets in a spreadsheet to find the value. The only problem with this bit is: My cell is highlighted yellow, but the cell with the value found isn't selected. See code below for hopping through sheets. I can't get my head around this :)

function SearchAndFind() {

//determine value of selected cell
var sh = SpreadsheetApp.getActiveSpreadsheet();
var ss = sh.getActiveSheet();
var cell = ss.getActiveCell();
var value = cell.getValue();

//create array with sheets in active spreadsheet
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

//loop through sheets to look for value
for (var i in sheets) {

 //Set active cell to A1 on each sheet to start looking from there
  SpreadsheetApp.setActiveSheet(sheets[i])
  var sheet = sh.getActiveSheet();
  var range = sheet.getRange("A1");
  sheet.setActiveRange(range);

//set variables to loop through data on each sheet
  var activeR = cell.getRow()-1;
  var activeC = cell.getColumn()-1;
  var data = sheets[i].getDataRange().getValues()
  var step = 0

//loop through data on the sheet  
  for(var r=activeR;r<data.length;++r){
    for(var c=activeC;c<data[0].length;++c){
      step++
      Logger.log(step+' -- '+value+'  =  '+data[r][c]);
      if(data[r][c]==''||step==1){ continue };
      if(value.toString().toLowerCase()==data[r][c].toString().toLowerCase()){
         sheet.getRange(r+1,c+1).activate().setBackground('#ffff55');
        return;
      }
    }
  }
 }

}

推荐答案

这是一个这样的函数的例子，我在我的电子表格中插入了一个代表我分配脚本的按钮的图形，因此它很容易调用.

Here is an example of such a function, I inserted a drawing in my spreadsheet representing a button which I assigned the script so it's easy to call.

我添加了一项功能，可以在生成的选定单元格上设置浅黄色背景，以便更容易看到选定的单元格，但这是可选的.

I added a feature to set a light yellow background on the resulting selected cell so it's easier to see the selected cell but this is optional.

function findAndSelect(){
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var cell = ss.getActiveCell();
  cell.setBackground('#ffff55');// replace by cell.setBackground(null); to reset the color when "leaving" the cell
  var activeR = cell.getRow()-1;
  var activeC = cell.getColumn()-1;
  var value = cell.getValue();
  var data = ss.getDataRange().getValues();
  var step = 0
  for(var r=activeR;r<data.length;++r){
    for(var c=activeC;c<data[0].length;++c){
      step++
      Logger.log(step+' -- '+value+'  =  '+data[r][c]);
      if(data[r][c]==''||step==1){ continue };
      if(value.toString().toLowerCase()==data[r][c].toString().toLowerCase()){
        ss.getRange(r+1,c+1).activate().setBackground('#ffff55');
        return;
      }
    }
  }
}

警告

此代码仅搜索向下"，即忽略所选单元格之前的行中出现的任何事件，列相同...

Caveat

This code only searches 'downwards', i.e. any occurrence in a row that would precede the selected cell is ignored, same for columns...

如果这对您来说是个问题，那么应该修改代码以从 0 开始迭代.但是在这种情况下，如果需要忽略初始起始单元格，那么您还应该记住它的坐标并在迭代中跳过这个值.

If that's an issue for you then the code should be modified to start iterating from 0. But in this case if one need to ignore the initial starting cell then you should also memorize its coordinates and skip this value in iteration.

这篇关于使用谷歌脚本在电子表格中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！