← All posts · 2026-08-17 · Sophia Excel & Automation

Google Sheets GPS Check-In Distance Validation

Intro – How do you block off-site check-ins?

People looking for Google Sheets GPS check-in distance validation typically have the same headache. They want to prevent drivers or contractors from pressing the check-in button early, from home or while still on the road, before they even get to the warehouse. The sheet shows a check-in time that looks like an arrival time, but the truck hasn’t actually arrived yet, which throws off on-site operations and KPIs.

In the previous post, Google Sheets Container Number Check-In: Building a Driver Arrival Lookup by Appointment, we built a check-in web app screen with Google Apps Script and implemented pulling appointment information by container number. In this post we’ll layer on a “Google Sheets GPS check-in method” so that check-ins from outside a defined radius are never saved at all. The core idea is to configure a reference coordinate and an allowed radius, then use the haversine formula in Apps Script to calculate distance and decide whether a check-in is inside or outside that radius.

By following this post you’ll be able to: 1) manage the warehouse reference coordinate and the allowed radius (for example, 150m) as configuration values, and 2) implement an “on-site check-in location validation” flow that only saves a check-in to the sheet when the GPS coordinates from the driver’s phone fall inside that radius.

Understanding the GPS validation structure for check-in

The first design decision when setting up a Google Sheets on-site check-in restriction is “where do we validate?”. Typically you have two options: validate distance only in the browser (client), or perform the final validation on the Apps Script server. From an operational standpoint, the second option—making the server the final decision-maker—is safer.

If you only validate on the client, users can bypass it by editing the script in developer tools or tampering with HTTP requests. In contrast, if the server enforces a strict rule—“if outside the allowed radius, reject the check-in no matter what”—then even if the frontend script partially breaks, the actual stored data quality is still protected. In this post, the client simply reads the coordinates and sends them, and the CHK2_validateCheckinData_() function on the server makes the final decision.

Here’s the flow in summary:

1) When the user presses the check-in button in the browser, it reads latitude and longitude via navigator.geolocation and stores them in formData.lat and formData.lng.

2) This object is passed to an Apps Script server function like google.script.run.CHK2_checkIn.

3) Inside the server function, it calls CHK2_validateCheckinData_() to validate container, appointment status, and GPS together.

4) Inside CHK2_validateCheckinData_(), it calls CHK2_distanceMeters_() to calculate the distance from the reference coordinate; if it’s greater than the allowed radius, the function returns a failure message and the sheet write is skipped.

5) If the check-in is inside the radius, it returns ok: true and a cleaned data object, and the save function uses this to append a check-in row.

Once you establish this structure, moving the warehouse, changing the allowed radius, or changing the “GPS required or not” policy can all be handled by tweaking configuration constants, which keeps maintenance simple.

Setting reference coordinates and radius – check-in-specific config constants

For GPS validation, the basic question is “what point is the center, and how far out is allowed?”. In practice, you usually pick a single point such as the warehouse main gate, security checkpoint, or the center of the dock doors. It’s best to group this coordinate and the allowed radius in a single object instead of sprinkling them throughout your source code, so one change updates the entire system.

To avoid conflicts when combining this code with other posts in the series in the same Apps Script project, the constants and functions in this part use the CHK2_ prefix. The previous post already used the CHK_ namespace, so in this post we use only CHK2_ to prevent “Identifier has already been declared” errors. Add the constants below to your Google Sheets Apps Script project so you can manage “warehouse coordinates” and “allowed radius” at a glance.

Step 1 — Create constants for the check-in reference coordinate and radius

• What it does: Defines the latitude/longitude of the allowed check-in center, the allowed radius in meters, and whether GPS is mandatory.
• Where to paste: Google Sheets → Extensions → Apps Script → at the very top of the check-in (GPS) related file.
• What to do after pasting: Update the values with your actual warehouse coordinates and policy, then save.

const CHK2_CONFIG = {                          // → Check-in (GPS) configuration bundle
  BASE_LAT: 37.123456,                         // → Warehouse reference latitude (edit here)
  BASE_LNG: -122.123456,                       // → Warehouse reference longitude (edit here)
  MAX_DISTANCE_M: 150,
  MAX_ACCURACY_M: 100,                         // -> Refuse to judge a fix looser than this                         // → Allowed radius (in meters)
  REQUIRE_GPS: true                            // → Whether to reject if location is missing
};                                             

How to check it: If save works without errors and you see CHK2_CONFIG in autocomplete from other files, it’s set up correctly. For the reference coordinate, right-click your warehouse in Google Maps and copy the latitude/longitude from there.

Calculating distance with the haversine formula

Next we need a function that calculates the distance between two GPS coordinates. We’ll use an Apps Script haversine distance calculation to find the shortest distance on the earth’s surface in meters. There’s one important detail: you should immediately reject any input that can’t be converted to a number. Otherwise NaN can quietly propagate through the calculation, causing validations to always fail or incorrectly pass.

In real deployments, depending on the browser or device, latitude and longitude may arrive as strings like "37.123456" instead of numeric types. If you only check with Number.isFinite(lat), all of these strings will fail validation. In this post we first run them through Number(), then verify that the result is a finite number.

Step 2 — Create the CHK2_distanceMeters_ function

• What it does: Calculates the distance between two coordinates (latitude/longitude) and returns an integer number of meters.
• Where to paste: In the same Apps Script file, directly below CHK2_CONFIG.
• What to do after pasting: Add the test function shown below and check the execution log to make sure the distance is being logged correctly.

function CHK2_distanceMeters_(lat1Raw, lng1Raw, lat2Raw, lng2Raw) { // → Distance between two points
  const lat1 = Number(lat1Raw);                          // → Convert first latitude to number
  const lng1 = Number(lng1Raw);                          // → Convert first longitude to number
  const lat2 = Number(lat2Raw);                          // → Convert second latitude to number
  const lng2 = Number(lng2Raw);                          // → Convert second longitude to number

  if (!Number.isFinite(lat1) || !Number.isFinite(lng1) || // → Validate after conversion
      !Number.isFinite(lat2) || !Number.isFinite(lng2)) { // → Check all four values
    throw new Error('Coordinate values are invalid');          // → Block invalid inputs
  }

  if (lat1 < -90 || lat1 > 90 ||                        // → Latitude allowed range
      lat2 < -90 || lat2 > 90 ||                        // → Check both coordinates
      lng1 < -180 || lng1 > 180 ||                      // → Longitude allowed range
      lng2 < -180 || lng2 > 180) {                      // → Block typos/outliers
    throw new Error('Coordinate range is not valid');        // → Range error message
  }

  const R = 6371000;                                    // → Earth radius (meters)
  const toRad = Math.PI / 180;                          // → Degrees-to-radians factor

  const φ1 = lat1 * toRad;                              // → First latitude in radians
  const φ2 = lat2 * toRad;                              // → Second latitude in radians
  const Δφ = (lat2 - lat1) * toRad;                     // → Latitude difference in radians
  const Δλ = (lng2 - lng1) * toRad;                     // → Longitude difference in radians

  const sinHalfDLat = Math.sin(Δφ / 2);                 // → Sine of half the latitude diff
  const sinHalfDLng = Math.sin(Δλ / 2);                 // → Sine of half the longitude diff

  const a = sinHalfDLat * sinHalfDLat +                 // → Haversine intermediate value
            Math.cos(φ1) * Math.cos(φ2) *               // → Latitude adjustment term
            sinHalfDLng * sinHalfDLng;                  // → Squared longitude term

  const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a)); // → Central angle

  const d = R * c;                                      // → Distance (meters)
  return Math.round(d);                                 // → Return rounded integer
}                                                       

To check it, add a test function like this and run it:

function CHK2_testDistance_() {                        // → Distance test function
  const baseLat = CHK2_CONFIG.BASE_LAT;               // → Reference latitude
  const baseLng = CHK2_CONFIG.BASE_LNG;               // → Reference longitude
  const dist = CHK2_distanceMeters_(                  // → Run distance calculation
    baseLat, baseLng, baseLat, baseLng + 0.001        // → Slight longitude shift
  );
  Logger.log('Distance (m): ' + dist);                // → Log the result
}                                                     

After execution, if the log shows a value on the order of tens or hundreds of meters, it’s working. The exact value will depend on your reference coordinate.

Adding a GPS distance restriction to check-in validation

But it is worth being precise about what this does and does not stop. Server-side validation removes the easy bypass of editing the page script to skip the check: the radius decision lives on the server, so the saved data keeps its standard even if the front end is broken or tampered with.

What it does not do is authenticate the position. The latitude and longitude arriving at the server are values the browser sent. Anyone crafting the request directly can post the warehouse coordinates, and the server cannot tell those apart from a real fix. So treat this as an operational safeguard, not a strong access control. It filters out mistakes and habitual early check-ins; it does not stop a determined forgery. Sites that need real enforcement should add a signal that only exists on location - a gate terminal, a QR code, or a beacon.

The rules are:

• When REQUIRE_GPS === true
• If latitude/longitude are missing or can’t be converted to numbers, we fail with a message like “You must allow location access to check in.”
• If coordinates exist but are outside the radius (MAX_DISTANCE_M), we fail with a message like “You can only check in on-site (within Xm).”
• If the coordinates are inside the radius, we let the check-in pass and add lat, lng, and distanceM to the cleaned data so later save logic can use them.

• When REQUIRE_GPS === false
• If there are no coordinates, we skip GPS validation and only validate the other fields.
• If coordinates are present, we calculate distance and keep it with the data, but we don’t block check-ins just for being outside the radius (useful for logging or during policy rollout).

Step 3 — Add GPS validation into CHK2_validateCheckinData_

• What it does: Integrates GPS distance validation into the existing container/appointment validation flow, and blocks check-ins outside the radius.
• Where to paste: Use this instead of your existing check-in validation function, or migrate your current validateCheckinData_ logic into this structure. When merging with code from other parts of the series, keep the function name exactly CHK2_validateCheckinData_ so names don’t collide.
• What to do after pasting: Try a real check-in via the web app and confirm that the behavior and sheet writes match your expectations in three scenarios: location permission off, outside radius, and inside radius.

function CHK2_validateCheckinData_(formData) {         // → Validate check-in input
  const result = {                                     // → Default validation result
    ok: false,                                         // → Success flag
    message: '',                                       // → User-facing message
    cleaned: null                                      // → Cleaned data payload
  };                                                  

  if (!formData) {                                     // → When no payload came through
    result.message = 'No data was sent.';              // → Error message
    return result;                                     // → Exit immediately
  }                                                    

  const containerNo = (formData.containerNo || '')     // → Extract container number
    .toString()                                        // → Convert to string
    .trim();                                           // → Trim whitespace

  if (!containerNo) {                                  // → If container number is empty
    result.message = 'Please enter a container number.'; // → Prompt message
    return result;                                     // → Return failure
  }                                                    

  // ---- Start GPS location validation ----
  // A whitespace-only string is not the empty string, but Number(' ') is **0** - which would
  // measure the distance from latitude/longitude 0, in the middle of the Atlantic. Trim first.
  const latRaw = (formData.lat == null) ? '' : String(formData.lat).trim();
  const lngRaw = (formData.lng == null) ? '' : String(formData.lng).trim();
  const accRaw = (formData.accuracyM == null) ? '' : String(formData.accuracyM).trim();

  let latNum = null;                                   // → Numeric latitude storage
  let lngNum = null;                                   // → Numeric longitude storage
  let distanceM = null;                                // -> Distance from the base point
  let accuracyM = null;                                // -> Reported fix accuracy (m)

  if (latRaw !== '' && lngRaw !== '') {               // -> Both values actually present
    try {                                              // → Guard against conversion errors
      distanceM = CHK2_distanceMeters_(               // → Calculate distance from reference
        latRaw,                                       // → Raw current latitude
        lngRaw,                                       // → Raw current longitude
        CHK2_CONFIG.BASE_LAT,                         // → Reference latitude
        CHK2_CONFIG.BASE_LNG                          // → Reference longitude
      );
      latNum = Number(latRaw);                        // → Store numeric latitude
      lngNum = Number(lngRaw);                        // → Store numeric longitude
      accuracyM = (accRaw === '') ? null : Number(accRaw); // -> Accuracy (null if absent)
    } catch (e) {                                      // → Coordinate/range errors, etc.
      result.message = 'An error occurred while reading location information.'; // → Generic error
      return result;                                   // → Return failure
    }
  }                                                   

  if (CHK2_CONFIG.REQUIRE_GPS) {                       // → When location is required
    if (latNum === null || lngNum === null) {         // → No coordinates available
      result.message = 'You must allow location access to check in.'; // → Permission hint
      return result;                                   // → Return failure
    }
    // If the fix is looser than the radius, "inside" and "outside" are not distinguishable.
    // Judging a 150 m radius with a 500 m fix is the same as judging nothing at all.
    if (!Number.isFinite(accuracyM) ||                // -> Accuracy missing, or
        accuracyM > CHK2_CONFIG.MAX_ACCURACY_M) {     // -> too imprecise
      result.message =
        'GPS accuracy is too low (error ' +
        (Number.isFinite(accuracyM) ? Math.round(accuracyM) + 'm' : 'unknown') +
        '). Please step outside and try again';
      return result;                                   // -> Hold the decision
    }
    if (distanceM > CHK2_CONFIG.MAX_DISTANCE_M) {     // → Exceeds allowed radius
      result.message =
        'You can only check in on-site (within ' +     // → Off-site message
        CHK2_CONFIG.MAX_DISTANCE_M + 'm).';            // → Show allowed radius
      return result;                                   // → Return failure
    }
  } else {                                             // → When location is optional
    // If coordinates are present, we keep them and the distance for reference,
    // but we do not block the check-in for being outside the radius.
    // If there are no coordinates, we simply continue without extra checks.
  }
  // ---- End GPS location validation ----

  // ---- Below is where you plug in appointment lookup, status checks, and final cleaning ----
  // Example: const apptInfo = getApptInfoByCntr(containerNo); etc.
  // This post focuses on the GPS flow, so we include only a minimal example here.

  result.ok = true;                                    // → All checks passed (example)
  result.message = 'Check-in is allowed.';             // → Success message
  result.cleaned = {                                   // → Example of cleaned data
    containerNo: containerNo,                          // → Container number
    lat: latNum,                                       // → Latitude (null if missing)
    lng: lngNum,                                       // → Longitude (null if missing)
    accuracyM: accuracyM,                             // -> Fix accuracy (null if absent)
    distanceM: distanceM                               // → Distance from reference (null if missing)
  };

  return result;                                       // → Final return
}                                                      

How to verify: from the web app,

1) With location permission turned off, attempt a check-in and confirm the message “You must allow location access to check in.” appears.

2) From a location far from your reference point, hard-code a test coordinate and confirm that “You can only check in on-site (within Xm).” appears and no row is added to the sheet.

3) With a test coordinate near your reference point, make sure you see “Check-in is allowed.” and a check-in row is actually appended to the sheet.

Practical tips – radius, policy, and error handling

Once you deploy a Google Sheets GPS check-in method in the real world, you’ll find that policy and exceptions matter more than the code itself. Based on field experience, here are a few guidelines:

1. Set the radius as “GPS error + waiting area distance”

Outdoor GPS error commonly runs 10–30m. On top of that, many facilities accept check-ins not right at the dock but in a staging or queue area. If the distance from your reference point to the waiting area is 50–70m, you’ll need at least 100m, and more comfortably 150–200m, so drivers aren’t rejected unnecessarily.

1. Don’t flip REQUIRE_GPS to true on day one; plan a transition

Some older devices or internal security policies make it hard to keep location services always on. In those environments, start with REQUIRE_GPS: false so that location is logged when available but check-ins are still allowed when it’s missing. Once device and policy updates are complete, switch it to true; this step-down approach reduces confusion.

1. Use LockService at save time in addition to GPS validation

If multiple users press check-in for the same container almost simultaneously, you can still get duplicate rows even if GPS validation passes. To avoid this, reuse the pattern from Preventing Duplicate Inbound Appointments in Google Sheets with LockService.

After passing GPS validation, acquire a LockService.getScriptLock(), re-check the appointment status, write the row, and release the lock in a finally block. In production this has proven to be the most reliable pattern.

1. When changing policy, communicate clearly and define exceptions

If you suddenly start blocking check-ins outside “Xm from the warehouse” without explanation, drivers will assume the system is broken. Announce that “location permission and an on-site radius of Xm are now required,” and for at least the first week define a clear exception process (for example, manual check-in by on-site staff approval when the system rejects) to minimize disruption.

Closing

Implementing Google Sheets GPS check-in distance validation in Apps Script goes a long way toward blocking “early check-ins pressed off-site.” In this post, we separated the series into a CHK2_ namespace so it can live in the same project as the previous code without collisions like Identifier 'CHK_CONFIG' has already been declared.

The three key pieces are:

1) CHK2_CONFIG for centralized management of reference coordinate, radius, and policy,

2) CHK2_distanceMeters_() for robust distance calculation between coordinates, and

3) CHK2_validateCheckinData_() to use that distance on the server side to strictly block check-ins outside the radius.

One concrete next step is to open the Apps Script editor, paste in the three blocks—CHK2_CONFIG, CHK2_distanceMeters_(), and CHK2_validateCheckinData_()—and then, from a test web app, simulate the three scenarios: inside radius, outside radius, and with location turned off. Once you do this once, you’ll see a noticeable jump in the reliability of your Google Sheets–based check-in system on the ground.