Excel Spreadsheet Automation on Short.io Data with the QUERY Formula

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

  Using API Key Authentication

  Short.io uses API Key authentication. To obtain your API key:

  1. Log in to your Short.io account
  2. Navigate to Settings > Integrations & API > API
  3. Click Create API Key and copy your API key

  After obtaining the API key, you are ready to connect:

  • AuthScheme: Set this to APIKey.
  • APIKey: Set this to your Short.io API key obtained from Settings > Integrations & API > API.

  Example connection string:

  Profile=C:\profiles\ShortIo.apip;AuthScheme=APIKey;ProfileSettings='APIKey=your_api_key';
  

  Available Tables

  The Short.io profile provides access to the following tables:

  • Domains - Short.io domains associated with the authenticated account
  • Links - Short links for a domain
  • LinkExpand - Expand a short link by domain and path
  • LinksByOriginalUrl - Retrieve multiple short links matching a given original destination URL
  • Folders - Link folders within a specific domain
  • LinkPermissions - Permission records for a specific link within a domain
  • CountryTargeting - Country-based redirect targeting rules for a specific short link
  • RegionTargeting - Region-based redirect targeting rules for a specific short link
  • Regions - List of available regions/states for a given country code
  • DomainStatistics - Aggregated click and traffic statistics for a Short.io domain
  • LinkStatistics - Aggregated click and traffic statistics for a specific Short.io link
• 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 Short.io 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 Domains WHERE  = '"&B5&"'","Profile="&B1&";AuthScheme="&B2&";ProfileSettings="&B3&";Provider=API",B6)


3. Change the filter to change the data.