Dimensions API Connector for Microsoft Excel

This Connector brings the power of the Dimensions Search Language (DSL) directly into your Excel workbook, providing parity with our Google Sheets Connector while leveraging the native performance of Microsoft Excel.

The Dimensions Excel Add-In is an Excel add-in which permits to launch DSL queries directly from an Excel window. The results are transformed from JSON to a tabular format, thus allowing further manipulations using the usual Excel tools.

The add-on is freely available on the Microsoft AppSource.

Note

The Dimensions Analytics API is subscription-only, so your Dimensions account needs to be activated for this service and subject to restrictions on use. Please send an email to supportapi@dimensions.ai if you have any questions.

Overview

The Dimensions API Connector for Excel allows researchers, analysts, and data scientists to query the Dimensions database without leaving Excel. By integrating DSL queries directly into spreadsheet workflows, you can automate data retrieval, perform bulk lookups via cell referencing, and transform complex JSON API responses into clean, tabular data instantly.

Note

Access requires an active Dimensions subscription with API access enabled. Please send an email to supportapi@dimensions.ai if you have any questions.

Key Features

1. Direct DSL Query Execution

Run raw DSL queries within Excel to retrieve data from any Dimensions entity (Publications, Grants, Patents, etc.). You can choose to output results into your active sheet or automatically generate a new sheet for the dataset.

2 Automatic Data Formatting

The Connector handles the heavy lifting of data transformation. It automatically flattens nested JSON responses from the Dimensions API into a structured, row-and-column tabular format compatible with Excel’s data tools.

3. Custom Excel Functions

The Connector introduces native-style formulas to your cells:

  • =DIMENSIONS.RUN(query, [pages], [returnHeader])

    • Returns a table with results of the DSL query, positioned in the current cell

    • query: The DSL query string

    • pages: Number of pages to retrieve (1 page = 1,000 records)

    • returnHeader: (Optional) Boolean TRUE/FALSE to include the field names as a header row.

  • =DIMENSIONS_TOTAL(query)

    • Returns a single integer representing the total count of records matching the query in the Dimensions database.

4. Dynamic Cell Referencing

Build dynamic queries by referencing cell ranges directly within your DSL string. This is particularly powerful for passing lists of IDs (e.g., DOIs, PMIDs, or Grant IDs) from your spreadsheet back into the API.

search publications where doi in [{A3:A25}] return publications [doi+title]

5. Auto-Pagination

The standard API limit is 1,000 records per request. The Connector bypasses this by offering auto-pagination:

  • Extract up to 50,000 records (50 pages) in a single operation.

  • The Connector manages sequential API calls and rate-limiting delays automatically to ensure data integrity.

Working with Queries

User Interface

The Connector integrates seamlessly with the Excel Ribbon, providing:

  • Custom Menus: Quick access to account settings and documentation.

  • Sidebar Widgets: A dedicated space to draft, test, and execute complex DSL queries before committing them to a sheet.

Getting Started

  1. Install: Visit the Microsoft AppSource Marketplace to add the extension to your Excel account.

  2. Authenticate: Open the Dimensions Sidebar in Excel and log in using your Dimensions API credentials.

  3. Run your first query: Try entering =DIMENSIONS("search publications return publications", 1) in any cell to see the Connector in action.

Dimensions API Connector Privacy Policy

This Notice applies to use of the Dimensions API Connector for Excel, which is part of the wider Dimensions product family, and supplements the Dimensions Privacy Policy.

Dimensions privacy policy / Terms of use

Use of the Connector is provided for free and subject to the Dimensions terms of use. It does not form part of any subscription for Dimensions and may be withdrawn at any time. For comprehensive information on how we collect, process and use your personal information, please refer to the Dimensions Privacy Policy, which this notice forms a part.

FAQs

  1. How do I escape quotes in a keyword search?

When using full-text search with multiple keywords, you must escape inner quotes with \ (backslash). For example, in the query: search publications for "graphene AND \\"modern agriculture\\" " return publications[basics].

Note: this applies only to the ‘Run Queries’ sidebar widget. When using the DIMENSIONS.RUN formula, repeating the quotes escapes them e.g. search term. See point 4 below for more details.

  1. What does the ‘Open new sheet’ checkbox do?

Normally API query results are added to the active sheet. By using this option it is possible to open the results of a query in a new sheet (named with the current timestamp).

  1. What does the ‘Auto-pagination’ option do?

Normally, API queries can return a maximum of 1000 records per request. However by using the limit and skip controls it is possible to get more results (see also the Paginating Results section).

When this option is turned on, paginated queries are automatically created, based on the query you entered. The slider value determines how many pages/records to extract. Once the queries are completed, results from different pages are merged together and presented within the same sheet.

Note: a maximum of 50 pages, or 50k records, can be extracted using this method. In order to comply with the API call rate limits, paginated queries are run every two seconds, thus large queries may take several minutes to complete.

  1. Can I run a query directly from a cell?

Yes. The add-on includes an Excel formula DIMENSIONS.RUN that does exactly that. For example you could type the following in a cell: =DIMENSIONS.RUN("search publications for "graphene" return publications[id+doi+title]"). The results of the query (including headers) will appear on the same row.

Please note that in order to use quotes inside an Excel formula they must be escaped using an extra quote symbol (repeated quotes, e.g. in graphene). A special case is when your query includes a keyword search with exact phrases, hence the inner quotes need to be escaped as well, only this time using the standard DSL escaping rules. For example: =DIMENSIONS.RUN("search publications for """graphene AND "modern agriculture" """ return publications[id+doi+title]")

  1. Can I reference other cells dynamically from a DSL query?

Yes, DSL queries can reference other cells (within the same sheet or other sheets), eg: search publications where id in [" & TEXTJOIN(","; TRUE; CHAR(34) & A1:A2 & CHAR(34)) & "] return publications[doi+title].

This feature simply transforms a cell’s range into a comma-separated list of strings. So it is meant to be used primarily with queries where a list of IDs are passed (e.g. DOIs, ISSNs, Dimensions IDs, etc..) and not for example in keyword search queries.

Note

The TEXTJOIN function is used to concatenate the values in the specified range into a single string, with each value separated by a comma. CHAR(34) is used to add double quotes around each value. The separator between function arguments is a semicolon (;) as per Excel’s locale settings, or it could be comma (,).

  1. Does the Connector support JSON unnesting?

Yes, the Excel Connector supports JSON unnesting through Power Query. After running a query that returns nested JSON data, you can use Power Query to transform and flatten the data structure, making it easier to work with in Excel. For detailed instructions on how to unnest JSON data in Excel using Power Query, please refer to the How to Unnest JSON Data in Excel guide.