Sharadnar Sharadnar - 4 years ago 80
Javascript Question

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


  1. Search a specified column in each sheet for a given number (tag).

  2. Once it finds that number, store other information from nearby cells in a string

  3. 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;
}
}
}
}
}

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download