Google Sheets price list: columns, price formula, auto-update and limits

Contents
  1. When people look for a price list in Google Sheets
  2. Which columns a price list needs
  3. How to calculate the selling price with a formula
  4. How to pull a supplier's price list into your sheet
  5. How to give customers access to the price list
  6. How to update the exchange rate and prices automatically
  7. Where a spreadsheet runs out
  8. When to hand price lists to software
  9. Frequently asked questions
  10. Sources

A Google Sheets price list is one sheet with five columns — SKU, Name, Cost, Price and Stock. The selling price is calculated by a formula from the exchange rate and your markup, supplier price lists come in through IMPORTRANGE or IMPORTDATA, a short Apps Script writes the daily ECB exchange rate into a cell, and customers get a separate view-only file. This works up to a few thousand items and a handful of suppliers.

The usual mistakes are about access and scale. A working file shared by link shows customers your purchase prices on the next tab, and import formulas do not refresh instantly and run into Google's limits. Below: the structure, the formula with a worked example, connecting suppliers, customer access, automation, and the point where a spreadsheet stops coping.

When people look for a price list in Google Sheets

It is usually a small store or wholesaler whose price list lives in Excel on one computer. They want sales staff to see current prices, customers to open the list by link, and exchange rates and supplier prices to update on their own. That is where searches like "google sheets price list template", "google sheets currency conversion" and "importrange" come from.

"Which formula should I write" takes ten minutes to solve. The harder questions are how not to show customers your cost prices, and what to do when a supplier swaps columns around.

Which columns a price list needs

Five columns at minimum. Put the exchange rate and the markup in separate cells so you change them in one place.

Google Sheets price list template: SKU, name, cost in dollars, price in euros and stock
Columns of a working price list
ColumnWhat it holdsWhere it comes from
A — SKUProduct code used to search and to match price listssupplier
B — NameYour product name for customersyou
C — Cost $Supplier price in the price list currencysupplier
D — Price €Selling price — formula onlyformula
E — Stock"in stock", "on order", "out of stock" — from a dropdownsupplier
H1, H2Exchange rate and markupscript and you

Make "Stock" a dropdown (Data → Data validation). Then "yes", "+" and "avail." do not get mixed up, and filters keep working.

How to calculate the selling price with a formula

Selling price = cost ÷ exchange rate × (1 + markup), rounded up to a price ending in .99. Cell H1 holds the ECB rate, quoted as dollars per one euro. In D2:

=ROUNDUP(C2/$H$1*(1+$H$2)+0.01,0)-0.01

Illustrative example for the arithmetic, not market prices. The supplier charges $22, the rate in H1 is 1.10 dollars per euro, the markup in H2 is 25%.

  • 22 ÷ 1.10 = €20.00 — cost in euros;
  • 20.00 × 1.25 = €25.00 — with markup;
  • 25.00 + 0.01 = 25.01 → up to 26 → 26 − 0.01 = €25.99.
Price calculation: $22 ÷ 1.10 = €20, plus 25% = €25, rounded up to €25.99

The +0.01 stops a price that is already a whole number, like €25.00, from dropping to €24.99 — below your markup. If you sell in dollars and buy in euros, multiply by the rate instead of dividing. Spreadsheets with a European locale separate arguments with a semicolon instead of a comma. If the markup differs by category, add a "Markup" column and point the formula at it instead of $H$2. To check what markup covers shipping and fees, use the markup and margin calculator.

How to pull a supplier's price list into your sheet

It depends on how the supplier sends the price list: a Google Sheet — IMPORTRANGE, a link to a CSV — IMPORTDATA, an XML feed — IMPORTXML.

Three Google Sheets functions pull data into your price list: IMPORTRANGE, IMPORTDATA and IMPORTXML
Google Sheets import functions
Supplier price listFunctionWhat to keep in mind
Google Sheet=IMPORTRANGE("link","Prices!A2:E")Click "Allow access" once; up to 10 MB of data per request
Link to a CSV or TSV file=IMPORTDATA("link")Works only with a direct link to the file
XML feed=IMPORTXML("link","//item/price")The second argument is an XPath query to the nodes you need

Imports pull a range by address, not by column name. If the supplier inserts a "Brand" column between SKU and price, your "Cost" column fills with brand names — and the price formula quietly returns an error or nonsense. Keep imports on a separate tab and check the headers after every change to the price list. What to do when it happens — when a supplier changes the price list structure.

How to give customers access to the price list

Give customers a separate file, not your working one. "Anyone with the link → Viewer" opens the whole spreadsheet, including the tab with purchase prices.

  1. Create a "Customer price list" file and pull only SKU, Name, Price and Stock into it from the working file with IMPORTRANGE.
  2. Share → "Anyone with the link" → "Viewer".
  3. Or File → Share → "Publish to web": the published version updates itself after changes to the original, with a delay of a few minutes.

Make separate files with separate price columns for different customer groups, such as wholesale and retail: links get forwarded.

How to update the exchange rate and prices automatically

Write the ECB reference rate into the sheet with a scheduled Apps Script. The European Central Bank publishes it once per working day at around 16:00 CET, so prices do not shift during the day the way a live market quote from GOOGLEFINANCE does. Open Extensions → Apps Script and paste the function:

function updateRate() {
  var url = 'https://data-api.ecb.europa.eu/service/data/EXR/D.USD.EUR.SP00.A?lastNObservations=1&format=csvdata';
  var rows = Utilities.parseCsv(UrlFetchApp.fetch(url).getContentText());
  var rate = rows[1][rows[0].indexOf('OBS_VALUE')];
  SpreadsheetApp.getActive().getSheetByName('Price list')
    .getRange('H1').setValue(Number(rate));
}

Then in the script editor: Triggers → Add trigger → function updateRate → "Time-driven" → "Day timer" → "5pm to 6pm". Time-driven triggers can run from every minute to once a month; Google shifts the exact run time slightly. For the pound or the zloty, replace USD in the link with GBP or PLN. The ECB publishes these rates for information: if your contract with a supplier names a different rate, use that one.

A script can also fetch a supplier's price list by link every night and write it to a tab. If prices must update from your website or app, there is the Google Sheets API: standard use costs nothing, and the limits are 300 read and 300 write requests per minute per project and 60 per user. Google says it plans to charge for exceeding the quota later in 2026.

Where a spreadsheet runs out

A spreadsheet hits the limit on updates, not on rows: import formulas refresh every 30–60 minutes, and scripts have a daily runtime quota.

Google Sheets limits: imports refresh every 30–60 min, up to 20 million cells, a script runs up to 6 min
Google limits that affect a price list (checked October 31, 2026)
WhatLimit
Spreadsheet sizeup to 20 million cells or 100 MB
IMPORTRANGEup to 10 MB of data per request; refreshes every 30 minutes, and checks for changes hourly while the file is open
IMPORTDATA, IMPORTXML, IMPORTHTMLrefresh every hour
Apps Script runup to 6 minutes per execution
Triggers, consumer Gmail accountup to 90 minutes of runtime per day
UrlFetchApp calls, consumer Gmail accountup to 20,000 per day

Practical signs the spreadsheet can no longer keep up:

  • you have more than three or four suppliers, each with their own columns, currency and stock labels;
  • the same product appears in several price lists, and you pick the price by hand — how to do it with a rule, in how to merge supplier price lists;
  • prices on your website must update more often than once an hour;
  • someone spends hours every week fixing formulas broken by a changed price list.

When to hand price lists to software

When you have several suppliers and their prices must reach your website without anyone checking formulas by hand.

Elbuz takes a supplier's price list straight from a Google Sheet by link — even a private one the supplier shared with Elbuz's service email — as well as from email, FTP or a website. It recognizes columns by their headers, so swapped columns do not break prices, and exchange rate, markup and rounding are set by rules. More on the supplier price list automation page.

Who it is not for: with one supplier and a few hundred items, Google Sheets with a formula and a rate script is enough.

Google Sheets price lists in ElbuzColumns, exchange rate and markup by rules, 14 days free

Frequently asked questions

How do I make a price list in Google Sheets?

Create a sheet with SKU, Name, Cost, Price and Stock columns, put the exchange rate and markup in separate cells, and calculate the price with a formula. The column template is in which columns a price list needs.

How do I share a price list with customers as view-only?

Make a separate customer file without purchase prices and share it as "Anyone with the link → Viewer", or publish it via File → Share → "Publish to web".

How can I automatically convert currency in Google Sheets?

Keep the rate in one cell and divide or multiply prices by it. Update the cell with GOOGLEFINANCE("CURRENCY:EURUSD") for a market quote, or with an Apps Script that writes the daily ECB reference rate — the code is in updating the rate automatically.

How do I pull data from another Google Sheet?

With IMPORTRANGE: give it the spreadsheet link and a range such as "Prices!A2:E", and allow access once.

How often do IMPORTRANGE and IMPORTXML update?

According to Google's help, IMPORTRANGE refreshes every 30 minutes, and IMPORTDATA, IMPORTXML and IMPORTHTML every hour. For faster updates you need a script or software.

Is the Google Sheets API free?

Yes, standard use is free within the limits of 300 requests per minute per project and 60 per user. Google plans to charge for exceeding the quota later in 2026.

How much data can a Google Sheet hold?

Up to 20 million cells or 100 MB per spreadsheet. For a price list, the limits on import refresh and script runtime matter more.

Sources

Checked on October 31, 2026.

Save a link to this article Telegram Facebook X LinkedIn Viber WhatsApp
Learn more about Elbuz with AI Ask your question — pick a chat and let AI study this page for you:

Discussion of the topic – Google Sheets price list: columns, price formula, auto-update and limits

0 comments

There are no reviews for this product.

Write a comment

Your email address will not be published. Required fields are checked

Next

Try Elbuz free