SyncWith

Google Sheets AddonCrypto

Cell references with Binance

Alex Black

Alex Black

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
  1. 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.
  1. Write out the list of values in your sheet, ideally in a column
  1. Setup a new connection in the SyncWith addon.
  1. 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.
  1. We'll create a sheet called MyCoins, and put the coin ticker symbols in column A, eg BTC, BCH, ETH
  1. We'll then create a new connection using the addon
  1. We'll use the URL endpoint https://api.binance.com/api/v3/ticker/price
  1. Then add a parameter named symbol
  1. 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 initial set of coins who's prices we want to lookup
The connection setup in the Addon, referencing the coin symbols from the sheet
The connection setup in the Addon, referencing the coin symbols from the sheet
A preview of the data pulled down from Binance
A preview of the data pulled down from Binance
The final result
The final result