Automation in waybills through Google spreadsheets and scripts in 2023

A waybill is a document that is the basis for writing off fuel as an expense in accounting and tax accounting. And still waybills are needed to justify the need for renting or leasing vehicles.

However, the waybill is a rather confusing primary document and you can spend a lot of time filling it out. And this time should be spent regularly. This article is my attempt to make a universal template in Google Sheets with the ability to complete automatic filling in just two clicks.

My template does not claim to comply with all applicable regulations, but is primarily aimed at drastically reducing the time spent filling out a waybill.

In this case, filling occurs with randomly generated data based on only a few reliable initial parameters: the initial and final odometer readings of the car and the assignment of the exact area where this car is operated.

New waybill from 2023

At the moment, in 2023, the form of the waybill can be arbitrary, but must be approved by the head of the enterprise, and yet it must reflect certain information, such as:

  • Driver Information

  • Vehicle data

  • Vehicle data

  • Voucher validity period

Waybills are compiled by organizations and individual entrepreneurs who use transport in their activities.

How the table works

My waybill form available via link. The link will open a request to create your copy. If you agree, copies of the attached Apps Script file and related address generation functions will also be created.

Upper part of the waybill.  Editable fields are highlighted in red
Upper part of the waybill. Editable fields are highlighted in red

The top part of the waybill contains regular text fields that do not affect the automation:

  1. Information about the owner of the vehicle: name, TIN, PSRN, address, telephone;

  2. Information about the car: model, state registration plate, identification number (VIN);

  3. Information about the driver: last name, first name, driver’s license number.

Automation starts from the first check-out date – cells B24 – as soon as you enter this date, the table itself will fill in the remaining dates of this column with working days – the formula is written there.

The bottom of the waybill.  Editable fields are highlighted in red
The bottom of the waybill. Editable fields are highlighted in red

The bottom of the waybill contains a very important input for automation:

  1. Starting odometer mileage

  2. Final odometer mileage

As soon as you fill in these two cells, the table through the formulas will fill in the daily odometer readings and daily mileage – every day there will be a different mileage, but in average values.

Check-out and check-in times are also calculated randomly – you can change the numbers in the cell and change them to any other hourly interval.

The lowest lines related to the remaining fuel in the tank and the amount of fuel filled do not affect the automation.

How to set up a random destination in your area

After the dates and times of arrival and departure, as well as the odometer readings for each day, are filled in, we proceed to random addresses. This is where Apps Script functions come in handy.

The following reverse geocoding code (calculating an address from coordinates) has already been added to the script file associated with this table:
/**
 * 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;
};

The script will allow you to run a formula like =GOOGLEMAPS_REVERSEGEOCODE(58,0205391879819; 56,2652395772995) which returns the address by coordinates from google maps. For example, this request returns the address Краснокамская ул., 20, Пермь, Пермский край, Россия, 614060.

There is a limit on the number of geocoding requests, but several sheets per day can be filled without problems. If you need more, then you need to get the service API key.

Next, you need to generate random geographic coordinates by the number of days, in order to then turn them into a regular address through reverse geocoding.

This code allows you to get a random geographic point near a given point, within a certain radius, for example 2 kilometers.
/**
 * 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;

Step by step guide to fill out

At the beginning select point on google mapsfrom which you want to push off to generate random addresses.

Selecting geographic coordinates to randomly generate other points in the area
Selecting geographic coordinates to randomly generate other points in the area

Next, go to Google scripts – to do this, select the menu item Apps Script.

Go to the menu item Apps Script
Go to the menu item Apps Script

Enter the copied point in the script and specify the name of the table tab. Additionally, specify the radius of generation of random points in meters. In my example, 2000 meters are indicated.

When you first run the script, you must allow the script to run from your account.

After executing the script, in the appeared execution log, you can see information about the successful generation of random addresses:

Result of successful generation of random addresses
Result of successful generation of random addresses

After that, all destinations on the specified tab will be filled with random coordinates, which will be immediately converted into addresses using the reverse geocoding formula.

What to do after filling in the data

After generating coordinates and reverse geocoding, I recommend copying the destination addresses as in the screenshot below and pasting Только значения so that the addresses become just text, without a formula.

After all, if there are a lot of tabs, then you can easily waste your own daily limit of free conversions for nothing.

Paste formulas back as plain text
Paste formulas back as plain text

Outcome

The result is a fully completed waybill, which is as similar as possible to the real one. In this case, the filling occurs with randomly generated data based on only a few reliable initial parameters:

  • initial and final odometer readings of the car;

  • specifying the exact area where this vehicle is operated.

When the initial data is already set, the next month can be completely filled with data in less than a minute.

Author: Mikhail Shardin,

January 18, 2023

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *