/**
* 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;
}