Show external query results in a Google Sheet
When working with data it's likely that you will want to not only retrieve data from your Google Sheets, but you may also want to surface data from external locations within your sheets. In this example, we will show you how to do so with an external csv file from Github that lists out the S&P500 so that we can take advantage of Google Sheet's integration with Google Finance to get prices of the stocks in this index.
You can see the completed example above that we will be taking steps to replicate.
The first step is to create the query that we want to submit to the api. For demo purposes here is the query we want for the sheet that retrieves a listing of ticker symbols.
SELECT Symbol
FROM "<https://raw.githubusercontent.com/dylanroy/sandp500-dataset/main/data.csv>"
The easiest way to build the query will be to copy, and paste this query within the OpenAPI documentation linked below.
After providing your API key as a query parameter you will need to set the format to
csv, and submit your query. Following the successful response you can copy the request URL that shows up. It should look like the following request, but with your own API key.<https://usesql.com/sql?query=select%20Executive%2C%20Company%2C%20Since%20from%20%22https%3A%2F%2Fraw.githubusercontent.com%2Fdylanroy%2Fceo-dataset%2Fmain%2Fdata.csv%22%20LIMIT%2010&format=json&key=YOUR_KEY>
Note: You may need to manually url encode the
" to be %22 in this demo if you don't end up using the OpenAPI.The next step will be to use the
IMPORTDATA Google Sheet formula. Below is an example of this usage.=IMPORTDATA("<http://usesql.com/sql?query=SELECT> Symbol FROM %22https://raw.githubusercontent.com/dylanroy/sandp500-dataset/main/data.csv%22&format=csv&key=&key=PM6qT1X9Z1CywydNlxlX")
Now to complete our example the next step is to use another Google Sheet formula. We want to get the prices for the stock symbols listed in column A. So the next step would be pasting the following formula to B2, and then duplicate this formula for each row.
=GOOGLEFINANCE(A2)
At this stage you have successfully surfaced your external data in a Google Sheet. Take a look at the example below to see the end result.