请求排查Binance API导入Google Sheets失败问题及修正方案

Hey there! Let’s break down why your Binance bookTicker API call might be failing in Google Sheets—since you’ve got working scripts for other exchanges, it’s almost certainly a small tweak that’ll get things running smoothly.

1. JSON Array Parsing Issues #

Binance’s https://api.binance.com/api/v3/ticker/bookTicker returns a JSON array of objects (one entry per trading pair), whereas some other exchanges return a single object or simpler structure. If you’re using Google Sheets’ native IMPORTDATA function directly, it’ll dump the entire array into one cell instead of parsing it into usable rows and columns.

Fix for Native Functions: #

Use a custom IMPORTJSON function built to handle nested arrays. Here’s how to set it up:

• Open your sheet → Go to Extensions > Apps Script
• Replace the default code with this simplified IMPORTJSON script:

function IMPORTJSON(url, query) {
  var response = UrlFetchApp.fetch(url, {headers: {"User-Agent": "Google Sheets"}});
  var data = JSON.parse(response.getContentText());
  
  // Filter specific fields if a query is provided (e.g., "symbol, bidPrice")
  if (query) {
    var keys = query.split(",").map(k => k.trim());
    var result = [keys];
    data.forEach(item => result.push(keys.map(k => item[k] || "")));
    return result;
  }
  
  // Return full dataset if no query is specified
  var headers = Object.keys(data[0]);
  var result = [headers];
  data.forEach(item => result.push(headers.map(h => item[h] || "")));
  return result;
}

• Save the script (name it something like BinanceImportTools)
• Back in your sheet, use the function like this:
  • Get all data: =IMPORTJSON("https://api.binance.com/api/v3/ticker/bookTicker")
  • Get specific fields: =IMPORTJSON("https://api.binance.com/api/v3/ticker/bookTicker", "symbol, bidPrice, askPrice")

2. Missing User-Agent Header #

Binance’s API often blocks requests without a valid User-Agent header (a common anti-scraping measure). If your existing script uses UrlFetchApp without setting headers, this is likely the root cause.

Fix for Custom Apps Scripts: #

Update your UrlFetchApp.fetch call to include the header:

function getBinanceBookTicker() {
  var url = "https://api.binance.com/api/v3/ticker/bookTicker";
  var options = {
    headers: {
      "User-Agent": "Google Sheets Script" // Any valid UA string works
    }
  };
  
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response.getContentText());
    
    // Write data to your sheet
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var headers = Object.keys(data[0]);
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    
    var rows = data.map(item => headers.map(h => item[h] || ""));
    sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
    
    return "Data imported successfully!";
  } catch (e) {
    return "Error: " + e.toString();
  }
}

This tells Binance your request is legitimate and avoids being blocked.

3. Data Volume Overload #

The bookTicker endpoint returns data for over 1000 trading pairs, which can hit Google Sheets’ row limits or cause slowdowns. If you only need specific pairs, filter the data first.

Fix for Filtering Specific Pairs: #

Add a filter to your script to keep only the pairs you care about:

// After parsing the data in getBinanceBookTicker():
var targetPairs = ["BTCUSDT", "ETHUSDT", "BNBUSDT"];
var filteredData = data.filter(item => targetPairs.includes(item.symbol));

// Use filteredData instead of the full data set when writing to the sheet

Quick Debug Trick #

If you’re still stuck, log the raw API response to see exactly what Binance is sending back:

Logger.log(response.getContentText());

Run the script, then check View > Logs—you’ll see error messages like 403 Forbidden or invalid JSON that’ll point you to the exact issue.

内容的提问来源于stack exchange，提问作者TUFAN POYRAZ