Quick Guide
How to import US patent data to Google Sheets with Amigo Data:
1. Install Amigo Data
2. Get the API key
3. Choose the endpoint of the data to import
4. Launch Amigo Data from Google Sheets
5. Select Custom API, and enter the endpoint
6. Preview the data then import
The U.S. Patent and Trademark Office (USPTO) is the agency responsible for granting U.S. patents and registering trademarks. It has issued more than 10 million patents and received patent applications nearly double that figure.
Most of the information relating to patents and trademarks can be publicly accessed. This includes the number and types of patents received and issued, patents citations, inventors and assignees, and patents by industries, amongst others.
These huge and varied data can be imported to Google Sheets using the API provided by the USPTO. Importing patent data to Google Sheets gives it much more utility and makes it a lot more versatile.
From Google Sheets, the data can then be used to gain knowledge and insights such as:
- Relationship between location and patenting activity;
- Places with a high concentration of innovation;
- Discover patenting trends over time and compare them between dates;
- Persons or countries with the most patents;
- Types of patents and citations;
- Number of patents issued by gender; and so on.
It is then possible–or at least, easier–to establish correlations such as innovation and factors conducive to innovation, such as infrastructure, human capital, government policies, financial resources, educational institutions, etc.
Here’s a simple and short guide to help you learn how to import US patent data to Google Sheets.
Install Amigo Data
We will use Amigo Data, a no-code Google Sheets extension to import US patent data to Google Sheets.
Get it from the Google Workspace Marketplace here.
Alternatively, you can open a Google Sheets document, then go to Extensions→Add-ons→Get add-ons. And search for Amigo Data and install it.
Request API Key
We need to get an API key so that can import US patent data to Google Sheets.
To get the key, follow these steps:
Step 1: Go to the US Patent and Trademark Office. Then click Learning and Resources.
Step 2: Select Statistics and data→PatentsView
Step 3: On the PatentsView page, navigate to API→Purpose
Step 4: Request an API key. Follow the link. Click Request an API Key.
Submit your name and email address. The key will be sent to your email address. Do note that it may take some hours before you receive your key.
Get API endpoint
Each dataset has an endpoint which allows an application to make a request and pull the data to the destination.
Before we can get the endpoint, we need to figure out what it is that we want to import to Google Sheets. Let us import the information of all patents granted since January 1, 2015.
The base endpoint for patents data is:
https://search.patentsview.org/api/v1/patent/
All API calls to request patent information must begin with this. The query parameters can be specified either in the endpoint URL or manually (more about this later).
On the PatentsView page, navigate to API→API Endpoints.
From here, go to the Swagger Documentation page by clicking the “Swagger interface page” link. This will take us to an interactive query page, from which we can search the data endpoint and test it.
Before we can make an API call, we need to gain permission to make a request by using the API key to authenticate. Click Authorize.
Then enter the API key in the Value box and click Authorize.
Expand the first option under Patent and click the Try it out button.
There are four parameters, each of which is explained briefly hereunder:
- f string: the array of fields to include in the results, eg. patent number This is an optional parameter.
- o string: object of options to modify the results. There are two options: size and offset. Size is the number of results to return; offset is the number of results to skip. This is also an optional parameter and if left unspecified, returns the default values: 100 and 0, respectively.
- q string: This is the query parameter and is mandatory.
- s string: This is the sort parameter. It let us sort the results either in ascending or descending order. This too is an optional parameter, and if not specified, the results are sorted in ascending order.
To get the information of all patents granted since January 1, 2015, we’ll use the following parameters:
- In the field parameter (f string): [“patent_number”, “patent_title”, “patent_abstract”, “patent_date”]
This will give us the patent number, title, abstract and date in the result. You may delete or add more fields.
- o string: {“size”: 500, “offset”: 0}
We set the size to 500 to get the result of the latest 500 patents. If we don’t specify this, we’ll get the results for all patents granted since Jan 1, 2015, which could be some tens of thousands.
- q string: {“_gte”:{“patent_date”:”2015-01-01″}}
This will return all the patents granted since the date. The “_gte” stands for greater than or equal to. The accepted date format is YYYY-MM-DD.
- s string: {“patent_date”: “desc”}
This will return the results in descending order, ie, from the newest to the oldest.
For detailed information on the query language format, visit this page: https://patentsview.org/apis/api-query-language
Click Execute to test if the queries are correct. If all goes well, we should see the result in JSON format under Responses with the “error” status false.
Copy the Request URL or the curl without the apostrophes, whichever you prefer–they’re both the same. This is the endpoint URL.
https://search.patentsview.org/api/v1/patent/?f=%5B%22patent_number%22%2C%20%22patent_title%22%2C%20%22patent_abstract%22%2C%20%22patent_date%22%5D&o=%7B%22size%22%3A%20500%2C%20%22offset%22%3A%200%7D&q=%7B%22_gte%22%3A%7B%22patent_date%22%3A%222015-01-01%22%7D%7D&s=%5B%7B%22patent_date%22%3A%20%22desc%22%7D%5DNow that we’ve got the API endpoint for the dataset we want, we can now begin to import US patent data to Google Sheets.
Import US patent data to Google Sheets
To import US patent data to Google Sheets using Amigo Data, follow the steps below.
- Open a new Google Sheets document and launch Amigo Data
Extensions→Add-ons→Amigo Data→Connect
- Select Custom API from the list of sources
- Enter the endpoint in the API URL
- Expand the Details tab and enter the add the following header:
Key: X-Api-Key, Value: Your API Key
- Preview the data to import and flatten them. Then click IMPORT.
If there are data that you do not want to be imported, such as the error status and total hits, you can remove them by clicking the delete button on the preview menu.
In order to change the parameters or add or remove them, you can do so from the Details tab.
Instead of getting the whole endpoint from the Swagger Documentation page and pasting it in the API URL bar, we can also specify the queries manually in Amigo Data’s Custom API connector.
- Paste the base endpoint in the API URL box, which is https://search.patentsview.org/api/v1/patent/
- Add the API key as a header
- Add the query parameters following the same format as given in API Query Language.
For example:
- Query 1 – Key: q, Value: {“_gte”:{“patent_date”:”2015-01-01″}}
- Query 2 – Key: f, Value: [ “patent_number”, “patent_title”, “patent_abstract”, “patent_date” ]
- Query 3 – Key: o, Value: { “size”: 500,”offset”: 0 }
- Query 4 – Key, s, Value: {“patent_date”:”desc”}
Before you import the data, it is recommended that you flatten the data so that they’re laid out in a more orderly fashion.
The data will be imported into Google Sheets in a moment. Shown below is a snippet of the imported data.
Setting refresh schedule
To get the information updated periodically, click the report you want to update automatically, toggle on the Refresh Schedule and select the schedule–hourly, daily, weekly or custom days.
Then click Save & Run
Alerts via email or Slack
You can get the report sent to your email or a Slack channel if you want to.
To set up an alert, click Get Alerts→Add alert data+. Edit the details and click Set Alert.
Conclusion
Amigo Data makes it much easier and quicker to import US patent data to Google Sheets than the other way of doing it–manually. And as you use if more frequently and become familiar with it, the process becomes a lot more hassle-free and effortless.
There are also several other sources from which you can import data to Google Sheets using Amigo Data, such as MySQL, Snowflake, and Google Analytics; and disparate data such as music, weather, and the stock market.
You can find the articles on them as well as tutorials and tips on Google Sheets that can help you make the most from data on our blog.
Here are some similar articles that you may like:
https://blog.tryamigo.com/import-indian-government-open-data-to-google-sheets/
https://blog.tryamigo.com/export-data-from-google-trends-to-google-sheets/
https://blog.tryamigo.com/import-data-google-search-console-to-google-sheets/