CODEWITHSUNDEEP
google-sheetsfinnhub
Ziwdigforbugs
Ziwdigforbugs

Reputation: 1214

Error returned by action script when calling an API with UrlFetchApp.fetch

Hello I have coded this simple action script in google sheet :

var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to this script)
var sheet = ss.getSheetByName('Stock Candidates'); //The name of the sheet tab where you are sending the info

var url = "https://finnhub.io/api/v1/index/constituents?symbol=^SP500-50&token=ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg"; //api endpoint as a string 

var response = UrlFetchApp.fetch(url); // get api endpoint
var json = response.getContentText(); // get the response content as text
var constituents = JSON.parse(json); //parse text into json

Logger.log(constituents); //log data to logger

var stats=[]; //create empty array to hold data points

var date = new Date(); //create new date for timestamp

//The number in brackets refers to which instance we are looking at - soonest upcoming call is [0], next after that is [1], etc.
stats.push(date); //timestamp
stats.push(constituents[0]);

//append the stats array to the active sheet 
sheet.appendRow(stats);
}

When running the code I get this error :

Exception: Invalid argument: https://finnhub.io/api/v1/index/constituents?symbol=^SP500-50&token=ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg
IndiceConstituent   @ Code.gs:9

The code fails when calling UrlFetchApp.fetch. When I replace the finnhub url with any other API url it works.

I have also called the exact same url in postman and I can get this response.


{
    "constituents": [
        "META",
        "GOOGL",
        "GOOG",
        "CMCSA",
        "NFLX",
        "TMUS",
        "DIS",
        "VZ",
        "CHTR",
        "ATVI",
        "T",
        "EA",
        "WBD",
        "TTWO",
        "OMC",
        "IPG",
        "PARA",
        "MTCH",
        "FOXA",
        "LYV",
        "NWSA",
        "FOX",
        "NWS",
        "DISH"
    ],
    "symbol": "^SP500-50"
}

Upvotes: 1

Views: 100

Answers (1)

Bryan Monterrosa
Bryan Monterrosa

Reputation: 1470

It appears that Google Apps Script is having trouble recognizing the ^ symbol, it works if you Encode that character to its UTF-8 form %5E:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to this script)
  var sheet = ss.getSheetByName('Stock Candidates'); //The name of the sheet tab where you are sending the info

  var url = "https://finnhub.io/api/v1/index/constituents?symbol=%5ESP500-50&token=ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg"; //api endpoint as a string 
...
}

You can also use encodeURI() to make sure that the special characters are no longer an issue:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet (bound to this script)
  var sheet = ss.getSheetByName('Stock Candidates'); //The name of the sheet tab where you are sending the info

  var url = "https://finnhub.io/api/v1/index/constituents?symbol=^SP500-50&token=ch7rev1r01qhapm5f5r0ch7rev1r01qhapm5f5rg"; //api endpoint as a string 
  url = encodeURI(url);
...
}

enter image description here

Upvotes: 1

Related Questions