BigCommerce

BigCommerce Functions & Syntax

Overview

BigCommerce is a leading ecommerce platform for managing high performance online stores with large product catalogs and high sales volumes. With this spreadsheet integration for Google Sheets and Microsoft Excel, you can quickly lookup and work with customer data, product information, and current orders from your BigCommerce store without leaving your spreadsheet.

What you can do

With this integration, you can:

  • Interactively search and filter orders and products
  • Lookup and track current order activity
  • Lookup information for a list of customers
  • Enrich your spreadsheet with data from your BigCommerce store

What you need

You need to have the API access credentials for your BigCommerce store to use this integration. Also, if you haven't already installed the Flex.io add-on for Excel or Google Sheets, you need to set this up as well.

Functions and Syntax

This integration for Microsoft Excel or Google Sheets includes the following functions:

BigCommerce Customers

Returns a list of customers for a BigCommerce Store

Syntax

=FLEX("YOUR_TEAM_NAME/bigcommerce-customers", [properties], [filter])

Sample Usage

=FLEX("YOUR_TEAM_NAME/bigcommerce-customers", "*")
=FLEX("YOUR_TEAM_NAME/bigcommerce-customers", "id, email, first_name, last_name")
=FLEX("YOUR_TEAM_NAME/bigcommerce-customers", "", "ACME")
=FLEX("YOUR_TEAM_NAME/bigcommerce-customers", "id, company, email, first_name, last_name", "company:acme")

Parameters

Property Type Description Required
properties array The properties to return, given as a string or array; defaults to all properties; see "Returns" for available properties false
filter array Search query to determine the rows to return, given as a string or array false

Returns

Property Type Description
id integer ID of the customer
company string Company of the customer
customer_group_id integer Group ID of the customer
email string Email of the customer
first_name string First name of the customer
last_name string Last name of the customer
notes string Notes for the customer
phone string Phone number of the customer
registration_ip_address string Registration ip address for the customer
tax_exempt_category string Tax exempt category for the customer
accepts_product_review_abandoned_cart_emails boolean Flag field indicating whether or not the customr receives product review abandoned cart emails
date_created string The date on which the customer was created
date_modified string The date on which the customer was modified

Notes

BigCommerce Orders

Returns a list of orders for a BigCommerce Store

Syntax

=FLEX("YOUR_TEAM_NAME/bigcommerce-orders", [properties], [filter])

Sample Usage

=FLEX("YOUR_TEAM_NAME/bigcommerce-orders", "*")
=FLEX("YOUR_TEAM_NAME/bigcommerce-orders", "id, customer_id, date_created, status")

Parameters

Property Type Description Required
properties array The properties to return, given as a string or array; defaults to all properties; see "Returns" for available properties false
filter array Search query to determine the rows to return, given as a string or array false

Returns

Property Type Description
id integer The id of the order
customer_id integer The customer id associated with the order
billing_address_first_name string The first name of the person on the billing address
billing_address_last_name string The last name of the person on the billing address
billing_address_company string The company on the billing address
billing_address_street_1 string The first part of the street address for the billing address
billing_address_street_2 string The second part of the street address for the billing address
billing_address_city string The city part of the street address for the billing address
billing_address_state string The state part of the street address for the billing address
billing_address_zip string The zip part of the street address for the billing address
billing_address_country string The country part of the street address for the billing address
billing_address_country_iso2 string The country part, in ISO2 format, of the street address for the billing address
billing_address_phone string The phone number associated with the billing address
billing_address_email string The email associated with the billing address
order_source string The source of the order
date_created string The date the order was created
date_modified string The date the order was last modified
date_shipped string The date the order was shipped
cart_id integer The cart id associated with the order
status_id integer The status id of the order
status string The status of the order
is_deleted boolean Indicates whether the order was deleted
order_is_digital boolean Whether this is an order for digital products
items_total integer The total number of items in the order
items_shipped integer The number of items that have been shipped
payment_method string The payment method for this order
payment_status string The status of the payment for this order
staff_notes string Any additional notes for staff
customer_message string Message that the customer entered in the Order Comments box during checkout
subtotal_ex_tax number Override value for subtotal excluding tax
subtotal_inc_tax number Override value for subtotal including tax
subtotal_tax number Override value for tax
base_shipping_cost number The value of the base shipping cost
shipping_cost_ex_tax number The value of shipping cost, excluding tax
shipping_cost_inc_tax number The value of shipping cost, including tax
shipping_cost_tax number The tax part of the value of the shipping cost
shipping_cost_tax_class_id integer The shipping cost tax class id
base_handling_cost number The value of the base handling cost
handling_cost_ex_tax number The value of the handling cost, excluding tax
handling_cost_inc_tax number The value of the handling cost, including tax
handling_cost_tax number The tax part of the value of the handling cost
handling_cost_tax_class_id integer The handling cost tax class id
base_wrapping_cost number The value of the base wrapping cost.
wrapping_cost_ex_tax number The value of the wrapping cost, excluding tax
wrapping_cost_inc_tax number The value of the wrapping cost, including tax
wrapping_cost_tax number The tax part of the value of the wrapping cost
wrapping_cost_tax_class_id integer The wrapping cost tax class id
total_ex_tax number Override value for the total, excluding tax
total_inc_tax number Override value for the total, including tax
total_tax number The tax part of the value of the total cost
discount_amount number The discount amount associated with this order
gift_certificate_amount number The gift certificate amount associated with this order
coupon_discount number The coupon amount associated with this order
store_credit_amount number The store credit that the shopper has redeemed on this order
refunded_amount number The amount refunded from this transaction
ip_address string The IP Address of the customer, if known
geoip_country string The full name of the country where the customer made the purchase, based on the IP
geoip_country_iso2 string The country where the customer made the purchase, in ISO2 format, based on the IP
default_currency_code string The currency code of the default currency for this type of transaction
currency_code string The currency code of the currency being used in the order
currency_exchange_rate number The currency exchange rate associated with the order

Notes

BigCommerce Products

Returns a list of products for a BigCommerce Store

Syntax

=FLEX("YOUR_TEAM_NAME/bigcommerce-products", [properties], [filter])

Sample Usage

=FLEX("YOUR_TEAM_NAME/bigcommerce-products", "*")
=FLEX("YOUR_TEAM_NAME/bigcommerce-products", "id, name, sku, description, price")
=FLEX("YOUR_TEAM_NAME/bigcommerce-products", "", "coffeemaker")
=FLEX("YOUR_TEAM_NAME/bigcommerce-products", "id, sku, search_keywords, meta_keywords", "search_keywords:jar")

Parameters

Property Type Description Required
properties array The properties to return, given as a string or array; defaults to all properties; see "Returns" for available properties false
filter array Search query to determine the rows to return, given as a string or array false

Returns

Property Type Description
id integer ID of the product
name string The product name
type string The product type
sku string User defined product code/stock keeping unit (SKU)
description string The product description, which can include HTML formatting
weight number Weight of the product, which can be used when calculating shipping costs
width number Width of the product, which can be used when calculating shipping costs
depth number Depth of the product, which can be used when calculating shipping costs
height number Height of the product, which can be used when calculating shipping costs
price number The price of the product
cost_price number The cost price of the product
retail_price number The retail cost of the product
sale_price number If entered, the sale price will be used instead of value in the price field when calculating the product’s cost
map_price number Minimum advertised price
calculated_price number The price of the product as seen on the storefront
tax_class_id integer The ID of the tax class applied to the product
product_tax_code string AvaTax System tax codes
categories string An comma-delimited list of IDs for the categories to which this product belongs
brand_id integer The brand id
option_set_id integer Indicates that the product is in an Option Set (legacy V2 concept)
option_set_display string Legacy template setting which controls if the option set shows up to the side of or below the product image and description
inventory_level integer Current inventory level of the product
inventory_warning_level integer Inventory warning level for the product
inventory_tracking string The type of inventory tracking for the product
reviews_rating_sum integer The total rating for the product
reviews_count integer The number of times the product has been rated
view_count integer The number of times the product has been viewed
total_sold integer The total quantity of this product sold
fixed_cost_shipping_price number A fixed shipping cost for the product
is_free_shipping boolean Flag used to indicate whether the product has free shipping
is_visible boolean Flag to determine whether the product should be displayed to customers browsing the store
is_featured boolean Flag to determine whether the product should be included in the featured products panel when viewing the store
related_products string An comma-delimited list of IDs for the related products
warranty string Warranty information displayed on the product page
bin_picking_number string The BIN picking number for the product
layout_file string The layout template file used to render this product category
upc string The product UPC code, which is used in feeds for shopping comparison sites and external channel integrations
mpn string Manufacturer Part Numbe
gtin string Global Trade Item Numbe
search_keywords string A comma-separated list of keywords that can be used to locate the product when searching the store
availability string Availability of the product
availability_description string Availability text displayed on the checkout page, under the product title
gift_wrapping_options_type string Type of gift-wrapping options
gift_wrapping_options_list string A comma-delimited list of gift-wrapping option IDs
sort_order integer Priority to give this product when included in product lists on category pages and in search results
condition string The product condition
is_condition_shown boolean Flag used to determine whether the product condition is shown to the customer on the product page
order_quantity_minimum integer The minimum quantity an order must contain, to be eligible to purchase this product
order_quantity_maximum integer The maximum quantity an order can contain when purchasing the product
page_title string Custom title for the product page
meta_keywords string A comma-delimited list of custom meta keywords for the product page
meta_description string Custom meta description for the product page
preorder_release_date string Pre-order release date
preorder_message string Custom expected-date message to display on the product page
is_preorder_only boolean If set to true then on the preorder release date the preorder status will automatically be removed
is_price_hidden boolean False by default, indicating that this product’s price should be shown on the product page
price_hidden_label string If is_price_hidden is true, the value of price_hidden_label is displayed instead of the price
url string Category URL on the storefront
custom_url string The custom URL for the category on the storefront
base_variant_id integer The unique identifier of the base variant associated with a simple product
open_graph_type string Type of product, defaults to product
open_graph_title string Title of the product, if not specified the product name will be used instead
open_graph_description string Description to use for the product, if not specified then the meta_description will be used instead
open_graph_use_meta_description boolean Flag to determine if product description or open graph description is used
open_graph_use_product_name boolean Flag to determine if product name or open graph name is used
open_graph_use_image boolean Flag to determine if product image or open graph image is used
date_created string The date on which the product was created
date_modified string The date on which the product was modified

Notes

Need help?

Have questions? Need help getting started? Need other BigCommerce templates or functions for Microsoft Excel or Google Sheets? Chat with us; we're happy to help!