Excel Spreadsheet Automation with the QUERY Formula

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

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

  Obtaining an API Token

  An API token is necessary for account authentication. To generate one, login to your Atlassian account and navigate to API tokens > Create API token. The generated token will be displayed.

  Connect Using a Confluence Cloud Account

  To connect to a Cloud account, provide the following (Note: Password has been deprecated for connecting to a Cloud Account and is now used only to connect to a Server Instance.):

  • User: The user which will be used to authenticate with the Confluence server.
  • APIToken: The API Token associated with the currently authenticated user.
  • Url: The URL associated with your JIRA endpoint. For example, https://yoursitename.atlassian.net.

  Connect Using a Confluence Server Instance

  To connect to a Server instance, provide the following:

  • User: The user which will be used to authenticate with the Confluence instance.
  • Password: The password which will be used to authenticate with the Confluence server.
  • Url: The URL associated with your JIRA endpoint. For example, https://yoursitename.atlassian.net.
• 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 Confluence 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 Pages WHERE Id = '"&B5&"'","User="&B1&";APIToken="&B2&";Url="&B3&";Timezone="&B4&";Provider=Confluence",B6)
3. Change the filter to change the data.