CODEWITHSUNDEEP
google-sheets
user1106252
user1106252

Reputation: 91

Looping through range and trying (and failing) to get value from adjacent cell

Got this function which loops through a range and, when it finds a certain text in a cell, I want to get the value of the adjacent cell.

Can't seem to get it working, keep getting:

'TypeError: Cannot find function getvalue in object Range. (line 30, file "Code")Dismiss

Error.

Anyone help?

    function Test (){
 var mysheet="2016";
  x=get(mysheet,"Interest");
}

function get(sheetname, txt) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname)
  var cellvalue;
  var myval;
  var startRow = 1;  // First row of data to process
  var numRows = 100;   // Number of rows to process

  var dataRange = sheet.getRange(startRow, 1, numRows, 26)

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
      var row = data[i];
      for (c=0; c< row.length; c++) 
      {
        cellvalue = row[c];
//        Logger.log ("Row: " + i + " Cell: " + c + " Cellvalue: " + cellvalue);

        if (cellvalue == txt)
            {
              Logger.log ("Row: " + i + " Cell: " + c + " Cellvalue: " + cellvalue + "Myval: " + myval);
              myval=sheet.getRange(i, c+1).getvalue();

            }
      }
      }; 

};

Upvotes: 0

Views: 67

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

The arrays are 0 bases, so you need to add to i & c to get the proper row and column. The length of row should be row[0].length. Also, once you find your value you can break the loop. Try this:

 function Test (){
 var mysheet="2016";
  x=get(mysheet,"Interest");
}
function get(sheetname, txt) {
  var sheet = 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname)
  var cellvalue;
  var myval;
  var startRow = 1;  // First row of data to process
  var numRows = 100;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 26)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
      var row = data[i];
      for (c=0; c< row[0].length; c++) 
      {
        cellvalue = row[c];
        if (cellvalue == txt)
        {
              i=i+1
              c=c+2
              myval=sheet.getRange(i, c).getValue()
              Logger.log( myval)
              break
            }
      }
      } 

}

Upvotes: 0

Related Questions