Google Sheets中IMPORTJSON函数偶尔无法获取数据的排查求助

Hey there, let's break down the two problems you're facing with your crypto portfolio spreadsheet—let's get to the bottom of this!

Issue 1: Original IMPORTJSON Returns "Error getting data" (Works Manually) #

This is super common with Google Apps Script + external APIs, and here are the most likely culprits:

• Google Script Quota Limits: Google enforces daily execution quotas and rate limits for Apps Script functions. If your spreadsheet has multiple cells calling IMPORTJSON, the cumulative requests might hit these limits during automatic execution. When you run it manually, you're making a single request that flies under the radar.
• CoinMarketCap API Rate Limiting: Free tiers of the CoinMarketCap API have strict rate limits (like 10-30 requests per minute). If your sheet is refreshing frequently, you're probably getting throttled. Manual runs have longer gaps between requests, so they don't trigger this.
• Missing Caching: Without caching, every cell refresh triggers a new API call. This not only wastes quota but also slows down your sheet and increases the chance of errors.

Fixes to Try: #

• Add caching to your IMPORTJSON function using Google's CacheService to store API responses for 5-15 minutes (crypto prices don't change that fast anyway).
• Check your CoinMarketCap API dashboard to see if you've hit your daily request limit. Consider upgrading to a paid tier if you need more calls.
• Optimize your sheet: Instead of calling IMPORTJSON for every single crypto, use a single call to fetch multiple assets at once, then parse the data across cells.

Issue 2: New IMPORTJSON Throws "TypeError: Cannot read property 'quotes' from null" #

This error means your code is trying to access the quotes property of a value that's null—so somewhere, the expected data isn't coming through as expected. Here's why:

• API Response Structure Changed: CoinMarketCap might have updated their API's JSON output, and the new IMPORTJSON script isn't handling the new structure. The path to quotes might now be different, or the node might be missing entirely for some assets.
• Invalid Request Parameters: If you're passing a wrong crypto ID/symbol in your API call, CoinMarketCap will return a null or empty response. The script doesn't check for this before trying to access quotes.
• Poor Error Handling in the Script: The alternative IMPORTJSON code you found doesn't include checks for null values. It assumes every API call returns a valid object with a quotes property, which isn't always the case.

Fixes to Try: #

• First, test your CoinMarketCap API URL directly in a browser or tool like Postman to see what the actual response looks like. Check if quotes exists in the output, and note the correct path.
• Add null checks to the IMPORTJSON code before accessing quotes. For example:

  // Before accessing data.quotes, make sure data isn't null
  if (!data || !data.quotes) {
    return "Invalid or missing data from API";
  }
  // Proceed with processing quotes
  

• Double-check all your API parameters: Ensure crypto symbols/IDs are correct, your API key is included, and you're using the right endpoint (e.g., /v1/cryptocurrency/quotes/latest vs. an older version).

内容的提问来源于stack exchange，提问作者John Slegers