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.
/**
* 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_reusedbecause 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
429the script waits forRetry-Afterand 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}/labelto 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/quotebefore you create, to review prices first.