Retrieve the specific row in the spreadsheet that corresponds to a found value

Question

Retrieve the specific row in the spreadsheet that corresponds to a found value

I'm a beginner with Google Apps Script and I'm working on creating a script for a spreadsheet where I need to retrieve the range for a matched value. Unfortunately, there doesn't seem to be a function like .getRow() or .getRange() that can be used directly since the value is in string format. Below are the codes I have written:

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() cannot be applied here as it's a string value
        getZone = ss2.getRange(getrow_cr + 1, 2).getValue();
    }
}
Logger.log(getZone);
}

Can anyone help me figure out how to determine the row containing the matched string so I can retrieve the cell value next to it?

javascript google-apps-script google-sheets

Answer 1

Answer №1

Your variable for looping, i, starts at 0, which corresponds to Row 2 in this case. To avoid confusion, it might be helpful to use a named variable to keep track of the first row with data:

var firstRow = 2;

When you find a match, the row in the spreadsheet will be i + firstRow.

Here is the updated code snippet:

function getInfo() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet();
  var ss1 = sheet1.getSheetByName("Sheet1");
  var ss2 = sheet1.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 getRowCr = i + firstRow;
      getZone = ss2.getRange(getRowCr, 2).getValue();
      break; // Found what we needed, so exit the loop
    }
  }
  Logger.log(getZone);
}

It's important to note that since countryRange is a two-dimensional array with one element per row, you should reference the value in row i with countryRange[i][0]. Comparing just countryRange[i] may rely on JavaScript coercing the "row" into a String object.

Answer 2

Your variable for looping, i, starts at 0, which corresponds to Row 2 in this case. To avoid confusion, it might be helpful to use a named variable to keep track of the first row with data:

var firstRow = 2;

When you find a match, the row in the spreadsheet will be i + firstRow.

Here is the updated code snippet:

function getInfo() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet();
  var ss1 = sheet1.getSheetByName("Sheet1");
  var ss2 = sheet1.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 getRowCr = i + firstRow;
      getZone = ss2.getRange(getRowCr, 2).getValue();
      break; // Found what we needed, so exit the loop
    }
  }
  Logger.log(getZone);
}

It's important to note that since countryRange is a two-dimensional array with one element per row, you should reference the value in row i with countryRange[i][0]. Comparing just countryRange[i] may rely on JavaScript coercing the "row" into a String object.

Retrieve the specific row in the spreadsheet that corresponds to a found value

Answer №1

Similar questions

Sorting items using jQuery filter

Utilize PHP within an HTML form for action without causing a redirection

How can I retrieve the Id from a TextField in MUI when the select property is activated?

Tips for excluding a value in a Vue loop

Sorting data in AngularJS using the OrderBy filter in ascending

Adding elements within a loop using jquery

What is the correct way to integrate a HTML/CSS/JS theme into a Vue project effectively?

Issue with the loss of scope in the Subscribe event causing the Clipboard Copy Event

Encounter a Socket.io connection error due to net::ERR_CONNECTION_REFUSED when trying to access from multiple devices

Steps for dynamically expanding a textarea in Selenium WebDriver when the element path is constantly changing

When navigating within a page, Firefox shows {{ }} tags while Chrome does not in AngularJS

How can I set a condition to open a specific page in ReactJs?

Steps to invoke another service (function) prior to calling the res.view() function in sails JS:

Using Javascript within a PHP file to generate JSON output

How can HTML be dynamically inserted using JavaScript without utilizing an id attribute?

What is the method for choosing an Object that includes an Array within its constructor?

Ever since transitioning to Discord.js 13, why am I suddenly encountering a bug with the messageCreate event?

The fade effect on an element cannot be achieved using setTimeout

What is the best way to include an uncomplicated HTML Element alongside a touch of JavaScript?

Issue with isNan() and typeof in React framework