Importing data with multiple API requests
In this article we'll show how to use the SyncWith google sheets add-on to import data from multiple requests to one API using Cell References. This is a great time saver, enabling you to create powerful datasets driven from data in your sheet.
Overview
Here are the main steps you need to do:
- Install the SyncWith google sheets addon, and launch it within a google sheet
- Find an API that you want to use, to import many rows of data from. For example maybe you have a list of SEO keywords you want to find traffic volume for, or a list of stock ticker symbols that you want to get the latest prices for.
- Write out the list of values in your sheet, ideally in a column
- Setup a new connection in the SyncWith addon.
- Where you'd normally enter a single value into the API call (eg a SEO keyword, or stock ticker symbol), instead you're going to reference the list of values in the sheet, using a cell reference, in the format: {{Sheet1!C2:C10}}
Formatting a cell reference to reference a range of values
SyncWith expects your cell reference to be a standard google sheets range, including the sheet name, surrounded by double curly brackets, for example {{Sheet10!A2:A200}}.
- If your sheet name has spaces, then you need to enclose it in single quotes, eg: {{'My sheet name'!A10:A20}}
- SyncWith supports referencing a single value, like {{Sheet1!A1}} or a column or row of values like {{Sheet1!A1:A10}} or {{Sheet1!A2:M2}}
Where can you use a cell reference?
SyncWith permits cell references in most spots for an API connection:
- In the URL
- In a header value
- In a query string parameter value
- In the body
A full example
In this example we're going to import the prices for a list of cryptocurrency coins fromhat is great for pulling information about cryptocurrencies.
In this example we're going to import the prices for a list of cryptocurrency coins from Binance. Binance has a free public API that we can use.
- We'll create a sheet called MyCoins, and put the coin ticker symbols in column A, eg BTC, BCH, ETH
- We'll then create a new connection using the addon
- We'll use the URL endpoint https://api.binance.com/api/v3/ticker/price
- Then add a parameter named symbol
- In the value of symbol, we need to put something like BTCUSDT, but we want to pull from our list, so instead we put {{MyCoins!A2:A7}}USDT, this will cause the addon to look the values from the sheet, and make one request for each value.
