Excel Spreadsheet Automation on Landbot Data with the QUERY Formula

The CData Excel Add-In for Landbot provides formulas that can query Landbot data. The following three steps show how you can automate the following task: Search Landbot 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 Landbot 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 Landbot data, separated by semicolons.

  Using API Key Authentication

  Landbot uses token-based authentication. Obtain your agent token from Settings > Account in your Landbot account.

  Set the following connection properties:

  • AuthScheme: Set this to APIKey.
  • APIKey: Set this to your Landbot agent token.

  Sample Connection String

  Profile=C:\profiles\Landbot.apip;AuthScheme=APIKey;APIKey=your_agent_token_here;
  

• 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 Landbot data, such as .
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 Agents WHERE = '"&B4&"'","Profile="&B1&";AuthScheme="&B2&";APIKey="&B3&";Provider=API",B5)

👁 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.)