Excel Spreadsheet Automation on Linear Data with the QUERY Formula

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

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

  You can authenticate to Linear with a personal API key or with OAuth 2.0. The API key is the simplest option for connecting with your own Linear account.

  Authenticating with an API Key

  Set the following connection properties:

  • AuthScheme: Set this to APIKey.
  • APIKey: A Linear personal API key.

  To create a personal API key, log in to Linear, open Settings > Security & access > Personal API keys, select New API key, and create it. Copy the key immediately, because Linear shows it only once.

  Authenticating with OAuth

  OAuth requires a custom OAuth application registered in Linear (Settings > API > OAuth applications), which provides the OAuthClientId and OAuthClientSecret. Two flows are supported:

  • Authorization code: Set AuthScheme to OAuth, InitiateOAuth to GETANDREFRESH, and provide OAuthClientId, OAuthClientSecret, and the CallbackURL defined in your application (e.g., http://localhost:33333). The driver opens Linear in your browser so you can grant access.
  • Client credentials: Set AuthScheme to OAuthClient and provide OAuthClientId and OAuthClientSecret. This authenticates the application itself, with no browser interaction, and suits machine-to-machine integrations.

  By default, the driver requests the read,write scopes. The driver refreshes the access token automatically when it expires.

• 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 Linear data, such as key.
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 Team WHERE key = '"&B3&"'","AuthScheme="&B1&";APIKey="&B2&";Provider=Linear",B4)


3. Change the filter to change the data.