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.

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

回答1:

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.