Автоматизация в путевых листах через гугл таблицы и скрипты в 2023 году

Путевой лист — это такой документ, который является основанием для списания горючего в состав расходов в бухгалтерском и налоговом учёте. А ещё путевые листы нужны, чтобы обосновать необходимость аренды или лизинга автотранспорта. 

Однако путевой лист достаточно запутанный первичный документ и на его заполнение можно потратить много времени. И это время нужно тратить регулярно. Эта статья - моя попытка сделать универсальный шаблон в гугл таблицах с возможностью полного автоматического заполнения буквально в два клика.  

Мой шаблон не претендует на соответствие всем действующим нормам, а в первую очередь нацелен на кардинальное уменьшение времени, которое тратится на заполнение путевого листа.

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

Новый путевой лист с 2023 года

В настоящий момент, в 2023 году форма путевого листа может быть произвольной, но должна быть утверждена руководителем предприятия, и всё же в ней необходимо отразить определенную информацию, такую как:

• Информацию о водителе

• Данные о транспортном средстве

• Данные о ТС

• Срок действия путевого листа

Путевые листы составляют организации и индивидуальные предприниматели, которые используют транспорт в своей деятельности.

Как работает таблица

Моя форма путевого листа доступна по ссылке. По ссылке откроется запрос на создание вашей копии. При согласии будут также созданы копии прикрепленного файла Apps Script и относящихся к нему функций генерации адресов.

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

1. Информация о владельце транспортного средства: наименование, ИНН, ОГРН, адрес, телефон;

2. Информация об автомобиле: модель, государственный регистрационный знак, идентификационный номер (VIN);

3. Информация о водителе: фамилия имя отчество, номер водительского удостоверения.

Автоматизация начинается с первой даты выезда - ячейки B24 - как только вы введете эту дату таблица сама заполнит оставшиеся даты этого столбца рабочими днями - там прописана формула.

Нижняя часть путевого листа содержит очень важные исходные данные для автоматизации:

1. Начальный километраж по одометру.

2. Конечный километраж по одометру.

Как только вы заполните эти две ячейки таблица через формулы заполнит ежедневные показания одометра и дневной пробег - каждый день будет разный пробег, но в усредненных значениях. 

Время выезда и заезда тоже вычисляется рандомно - можно поменять цифры в ячейке и изменить на любой другой часовой интервал.  

Самые нижние строчки, связанные с остатком топлива в баке и количеством заправленного топлива и на автоматизацию не влияют.

Как настроить случайное место назначения в нужном районе

После того как даты и времена заезда и выезда, а ещё показаниями одометра на каждый день заполнены переходим к случайным адресам. Здесь пригодятся функции Apps Script. 

/**
 * Use Reverse Geocoding to get the address of
 * a point location (latitude, longitude) on Google Maps.
 *
 * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
 * 
 * https://www.labnol.org/google-maps-sheets-200817
 * 
 * https://developers.google.com/apps-script/reference/maps/geocoder#setlanguagelanguage
 *
 * @param {String} latitude The latitude to lookup.
 * @param {String} longitude The longitude to lookup.
 * @return {String} The postal address of the point.
 * @customFunction
 */

const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => {
    const {
        results: [data = {}] = []
    } = Maps.newGeocoder().setLanguage('ru').reverseGeocode(latitude, longitude);
    return data.formatted_address;
};

Скрипт позволит запускать формулу вида =GOOGLEMAPS_REVERSEGEOCODE(58,0205391879819; 56,2652395772995) которая возвращает адрес по координатам из гугл карт. Например, для этого запроса возвращается адрес Краснокамская ул., 20, Пермь, Пермский край, Россия, 614060.

Существует ограничение на количество запросов геокодирования, но несколько листов в день можно заполнить без проблем. Если нужно, больше, то надо уже получать ключ API сервиса.

Дальше надо генерировать случайные географические координаты по количеству дней, для того чтобы потом превращать их через обратное геокодирование в обычный адрес.

/**
 * Get random geo point [latitude, longitude] within some distance from specified geo point (lat, long)
 *
 * Points will be uniformly-distributed on multiple calls.
 *
 * @param lat Latitude in degrees
 * @param lng Longitude in degrees
 * @param distance Distance in meters to limit point distribution to.
 *                 If negative value is provided, points will be generated on exact distance (e.g. on circle border).
 * @returns {*[]} Array with [latitude, longitude]
 */

var getRandomLocation = function(lat, lng, distance = 10000) {
    // Convert to radians
    lat *= Math.PI / 180;
    lng *= Math.PI / 180;

    var radius;

    // Distance should be set in meters, negative for exact distance
    if (distance < 0) {
        // Exact distance
        radius = Math.abs(distance);
    } else {
        // Get uniformly-random distribution within peovided distance
        // http://stackoverflow.com/questions/5837572/generate-a-random-point-within-a-circle-uniformly
        radius = Math.random() + Math.random();
        radius = radius > 1 ? 2 - radius : radius;
        radius *= distance ? distance : 10000; // multiply by distance meters
    }

    // Convert radius from meters to degrees to radians
    // 111319.9 meters = one degree along the equator
    radius /= 111319.9;
    // Correction for the actual distance from equator is NOT needed here
    // radius *= Math.cos(lat);
    // Convert to radians
    radius *= Math.PI / 180;

    // Random angle
    var angle = Math.random() * Math.PI * 2;

    // Get a point {nLat,nLng} in a distance={radius} out on the {angle} radial from point {lat,lng}
    // From Aviation Formulary V1.46 By Ed Williams:
    // → http://williams.best.vwh.net/avform.htm#LL
    // → ftp://ftp.bartol.udel.edu/anita/amir/My_thesis/Figures4Thesis/CRC_plots/Aviation%20Formulary%20V1.46.pdf
    // → https://github.com/arildj78/AvCalc/blob/master/avform.txt
    // [section "Lat/lon given radial and distance"]
    var nLng,
        nLat = Math.asin(Math.sin(lat) * Math.cos(radius) + Math.cos(lat) * Math.sin(radius) * Math.cos(angle));
    if (Math.cos(nLat) == 0) {
        nLng = lng;
    } else {
        nLng = (lng - Math.asin(Math.sin(angle) * Math.sin(radius) / Math.cos(nLat)) + Math.PI) % (Math.PI * 2) - Math.PI
    }

    // Convert to degrees
    nLat *= 180 / Math.PI;
    nLng *= 180 / Math.PI;

Пошаговое руководство по заполнению

В самом начале выбирайте на гугл картах точку, от которой хотите оттолкнуться для генерации случайных адресов.

Далее перейдите в гугл скрипты - для этого выбирайте пункт меню Apps Script.

Вписывайте скопированную точку в скрипт и указывайте имя вкладки таблицы. Дополнительно указывайте радиус генерации случайных точек в метрах. В моём примере указано 2000 метров.

При первом запуске скрипта надо разрешить выполнение скрипта от своего аккаунта.

После выполнения скрипта в появившемся журнале выполнения можно будет увидеть информацию об успешной генерации случайных адресов:

После этого все места назначения на указанной вкладке будут заполнены случайными координатами, которые при помощи формулы обратного геокодирования будут сразу преобразованы в адреса.

Что делать после заполнения данных

После генерации координат и обратного геокодирования рекомендую скопировать адреса мест назначений как на скриншоте ниже и вставить Только значения чтобы адреса стали просто текстом, без формулы.

Ведь если много вкладок, то можно легко потратить собственный суточный лимит бесплатных конвертаций впустую.

Итог

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

• начального и конечного показания одометра автомобиля;

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

Когда начальные данные уже заданы, то следующий месяц можно полностью заполнить данными меньше чем за минуту.

Автор: Михаил Шардин,

17 января 2023 г.