Excel Add-In

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.

Dimensions API Connector Privacy Policy

This Notice applies to use of the Dimensions Excel Add-In (the “API Connector”), which is part of the wider Dimensions product family, and supplements the Dimensions Privacy Policy.

Dimensions privacy policy / Terms of use

Use of the Add-In 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.

Cell commands / formulas

Dimensions Excel Add-In provides these cell formulas:

  • DIMENSIONS.RUN(query, [pages], [returnHeader]): returns a table with results of the DSL query, positioned in the current cell, where query is a string representing a DSL query, pages is the count of pages to return and returnHeader is an optional boolean parameter, true by default, specifying whether a header is returned or results are put in the row with no additional information.

  • DIMENSIONS.TOTAL(query): returns a total count of records in Dimensions matching the specified DSL query.

Note

One can use DIMENSIONS.RUN function with either just query argument, as other arguments are optional, or has to specify all three of them. It is not possible to skip pages argument, but value 1 can be used.

FAQs

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

3. 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 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.

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

Yes. The add-on includes a 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 a 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]")

5. 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 cells 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 (eg DOIs, ISSNs, Dimensions IDs, etc..) and not for examples 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. Separator between function arguments is a semicolon (;) as per Excel’s locale settings, or it could be comma (,).

6. Does Excel Add-In support JSON unnesting?

Yes, the Excel Add-In 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.