Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for BigCommerce provides formulas that can edit, save, and delete BigCommerce data. The following three steps show how you can automate the following task: Search BigCommerce data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the BigCommerce data records you want to retrieve or the modifications to be made, written in standard SQL.

• Connection: Either the connection name, such as BigCommerceConnection1, or a connection string. The connection string consists of the required properties for connecting to BigCommerce data, separated by semicolons.

  BigCommerce authentication is based on the standard OAuth flow. To authenticate, you must initially create an app via the Big Commerce developer platform where you can obtain an OAuthClientId, OAuthClientSecret, and CallbackURL. These three parameters will be set as connection properties to your driver.

  Additionally, in order to connect to your BigCommerce Store, you will need your StoreId. To find your Store Id please follow these steps:

  1. Log in to your BigCommerce account.
  2. From the Home Page, select Advanced Settings > API Accounts.
  3. Click Create API Account.
  4. A text box named API Path will appear on your screen.
  5. Inside you can see a URL of the following structure: https://api.bigcommerce.com/stores/{Store Id}/v3.
  6. As demonstrated above, your Store Id will be between the 'stores/' and '/v3' path paramters.
  7. Once you have retrieved your Store Id you can either click Cancel or proceed in creating an API Account in case you do not have one already.
• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search BigCommerce data, such as FirstName.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM Customers WHERE FirstName = '"&B5&"'","OAuthClientId="&B1&";OAuthClientSecret="&B2&";StoreId="&B3&";CallbackURL="&B4&";Provider=BigCommerce",B6)
3. Change the filter to change the data.