Apply custom formula across all columns using Array Formula

Question

I have trouble applying my custom formula across all columns using Array Formula. My custom formula is named getCountry where it could get the country based on an address. The address components e.g. street or city are also separated per column. When I apply the formula to a cell, it displays the correct country. When I enclosed it in ArrayFormula, it displays the list of countries in the selected cell.

I want to apply the ArrayFormula function because if there are new rows I want to apply the formula automatically. Is there a workaround for this if ArrayFormula will not work?

Here's the sample spreadsheet.

https://docs.google.com/spreadsheets/d/1W3s1a8mG2fkmFlrez9mQLqwtyIcl-gNym1xtvLPFN1Q/edit?usp=sharing

function getCountry(address) {
  country_list = [];
    var response = Maps.newGeocoder().geocode(address);
    country = []
    for (var i = 0; i < response.results.length; i++) {
      var result = response.results[i];
      for (var comp of result.address_components) {
        if (comp.types.indexOf("country") > -1) country.push(comp.long_name)
      }
    }
    country_list.push((country.length > 1)?country.join(): country[0]);
  return country_list;
}

Edit: I've managed to make it work on a smaller dataset. But when I apply it to a much bigger dataset it will prompt with an error "exceeded maximum execution time". How do I make it better so that it will not return with that error when applied on a bigger dataset? The formula I used is:

=transpose(split(getCountryList(transpose(query(transpose(filter(A2:D,not(isblank

and the custom functions that I used now are:

function getCountry(address){
  var response = Maps.newGeocoder().geocode(address);
    country = []
    for (var i = 0; i < response.results.length; i++) {
      var result = response.results[i];
      for (var comp of result.address_components) {
        if (comp.types.indexOf("country") > -1) country.push(comp.long_name)
      }
    }
    return (country.length > 1)?country.join(): country[0];
}

function getCountryList(address_array) {
  country_list = []
  for (i in address_array){
    var country = getCountry(address_array[i]);
    country_list.push(country);
  }
  return country_list.join();
}

Answer 1

You can modify the getCountryList() function so that it accepts a range argument, and add online function help, like this:

/**
* Guesses the country of a number of addresses listed in rows.
* Each address can consist of several consecutive cells in the same row.
*
* @param {A2:D} addresses The range that contains street addresses.
* @return {String[][]} The countries that match the addresses.
* @customfunction
*/
function GetCountry(addresses) {
  if (!Array.isArray(addresses)) {
    addresses = [[addresses]];
  }
  return addresses.map(row => [getCountry_(row.join(' '))]);
}

function getCountry_(address) {
  if (!address || !address.trim()) {
    return null;
  }
  const country = [];
  const responses = Maps.newGeocoder().geocode(address);
  responses.results.forEach(result => {
    for (let comp of result.address_components) {
      if (comp.types.indexOf('country') !== -1) {
        country.push(comp.long_name);
      }
    }
  });
  return country.join();
}

You can use the custom function like this:

=GetCountry(A2:D)

These changes should result in a bit smaller number of calls to the Maps service, but each address is still retrieved separately, which is a performance bottleneck. I have not checked whether there is a batch call method in Maps that would let you send multiple requests in one go. If you do not find a batch call method, try using several GetCountry() formulas, each handling say 100 rows.

See custom function optimization tips.