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:
1. Install the SyncWith google sheets addon, and launch it within a google sheet
2. 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.
3. Write out the list of values in your sheet, ideally in a column
4. Setup a new report in the SyncWith addon.
5. 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
Can I use values from multiple cells in a single request?
Yes. If you have multiple ranges, SyncWith will combine values on the same row. For example, imagine you wanted to see how your sales rep were doing. To do this, you want to query your sales CRM for sales rep performance for each year.
Your input spreadsheet looks like this:
Your cell references would look like:
{{Reps!A2:A4}}to give the sale rep names: Adam, Adam, Eve
{{Reps!B2:B4}}to give the years: 2022, 2021, 2022
Even though there are 6 cells here, SyncWith would only make 3 requests:
- Request 1 would use Adam and 2022 (because they are both on row 2)
- Request 2 would use Adam and 2021 (because they are both on row 3)
- Request 3 would use Eve and 2022 (because they are both on row 4)
A full example
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 report 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.
