使用Google脚本查找电子表格中任何表单上的值 [英] Find value on any sheet in spreadsheets using Google Script

问题描述

使用下面的代码，我可以查看电子表格中的多个工作表，找到等于所选单元格的第一个值。只有这个位的问题是：找到的值的单元格高亮显示为黄色，但找到值的单元格未选中。请参阅下面的代码跳过表单。我无法理解这一点：）

有趣的是，当我没有跳过列表的时候，突出显示和选择一个值的代码确实有效工作表，请参阅最佳答案：在电子表格中查找值使用谷歌脚本

 函数SearchAndFind（）{
 
 //确定selected cell 
 var sh = SpreadsheetApp.getActiveSpreadsheet（）; 
 var ss = sh.getActiveSheet（）; 
 var cell = ss.getActiveCell（）; 
 var value = cell.getValue（）; 
 
 //在活动电子表格中创建数组
 var sheets = SpreadsheetApp.getActiveSpreadsheet（）。getSheets（）; 
 
 //通过工作表循环查找值
（工作表中的变量i）{
 
 //将活动单元格设置为A1，以便开始查找从那里
 SpreadsheetApp.setActiveSheet（sheets [i]）
 var sheet = sh.getActiveSheet（）; 
 var range = sheet.getRange（A1）; 
 sheet.setActiveRange（range）; 
 
 //设置变量循环每张表中的数据
 var activeR = cell.getRow（） -  1; 
 var activeC = cell.getColumn（） -  1; 
 var data = sheets [i] .getDataRange（）。getValues（）
 var step = 0 
 
 //循环表中的数据
 for（var r = activeR; 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（））{$ b $ sheet.getRange（r + 1，c + 1） .activate（）的setBackground（ '＃ffff55'）; 
 return; 
 
 
 
 
 
 $ b code $ pre $ $这段代码能够搜索多个工作表，它显然是基于你发布的代码，但是使用一个内存（scriptProperties）来保持搜索值'alive '从一张纸更换到下一张时，知道何时去搜索。 
它具有2个非最优方面：1°在开始新的搜索之前，您必须继续搜索到最后一个事件。 
 2°：当它从表单n切换到表单n + 1时，它首先选择单元格A1，然后查找值的出现位置。
 
 
我猜这应该是可能的摆脱这些问题，但现在我不知道如何:-) 
也许这种方法是不是最好的，我从一个简单的一个脚本修改和复杂的开始...通常不是最好的发展战略（我知道），但无论如何，这是一个有趣的实验和一个很好的逻辑练习... 
谢谢你。
  function SearchAndFind（）{
 
 //确定所选单元格的值
 var sh = SpreadsheetApp.getActiveSpreadsheet（）; 
 var ss = sh.getActiveSheet（）; 
 var cell = ss.getActiveCell（）; 
 var value = cell.getValue（）; 
 if（ScriptProperties.getProperty（'valueToFind'）！=''）{value = ScriptProperties.getProperty（'valueToFind'）}; 
 
 //在活动电子表格中创建数据表
 var sheets = SpreadsheetApp.getActiveSpreadsheet（）。getSheets（）
 var sheetNumber = sheets.length; 
 var currentSheet = ss.getIndex（） -  1; 
 Logger.log（currentSheet）; 
 
 //循环查找值
（var i = currentSheet; i< sheetNumber; ++ i）{
 Logger.log（'currentSheet ='+ i）
 //将活动单元格设置为A1，然后从每个表单开始查找
 SpreadsheetApp.setActiveSheet（sheets [i]）
 // sheets [i] .getRange（1,1 ）。启用（）; 
 //设置变量循环通过每张纸上的数据
 var activeR = cell.getRow（） -  1; 
 var activeC = cell.getColumn（） -  1; 
 var data = sheets [i] .getDataRange（）。getValues（）
 var step = 0; 
 
 //循环表中的数据
 for（var r = activeR; r  for（var c = activeC; c< Logger.log（'sheet：'+ i +'step：'+ step +'value'+ value +'='+ data [r] data [0] .length; ++ c）{
 step ++ 
 Logger.log [C]）; 
 if（data [r] [c] ==''||（step == 1&& i == currentSheet））{continue}; 
 if（value.toString（）。toLowerCase（）== data [r] [c] .toString（）。toLowerCase（））{
 sheets [i] .getRange（r + 1，c 1）.activate（）的setBackground（ '＃ffff55'）; 
 ScriptProperties.setProperty（'valueToFind'，value）; 
 return; 
} 
} 
} 
 cell = sheets [i] .getRange（1,1）; 
 
 
 ScriptProperties.setProperty（'valueToFind'，''）; 
 Logger.log（'reset'）; 
} 
  
 
Using the code below I'm able to look through multiple sheets in a spreadsheet to find the first value that equals the selected cell. Only problem with this bit is: The cell with the value found 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 :)

Funny thing is that the code for highlighting and selecting a value does work when I'm not hopping through the list of sheets, see best answer: Find value in spreadsheet using google script
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 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;
      }
    }
  }
 }

}

解决方案
This code is able to search across multiple sheets, it is obviously based on your published code but uses a memory (scriptProperties) to keep the search value 'alive' when changing from one sheet to the next one and to know when to search for it.
It has 2 non-optimal aspects : 1° you have to keep searching up to the last occurrence before you can begin a new search.
2° : when it switches from sheet n to sheet n+1 it first selects cell A1 before finding the value occurrence.

I guess it should be possible to get rid of these issues but right now I don't find how :-)
Maybe the approach is simply not the best, I started from a simple one sheet script modified and complexified... that's usually not the best development strategy (I know), but anyway, it was a funny experiment and a good logic exercise ...
Thanks for that.
function SearchAndFind() {

  //determine value of selected cell
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var cell = ss.getActiveCell();
  var value = cell.getValue();
  if(ScriptProperties.getProperty('valueToFind')!=''){value = ScriptProperties.getProperty('valueToFind')};

  //create array with sheets in active spreadsheet
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var sheetNumber = sheets.length;
  var currentSheet = ss.getIndex()-1;
  Logger.log(currentSheet);

  //loop through sheets to look for value
  for (var i = currentSheet ; i<sheetNumber ; ++i ){
    Logger.log('currentSheet = '+i)
    //Set active cell to A1 on each sheet to start looking from there
    SpreadsheetApp.setActiveSheet(sheets[i])
//    sheets[i].getRange(1,1).activate();
    //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 sheet  
    for(var r=activeR;r<data.length;++r){
      for(var c=activeC;c<data[0].length;++c){
        step++
          Logger.log('sheet : '+i+'    step:'+step+'   value '+value+'  =  '+data[r][c]);
        if(data[r][c]==''||(step==1&&i==currentSheet)){ continue };
        if(value.toString().toLowerCase()==data[r][c].toString().toLowerCase()){
          sheets[i].getRange(r+1,c+1).activate().setBackground('#ffff55');
          ScriptProperties.setProperty('valueToFind',value);
        return;
        }
      }
    }
      cell = sheets[i].getRange(1,1);

  }  
  ScriptProperties.setProperty('valueToFind','');
  Logger.log('reset');
}


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