How to find a cell in Google Sheets using string or number input?

Other Questions Google Sheets
1

I’m working on a big Google Sheet with book info. It’s got 3000 rows of ISBNs plus author, title, and genre data. I want to make a simple tool where I can type in an ISBN and a number, then press a button to find that ISBN in the sheet and change a value in its row.

The tricky part is finding the right row using the ISBN I input. I can’t seem to find a straightforward getValue() function in Google Sheets. Some folks suggest putting all the data in an array and searching through it, but that seems slow for so many rows.

Is there a quick way to search for a value and get its row in Google Sheets? I feel like I’m missing something obvious. Any tips would be great!

Here’s a basic example of what I’m trying to do:

function updateBookInfo(isbn, newValue) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == isbn) {
      sheet.getRange(i + 1, 2).setValue(newValue);
      break;
    }
  }
}

But I’m sure there’s a better way. Help!

2

Having worked with large datasets in Google Sheets myself, I can attest that efficiency is key. While the MATCH and INDEX approach suggested is good, I’ve found that using Google Sheets’ query function can be even more powerful and flexible.

Here’s a script I’ve used successfully:

function updateBookInfo(isbn, newValue) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var query = '=QUERY(' + range.getA1Notation() + ', "SELECT ROW WHERE A = '"' + isbn + '"'")';
  var result = sheet.getRange('A1').setFormula(query).getValue();
  
  if (result) {
    sheet.getRange(result, 2).setValue(newValue);
    sheet.getRange('A1').clearContent();
  }
}

This method leverages SQL-like syntax for searching, which is incredibly fast even with thousands of rows. It’s been a reliable solution in my projects dealing with large book databases.

3

I’ve been in a similar situation with a large inventory database, and I can tell you that looping through all rows can indeed be slow. Here’s a more efficient approach I’ve used:

Try the MATCH function combined with INDEX. In your Google Apps Script, you can use something like this:

function updateBookInfo(isbn, newValue) {
  var sheet = SpreadisbnheetApp.getActiveSheet();
  var isbnColumn = 1; // Assuming ISBNs are in column A
  var updateColumn = 2; // Column to update

  var isbnRange = sheet.getRange(1, isbnColumn, sheet.getLastRow(), 1);
  var rowIndex = isbnRange.createTextFinder(isbn).findNext();

  if (rowIndex) {
    sheet.getRange(rowIndex.getRow(), updateColumn).setValue(newValue);
  }
}

This method uses built-in search functionality, which is much faster than looping through all rows manually. It’s been a game-changer for me when dealing with large datasets in Sheets.

4

hey there! i’ve dealt with similar stuff before. have u tried the VLOOKUP function? it’s pretty nifty for this kinda thing. here’s a quick example:

function updateBookInfo(isbn, newValue) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A:B");
  var formula = '=VLOOKUP("' + isbn + '", ' + range.getA1Notation() + ', 2, FALSE)';
  var result = sheet.getRange('A1').setFormula(formula).getValue();
  
  if (result != '#N/A') {
    sheet.getRange('A1').clearContent();
    sheet.getRange(result, 2).setValue(newValue);
  }
}

hope this helps! lemme kno if u need anything else