Блог

У меня возникли проблемы с применением моей пользовательской формулы ко всем столбцам с использованием формулы массива. Моя пользовательская формула называется getCountry, где она может получить страну на основе адреса. Компоненты адреса, например улица или город, также разделены по столбцам. Когда я применяю формулу к ячейке, она отображает правильную страну. Когда я вложил его в ArrayFormula, он отображает только страну первой строки.

Я хочу применить функцию ArrayFormula, потому что, если появятся новые строки, я хочу автоматически применить формулу. Есть ли обходной путь для этого, если ArrayFormula не будет работать?

Вот образец электронной таблицы.

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

Вот код для моей пользовательской функции:

 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]
  }
}
 

Правка: Мне удалось заставить его работать с меньшим набором данных. Но когда я применю его к гораздо большему набору данных, он выдаст сообщение об ошибке «превышено максимальное время выполнения». Как сделать его лучше, чтобы он не возвращался с этой ошибкой при применении к большему набору данных? Формула, которую я использовал, такова:

 =transpose(split(getCountryList(transpose(query(transpose(filter(A2:D,not(isblank(A2:A)))),,rows(filter(A2:A,not(isblank(A2:A))))))),","))
 

и пользовательские функции, которые я использовал сейчас, это:

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

Вы можете изменить getCountryList() функцию так, чтобы она принимала аргумент диапазона, и добавить интерактивную справку по функциям, например:

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

Вы можете использовать пользовательскую функцию следующим образом:

=GetCountry(A2:D)

Эти изменения должны привести к несколько меньшему количеству вызовов Maps службы, но каждый адрес по-прежнему извлекается отдельно, что является узким местом производительности. Я не проверял, существует ли метод пакетного вызова Maps , который позволил бы вам отправлять несколько запросов за один раз. Если вы не нашли метод пакетного вызова, попробуйте использовать несколько GetCountry() формул, каждая из которых обрабатывает, скажем, 100 строк.

См.раздел Советы по оптимизации пользовательских функций.