CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Shihan Khan
Shihan Khan

Reputation: 2188

Get the spreadsheet row for a matched value

I'm new to Google Apps Script & I'm trying to make a script for a spreadsheet where I can get the range for the matched value. But there's no function for the cell value like .getRow() or .getRange() or something like this since it's a string value. Here are my codes,

function getInfo() {
var ss1 = sheet1.getSheetByName("Sheet1");
var ss2 = sheet2.getSheetByName("Rates");
var getCountry = ss1.getRange(ss1.getLastRow(), 11).getValue();
var countryRange = ss2.getRange(2, 1, ss2.getLastRow(), 1).getValues();
var getZone = 0;

for (var i = 0; i < countryRange.length; i++) {
    if (countryRange[i] == getCountry) {
        var getrow_cr = countryRange[i].getRow();    //.getRow() doesn't apply here for string value
        getZone = ss2.getRange(getrow_cr + 1, 2).getValue();
    }
}
Logger.log(getZone);
}

How can I get the row for the matched string so that I can get the cell value beside the matched cell value?

Upvotes: 1

Views: 110

Answers (1)

Mogsdad
Mogsdad

Reputation: 45720

Your looping variable i starts at 0, which in your case is equivalent to Row 2. To help keep that clear, you could use a named variable to track the first row with data: 

var firstrow = 2;

Then when you find a match, the spreadsheet row it's in is i + firstrow.

Updated code:

function getInfo() {
  var ss1 = sheet1.getSheetByName("Sheet1");
  var ss2 = sheet2.getSheetByName("Rates");
  var getCountry = ss1.getRange(ss1.getLastRow(), 11).getValue();
  var firstrow = 2;
  var countryRange = ss2.getRange(firstrow, 1, ss2.getLastRow(), 1).getValues();
  var getZone = 0;

  for (var i = 0; i < countryRange.length; i++) {
    if (countryRange[i][0] == getCountry) {
      var getrow_cr = i + firstrow;
      getZone = ss2.getRange(getrow_cr, 2).getValue();
      break;  // Found what we needed; exit loop
    }
  }
  Logger.log(getZone);
}

Since countryRange is a two-dimensional array with one element in each row, the correct way to reference the value in row i is countryRange[i][0]. If you instead just compare countryRange[i], you are relying on the JavaScript interpreter to coerce the "row" into a String object.

Upvotes: 1

Related Questions