Import API data to Google Sheets
With examples using Hashnode, Openai image generation, Serply Google news and images.
A simple use case
I like using low-code tools to provide simple solutions. I will show you how to import data from any API directly into Google Sheets. This can be useful in a wide variety of scenarios and will work with almost any API that returns data in JSON format.
In the first example, I will import stories from the Hashnode GraphQL API and explain how it works. It is free to use and does not require an authorization key. After that, I will show you three additional examples. You can be creative with this technique and develop your formulas. The possibilities are endless.
Importing stories from the Hashnode API
Let's take a look at the first example. As soon as we visit the spreadsheet, the formula on cell A7 uses the IMPORTJSONAPI function to request the Hashnode API and populates the rows with the results. Notice the Auto-refresh option in the following screenshot. When this option is enabled, the function makes a new request each time you revisit or edit the spreadsheet. In this case, you will always get the latest Hashnode stories.
This formula is made easy by using an open-source Google app script called IMPORTJSONAPI. It provides a custom function to selectively extract data from an API in a tabular format suitable for importing into a Google Sheets spreadsheet.
Installation steps
Access your free Google Sheets account at https://docs.google.com/spreadsheets.
Open Extensions > Apps Script from the top menu.
Create a blank script file called IMPORTJSONAPI.gs.
Copy and paste the entire contents of the IMPORTJSONAPI.gs file from the Github repository found here.
You should now be able to use the \=IMPORTJSONAPI() function in your sheet.
About Google Sheets
You are probably already familiar with Google Sheets. I think it is worth going over what makes Google Sheets such a popular tool and how you can leverage Google Apps Script's extensions.
Why is Google Sheets so popular?
Its popularity stems from its user-friendly interface, cloud-based accessibility, and cost-effectiveness, as it is a free tool. Additionally, it offers collaborative features that enable multiple users to work on the same sheet simultaneously from anywhere in the world, making it easy for team members to collaborate on a project. Users can also customize their sheets with various formatting options, charts, and formulas to analyze data and make informed decisions.
What is Google Apps Script?
Google Apps Script is a scripting language that allows you to extend and automate the functionality of various Google Workspace applications, including Google Sheets, Docs, Forms, and more. Some of the benefits of using Google Apps Script include:
Automating repetitive tasks, such as data entry or report generation.
Customizing your Google Workspace applications to fit your specific needs. For example, you can create custom functions or menus to simplify your workflow.
Integrating seamlessly with other Google services, allowing you to easily access and manipulate data from different sources.
Collaborating with multiple users on the same project simultaneously.
Developing powerful scripts with a wide range of capabilities, including sending emails, creating Google Calendar events, accessing Google Drive files, and more.
Free to use, making it a cost-effective solution for individuals and businesses that need to automate and extend their Google Workspace applications.
A closer look at the formula
=IMPORTJSONAPI(
"https://api.hashnode.com/graphql",
"$.data.storiesFeed[*]",
"title, author.username, slug",
"method=post",
"payload={
'query': '{
storiesFeed(type: FEATURED) {
title
slug
author {
username
}
}
}'
}",
"contentType=application/json",
'Auto-refresh'!$B$1
)
When you read this formula it should be easy to understand the purpose of the arguments. The required arguments are the URL, JSONPath query and columns. The rest of the arguments are optional.
IMPORTJSONAPI Function Arguments
| Parameter | Description |
| URL | The URL endpoint of the API. |
| JSONPath query | JSONPath query expression. |
| Columns | Comma-separated list of column path expressions. |
| Parameters | An optional list of parameters. |
URL
I used the GraphQL API URL api.hashnode.com/graphql. You can use any API that returns the data in JSON format.
JSONPath query
JSONPath allows you to write expressions to selectively extract data from JSON objects. I used the $.data.storiesFeed[*] expression to extract the array of stories from the response. Each JSON object in the stories feed will become a row in the spreadsheet. To learn more about how to use JSONPath, you can take a look at the article written by Stefan Goessner, referenced at the end of this article.
Columns
I selected the title, username and slug from each story object. The columns will be populated left to right in that order, starting from the formula's cell.
Parameters
The rest of the parameters are optional but may be necessary for the request to work properly. For example, this GraphQL request requires that we include the query with the request body (payload). The request method and headers are additional parameters you can use. Some APIs may require an Authorization header.
Hashnode stories feed response
{
"data": {
"storiesFeed": [
{
"title": "On-Demand Code Review With ChatGPT",
"slug": "on-demand-code-review-with-chatgpt",
"author": {
"username": "DamoGirling",
"name": "NearForm"
}
},
{
"title": "React-ing to TypeScript",
"slug": "react-ing-to-typescript",
"author": {
"username": "TreciaKS",
"name": "Trecia Kat "
}
},
{
"title": "Next.js and Rust: An Innovative Approach to Full-Stack Development",
"slug": "nextjs-and-rust-an-innovative-approach-to-full-stack-development",
"author": {
"username": "joshuamo",
"name": "Josh Mo"
}
}
]
}
}
Other Examples
I explained how importing works by walking through the Hashnode API example. Now I will show you three additional examples with some minor differences.
First, let's take a look at the Serply REST API. It allows us to perform various kinds of web searches and gather market data. In the following example, I used the Serply News API.
Search Google news with Serply API
=IMPORTJSONAPI(
CONCATENATE("https://api.serply.io/v1/news/q=", ENCODEURL(B3)),
"$.entries[*]",
"title, link, source.title",
CONCATENATE("headers={
'X-Api-Key': '", B4, "'
}"),
'Auto-refresh'!$B$1
)
As you can see, this example requires an X-Api-Key header. Instead of hardcoding the API key as part of the argument, I concatenated it by referencing cell B4. The following screenshot shows the news populated in the spreadsheet.
I am referencing the value from cell B3 for the search term. CONCATENATE and ENCODEURL are built-in functions in Google Sheets.
Serply API news response
{
"feed": {
"generator_detail": {
"name": "NFE/5.0"
},
"generator": "NFE/5.0",
"title": "\"stock market\" - Google News",
"title_detail": {
"type": "text/plain",
"language": null,
"base": "",
"value": "\"stock market\" - Google News"
},
"links": [
{
"rel": "alternate",
"type": "text/html",
"href": "https://news.google.com/search?q=stock+market&hl=en-US&gl=US&ceid=US:en"
}
],
"link": "https://news.google.com/search?q=stock+market&hl=en-US&gl=US&ceid=US:en",
"language": "en-US",
"publisher": "news-webmaster@google.com",
"publisher_detail": {
"email": "news-webmaster@google.com"
},
"rights": "2023 Google Inc.",
"rights_detail": {
"type": "text/plain",
"language": null,
"base": "",
"value": "2023 Google Inc."
},
"updated": "Sun, 12 Mar 2023 00:59:28 GMT",
"updated_parsed": [
2023,
3,
12,
0,
59,
28,
6,
71,
0
],
"subtitle": "Google News",
"subtitle_detail": {
"type": "text/html",
"language": null,
"base": "",
"value": "Google News"
}
},
"entries": [
{
"title": "Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates - CNBC",
"title_detail": {
"type": "text/plain",
"language": null,
"base": "",
"value": "Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates - CNBC"
},
"links": [
{
"rel": "alternate",
"type": "text/html",
"href": "https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5"
}
],
"link": "https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5",
"id": "CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s",
"guidislink": false,
"published": "Fri, 10 Mar 2023 18:38:00 GMT",
"published_parsed": [
2023,
3,
10,
18,
38,
0,
4,
69,
0
],
"summary": "<ol><li><a href=\"https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5\" target=\"_blank\">Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates</a> <font color=\"#6f6f6f\">CNBC</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiSWh0dHBzOi8vd3d3Lm55dGltZXMuY29tLzIwMjMvMDMvMTAvYnVzaW5lc3Mvc3RvY2stbWFya2V0LWpvYnMtcmVwb3J0Lmh0bWzSAU1odHRwczovL3d3dy5ueXRpbWVzLmNvbS8yMDIzLzAzLzEwL2J1c2luZXNzL3N0b2NrLW1hcmtldC1qb2JzLXJlcG9ydC5hbXAuaHRtbA?oc=5\" target=\"_blank\">Silicon Valley Bank Collapse Jolts Stock Market</a> <font color=\"#6f6f6f\">The New York Times</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiTWh0dHBzOi8vZmluYW5jZS55YWhvby5jb20vbmV3cy9tdWx0aXBsZS1hbGFybS1tYXJrZXRzLWZpcmUtbWF5LTIyMzIyMzAyMC5odG1s0gFVaHR0cHM6Ly9maW5hbmNlLnlhaG9vLmNvbS9hbXBodG1sL25ld3MvbXVsdGlwbGUtYWxhcm0tbWFya2V0cy1maXJlLW1heS0yMjMyMjMwMjAuaHRtbA?oc=5\" target=\"_blank\">Traders Brace for More Market Shocks After Week of Wild Swings</a> <font color=\"#6f6f6f\">Yahoo Finance</font></li></ol>",
"summary_detail": {
"type": "text/html",
"language": null,
"base": "",
"value": "<ol><li><a href=\"https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5\" target=\"_blank\">Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates</a> <font color=\"#6f6f6f\">CNBC</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiSWh0dHBzOi8vd3d3Lm55dGltZXMuY29tLzIwMjMvMDMvMTAvYnVzaW5lc3Mvc3RvY2stbWFya2V0LWpvYnMtcmVwb3J0Lmh0bWzSAU1odHRwczovL3d3dy5ueXRpbWVzLmNvbS8yMDIzLzAzLzEwL2J1c2luZXNzL3N0b2NrLW1hcmtldC1qb2JzLXJlcG9ydC5hbXAuaHRtbA?oc=5\" target=\"_blank\">Silicon Valley Bank Collapse Jolts Stock Market</a> <font color=\"#6f6f6f\">The New York Times</font></li><li><a href=\"https://news.google.com/rss/articles/CBMiTWh0dHBzOi8vZmluYW5jZS55YWhvby5jb20vbmV3cy9tdWx0aXBsZS1hbGFybS1tYXJrZXRzLWZpcmUtbWF5LTIyMzIyMzAyMC5odG1s0gFVaHR0cHM6Ly9maW5hbmNlLnlhaG9vLmNvbS9hbXBodG1sL25ld3MvbXVsdGlwbGUtYWxhcm0tbWFya2V0cy1maXJlLW1heS0yMjMyMjMwMjAuaHRtbA?oc=5\" target=\"_blank\">Traders Brace for More Market Shocks After Week of Wild Swings</a> <font color=\"#6f6f6f\">Yahoo Finance</font></li></ol>"
},
"source": {
"href": "https://www.cnbc.com",
"title": "CNBC"
},
"sub_articles": [
{
"url": "https://news.google.com/rss/articles/CBMiRGh0dHBzOi8vd3d3LmNuYmMuY29tLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s0gFIaHR0cHM6Ly93d3cuY25iYy5jb20vYW1wLzIwMjMvMDMvMDkvc3RvY2stbWFya2V0LXRvZGF5LWxpdmUtdXBkYXRlcy5odG1s?oc=5",
"title": "Dow closes more than 300 points lower, posts worst week since June as Silicon Valley Bank collapse sparks selloff: Live updates",
"publisher": "CNBC"
},
{
"url": "https://news.google.com/rss/articles/CBMiSWh0dHBzOi8vd3d3Lm55dGltZXMuY29tLzIwMjMvMDMvMTAvYnVzaW5lc3Mvc3RvY2stbWFya2V0LWpvYnMtcmVwb3J0Lmh0bWzSAU1odHRwczovL3d3dy5ueXRpbWVzLmNvbS8yMDIzLzAzLzEwL2J1c2luZXNzL3N0b2NrLW1hcmtldC1qb2JzLXJlcG9ydC5hbXAuaHRtbA?oc=5",
"title": "Silicon Valley Bank Collapse Jolts Stock Market",
"publisher": "The New York Times"
},
{
"url": "https://news.google.com/rss/articles/CBMiTWh0dHBzOi8vZmluYW5jZS55YWhvby5jb20vbmV3cy9tdWx0aXBsZS1hbGFybS1tYXJrZXRzLWZpcmUtbWF5LTIyMzIyMzAyMC5odG1s0gFVaHR0cHM6Ly9maW5hbmNlLnlhaG9vLmNvbS9hbXBodG1sL25ld3MvbXVsdGlwbGUtYWxhcm0tbWFya2V0cy1maXJlLW1heS0yMjMyMjMwMjAuaHRtbA?oc=5",
"title": "Traders Brace for More Market Shocks After Week of Wild Swings",
"publisher": "Yahoo Finance"
}
]
}
],
"ts": 1.8348586559295654,
"device_type": null
}
If you want to try this example, you can get your API key by creating a free account at serply.io.
Cute puppy images generated with Openai
Let's try something fun and import images generated with Openai. I am selecting only one column: the image URL. Similar to the previous example, I concatenated the search term as the prompt for the request payload. I also concatenated the Openai API key as the Authorization header.
=IMPORTJSONAPI(
"https://api.openai.com/v1/images/generations",
"$.data[*]",
"url",
"method=post",
CONCATENATE("payload={
'n': 3,
'size': '256x256',
'prompt':'", B3, "'
}"),
"contentType=application/json",
CONCATENATE("headers={
'Authorization': 'Bearer ", B4, "'
}"),
'Auto-refresh'!$B$1
)
Cute puppy images
Rendering images
An additional formula is needed to render the images. Without it, the script would just populate the rows with URLs. The solution is simple. I used the first formula in column B and the image() function in column A. I combined it with the Arrayformula() function which allows iterating through the rows of a single column to apply the image formula. Both of these functions are built-in into Google Sheets. What I would like for you to take away from this example is that you can process cells after the data has been imported.
=Arrayformula(image(B7:B9))
Openai API image generation response
{
"created": 1679707702,
"data": [
{
"url": "https://oaidalleapiprodscus.blob.core.windows.net/private/org-mnywxW4v2jQ8xH3mh4x7znhs/user-rLuI09VBzVeNpWdV3HZy0mRm/img-Ks1LtvvPJBWwlbmPVqG8mtiG.png?st=2023-03-25T00%3A28%3A22Z&se=2023-03-25T02%3A28%3A22Z&sp=r&sv=2021-08-06&sr=b&rscd=inline&rsct=image/png&skoid=6aaadede-4fb3-4698-a8f6-684d7786b067&sktid=a48cca56-e6da-484e-a814-9c849652bcb3&skt=2023-03-24T22%3A33%3A50Z&ske=2023-03-25T22%3A33%3A50Z&sks=b&skv=2021-08-06&sig=bwsEsZqY3hrCsT4/6zsyAoIEkasCfZ2xt6mw9jGphTc%3D"
},
{
"url": "https://oaidalleapiprodscus.blob.core.windows.net/private/org-mnywxW4v2jQ8xH3mh4x7znhs/user-rLuI09VBzVeNpWdV3HZy0mRm/img-36HPr7Y1rTSvlf1kMHKhFus0.png?st=2023-03-25T00%3A28%3A22Z&se=2023-03-25T02%3A28%3A22Z&sp=r&sv=2021-08-06&sr=b&rscd=inline&rsct=image/png&skoid=6aaadede-4fb3-4698-a8f6-684d7786b067&sktid=a48cca56-e6da-484e-a814-9c849652bcb3&skt=2023-03-24T22%3A33%3A50Z&ske=2023-03-25T22%3A33%3A50Z&sks=b&skv=2021-08-06&sig=j4sLyf/C0Vfi8FmUCeDFjUHDyothhp2Nc2GCD1xcdRg%3D"
},
{
"url": "https://oaidalleapiprodscus.blob.core.windows.net/private/org-mnywxW4v2jQ8xH3mh4x7znhs/user-rLuI09VBzVeNpWdV3HZy0mRm/img-OnD3pixbq0kEFoDN0LqPuoWP.png?st=2023-03-25T00%3A28%3A22Z&se=2023-03-25T02%3A28%3A22Z&sp=r&sv=2021-08-06&sr=b&rscd=inline&rsct=image/png&skoid=6aaadede-4fb3-4698-a8f6-684d7786b067&sktid=a48cca56-e6da-484e-a814-9c849652bcb3&skt=2023-03-24T22%3A33%3A50Z&ske=2023-03-25T22%3A33%3A50Z&sks=b&skv=2021-08-06&sig=SY2jkUtS2mVIO3Ji%2BZUJeHJl2eMMp53%2B8ncGvwGekBA%3D"
}
]
}
You can get your Openai API key by creating your account at platform.openai.com. You will get a generous amount of free credits to play with.
Funny cat images with Serply API
Here is another fun example using the Serply Image Search API. This is very similar to the Openai example, except these images are not generated. These come from Google Image search results.
=IMPORTJSONAPI(
CONCATENATE("https://api.serply.io/v1/image/q=", ENCODEURL(B3)),
"$.image_results[*]",
"link.title, image.src",
CONCATENATE("headers={
'X-Api-Key': '", B4, "'
}"), 'Auto-refresh'!$B$1
)
Funny cat images
Serply API image search response
{
"ads": [],
"ads_count": 0,
"answers": [],
"results": [],
"shopping_ads": [],
"places": [],
"related_searches": { "images": [], "text": [] },
"image_results": [
{
"image": {
"src": "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSpcPBqqdl0GyyJldY3wynsNbK5RVQpBavKabUPq-MRjHxmKHlZTRP_gdog0A&s",
"alt": ""
},
"link": {
"href": "https://www.google.com/url?q=https://www.nationalgeographic.com/animals/mammals/facts/domestic-cat&sa=U&ved=2ahUKEwiQ4_ym2db9AhWaU2wGHVtZD2sQr4kDegQIBBAC&usg=AOvVaw0i8bYceafnn6-F69n1uHjS",
"title": "Domestic cat www.nationalgeographic.com",
"domain": "Domestic cat www.nationalgeographic.com"
}
},
{
"image": {
"src": "https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSQVJ8iIWM5ZRV2OyXAz2qN-JtpNbHmSgGbF9gkW5yzQx9llnHlAu-zhT-z4A&s",
"alt": ""
},
"link": {
"href": "https://www.google.com/url?q=https://www.britannica.com/animal/cat&sa=U&ved=2ahUKEwiQ4_ym2db9AhWaU2wGHVtZD2sQr4kDegQIExAC&usg=AOvVaw1SvlVb_lV7ZpZ5ePxNCCmg",
"title": "Cat | Breeds & Facts |... www.britannica.com",
"domain": "Cat | Breeds & Facts |... www.britannica.com"
}
}
],
"total": null,
"knowledge_graph": "",
"related_questions": [],
"ts": 2.8050615787506104,
"device_type": null
}
Again, I am using the same formula to render images here.
=Arrayformula(image(C7:C20))
Endless possibilities
The Google Apps Script platform is a very powerful scripting language and automation tool. I only scratched the surface of what you can do with it. You can build sophisticated solutions that are easy to use for Google Sheets users and seamlessly integrate with other applications within and outside the Google Workspace ecosystem.
Reference Links
Openai Documentation - Image Generation
Introduction to JSONPath expressions by Stefan Goessner
JSONPath Plus open-source repository