UseSQL
  • Introduction
  • Getting Started
    • Source Formats
    • Output Formats
    • Managing Keys
    • Sandbox
    • Saved Queries
  • Client Libraries
    • HTML Templating
    • JavaScript Web Client
  • Tutorials
    • Integrate With Your Static Site
    • Share Data In Slack
    • Show external query results in a Google Sheet
    • Query across a Google Sheet and a CSV file
    • Securing Your Key
    • Querying Airtable
    • Integrate With ProcFu
    • Integrate with a private Google Sheet
    • Updating Google Sheets
    • Retrieving data from tables on a web page
    • Displaying reports in Google Sheets from MySQL
    • Querying StackBy Tables
  • Resources
    • Demo Sandbox
    • OpenAPI Docs
    • Redoc API Docs
    • Roadmap
    • Changelog
Powered by GitBook
On this page
  • Introduction
  • Step 1: Creating The Query
  • Step 2: Embedding Query Results
  • Step 3: Create Google Finance Formula

Was this helpful?

  1. Tutorials

Show external query results in a Google Sheet

PreviousShare Data In SlackNextQuery across a Google Sheet and a CSV file

Last updated 4 years ago

Was this helpful?

Introduction

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.

Step 1: Creating The Query

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.

Step 2: Embedding Query Results

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")

Step 3: Create Google Finance Formula

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.

LogoUseSQL API - Swagger UI