How to import data from the FDA to Google Sheets:
Step 1: Install Amigo Data extension in Google Sheets
Step 2: Choose an endpoint of data to import
Step 3: Launch Amigo Data, enter the endpoint, import
The United States Food and Drug Administration (FDA) is a federal agency responsible for protecting public health by ensuring the safety, efficacy, and security of human and veterinary drugs, biological products, and medical devices. It also oversees and certifies the approval of food products, cosmetics, and products that emit radiation.
The FDA keeps a record of all the food- and drug-related data, most of which it makes available to the public for transparency as well as commercial and personal use. Some of these data include a number of high-value, high-priority and scalable structured datasets, including adverse events caused by a particular food or drug, drug product labelling, and recall enforcement reports. These data can be accessed from the openFDA platform.
To get the most, you can import FDA data into Google Sheets by using API. You can then have the data in a more readable format. You could then search, sort, filter, highlight, create visualisations, and several others that can make understanding the data much easier and simpler.
So, let me walk you through how to import data from the FDA to Google Sheets by making a call to the openFDA API.
Step 1: Getting a tool to make the API call
Before we can begin importing data from the FDA to Google Sheets, we need to get the tool with which to pull the data. We’ll be using Amigo Data, a Google Sheets extension, a simple but versatile tool that can pull data from various sources.
Install it from the Google Workspace Marketplace. Here’s the installation link: https://workspace.google.com/marketplace/app/amigo_data/274781802717
Alternatively, you can get it from Google Sheets itself.
- Open a new sheet
- Go to Extensions→Add-ons→Get add-ons
- Search for Amigo Data and install
Step 2: Getting the API endpoint
We now need to get the endpoint for the dataset we want to import into Google Sheets. All API calls to the FDA start with the base endpoint below.
https://api.fda.gov/💡️ An endpoint is the connection point of a service, tool, or application accessed over a network, identical to the URL for websites.
Let us import the data for adverse drug event reports submitted to the FDA. These include any undesirable experience associated with the use of the drug, including serious drug side effects, product use errors, product quality problems, and therapeutic failures.
Note: For limited requests, an API key is not required. However, if you need to make more than 240 requests per minute and 1,000 requests per day, you need an API key. You can get a key by creating an account by following this link.
To get a list of various categories of endpoints, go to open.fda.gov. Then move the cursor to APIs and select Drug Endpoints.
Then select Adverse Events from the list of Drug API Endpoints.
From the left of the screen, select Explore the API with an Interactive Chart.
Here you’ll see a chart showing the number of adverse drug event reports since 2004. Copy the current query URL shown underneath the chart.
This is the endpoint for the adverse drug reports since 2004 up to the current date. To change the period for which you want to get the reports, you can specify the date range in the API URL.
For example, to get the report for adverse effects from 2010, January 1 to 2022, August 31, we can change the date parameter to 20220101+TO+20220831. The date format is YYYYMMDD.
Shown below is the updated endpoint URL with the limit set to 200.
https://api.fda.gov/drug/event.json?search=receivedate:[20220101+TO+20220831]&limit=200You can do this by navigating to API Example queries and changing the date in the example endpoint. The limit parameter specifies the number of records to return. If you want all the reports for the specified period, delete the parameter.
Then click the Run query button to check if the input parameter is correct. If it is, you should see the result in JSON format below.
Another way to do it is to manually specify the query parameters in Amigo Data from the Details tab.
Step 3: Importing Data from FDA to Google Sheets
To import data from the FDA 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
- Preview the data to import and flatten them. Then click IMPORT.
To change the parameter or add or remove them, you can do so before you click IMPORT. You can also edit them later from the reports menu.
After entering the endpoint, expand the Details tab and click Add queries.
In the Key box, enter search; and in the Value box, enter receivedate: [20100101+TO+20220831]. To add a limit, enter limit in the Key box and the number (say, 100) in the Value box.
Note: If you are using a key to make requests in excess of the limit, you need to specify the key in the query parameter. In the Key box, type in api_key; and in the Value box, enter your key.
The requested data will be imported into Google Sheets in a couple of seconds. Shown below is a snippet of the data imported from the FDA to Google Sheets
Other endpoints
We can as easily get the endpoints for other datasets to import data from the FDA website to Google Sheets by following the same procedure as the above.
For example, to get the records of patient drug reactions, navigate to the Drug reactions section.
By default, it will show the records of all reaction types. We can narrow the reaction by giving a specific kind of reaction, eg. nausea, by inputting the reaction name in the custom search parameter box.
The URL for the specified endpoint can be found under current query, which is
https://api.fda.gov/drug/event.json?search=(receivedate:[20040101+TO+20221007])+AND+nausea&count=patient.reaction.reactionmeddrapt.exact
Tip: Since the data size is huge, it is recommended that you set a limit by adding the limit parameter.
For more details on various endpoints and other information, visit the FDA api documentation page: https://open.fda.gov/apis/.
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
Using Amigo Data you can easily import data from the FDA to Google Sheets. And not just import them but get them updated automatically without having to repeat the process. There are also several sources from which you can use Amigo Data to import data from the FDA to Google Sheets. Few examples are, data from open government data platforms of the US, UK, and India; Google Search Console, Google Trends, Snowflake, Spotify; etc.
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-accuweather-data-to-google-sheets/
https://blog.tryamigo.com/import-data-from-binance-to-google-sheets/
https://blog.tryamigo.com/import-yahoo-finance-data-into-google-sheets/
https://blog.tryamigo.com/import-data-from-nasdaq-to-google-sheets/