Google Scripts: Simple search through all sheets in a spreadsheet
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.
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;
}
}
}
}
}
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.