Google Sheets Connector
The Dimensions API Connector is a Google Sheets add-on which permits to launch DSL queries directly from a Gsheets window. The results are transformed from JSON to a tabular format, thus allowing further manipulations using the usual Google Sheets tools.
The add-on is freely available on the G Suite Marketplace.
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.
Installation Tutorial
Please see this YouTube video tutorial for a detailed walk through of how to find the add-on, how install it and use it to query the Dimensions API.
Dimensions API Connector Privacy Policy
This Notice applies to use of the Dimensions API Connector add-on for Google Sheets (the “API Connector”), which is part of the wider Dimensions product family, and supplements the Dimensions Privacy Policy.
The API Connector is designed to help subscribers to the Dimensions API make better use of the Dimensions API by using Google Apps Script to automate data handling processes within your Google Sheets. The API Connector runs entirely within Google Sheets, so your spreadsheet data does not leave your Google account.
Upon installation, the API Connector requests the following permissions:
See, edit, create and delete your spreadsheets in Google Drive. This permission is required in order for the API Connector to import data (from the Dimensions API) into your spreadsheets, a process which involves reading and modifying those spreadsheets.
Display and run third-party web content in prompts and sidebars inside Google applications. This permission is required in order to create custom menus, dialogs and sidebars to make it easier for you to create and run queries against the Dimensions API.
Connect to an external service. This permission is required to fetch your Dimensions API subscription info and retrieve data from the Dimensions API. This is equivalent to the process of sending a POST query to the Dimensions API, as described in https://docs.dimensions.ai/dsl/api.html.
Log in credentials
The API Connector connects directly to the Google via oAuth 2.0, which enables you to login without sharing your access credentials with us. Your Google account data is not accessed, viewed, or stored by us. When logging into Dimensions in order to access the Dimensions API, the API Connector stores your login credentials only locally on your browser using the built-in services provided by Google Sheets APIs.
Dimensions privacy policy / Terms of use
Use of the API 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.
Cell commands / formulas
Dimensions Gsheets plugin provides these cell formulas:
DIMENSIONS(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.
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 formula, repeating the quotes escapes them e.g. “”search term””. See point 4 below for more details.
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 Gsheet formula DIMENSIONS that does exactly that. For example you could type the following in a cell: =DIMENSIONS("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 Gsheets 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("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 doi in [{A3:A25}]return publications[doi+title] or search publications where id in [{Sheet6!A3:A25}]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.
Screenshots
