0

A script that I've been using for months now suddenly stopped working because I get a 403 when trying to reach the Coinmarketcap API from Google Apps Script. There is nothing wrong with my API-key because I tried the same request in a Python script from my local machine and that works.

This is my Google Apps Script:

function coin_price() {
    const myGoogleSheetName =
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tokens')
    
    // Call CoinMarketCap and let them know who you are.
    const coinMarketCapAPICall = {
      method: 'GET',
      uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
      qs: {
        start: '1',
        limit: '5000',
        convert: 'USD',
      },
      headers: { 'X-CMC_PRO_API_KEY': '##########' },
      json: true,
      gzip: true,
      muteHttpExceptions: true
    }
  
    // Put the coin symbols that you want to follow here.
    const myCoinSymbols = ['BTC', 'ETH']
    
    // Let's itereate 
    for (let i = 0; i < myCoinSymbols.length; i++) {
      const coinSymbol = myCoinSymbols[i]
      console.log(coinSymbol)
      const coinMarketCapUrl = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${coinSymbol}`
      const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall)
      const txt = result.getContentText()
      console.warn(txt)
      const d = JSON.parse(txt)
      const row = i + 2
      // Puts a column of at symbols into the sheet at A2.
      myGoogleSheetName.getRange(row, 1).setValue(coinSymbol)
      // Puts a column of current market price's in dollars into the sheet at B3.
      myGoogleSheetName
        .getRange(row, 2)
        .setValue(d.data[coinSymbol].quote.USD.price)
  
    }
  }

This is the error I get:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>ERROR: The request could not be satisfied</TITLE>
</HEAD><BODY>
<H1>403 ERROR</H1>
<H2>The request could not be satisfied.</H2>
<HR noshade size="1px">
Request blocked.
We can't connect to the server for this app or website at this time. There might be too much traffic or a configuration error. Try again later, or contact the app or website owner.
<BR clear="all">
If you provide content to customers through CloudFront, you can find steps to troubleshoot and help prevent this error by reviewing the CloudFront documentation.
<BR clear="all">
<HR noshade size="1px">
<PRE>
Generated by cloudfront (CloudFront)
Request ID: ##########
</PRE>
<ADDRESS>
</ADDRESS>
</BODY></HTML>

Is Coinmarketcap blocking Google IP's?

Stanko
  • 4,275
  • 3
  • 23
  • 51
  • 1
    Unfortunately, at UrlFetchApp of Google Apps Script, there are no properties of `uri`, `qs`, `json` and `gzip`. So is this related to your current issue? [Ref](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetchurl,-params) I'm worried about this. – Tanaike Dec 11 '21 at 11:52
  • This exact code used to work but I'll try without these properties. – Stanko Dec 11 '21 at 13:41
  • 1
    The script is working again. So it must of been a temporary block by the Coinmarketcap API. Still not really sure why it has occured so I'll leave the question open. – Stanko Dec 11 '21 at 14:40

1 Answers1

2

This is a common issue with UrlFetchApp because all requests are coming from the same IP pool.

You can sometimes get around this though using exponential backoff and binomial probability theory (aka try a bunch of times until it works)

I managed to get this to work with the Binance API this way (full github repo here) but the idea is as such:

function exponentialBackoff(url, params) {
  for (let count = 0; count < 50; count++) { // choose your amount
    try {
      Utilities.sleep((count * count) + (Math.random() * 1000))
      const response = UrlFetchApp.fetch(`${endpoint}${query}`, params)
    }    
    catch(e) {
      console.info(e)
      continue
    }
    if (response.getResponseCode() === 200) {
      return response
    }
  }
}
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54