GROperator

Create shipments from Google Sheets

This recipe turns a Google Sheet into a bulk shipment creator. A script reads every row that has no tracking number yet, creates the shipment through the API, and writes back the tracking number and status, or the error code and message so you can fix the row and run it again.

It uses an API key with the scopes shipments:write and shipments:read. The key stays in the script's private properties: it is never in a cell. Create the key as described in Enable the API.

Create a sheet named Shipments. Row 1 holds headers; data starts at row 2.

Column Header Notes
A Reference Your order number. Required. Becomes the shipment reference and the idempotency key
B Merchant Merchant id or exact account name. Required. The merchant's primary saved address is the pickup
C Recipient name Required
D Recipient phone Required. Format the column as Plain text so leading zeros stay
E City Arabic or English name. Required
F District Arabic or English name. Required
G Address line Street, building, landmark. Required
H Short address Saudi national short address, for example RNBA6769. Plain text
I-L Weight kg, Length cm, Width cm, Height cm Required, numbers
M COD amount Optional, defaults to 0
N Description Optional
O-R Tracking number, Status, Error code, Error message Written by the script

Arabic names such as جده and حي البرية work: spelling variants are tolerated (Places by name). The script sends the country as SA; change it in the script if you ship elsewhere.

Open Extensions, Apps Script, paste the script, save, and set three script properties (Project Settings): LASTMILE_API_URL, LASTMILE_API_KEY and LASTMILE_API_SECRET. Reload the sheet: a LastMile menu appears. The first run asks you to authorise the script.

Apps Script
/**
 * Create LastMile shipments from a Google Sheet.
 *
 * Setup: Extensions > Apps Script, paste this file, then Project Settings > Script properties:
 *   LASTMILE_API_URL     base address of the API, no trailing slash
 *   LASTMILE_API_KEY     lmc_live_...
 *   LASTMILE_API_SECRET  the secret shown once when the key was created
 * The key needs the scopes shipments:write (create) and shipments:read (replays return the shipment).
 *
 * Sheet "Shipments", row 1 = headers, data from row 2. Format the phone and short-address columns as Plain text.
 *   A Reference | B Merchant | C Recipient name | D Recipient phone | E City | F District | G Address line
 *   H Short address | I Weight kg | J Length cm | K Width cm | L Height cm | M COD amount | N Description
 * The script writes:  O Tracking number | P Status | Q Error code | R Error message
 */
const SHEET_NAME = "Shipments";
const FIRST_RESULT_COLUMN = 15; // column O

function onOpen() {
  SpreadsheetApp.getUi().createMenu("LastMile").addItem("Create shipments", "createShipments").addToUi();
}

function createShipments() {
  const props = PropertiesService.getScriptProperties();
  const api = props.getProperty("LASTMILE_API_URL") + "/api/public/v1";
  const headers = {
    "X-API-Key": props.getProperty("LASTMILE_API_KEY"),
    "X-API-Secret": props.getProperty("LASTMILE_API_SECRET"),
    "Accept-Language": "en",
  };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;

  // getDisplayValues keeps phone numbers and short addresses exactly as typed (no lost leading zero).
  const rows = sheet.getRange(2, 1, lastRow - 1, 18).getDisplayValues();

  rows.forEach((row, index) => {
    const [reference, merchant, name, phone, city, district, addressLine, shortAddress, weight, length, width, height, cod, description] = row;
    const alreadyDone = row[14]; // tracking number present
    if (!reference || alreadyDone) return;

    const missing = [["Merchant", merchant], ["Recipient name", name], ["Recipient phone", phone], ["City", city], ["District", district],
      ["Address line", addressLine], ["Weight", weight], ["Length", length], ["Width", width], ["Height", height]]
      .filter(([, value]) => !String(value).trim()).map(([label]) => label);
    if (missing.length) {
      writeResult(sheet, index + 2, ["", "", "missing_fields", "Fill in: " + missing.join(", ")]);
      return;
    }

    const body = {
      reference: reference,
      sender: { type: "merchant", merchant: merchant },
      recipient: {
        country: "SA",
        city: city,
        district: district,
        addressLine: addressLine,
        shortAddress: shortAddress || undefined,
        name: name,
        phone: phone,
      },
      parcel: {
        weightKg: Number(weight),
        lengthCm: Number(length),
        widthCm: Number(width),
        heightCm: Number(height),
        description: description || undefined,
      },
      payment: { codAmount: Number(cod || 0) },
    };

    // One key per row, derived from the reference: running the script twice never creates a second shipment.
    const idempotencyKey = ("sheet-" + reference).replace(/[^A-Za-z0-9._-]/g, "-").slice(0, 128);
    const response = send(api + "/shipments", headers, body, idempotencyKey);
    const data = JSON.parse(response.getContentText() || "{}");

    if (response.getResponseCode() === 201 || response.getResponseCode() === 200) {
      writeResult(sheet, index + 2, [data.trackingNumber, data.statusLabelEn, "", ""]);
    } else {
      writeResult(sheet, index + 2, ["", "", data.code || "http_" + response.getResponseCode(), describeError(data)]);
    }
  });
}

/** POST with one automatic wait when the rate limit (120 requests per minute) is hit. */
function send(url, headers, body, idempotencyKey) {
  const options = {
    method: "post",
    contentType: "application/json",
    headers: Object.assign({ "Idempotency-Key": idempotencyKey }, headers),
    payload: JSON.stringify(body),
    muteHttpExceptions: true,
  };
  let response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() === 429) {
    const wait = Number(response.getHeaders()["Retry-After"] || 30);
    Utilities.sleep(Math.min(wait, 60) * 1000);
    response = UrlFetchApp.fetch(url, options);
  }
  return response;
}

/** A readable message: the API text, the failing fields, or "did you mean" suggestions for places. */
function describeError(data) {
  const parts = [data.message || "Request failed"];
  const details = data.details || {};
  if (details.errors) {
    Object.keys(details.errors).forEach((field) => parts.push(field + ": " + details.errors[field].join(" ")));
  }
  if (details.candidates && details.candidates.length) {
    parts.push("Did you mean: " + details.candidates.map((c) => c.nameAr + " / " + c.nameEn).join(", ") + "?");
  }
  return parts.join(" | ");
}

function writeResult(sheet, rowNumber, values) {
  sheet.getRange(rowNumber, FIRST_RESULT_COLUMN, 1, 4).setValues([values]);
  SpreadsheetApp.flush();
}
  • One idempotency key per row, built from the reference (sheet-ORD-1001). If the script stops half way, or you run it again, rows that were already created are not duplicated: the API replays the first answer. A row that failed can be corrected and sent again with the same key, because failed attempts are not remembered.
  • If you change a row after it succeeded and clear its tracking number to resend it, the API answers 409 idempotency_key_reused because the body changed. Use a new reference for a new shipment.
  • Errors are written back as a code and a message. Validation errors list the failing fields (recipient.phone: ...). A place that does not match lists the closest candidates ("Did you mean: جدة / Jeddah?"), so you can correct the cell.
  • Rate limit. The API allows 120 requests per minute per key. On 429 the script waits for Retry-After and tries once more. For thousands of rows, run it in batches.
  • Apps Script time limit. A script run stops after a few minutes. The script skips rows that already have a tracking number, so just run it again.
  • Add a column for the label and call GET /shipments/{trackingNumber}/label to save PDFs to Drive.
  • Refresh the Status column: loop over rows that have a tracking number and call GET /shipments/{trackingNumber} from a daily trigger, or receive webhooks in a deployed web app.
  • Add a Quote column that calls POST /pricing/quote before you create, to review prices first.