Excel Spreadsheet Automation on Webflow Data with the QUERY Formula

The CData Excel Add-In for Webflow provides formulas that can query Webflow data. The following three steps show how you can automate the following task: Search Webflow 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 Webflow data records you want to retrieve, written in standard SQL.

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

  Authentication

  Webflow uses OAuth 2.0 authentication to ensure secure access to sites, CMS collections, e-commerce data, and other resources. This authentication method allows you to securely connect to your Webflow workspace and manage resources with proper authorization.

  OAuth 2.0 Setup and Configuration

  Step 1: Create a Webflow OAuth Application

  To set up OAuth authentication:

  1. Visit the Webflow Developer Portal
  2. Navigate to "Apps & Integrations" in your Webflow account
  3. Click "Register an App" to create a new OAuth application
  4. Configure the application name, description, and redirect URI (CallbackURL)
  5. Copy the Client ID and Client Secret for use in your connection

  Required Connection Properties

  • AuthScheme: Set this to OAuth (required)
  • OAuthClientId: Client ID from your Webflow OAuth application (required)
  • OAuthClientSecret: Client secret from your Webflow OAuth application (required)
  • CallbackURL: Redirect URI specified in your OAuth application (required)
  • InitiateOAuth: Set to GETANDREFRESH for automatic token management (recommended)

  Required OAuth Scopes

  The Webflow API Profile requires the following OAuth scopes:

  • sites:read - Read access to site information and configuration
  • pages:read - Read access to site pages
  • cms:read - Read access to CMS collections and items
  • forms:read - Read access to forms and form submissions
  • assets:read - Read access to media assets and folders
  • ecommerce:read - Read access to products, orders, and inventory
  • authorized_user:read - Read access to the authorized user
• 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 Webflow data, such as Id.
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 Sites WHERE Id = '"&B7&"'","Profile="&B1&";AuthScheme="&B2&";InitiateOAuth="&B3&";OAuthClientId="&B4&";OAuthClientSecret="&B5&";CallbackUrl="&B6&";Provider=API",B8)


3. Change the filter to change the data.