A spreadsheet is a useful place to review eBay comparable listings with the person who understands the inventory. The analyst can see source links, reject a lens bundle, and explain why a damaged camera should not influence a body-only price estimate.
The connector in this guide pulls one SoldFetch search page into Google Sheets when you choose a menu command. Each successful refresh creates a dated snapshot tab. Prices remain numeric, unknown values remain blank, and every row carries its currency and quality context. There is no automatic refresh or hidden multi-page crawl.
1. Define the table before calculating a price
Start with a narrow query, such as sony a6400 body, a single storefront, and used condition. The example requests sold results through GET /v1/scrape. Its soldPrice field is the displayed listing amount, not a verified accepted Best Offer price. The table calls it displayedPrice to preserve that distinction for spreadsheet readers.
The output includes item ID, title, displayed amount, currency, shipping amount and currency, API-provided total, condition, source URL, observation time, price basis, verification flags, warnings, storefront, and query. Missing shipping is different from free shipping. An unknown total should not become the displayed price plus an assumed zero.
Current launch storefronts are ebay.com, ebay.co.uk, and ebay.de. The connector checks the public capabilities endpoint before making a data request. Keep each research run in its own market and currency; eBay US is not a reason to overwrite an unexpected currency with USD.
2. Add a bound Apps Script and keep the key out of cells
Create a blank spreadsheet, choose Extensions → Apps Script, and paste the complete script below into the bound project. Save it, return to Sheets, and reload the spreadsheet to see the SoldFetch menu. Google's custom-menu guide explains the bound-script requirement.
Choose Set up inputs to create the configuration tab. Choose Save my API key and enter a key from your SoldFetch dashboard. The script saves it in your user properties instead of the workbook, source code, a formula, or a log.
User properties are scoped to the current script user; they are not a separate security boundary against a malicious script editor. Share edit access only with people you trust to modify the bound code. Each person who refreshes should save their own key. Do not distribute a credential in a copied spreadsheet or paste it into a sheet cell for convenience. Google documents the scopes of PropertiesService.
3. Install the complete manual-refresh connector
Download the Apps Script or copy the complete code below. The first authorized refresh needs permission to edit the spreadsheet and make an external HTTPS request. Review the script and Google's authorization screen before granting those permissions.
/** Manual, one-page SoldFetch connector. Paste this entire file into bound Apps Script. */
function onOpen() {
SpreadsheetApp.getUi().createMenu('SoldFetch')
.addItem('Set up inputs', 'setupSoldFetch')
.addItem('Save my API key', 'saveSoldFetchKey')
.addItem('Refresh one page', 'refreshSoldFetch').addToUi();
}
function setupSoldFetch() {
const book = SpreadsheetApp.getActive();
if (book.getSheetByName('SoldFetch Config')) throw new Error('Config already exists; edit its values.');
book.insertSheet('SoldFetch Config').getRange('A1:B4').setValues([
['Setting', 'Value'], ['Keyword', 'sony a6400 body'], ['Site', 'ebay.com'], ['Count', 60]
]);
}
function saveSoldFetchKey() {
const ui = SpreadsheetApp.getUi();
const answer = ui.prompt('Save your SoldFetch key', 'Stored in your user properties, never in cells or logs. Use only a trusted script.', ui.ButtonSet.OK_CANCEL);
if (answer.getSelectedButton() !== ui.Button.OK) return;
const key = answer.getResponseText().trim();
if (!key) throw new Error('A key is required.');
PropertiesService.getUserProperties().setProperty('SOLDFETCH_API_KEY', key);
}
function safeText(value) {
const text = value == null ? '' : String(value);
// External text must never become a spreadsheet formula, including after CSV export.
return /^[\s]*[=+@-]|^[\t\r\n]/.test(text) ? "'" + text : text;
}
function amount(value) {
if (typeof value === 'number') return Number.isFinite(value) && value >= 0 ? value : '';
if (typeof value !== 'string' || !/^\d+(?:\.\d+)?$/.test(value)) return '';
const n = Number(value);
return Number.isFinite(n) ? n : '';
}
function rowsFromPayload(payload, site, keyword) {
if (!payload || !Array.isArray(payload.items)) throw new Error('Unexpected result shape; no sheet was changed.');
const headers = ['itemId','title','displayedPrice','currency','shippingPrice','shippingCurrency','totalPrice','condition','sourceUrl','observedAt','priceBasis','saleDateVerified','exactProductMatchVerified','warnings','site','query'];
const rows = [headers];
const seen = new Set();
for (const item of payload.items) {
const id = String(item.itemId || '');
if (!/^\d{9,15}$/.test(id) || seen.has(id)) continue;
seen.add(id);
const q = item.quality || {};
const warnings = [...(Array.isArray(payload.warnings) ? payload.warnings : []), ...(Array.isArray(item.warnings) ? item.warnings : [])];
rows.push([safeText(id),safeText(item.title),amount(item.soldPrice),safeText(item.soldCurrency),amount(item.shippingPrice),safeText(item.shippingCurrency),amount(item.totalPrice),safeText(item.condition),safeText(item.url),safeText(item.scrapedAt || payload.fetchedAt),safeText(q.priceBasis),q.saleDateVerified === true,q.exactProductMatchVerified === true,safeText(warnings.join(' | ')),safeText(site),safeText(keyword)]);
}
return rows;
}
function parseSuccess(response, label) {
const code = response.getResponseCode();
if (code < 200 || code >= 300) throw new Error(label + ' failed with HTTP ' + code + '; previous snapshots remain intact.');
return JSON.parse(response.getContentText());
}
function refreshSoldFetch() {
const lock = LockService.getDocumentLock();
if (!lock.tryLock(5000)) throw new Error('Another refresh is running.');
try {
const key = PropertiesService.getUserProperties().getProperty('SOLDFETCH_API_KEY');
if (!key) throw new Error('Choose SoldFetch > Save my API key first.');
const book = SpreadsheetApp.getActive();
const config = book.getSheetByName('SoldFetch Config');
if (!config) throw new Error('Choose SoldFetch > Set up inputs first.');
const inputs = config.getRange('B2:B4').getValues().map(row => row[0]);
const keyword = String(inputs[0]).trim(), site = String(inputs[1]).trim(), count = Number(inputs[2]);
if (!keyword || keyword.length > 200) throw new Error('Keyword must contain 1–200 characters.');
if (!['ebay.com','ebay.co.uk','ebay.de'].includes(site)) throw new Error('Choose a supported launch site.');
if (!Number.isInteger(count) || count < 1 || count > 200) throw new Error('Count must be 1–200.');
const caps = parseSuccess(UrlFetchApp.fetch('https://soldfetch.com/v1/capabilities', {muteHttpExceptions:true}), 'Capabilities');
if (!caps.dataEnabled || !Array.isArray(caps.enabledSites) || !caps.enabledSites.includes(site)) throw new Error('Data or this site is currently unavailable.');
const query = {keyword:keyword, ebaySite:site, sold:true, itemCondition:'used', count:count, page:1};
const params = Object.keys(query).map(k => encodeURIComponent(k)+'='+encodeURIComponent(query[k])).join('&');
const response = UrlFetchApp.fetch('https://soldfetch.com/v1/scrape?'+params, {headers:{'API-KEY':key}, muteHttpExceptions:true, followRedirects:false});
const payload = parseSuccess(response, 'Search');
const rows = rowsFromPayload(payload, site, keyword);
// A new tab makes a failed refresh distinguishable from an old successful snapshot.
const name = 'SoldFetch ' + Utilities.formatDate(new Date(), 'UTC', 'yyyyMMdd-HHmmss') + '-' + Utilities.getUuid().slice(0,4);
const sheet = book.insertSheet(name);
sheet.getRange(1,1,rows.length,rows[0].length).setNumberFormat('@');
if (rows.length > 1) {
sheet.getRange(2,3,rows.length-1,1).setNumberFormat('0.00');
sheet.getRange(2,5,rows.length-1,1).setNumberFormat('0.00');
sheet.getRange(2,7,rows.length-1,1).setNumberFormat('0.00');
}
sheet.getRange(1,1,rows.length,rows[0].length).setValues(rows);
sheet.setFrozenRows(1);
sheet.getRange('A1').setNote('Single page, not a complete market. More pages reported: '+String(payload.hasNextPage)+'; API fetchedAt: '+String(payload.fetchedAt || 'unknown')+'; one successful search = one data unit.');
SpreadsheetApp.getUi().alert('Saved '+(rows.length-1)+' rows to '+name+'. Review warnings before calculating statistics.');
} finally { lock.releaseLock(); }
}
The code uses UrlFetchApp for the request and writes the result as one rectangular range. It fetches and validates the payload before creating the result tab, so a failed HTTP response leaves previous snapshots available. It also takes a document lock to prevent two users from refreshing the same workbook simultaneously.
4. Run one refresh and inspect its provenance
In SoldFetch Config, edit the keyword, storefront, and count. Count must be between 1 and 200. Start with 60; the connector deliberately requests only page one. Select SoldFetch → Refresh one page to create a new tab with a UTC timestamp and a short random suffix.
A successful search consumes one SoldFetch data unit. The capabilities check consumes no data units. Repeated menu clicks make additional successful searches, even if the underlying search data is cached. There is no on-edit fetch and no custom spreadsheet function that can rerun unexpectedly during recalculation.
The note on the first header records fetchedAt and whether the API reports additional pages. The tab timestamp tells you when you ran the connector; observedAt reflects when the marketplace data was captured. Cached data can therefore predate the refresh. Keep both concepts separate when you compare snapshots.
An empty search creates a header-only snapshot. An authentication, rate-limit, or availability failure produces an error without replacing an older successful table. Do not interpret the continued presence of yesterday's tab as evidence that today's refresh succeeded.
5. Preserve nulls and prevent formula interpretation
The connector accepts only nonnegative decimal amounts and writes them as numbers. An API null becomes a blank. A genuine 0.00 becomes numeric zero, which is appropriate for explicitly free shipping. Currency codes stay in their own columns instead of being baked into strings such as $125.
External text needs separate handling. A seller-controlled title beginning with = must not become a spreadsheet formula. The safeText helper prefixes formula-like text with an apostrophe, including leading whitespace and common spreadsheet export triggers. IDs and descriptive fields are formatted as text, while the three amount columns use numeric formatting.
Google's Range documentation states that values beginning with = can be interpreted as formulas. Quoting a string in JavaScript does not prevent that behavior. Keep the neutralization step when changing the column layout or exporting a snapshot to CSV.
Warnings remain readable in a dedicated column. Rows with range_lower_bound price basis should be reviewed separately; the lower end of a displayed range is not a directly comparable single price. False verification flags describe uncertainty, not a failed import.
6. Calculate a statistic from a reviewed cohort
Before using a formula, review titles, conditions, bundle contents, and source pages. Put rejected rows in a separate review tab or add your own inclusion column on a copy. A keyword search does not verify exact product identity, and the API's exactMatch setting does not replace that review.
For a first calculation, rename a reviewed snapshot Research and create a separate Summary tab. The following formula includes only USD numeric amounts with ordinary displayed-price basis:
=IFERROR(MEDIAN(FILTER(Research!C2:C,Research!D2:D="USD",Research!K2:K="displayed_listing_price",ISNUMBER(Research!C2:C))),"No eligible values")
Use the matching count next to the median:
=IFERROR(COUNT(FILTER(Research!C2:C,Research!D2:D="USD",Research!K2:K="displayed_listing_price",ISNUMBER(Research!C2:C))),0)
The formulas are for a comma-separator spreadsheet locale. Substitute your reviewed currency explicitly and adapt separators if your locale requires them. Label the result “median displayed price in the reviewed sample.” It is not market-wide average sale value, a verified transaction-price estimate, or evidence of sales velocity.
Do not mix total-price and item-price statistics. totalPrice can be blank when shipping or currency context is incomplete; missing totals should reduce the eligible sample rather than trigger an invented shipping estimate.
7. Make refresh frequency a visible decision
Manual refresh is useful for research because a person chooses the query and sees when a snapshot was created. Keep the first connector simple until you know how many searches your team actually needs. Account plan limits and workspace concurrency still apply to spreadsheet requests.
On HTTP 429, wait for the documented retry interval instead of adding a tight loop. On 401 or 403, check the key and permissions. On 503, check capabilities and service status. This example does not automatically retry a billable data request or create a schedule.
If you later add scheduled refreshes, set a daily request budget and decide who owns the trigger and credential. Archive useful snapshots and remove obsolete tabs deliberately as the workbook grows. A separate history design is better than an indefinitely expanding workbook with no retention policy.
8. Verify the connector and expand deliberately
The offline check script runs the transformation code against fixtures: duplicate IDs, unknown amounts, explicit zero shipping, formula-like titles, malformed payloads, and empty results. It makes no Google or SoldFetch requests. Live authorization and spreadsheet rendering still need verification in your own account.
For more pages and inventory lists, move collection into the background-job workflow, then load its reviewed CSV. For a local cleaning pipeline, start with the Python sold-listings tutorial. The SoldFetch search reference covers the underlying endpoint.
Get your SoldFetch API key and run one search into a new snapshot. Review the source rows before turning the table into a pricing decision.
