Измеряем расстояние между городами в Google sheets

В начале июня было принято решение поехать в небольшой отпуск по окрестностям Санкт-Петербурга, а так же заехать на Валдай. В связи с этим, я захотел посчитать километраж по дням:

1. Дни недели в зависимости от даты

В гугл таблицах есть встроенная функция WEEKDAY, которая возвращает порядковый номер дня исходя из заданной даты, со следующим синтаксисом:
WEEKDAY(дата, [тип])

дата – дата, для которой нужно определить день недели. В качестве этого параметра используется ссылка на ячейку с датой.

тип – [необязательный – 1 по умолчанию] – числовой параметр, задающий систему отсчета дней недели. По умолчанию первым днем недели считается воскресенье — что мне совершенно не подходило.

Если тип равен 1, счет дней начинается с воскресенья. Воскресенью присваивается номер 1, а субботе – 7.

А вот то что нужно: если тип равен 2, счет дней начинается с понедельника. Понедельнику присваивается номер 1, а воскресенью – 7.

Но мне же нужно название (имя) дня, а не его порядковый номер.

И тут на помощь приходит функция CHOOSE, которая возвращает значение из списка на основании его порядкового номера.

Синтаксис у нее следующий: CHOOSE(индекс; значение1; [значение2; …])

И в результате получается следующая формула:

=CHOOSE(WEEKDAY(A1;2);"Понедельник";"Вторник";"Среда";"Четверг";"Пятница";"Суббота";"Воскресенье")

где, в WEEKDAY с типом 2 было получен «числовой индекс» дня, а затем через CHOOSE получено значение на основе индекса.

2. Измерение расстояния между городами

Тут уже не обойдется без скриптов. Описание класса есть на Google Developers, которое и было взято за основу:

https://developers.google.com/apps-script/reference/maps/direction-finder

Описание очень детальное и подробное, из него нужно следующее:

function getRoute(city1, city2) {
const directions = Maps.newDirectionFinder()
.setOrigin(city1)
.setDestination(city2)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
return route = Math.round(directions.routes[0].legs[0].distance.value / 1000);
}

Функция принимает 2 аргумента: city1 — город начала движения, city2 — конечная точка

.setOrigin(city1) — устанавливает начальную точку для расчета маршрута с использованием адреса.

.setDestination(city2) — устанавливает конечное местоположение, для которого нужно рассчитать маршрут, используя адрес.

.setMode(Maps.DirectionFinder.Mode.DRIVING) — устанавливает режим движения (по умолчанию — вождение).

Вообще режимов движения 4 — вождение, велосипед, пешком, общественным транспортом (где есть такая опция):

https://developers.google.com/apps-script/reference/maps/mode

.getDirections(); — возвращает объект с маршрутами, используя исходную точку, пункт назначения и другие заданные параметры.

если посмотреть внутрь объекта directions.routes, то можно увидеть следующее:

в нем нужен directions.routes[0].legs

и вот тут-то и находится расстояние между городами — distance. На примере расстояние между Москвой и Волгоградом: 970 км, как текст, а дальше — value — 969932 — число. То есть метры. Они то и нужны.

чтобы получить километры — делю на тысячу: directions.routes[0].legs[0].distance.value / 1000

Дальше округление и возврат результата:

return route = Math.round(directions.routes[0].legs[0].distance.value / 1000);

В самих гугл таблицах результат выглядит так:

У этой записи 6 комментариев

  1. Дмитрий

    Добрый день. Почему-то не получается использовать ваш скрипт, выдает ошибки на .setOrigin(city1). Но если я вместо city1, city2 ставлю конкретные адреса, то все работает

    1. Добрый день!
      Как именно Вы используете скрипт? В моей версии city1 и city2 = это получаемые аргументы написанной функции в гугл скрипте с листа гугл таблицы.

      function getRoute(city1, city2) {

      const directions = Maps.newDirectionFinder()
      .setOrigin(city1)
      .setDestination(city2)
      .setMode(Maps.DirectionFinder.Mode.DRIVING)
      .getDirections();

      return route = Math.round(directions.routes[0].legs[0].distance.value / 1000);

      Т.е названия городов она получает из гугл таблицы — из ячеек листа — там они прописаны руками (как на картинке в посте) и когда я пишу в гугл листе формулу =GETROUTE(C3;C4)
      С3 — Москва и С4 — Владимир — эти города передаются в функцию и она считает расстояние между ними

      Если вы используете функцию без привязки к чему-либо
      Под/над ней создайте функцию
      function test (){
      const city1 = ‘Москва’
      const city2 = ‘Владимир’
      getRoute(city1,city2)
      }
      и запустите ее — результат будет аналогичный

  2. Андрей

    Подскажи возможно как то побороть Exception: Service invoked too many times for one day: route. (строка 7).

    1. Добрый день.
      Без самого скрипта, кода, первое что приходит в голову это то, что Вы превысили квоты гугла на количество по выполнению скрипта. Подробнее тут — https://developers.google.com/apps-script/guides/services/quotas
      Второе — опять же связанное с квотами, но уже в рамках самого скрипта: может есть какой-то цикл по расчету расстояния…

  3. Данил

    Добрый день !
    Как сделать так, чтобы учитывались промежуточные точки или можно было брать данные с кругового маршрута ?
    Например дорога Екатеринбург — Челябинск — Екатеринбург, пишет что 0

    1. Добрый день.
      Для этого вам надо указать параметр .addWayPoint

      function getRoute() {
      const directions = Maps.newDirectionFinder()
      .setOrigin(55.9095511423134, 37.01185986937901) // химки
      .addWaypoint(54.217445563626946, 37.813861782518636) // тула
      .setDestination(55.10702296076177, 38.91249454024414) // коломна
      .setMode(Maps.DirectionFinder.Mode.DRIVING)
      .getDirections();

      route1KhimkiTula = Math.round(directions.routes[0].legs[0].distance.value / 1000); //253 км
      route2TulaKolomna = Math.round(directions.routes[0].legs[1].distance.value / 1000); //198 км
      console.log(route1KhimkiTula, route2TulaKolomna)

      let totalRoute = 0
      directions.routes[0].legs.forEach((element) =>
      totalRoute = totalRoute + (element.distance.value / 1000))
      console.log(totalRoute) // 450 км
      }

Добавить комментарий