CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
ZxNuClear
ZxNuClear

Reputation: 236

Finding a value from a range in Google Sheets

Here is my test sheet. I need to do a search on this table using Google Apps Script. For example, the page will have <input>, I enter there a number or text from the range B1:S54 of my sheet and when I click on the button, I get the result in the form of the Sector number, in row of which this number or text is located.

Upvotes: 0

Views: 1714

Answers (1)

doubleunary
doubleunary

Reputation: 18698

You already got two answers how to do this with a spreadsheet formula in the other thread. To get the formula result in Google Apps Script, use sheet.getRange('V3').getValue().

If your intention is to implement a search dialog box completely in Google Apps Script, use this:

function findSector(key) {
  const data = SpreadsheetApp.getActive().getRange('Main!A1:S').getValues();
  const sectors = data.map(row => row.shift());
  return sectors.filter((sector, index) => data[index].some(value => value === key));
}

The function will return an array that lists matching sectors. To get just the first result, use const result = findSector('item12')[0].

Upvotes: 2

Related Questions