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 report 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

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.



The initial set of coins who's prices we want to lookup



The report setup in the Addon, referencing the coin symbols from the sheet



A preview of the data pulled down from Binance



The final result

Updated on: 04/27/2024