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:
- Perform a search on the "Country Data" sheet using the
findAll()function. - The
getRow()function will return single row containing all country information. - A combination of
getLastColumn()andgetRange()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();
