Ez kell az IMPORTJSON-hoz

code.gs

/**
 * @OnlyCurrentDoc
 */

//Global Variables
var currSpreadsheet=SpreadsheetApp.getActiveSpreadsheet();// get current spreadsheet
var shtPortfolioSummary = currSpreadsheet.getSheetByName('Portfolio Summary');
var shtPortfolioHistory = currSpreadsheet.getSheetByName('Portfolio History');
var shtYahooDataRef = currSpreadsheet.getSheetByName('Yahoo Data Ref');
var shtYahooDataRefUSD = currSpreadsheet.getSheetByName('Yahoo Data Ref USD');


// function to move a copy of Today's Portfolio Summary into Portfolio History
// Note: You need to set a trigger to make this function run every 12 am for example
function copyLivePortfolioDataToHistory() {
  
  /* 
     these are the position of the cells where the data can be found.
     if you modify your portfolio summary, the position of the cells would change.
     do update them accordingly.
  */
  var cellRowCash = "2";
  var cellColCash = "B";
  var cellRowPortfolioData = "7";
  var cellColPortfolioCost = "B";
  var cellColPortfolioValue = "D";
  var cellColUnrealizedGainsLosses = "F";
  var cellColRealizedGainsLosses = "H";
  var cellColDividendsCollected = "I";
  var cellColExpectedDividends = "J";
  var cellColPortHistoryStart = "A";
  var cellColPortHistoryEnd = "H";
  var cellColPortHistorySecond = "B";
  
  var rngCash = shtPortfolioSummary.getRange(cellColCash+cellRowCash+":"+cellColCash+cellRowCash).getValues();
  var valCash = rngCash[0][0];
  //var b = a.getValues();
  //var c = b[0][0]
  Logger.log(valCash);
  var rngPortfolioCost = shtPortfolioSummary.getRange(cellColPortfolioCost+cellRowPortfolioData+":"+cellColPortfolioCost+cellRowPortfolioData).getValues();
  var valPortfolioCost = rngPortfolioCost[0][0];
  var rngPortfolioValue = shtPortfolioSummary.getRange(cellColPortfolioValue+cellRowPortfolioData+":"+cellColPortfolioValue+cellRowPortfolioData).getValues();
  var valPortfolioValue = rngPortfolioValue[0][0];
  var rngUnrealizedGainsLosses = shtPortfolioSummary.getRange(cellColUnrealizedGainsLosses+cellRowPortfolioData+":"+cellColUnrealizedGainsLosses+cellRowPortfolioData).getValues();
  var valUnrealizedGainsLosses = rngUnrealizedGainsLosses[0][0];
  var rngRealizedGainsLosses = shtPortfolioSummary.getRange(cellColRealizedGainsLosses+cellRowPortfolioData+":"+cellColRealizedGainsLosses+cellRowPortfolioData).getValues();
  var valRealizedGainsLosses = rngRealizedGainsLosses[0][0];
  var rngDividendsCollected = shtPortfolioSummary.getRange(cellColDividendsCollected+cellRowPortfolioData+":"+cellColDividendsCollected+cellRowPortfolioData).getValues();
  var valDividendsCollected = rngDividendsCollected[0][0];
  var rngExpectedDividends = shtPortfolioSummary.getRange(cellColExpectedDividends+cellRowPortfolioData+":"+cellColExpectedDividends+cellRowPortfolioData).getValues();
  var valExpectedDividends = rngExpectedDividends[0][0];
  
  var today = new Date();
  var arrNewRow = [today,valPortfolioCost,valPortfolioValue,valCash,valUnrealizedGainsLosses,valRealizedGainsLosses,valDividendsCollected,valExpectedDividends];
  //shtPortfolioHistory.getRange("A1:A1").setValue(today); //this is a sample code
  shtPortfolioHistory.appendRow(arrNewRow); //add this new row below the last row in Portfolio History sheet
  
  var lastRowNum = shtPortfolioHistory.getLastRow(); //get the value of the last row in Portfolio History sheet
  
  //here is how we format the date time and other cells in SimpleDateFormat
  var formatDate = [["yyyy MMM dd","$#,###,###.00","$#,###,###.00","$#,###,###.00","$#,###,###.00","$#,###,###.00","$#,###,###.00","$#,###,###.00"]];
  
  var lastRow = shtPortfolioHistory.getRange(cellColPortHistoryStart+lastRowNum+":"+cellColPortHistoryEnd+lastRowNum); //get the last row we just appended
  
  //we will format the row to look better
  lastRow.setNumberFormats(formatDate);
  lastRow.setBackgroundColor("#cfe2f3");
  lastRow.setVerticalAlignment("top"); // “top” or “middle” or “bottom”
  lastRow.setFontFamily("Open Sans");
  lastRow.setFontSize(9);
  lastRow.setBorder(true, true, true, true, true, true, "#cccccc", SpreadsheetApp.BorderStyle.SOLID); // setBorder(top, left, bottom, right, vertical, horizontal, color, SpreadsheetApp.BorderStyle.DASHED/DOTTED/SOLID)
  
  //we will align the date time to the left, and the rest center
  lastRow = shtPortfolioHistory.getRange(cellColPortHistoryStart+lastRowNum+":"+cellColPortHistoryStart+lastRowNum);
  lastRow.setHorizontalAlignment("left"); // “left” or “center” or “right” 
  lastRow = shtPortfolioHistory.getRange(cellColPortHistorySecond+lastRowNum+":"+cellColPortHistoryEnd+lastRowNum);
  lastRow.setHorizontalAlignment("center");
}

function getYahooFinanceData(string) { 
  //var d1 = Utilities.formatDate(new Date(), "GMT", "yyyyMMddHHmmss");
  var csvContent = UrlFetchApp.fetch(string).getContentText();
  var csvData = Utilities.parseCsv(csvContent);
  return csvData;
  
}

function refreshTime() {
  var d = new Date();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ReferenceData").getRange('L1').setValue(Utilities.formatDate(d, Session.getScriptTimeZone(), 'MMMM dd, yyyy hh:mm:ss Z'));
}

function getYahooLastPrice(symbol) {
  var value = UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=l1&p=.csv').getContentText();
  return parseFloat(value);
}

function getYahooDiv(symbol) {
  var value = UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=d&p=.csv').getContentText();
  return parseFloat(value);
}

function getYahooExDivDate(symbol) {
  var value =  UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=q&p=.csv').getContentText(); 
  return value;
}

function getYahooPayDate(symbol) {
  var value =  UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=r1&p=.csv').getContentText(); 
  return value;
}

function getYahoo52WkLow(symbol) {
  var value =  UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=j&p=.csv').getContentText(); 
  return parseFloat(value);
}

function getYahoo52WkHigh(symbol) {
  var value = UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=k&p=.csv').getContentText(); 
  return parseFloat(value);
}

function getYahoo50DMA(symbol) {
  var value = UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=m3&p=.csv').getContentText(); 
  return parseFloat(value);
}

function getYahoo200DMA(symbol) {
  var value =  UrlFetchApp.fetch("https://download.finance.yahoo.com/d/quotes.csv?s=" + symbol + '&f=m4&p=.csv').getContentText(); 
  return parseFloat(value);
}


/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){
  
  try{
    // /rates/EUR
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
    
    var patharray = xpath.split("/");
    //Logger.log(patharray);
    
    for(var i=0;i<patharray.length;i++){
      json = json[patharray[i]];
    }
    
    //Logger.log(typeof(json));
    
    if(typeof(json) === "undefined"){
      return "Node Not Available";
    } else if(typeof(json) === "object"){
      var tempArr = [];
      
      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else if(typeof(json) !== "object") {
      return json;
    }
  }
  catch(err){
      return "Error getting data";  
  }
  
}


// Get your API key for free from this site: https://www.alphavantage.co/support/#api-key
api_key = 'PUT YOUR API KEY HERE'
url = 'https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol='


/**
* Imports JSON from Alpha Vantage into your spreadsheet Ex: getAlphaVantagePrice("AAPL")
* @param symbol Ticker symbol of your stock
* @customfunction
*/
function getAlphaVantagePrice(symbol) {
var response = UrlFetchApp.fetch("https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" + symbol + "&apikey=" + api_key).getContentText();
var data = JSON.parse(response);
var timeSeries = data["Time Series (Daily)"];
var value = "–";
for (var dates in timeSeries) {
value = parseFloat(timeSeries[dates]["4. close"]);
break;
}
return value;
}

function getAlphaVantageOld(symbol) {
  var response = UrlFetchApp.fetch(url + symbol + "&apikey=" + api_key).getContentText();
  var data = JSON.parse(response);
  var timeSeries = data["Time Series (Daily)"];
  var value = 0.0;
  for (var dates in timeSeries) {
    value = timeSeries[dates]["4. close"];
    Logger.log("Within getAlphaVantage(symbol) function:" + value)
    if (value > 0.0) {
      break;
    }
  }
  return parseFloat(value);
}

function getAlphaVantageSlowly(symbol, seconds) {
  // Cannot sleep too long - A custom function call must return within 30 seconds
  // https://developers.google.com/apps-script/guides/sheets/functions
  temp = seconds % 30
  Utilities.sleep(temp * 1000) // function is in milliseconds 
  
  var response = UrlFetchApp.fetch(url + symbol + "&apikey=" + api_key).getContentText();
  var data = JSON.parse(response);
  var timeSeries = data["Time Series (Daily)"];
  var value = 0.0;
  for (var dates in timeSeries) {
    value = timeSeries[dates]["4. close"];
    Logger.log("Within getAlphaVantage(symbol) function:" + value)
    if (value > 0.0) {
      break;
    }
  }
  return parseFloat(value);
}