Introduction
In this article, we describe how to integrate with a vendor using Google Sheets (URL). Your vendor can use Google Sheets to sync products.
Disclaimer
❗️You can use our template to create your vendor's product catalog. Read the section MAPPING VALUES CORRECTLY below if you want to import a custom file.
For product creation the values SKU, handle, and title are mandatory.
The barcode is only mandatory if you don’t have a SKU.
The values retail_price and qty are mandatory if you want to use this file as the source of data for inventory levels and product pricing.
The other fields such as images, grams, compare_at_price and cost_price are highly recommended but not necessarily mandatory, as it will depend on the use case. The same happens with color and size which should be added, if applicable.
Creating your inventory feed
Format Guidelines:
The data in the spreadsheet should be in tabular columns, each with a header and the values below.
Choose Sheet tab.
Onport cannot import sheets that do not list data in this tabular format.
Not all columns have to be imported as well. Those can be left blank.
Data formats
It's fine to use formatting if you want (e.g. make the column headers bold or highlight out-of-stock items in red). However, you need to ensure the numeric data is correctly formatted.
❗️Important: When adding inventory quantities, they should be a whole number and not contain any text (e.g. 1 not 1 in stock). Decimal numbers (e.g. 10.00 will just be rounded to the nearest whole number). Price, sale price, cost price - Should be a number and not contain any text (e.g. 9.99 not 9.99 EUR). Headers are also case-sensitive.
Connecting to Google Drive
Video tutorial: How to share data from a Google Sheet
Once you've created your Google Spreadsheet and mapped the columns to Onport, you then need to add the URL of the document in the feed settings. Onport will then check the document for updates every hour (larger feeds might take longer). You can also manually trigger an import if needed by fetching the feed.
You'll need to publish the document you want to share under File > Publish to the Web so Onport can access it.
After that, click on the top right "Share" button and choose the option “Anyone on the internet with this link can view”:
Copy the URL of the document from the top bar. Do not copy the sharing URL.
Back in Onport, under the inventory feed settings add the document link.
Save the settings. Your feed should then be imported within the hour. If you need it quicker than that, click Feed Items on the top right and then click Fetch. Follow the steps on the pop-up - you'll be notified by email once your import is complete or you can access it at the bottom left section of the app, under Exports:
Mapping the inventory feed
Once you've set up your spreadsheet, you need to map the data. Under the Mappings section in the feed settings. There are four options:
Update inventory only
Select if you have two columns, sku and qty. The spelling and casing of these should match exactly.
Update inventory and prices only
Select if you have three columns, Price(retail_price, cost_price, compare_at_price), sku and qty. The spelling and casing of these should match exactly. For price, you need to have at least one of the price options but can also have additional ones.
Product data for publishing
Select if you want to use our preset mapping for product publishing. You can use this file as a template.
Custom mapping
Use this when you want to use your own column titles or want to add additional fields. For example, if you had a column that stored the sku.
Mapping values correctly
❗️Important: Make sure the headers in your file match what is shown in the template file shared above unless you're using Custom Mapping. For example, qty needs to be written that way, with all letters being lowercase. If you write quantity or Qty, it won't work. If you choose the option to set Custom Mapping, you'll be able to determine what capitalization is expected from the file.
You can also find the template under the Inventory Feed settings > Mapping:
Custom mapping
What happens if you or your vendors have written the headers in a different format or if you're extracting the details from a platform with a set format?
In this case, you can create a custom mapping under the mappings tab. Just map the header names with the Sku and Inventory quantity.
Enter sku for the header name field in Onport (or whatever you’ve entered for the column header in your spreadsheet)
Select sku from the dropdown and click Add inventory feed field.
You'll then need to repeat for any other columns. You only need to map the columns for which you want to sync data. There are sometimes additional fields such as separators that appear for the different field types, you can leave this blank.
Mapping meta fields
If you need to include meta fields in a Google sheet, you can do so as Onport has a value that can be mapped to your column for that purpose:
Option name (or meta field key) - it's usually the name of the meta field in the marketplace's platform (usually, lowercase with an underscore, for example: product_material).
Meta fields scope (or namespace) - could be left blank. It's the purpose of the meta field, for instance, product_info.
❗️If you have any questions about the naming convention of a certain meta field, we would recommend reaching out to your marketplace storefront developer.
Updating inventory levels by Google Sheets
Similar to the CSV method, you'll need to create a file using a Google sheet with 2 column headers: sku and qty.
This will allow the vendors to update inventory on their side in the same Google sheet while Onport automatically fetches these updates without the need to upload files.
Feed fetching
These feeds will be fetched daily under normal circumstances. If the party responsible for updating the file, be it you or your vendor, can’t do it frequently enough, it may result in discrepancies when the feed fetches again if any sales have not previously been accounted for in the file. If you realize that may be the case, you may need to manually fetch the feed each time it is updated.