Google Apps Script: Perform Text Search In Google Sheets and Return Matched Row

I've been delving further into the world of Google App Scripts and finding it my go-to when having to carry out any form of data manipulation. I don't think I've ever needed to develop a custom C# based import tool to handle the sanitisation and restructuring of data ever since learning the Google App Script approach.

In this post, I will be discussing how to search for a value within a Google Sheet and return all columns within the row the searched value resides. As an example, let's take a few columns from a dataset of ISO-3166 Country and Region codes as provided by this CSV file and place them in a Google Sheet named "Country Data".

The "Country Data" sheet should have the following structure:

name	alpha-2	alpha-3	country-code
Australia	AU	AUS	036
Austria	AT	AUT	040
Azerbaijan	AZ	AZE	031
United Kingdom of Great Britain and Northern Ireland	GB	GBR	826
United States of America	US	USA	840

App Script 1: Returning A Single Row Value

Our script will be retrieving the two-letter country code by the country name - in this case "Australia". To do this, the following will be carried out:

1. Perform a search on the "Country Data" sheet using the findAll() function.
2. The getRow() function will return single row containing all country information.
3. A combination of getLastColumn() and getRange() functions will output values from the row.

function run() {
  var twoLetterIsoCode = getCountryTwoLetterIsoCode("Australia"); 
}

function getCountryTwoLetterIsoCode(countryName) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var countryDataSheet = activeSheet.getSheetByName('Country Data');

  // Find text within sheet.
  var textSearch = countryDataSheet.createTextFinder(countryName).findAll();

  if (textSearch.length > 0) {
    // Get single row from search result.
    var row = textSearch[0].getRow();    
    // Get the last column so we can use for the row range.
    var rowLastColumn = countryDataSheet.getLastColumn();
    // Get all values for the row.
    var rowValues = countryDataSheet.getRange(row, 1, 1, rowLastColumn).getValues();

    return rowValues[0][1]; // Two-letter ISO code from the second column.
  }
  else {
    return "";
  }
}

When the script is run, the twoLetterIsoCode variable will contain the two-letter ISO code: "AU".

App Script 2: Returning Multiple Row Matches

If we had a dataset that contained multiple matches based on a search term, the script from the first example can be modified using the same fundamental functions. In this case, all we need to do is use a for loop and pass all row values to an array.

The getCountryTwoLetterIsoCode() will look something like this:

function getCountryTwoLetterIsoCode(countryName) {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var countryDataSheet = activeSheet.getSheetByName('Country Data');

  // Find text within sheet.
  var textSearch = countryDataSheet.createTextFinder(countryName).findAll();

  // Array to store all matched rows.
  var searchRows = [];

  if (textSearch.length > 0) {
    // Loop through matches.
    for (var i=0; i < textSearch.length; i++) {
      var row = textSearch[i].getRow();  
      // Get the last column so we can use for the row range.
      var rowLastColumn = countryDataSheet.getLastColumn();
      // Get all values for the row.
      var rowValues = countryDataSheet.getRange(row, 1, 1, rowLastColumn).getValues(); 

      searchRows.push(rowValues);
    }
  }

  return searchRows;
}

The searchRows array will contain a collection of matched rows as well as the column data. To carry out a similar output as shown in the first App Script example - the two-letter country code, the function can be called in the following way:

// Get first match.
var matchedCountryData = getCountryTwoLetterIsoCode("Australia")[0];

// Get the second column value (alpha-2).
var twoLetterIsoCode = matchedCountryData[0][1];

Conclusion

Both examples have demonstrated different ways of returning row values of a search term. The two key lines of code that allows us to do this are:

// Get the last column so we can use for the row range.
var rowLastColumn = countryDataSheet.getLastColumn();

// Get all values for the row.
var rowValues = countryDataSheet.getRange(row, 1, 1, rowLastColumn).getValues();