使用Google脚本在电子表格中查找值 [英] Find value in spreadsheet using google script
问题描述
$ b $ ol
$ 1电子表格
我想要做的是找到并将焦点设置到下一个单元格在任何与所选单元格相匹配的表单中（不区分大小写）单击电子表格中的按钮式图像。
我的方法是：
- 将所选单元格的值设置为变量（成功）
- 找到与该变量匹配的第一个单元格（不是所选单元格）（不成功）
- 将找到的单元格的值设置为变量2（不成功）
- 将电子表格的焦点设置为变量2 ）
$ p $ 函数FindSetFocus（）
{
var ss = SpreadsheetApp.getActiveSpreadsheet（） ;
var sheet = ss.getActiveSheet（）;
var activecell = sheet.getActiveCell（）;
var valueactivecell = activecell.getValue（）;
//这里是代码:)
}
我在下面的主题中发现了这个片段，但是我在设置输入和做输出时遇到了一些麻烦：
我认为我可以用'valueactivecell'替换'value' ，但我不知道如何设置范围来搜索电子表格中的所有表单。另外，我想输出是我可以设置重点使用像ss.setActiveSheet（工作表）.setActiveSelection（D5）;'
/ **
*查找给定范围内的值。
* @param value要查找的值。
* @param范围要搜索的范围。
* @return指向包含值
*的第一个单元格的范围，如果未找到则返回null。
* /
函数find（value，range）{
var data = range.getValues（）; （var j = 0; j if（var i = 0; i< data.length; i ++）{
（data [i] [j] == value）{
return range.getCell（i + 1，j + 1）;
}
}
}
返回null;
$ b $ p
$ b 也发现这个，但没有运气让它在选定的单元格和设置上工作重点：如何在Google Spreadsheets中搜索并查找某一行的坐标，最佳答案，第一个代码。请注意，我'm不是专业编码器:)如果提供代码示例，请评论内联hehe。
在此先感谢您的帮助。
编辑24/10：使用下面答案中的代码并编辑一下。现在可以查看电子表格中的多个工作表来查找值。只有这个位的问题是：我的单元格突出显示为黄色，但没有选中找到的单元格。请参阅下面的代码跳过表单。 ）
$ b $ pre $ 函数SearchAndFind（）{
//确定所选单元格的值
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
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 $ $这是一个这样的函数的例子，我在我的电子表格中插入了一个图形，表示一个我分配脚本的按钮，所以很容易调用。
我添加了一个功能，可以在所选单元格上设置浅黄色背景，以便更容易地看到选中的单元格，但这是可选的。
代码
函数findAndSelect（）{
var sh = SpreadsheetApp.getActiveSpreadsheet（）;
var ss = sh.getActiveSheet（）;
var cell = ss.getActiveCell（）;
cell.setBackground（'＃ffff55'）; //替换为cell.setBackground（null）;在离开单元时重置颜色
var activeR = cell.getRow（） - 1;
var activeC = cell.getColumn（） - 1;
var value = cell.getValue（）;
var data = ss.getDataRange（）。getValues（）;对于（var c = activeC; c< data [0] .length;）来说，
var step = 0
（var r = activeR; r
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;
$ b code
$ b $ h $>
这段代码只搜索'向下'，也就是说，任何出现在选中单元格之前的行都会被忽略，列相同。
$ b
如果这对你是一个问题，那么代码应该修改为从0开始迭代。但在这种情况下，如果需要忽略最初的起始单元格，那么你也应该记住它的坐标并跳过这个值迭代。
Situation:
- 1 spreadsheet
- multiple sheets
- 1 cell selected (may vary)
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 .
My approach is:
- set value of selected cell as 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 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 :)
}
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?
I think I can replace 'value' with 'valueactivecell', but I don't know how to set range to search through all sheets in the spreadsheet. Also I'd like the output to be something I can set focus to using soomething 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;
}
also found this but no luck on getting it to work on 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.
Thanks in advance for any help.
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. 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 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.
Code
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...
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.
这篇关于使用Google脚本在电子表格中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
