Excel Spreadsheet Automation on Monday.com Data with the QUERY Formula

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

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

  You can connect to Monday.com using either API Token authentication or OAuth authentication.

  Connecting with an API Token

  Connect to Monday.com by specifying the APIToken. Set the AuthScheme to Token and obtain the APIToken as follows:

  • API tokens for admin users
    1. Log in to your Monday.com account and click on your avatar in the bottom left corner.
    2. Select Admin.
    3. Select "API" on the left side of the Admin page.
    4. Click the "Copy" button to copy the user's API token.
  • API tokens for non-admin users
    1. Click on your profile picture in the bottom left of your screen.
    2. Select "Developers"
    3. Click "Developer" and then "My Access Tokens" at the top.
    4. Select "Show" next to the API token, where you'll be able to copy it.

  Connecting with OAuth Authentication

  Alternatively, you can establish a connection using OAuth (refer to the OAuth section of the Help documentation).

• 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 Monday.com data, such as Status.
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 Invoices WHERE Status = '"&B2&"'","APIToken="&B1&";Provider=Monday",B3)

👁 Formula inputs used in this example. (Google Apps is shown.)

3. Change the filter to change the data. 👁 The outputs of the formula. (Google Apps is shown.)