How to 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 only displays the country of the first row.

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

Here's the code for my custom function:

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

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(A2:A)))),,rows(filter(A2:A,not(isblank(A2:A))))))),","))

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();
}

You need to modify your script in order to make it ArrayFormula work with your custom function. Basically, the arrayformula passes an array of values to your function so you need your function to receive it as an array and process it 1 by one. — Jason E.

doubleunary doubleunary · Accepted Answer · 2021-06-15T19:48:39

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 A range that contains street addresses, one address per row.
* @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.

How to Apply custom formula across all columns using Array Formula

1 Answers