简单搜索电子表格中的所有工作表 [英] Simple search through all sheets in a spreadsheet
问题描述
我正在尝试编写一个函数，它将会
- 每一张表中列出一个给定的数字（标签）。
- 一旦找到该数字，将来自附近单元格的其他信息存储在一个字符串中
- 返回完成的字符串（使其成为单元格值）
该函数将按顺序作为= parent（tag）输入到单元格中以节省我追查信息和手动复制的麻烦。
我已经检查了其他几个问题，但我还不太清楚。请参阅我对以下来源的评论。
我如何搜索Google Spreadsheets？ - 这个问题的第一个答案很简单，但没有包含任何Google脚本特定的代码。
使用谷歌脚本在电子表格中查找价值 - 这一个似乎寻找类似的解决方案，所以我试图调整第一个答案的代码。
下面是我从源代码2改编的代码。似乎它应该工作，但是当我运行它时，我得到一个错误，
TypeError：无法从undefined读取属性0。 （第19行）。
TLDR：请帮我修复此代码以搜索电子表格。第19行有一个错误。
编辑：在我的问题结尾添加了正确的代码。新手的错误，我会责怪我被编译器宠坏了：）
function parent（tag）{
var sh = SpreadsheetApp.getActiveSpreadsheet（）;
var titleRow = 6;
var parentRow = 0;
//使用活动电子表格中的工作表创建数组
var sheets = SpreadsheetApp.getActiveSpreadsheet（）。getSheets（）;
//通过工作表循环查找值
for（var i in sheets）{
SpreadsheetApp.setActiveSheet（sheets [i]）
var sheet = sh.getActiveSheet（）;
var data = sheets [i] .getDataRange（）。getValues（）;
//循环表中的数据
for（var r = 1; r <= data.length; ++ r）{
if（typeof（数据[r] [0]）！==undefined）{//< - 这是发生错误的位置
if（data [r] [0] == tag）{
parentRow = r;
//填充基因
var result =＃+ tag +（;
var z = 0;
for（var j = 8; j< ; 12; j ++）{
if（data [titleRow] [j] ==Genotype）{
if（z == 0）{
result = result + data [titleRow- 1] [j];
z = 1;
}
else {
result = result + - + data [titleRow-1] [j];
}
}
}
result = result +）;
//填充基因型
var z = 0; （数据[titleRow] [j] ==基因型）{
if（z == 0）{
for（var j = 8; j <12; j ++）
result = result ++ data [dataRow] [j];
z = 1;
}
else {
result = result +/+ data [dataRow] [j];
}
}
}
// result = result ++ dataRow;
返回结果;
}
}
}
}
}
以下是更正后的代码（包括导致麻烦的重命名变量）。
$ b
function parent（tag）{
var sh = SpreadsheetApp.getActiveSpreadsheet（）;
var titleRow = 6;
var dataRow = 0;
//使用活动电子表格中的工作表创建数组
var sheets = SpreadsheetApp.getActiveSpreadsheet（）。getSheets（）;
//通过工作表循环查找值
for（var i in sheets）{
SpreadsheetApp.setActiveSheet（sheets [i]）
var sheet = sh.getActiveSheet（）;
var data = sheets [i] .getDataRange（）。getValues（）;
//循环表中的数据
for（var r = 0; r< data.length; r ++）{//< - 以下是修正
if if（data [r] [0] == tag）{
dataRow = r;
//填充基因
var result =＃+ tag +（;
var z = 0;
for（var j = 8; j< ; 12; j ++）{
if（data [titleRow] [j] ==Genotype）{
if（z == 0）{
result = result + data [titleRow- 1] [j];
z = 1;
}
else {
result = result + - + data [titleRow-1] [j];
}
}
}
result = result +）;
//填充基因型
var z = 0; （数据[titleRow] [j] ==基因型）{
if（z == 0）{
for（var j = 8; j <12; j ++）
result = result ++ data [dataRow] [j];
z = 1;
}
else {
result = result +/+ data [dataRow] [j];
}
}
}
// result = result ++ dataRow;
返回结果;
}
}
}
}
}
问题是数组索引从0开始。
因此data.length为2意味着索引为0,1的数组。试着修改你的for循环到以下内容：
$ b $ pre> for（var i = 0; i< data.length; i ++）
错误的解释：在您的循环中，代码尝试访问分配的数组值之外，因此它未定义。
编辑：多说一些我正在谈论的内容。所以当我说数据长度为2意味着索引为0,1的数组时，我仅仅试图指出，具有以下比较操作符的for循环<= data.length导致该值为i = 2 on最后的迭代。但是长度为2的数组没有索引2.希望这能够澄清我想表达的内容。
First of all, disclaimer: I am a reasonably experienced programmer, but very rusty with Javascript and brand new to Google Scripts.
I'm trying to write a function that will
- Search a specified column in each sheet for a given number (tag).
- Once it finds that number, store other information from nearby cells in a string
- Return the completed string (making it the cell value)
The function will be entered into cells as "=parent(tag)" in order to save me the hassle of hunting down the information and copying it manually.
I've checked a few other questions, but I'm still not quite there. See my comments on those sources below.
How do I search Google Spreadsheets? - The first answer to this question was simple, but didn't incorporate any of the Google Scripts-specific code.
Find value in spreadsheet using google script - This one seemed to be looking for a similar solution, so I've attempted to adapt the code from the first answer.
Below is my adapted code from source 2. It seems as though it should work, but when I run it I get an error,
TypeError: Cannot read property "0" from undefined. (line 19).
TLDR: Please help me fix this code to search through the spreadsheets. Line 19 has an error.
EDIT: Added correct code to the end of my question. Rookie mistake, which I'll blame on my being spoiled with compilers :)
function parent(tag) {
var sh = SpreadsheetApp.getActiveSpreadsheet();
var titleRow = 6;
var parentRow = 0;
//create array with sheets in active spreadsheet
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
//loop through sheets to look for value
for (var i in sheets) {
SpreadsheetApp.setActiveSheet(sheets[i])
var sheet = sh.getActiveSheet();
var data = sheets[i].getDataRange().getValues();
//loop through data on sheet
for (var r=1;r<=data.length;++r) {
if(typeof(data[r][0])!=="undefined") { //<-- This is where the error occurs
if (data[r][0] == tag) {
parentRow = r;
// Populate Genes
var result = "#" + tag + "(";
var z = 0;
for (var j=8; j<12; j++) {
if (data[titleRow][j] == "Genotype") {
if (z==0) {
result = result + data[titleRow-1][j];
z=1;
}
else {
result = result + "-" + data[titleRow-1][j];
}
}
}
result = result + ") ";
// Populate Genotype
var z = 0;
for (var j=8; j<12; j++) {
if (data[titleRow][j] == "Genotype") {
if (z==0) {
result = result + "" + data[dataRow][j];
z=1;
}
else {
result = result + "/" + data[dataRow][j];
}
}
}
// result = result + " " + dataRow;
return result;
}
}
}
}
}
Below is the corrected code (including a renamed variable that was causing trouble).
function parent(tag) {
var sh = SpreadsheetApp.getActiveSpreadsheet();
var titleRow = 6;
var dataRow = 0;
//create array with sheets in active spreadsheet
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
//loop through sheets to look for value
for (var i in sheets) {
SpreadsheetApp.setActiveSheet(sheets[i])
var sheet = sh.getActiveSheet();
var data = sheets[i].getDataRange().getValues();
//loop through data on sheet
for (var r = 0; r < data.length; r++) { //<-- Here's the fix
if(typeof(data[r][0])!=="undefined") {
if (data[r][0] == tag) {
dataRow = r;
// Populate Genes
var result = "#" + tag + "(";
var z = 0;
for (var j=8; j<12; j++) {
if (data[titleRow][j] == "Genotype") {
if (z==0) {
result = result + data[titleRow-1][j];
z=1;
}
else {
result = result + "-" + data[titleRow-1][j];
}
}
}
result = result + ") ";
// Populate Genotype
var z = 0;
for (var j=8; j<12; j++) {
if (data[titleRow][j] == "Genotype") {
if (z==0) {
result = result + "" + data[dataRow][j];
z=1;
}
else {
result = result + "/" + data[dataRow][j];
}
}
}
// result = result + " " + dataRow;
return result;
}
}
}
}
}
The problem is array index starts from 0. So data.length of 2 means array with index 0,1. Try modifying your for loop to the following
for(var i=0; i < data.length ;i++)
Explanation of the error: In your loop, the code is trying to access outside the assigned array value, hence it is undefined.
Edit: A little more explanation of what I am talking about. So when I say "data.length of 2 means array with index 0,1", I was merely trying to point out that a for loop with following comparison operator i <= data.length causes the value to be i =2 on the final iteration. But an array of length 2 doesn't have an index of 2. Hopefully, that clarifies what I was trying to convey.
这篇关于简单搜索电子表格中的所有工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
