Add carrier rate and shipping cost lookups

Northwind quotes shipping by hand. Someone takes the order weight and the destination postcode, opens the carrier’s portal, types it all in, copies the price back into the order sheet. It is slow, it is easy to fumble, and the quote is stale the moment carrier rates change.

This script turns the lookup into a spreadsheet function. Type =SHIPPING_RATE(...) next to an order and the cell calls the carrier’s rate API and returns the live cost. The order sheet becomes its own quoting tool — no portal, no copy-paste, no stale numbers.

What you’ll need

• A carrier account with a rate API (DHL, UPS or similar) and an API key.
• The API key saved as CARRIER_KEY in Script Properties — see Store API keys and secrets securely.
• A Google Sheet of orders with the origin postcode, destination postcode and parcel weight in their own columns.

The script

// Carrier rate endpoint. Swap this for your carrier's real URL.
const CARRIER_RATE_URL = 'https://api.carrier.example/rate';

/**
 * Custom function: returns the live shipping cost for one parcel.
 * Use it in a cell, e.g. =SHIPPING_RATE("EC2A 4NE", "10001", 2.5)
 *
 * @param {string} fromPostcode  Origin postcode.
 * @param {string} toPostcode    Destination postcode.
 * @param {number} weightKg      Parcel weight in kilograms.
 * @return {number|string} The quoted rate, or a readable error string.
 * @customfunction
 */
function SHIPPING_RATE(fromPostcode, toPostcode, weightKg) {
  // 1. Bail out early on missing inputs so the API is never called for nothing.
  if (!fromPostcode || !toPostcode || !weightKg) return '';

  // 2. Pull the carrier key from Script Properties — never hard-code it.
  const key = PropertiesService.getScriptProperties()
    .getProperty('CARRIER_KEY');
  if (!key) return 'No CARRIER_KEY set';

  // 3. POST the parcel details to the carrier's rate endpoint.
  const response = UrlFetchApp.fetch(CARRIER_RATE_URL, {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + key },
    payload: JSON.stringify({
      from: fromPostcode,
      to: toPostcode,
      weightKg: weightKg,
    }),
    muteHttpExceptions: true,
  });

  // 4. A non-200 means the carrier rejected the request — surface it, don't crash.
  if (response.getResponseCode() !== 200) {
    return 'Rate error ' + response.getResponseCode();
  }

  // 5. Parse the JSON body and return just the rate figure.
  const data = JSON.parse(response.getContentText());
  return data.rate;
}

How it works

1. SHIPPING_RATE is a custom function — the @customfunction tag makes it callable straight from a cell like a built-in formula.
2. It first checks all three arguments are present. An empty origin, destination or weight returns a blank cell instead of firing a pointless API request.
3. It reads the carrier key from Script Properties. If the key is missing it returns a readable message rather than a stack trace.
4. It POSTs the postcodes and weight to the carrier’s rate endpoint as JSON, with the key in an Authorization header.
5. muteHttpExceptions keeps a bad response from throwing — the function checks the status code itself and returns Rate error 4xx if the carrier refused the request.
6. On success it parses the JSON body and returns the rate field, which lands in the cell as a plain number you can sum or format as currency.

Example run

With an order sheet like this and the formula in column D:

From	To	Weight (kg)	Formula	Result
EC2A 4NE	10001	2.5	=SHIPPING_RATE(A2,B2,C2)	24.80
EC2A 4NE	SW1A 1AA	0.8	=SHIPPING_RATE(A3,B3,C3)	5.40
EC2A 4NE	75001	6.0	=SHIPPING_RATE(A4,B4,C4)	38.15

Each cell shows the live carrier rate for that parcel. Change a weight and the quote recalculates.

Run it

There is nothing to schedule — this is a custom function:

1. Paste the script into the spreadsheet’s bound Apps Script project.
2. Save, then return to the sheet.
3. In a cell, type =SHIPPING_RATE( and pass it the from postcode, to postcode and weight — by cell reference or as literals.
4. Approve the authorisation prompt the first time the function runs.

Watch out for

• Custom functions cannot use services that need user authorisation in some contexts, but UrlFetchApp works fine here. They do run with a tighter 30-second execution limit, so a slow carrier API can time the cell out.
• Every recalculation is a fresh API call. A column of 200 orders hits the carrier 200 times and can blow through rate limits or per-call pricing. For large sheets, paste the results as static values once they are correct.
• Custom functions cannot read Script Properties in every account setup. If No CARRIER_KEY set appears unexpectedly, run a normal function once to confirm the property is readable.
• The endpoint and payload shape here are placeholders. Match CARRIER_RATE_URL, the auth header and the request body to your real carrier’s API documentation.
• Carrier rates change. A quote is only as fresh as the last recalculation — press recalculate or re-enter the formula before relying on an old number.