We’ve created 7000+ products at our Shopify store since moving to this ecommerce platform. This is a lot of manual work to do! So in the process, we’ve developed a Google Spreadsheet template I’d like to share with you. It uses some quite advanced formulas (like ArrayFormula and Regexreplace) and requires some time to get it. However, we managed to teach our not so nerdy content manager to use it, which means you probably can do it too 😉

I’ve built a simplified public copy of our template, which can be found here. Feel free to copy and modify it to your needs. If you don’t use Brightpearl ERP, feel free to delete sheets ‘5’ and ‘5.1’ (cause they may slow down your doc a bit).

Next, I’m going to explain how to use the template.

General instructions

  1. We base our template on the assumption that you have some raw product data before you start. It may be your supplier’s price list or invoice that can be converted to Excel. We start with copying this data into our template.
  2. Blue columns require manual filling out or copy/pasting.
  3. Green and orange columns contain formulas.
  4.  Uncolored columns may be filled out automatically with the help of ArrayFormula function. If you try to delete data in such column and it keeps being overwritten, this means you have an ArrayFormula function applied to it.

How to use the template

Shopify Product Template

  1. Copy the template to your google drive.
  2. Paste your raw product data into the sheet ‘Paste Supplier Price List’.
  3. If you already have similar products at your Shopify store (same brand or type), export them as csv and paste to the list ‘Paste Product Export from Shopify’. This will help us build reference sheets for your brand names, product tags and product types. You want  to be consistent across your store → it’s better to copy than to fill in every time from scratch. Those sheets are located at the end and marked by the ‘#’ hash symbol in their name.
  4. Fill in all blue columns on the ‘Processing’ sheet. Brand, Type and Tag columns are supplied from reference sheets, which in turn are made from your Shopify export data.
  5. Get the readymade Shopify import data from the ‘Import to Shopify (without additional pics)’ sheet. Copy-paste it to the Shopify product import template. All columns are formatted just like native Shopify import/export cvs is. That’s all for basic  product creation.

Multiple product photos

Our template supports additional product pics besides two pics you can add at the ‘Processing’ stage (‘Image Src’ and ‘Variant Image’ columns). 

If you want to add more, you can do this with the ‘4.2.1 Add Additional product pics’ sheet.

  1. Copy-paste handles for products that you want to have more photos.
  2. Paste photo URLs into ‘Image Src’ column.
  3. Fill in ‘Image Alt Text’ cells. You probably would like them to be exact product titles.
  4. Copy paste rows with additional photos from the  ‘4.2.1 Add Additional product pics’ sheet to the end of  the ‘4.1 Import to Shopify (without additional pics)’ sheet.
  5. At the ‘4.2.2 Import to Shopify (WITH additional pics)’ sheet your product data is sorted by product handle and title. That way you additional product photo rows are at their right places. Copy-paste this sheet to the Shopify product import template. PROFIT!

Brightpearl product import

Sheets ‘5. BP import processing’ and ‘5.1 BP import file’ are there to help you create those same products inside your Brightpearl ERP account. I believe those sheets are pretty straightforward if you have managed to get so far.

  • ‘5. BP import processing’ lets you to set product supplier and collection names.
  • Grab ready for import to BP product data from ‘5.1 BP import file’ sheet.

BTW. Eventually, we’ve ditched this part and created a brightpearl automation flow that automatically downloads all new products from Shopify to BP.

That’s all. We hope this template will save someone’s time and energy for more creative and productive endeavours. Have fun!