/** * Zentria Flow for Google Sheets * * Custom functions that return US import duty for an HTS code and country of * origin, from the Zentria Flow tariff engine (https://zentriaflow.com): * * =ZF_DUTY("6109.10.00", "China") total US duty rate, in percent * =ZF_DUTY("6109.10.00", "CN", "2027-01-15") the rate for a later entry date * =ZF_LAYERS("6109.10.00", "CN") MFN, Section 301, forced-labor, 232, 338 (one row) * =ZF_NEXT_CHANGE("8607.21.10", "CN") date and total after the next scheduled change * =ZF_DESCRIPTION("6109.10.00") the HTSUS description of the line * =ZF_HTS_SEARCH("cotton t-shirt") candidate 8-digit HTSUS lines (code, description) * * Keyless. All Google Sheets users share 300 tariff checks and 1,500 searches * an hour (Google's servers make the calls, so a per-address limit would not * work); answers are cached for 6 hours, so a sheet that recalculates does not * spend the allowance again. Planning estimates, not customs rulings: * anti-dumping and countervailing duties are not included. * * MIT licence. Source: github.com/orhansavash (zentria-flow repository, google-sheets-addon/). */ var ZF_API = 'https://zentriaflow.com/api/public-tools'; var ZF_CACHE_SECONDS = 21600; // 6 hours, the longest CacheService keeps an entry var ZF_LAYER_KEYS = ['mfn', 'section_301', 'forced_labor', 'section_232', 'section_338']; /** @OnlyCurrentDoc */ function onOpen(e) { SpreadsheetApp.getUi() .createAddonMenu() .addItem('How to use', 'zfShowHelp') .addToUi(); } function onInstall(e) { onOpen(e); } function zfShowHelp() { var html = HtmlService.createHtmlOutput( '
' + '

Zentria Flow: US import duty in your sheet

' + '

=ZF_DUTY(A2, B2) total US duty in %, for an HTS code in A2 and an origin in B2 ' + '(ISO code like CN, or a name like China).

' + '

=ZF_DUTY(A2, B2, "2027-01-15") the rate for a later entry date (up to 12 months ahead).

' + '

=ZF_LAYERS(A2, B2) one row: MFN, Section 301, forced-labor tariff, Section 232, Section 338.

' + '

=ZF_NEXT_CHANGE(A2, B2) date and total after the next scheduled change.

' + '

=ZF_DESCRIPTION(A2) and =ZF_HTS_SEARCH("steel cookware").

' + '

All Sheets users share 300 checks an hour; answers are cached for 6 hours. ' + 'Estimates for planning, not customs rulings. Anti-dumping duties are not included.

' + '

For a whole list at once, with what changes next: zentriaflow.com/bulk-tariff-check

' ).setWidth(420).setHeight(380); SpreadsheetApp.getUi().showModelessDialog(html, 'Zentria Flow'); } /** * Total US import duty rate, in percent, for an HTS code from a country of origin. * * @param {string} hts HTS code, for example "6109.10.00" (8 or 10 digits are best). * @param {string} origin Country of origin: ISO code ("CN") or English name ("China"). * @param {string} as_of Optional. Entry date as YYYY-MM-DD, from today to 12 months ahead. * @return {number} Total duty rate in percent (MFN + Section 301 + forced-labor + 232 + 338). * @customfunction */ function ZF_DUTY(hts, origin, as_of) { return zfMap_(hts, origin, as_of, function (r) { return r.total_rate; }); } /** * Each US tariff layer, in percent, as one row: MFN, Section 301, forced-labor tariff, Section 232, Section 338. * * @param {string} hts HTS code, for example "6109.10.00". * @param {string} origin Country of origin: ISO code ("CN") or English name ("China"). * @param {string} as_of Optional. Entry date as YYYY-MM-DD. * @return {number[][]} One row of five rates in percent. * @customfunction */ function ZF_LAYERS(hts, origin, as_of) { return zfMap_(hts, origin, as_of, function (r) { return [ZF_LAYER_KEYS.map(function (k) { return zfRate_(r[k]); })]; }); } /** * The next scheduled change to the US duty on a product: its date and the total rate after it. * * @param {string} hts HTS code, for example "8607.21.10". * @param {string} origin Country of origin: ISO code ("CN") or English name ("China"). * @return {Array[]} One row: the date (YYYY-MM-DD) and the total rate after it in percent, or "None scheduled". * @customfunction */ function ZF_NEXT_CHANGE(hts, origin) { return zfMap_(hts, origin, '', function (r) { var list = r.scheduled_changes || []; if (!list.length) return 'None scheduled in the next 12 months'; return [[list[0].effective_date, list[0].total_rate_after]]; }); } /** * The HTSUS description of a tariff line. * * @param {string} hts HTS code, for example "6109.10.00". * @return {string} The official description. * @customfunction */ function ZF_DESCRIPTION(hts) { return zfMap_(hts, 'CN', '', function (r) { return r.description || ''; }); } /** * Candidate 8-digit HTSUS lines for product words: code and description, one per row. * A search hit is a candidate, not a classification ruling. * * @param {string} query Product words, for example "cotton t-shirt", or the start of a code. * @return {string[][]} Rows of [HTS code, description]. * @customfunction */ function ZF_HTS_SEARCH(query) { var q = String(query == null ? '' : query).trim(); if (q.length < 2) return 'Give at least 2 characters'; var body = zfFetch_(ZF_API + '/hts-search?q=' + encodeURIComponent(q.slice(0, 80)), 'search|' + q.toLowerCase()); if (typeof body === 'string') return body; var rows = (body.results || []).slice(0, 10).map(function (x) { return [x.hts, x.description]; }); return rows.length ? rows : 'No matching lines'; } // ── internals ──────────────────────────────────────────────────────────────── /** Calls the tariff check (cached) and passes the answer to `pick`, or returns an error text. */ function zfMap_(hts, origin, as_of, pick) { var code = String(hts == null ? '' : hts).trim(); var from = String(origin == null ? '' : origin).trim(); var date = zfDate_(as_of); if (!code) return 'Give an HTS code'; if (!from) return 'Give a country of origin'; if (date === null) return 'as_of must be a date (YYYY-MM-DD)'; var url = ZF_API + '/tariff-check?hts=' + encodeURIComponent(code.slice(0, 20)) + '&origin=' + encodeURIComponent(from.slice(0, 60)) + (date ? '&as_of=' + date : ''); var body = zfFetch_(url, 'check|' + code + '|' + from.toLowerCase() + '|' + date); if (typeof body === 'string') return body; if (!body.ok) return 'Error: ' + (body.error || 'this code could not be priced'); return pick(body); } /** A cell date or text as YYYY-MM-DD; '' when empty; null when it is not a date. */ function zfDate_(value) { if (value == null || value === '') return ''; if (Object.prototype.toString.call(value) === '[object Date]') { return Utilities.formatDate(value, 'UTC', 'yyyy-MM-dd'); } var s = String(value).trim(); return /^\d{4}-\d{2}-\d{2}$/.test(s) ? s : null; } function zfRate_(layer) { if (layer && typeof layer === 'object') return Number(layer.rate) || 0; return Number(layer) || 0; } /** GET a URL as JSON, through a 6-hour cache. Returns the parsed body, or an error text for the cell. */ function zfFetch_(url, cacheKey) { var cache = CacheService.getScriptCache(); var key = 'zf1|' + Utilities.base64EncodeWebSafe( Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, cacheKey)).slice(0, 40); var hit = cache.get(key); if (hit) return JSON.parse(hit); var res = UrlFetchApp.fetch(url, { muteHttpExceptions: true, headers: { Accept: 'application/json' } }); var status = res.getResponseCode(); var text = res.getContentText(); var body; try { body = JSON.parse(text); } catch (err) { body = null; } if (status === 429) return 'Busy: ' + ((body && body.detail) || 'the shared hourly limit is used up. Try again later.'); if (status >= 400 || !body) return 'Error: ' + ((body && (body.detail || body.error)) || ('HTTP ' + status)); if (text.length < 90000) cache.put(key, text, ZF_CACHE_SECONDS); return body; }