CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsforex
Laura C
Laura C

Reputation: 45

Extracting data from API to Spreadsheet using Google Appscript

I'm trying to build a spreadsheet based around DataDT's excellent API for 1-minute Forex data. It's simple: for a given date & time, I need the Open and Close price from DataDT. For example, in the following cases, Date+time are the input and Open and Close would be the output.

In other words, it would go from this:

Date        Time    Pair    Open    Close
04/03/2019  20:30   USDJPY  
04/03/2019  21:30   USDJPY      
04/03/2019  22:41   USDJPY

to this:

Date        Time    Pair    Open    Close
04/03/2019  20:30   USDJPY  111.478 111.475
04/03/2019  21:30   USDJPY  111.482 111.465 
04/03/2019  22:41   USDJPY  111.456 111.458

(Link to the GSpreadsheet)

I've been trying to fetch the data from the API following Ben Collins' excellent tutorial on connecting api's to GSpreadsheets, but given that the objects in this API have quite a different format, I haven't figured a way to handle them.

This is my Google Appscript code so far:

function callDataDT() {

  // Call the DataDT API
  var response = UrlFetchApp.fetch("http://www.datadt.com/api/data/AUDCAD/1/20190403/20190404?api_token=s3MoVn4GAJRJhKcdNJ6zZugFN2C92SBv");


  var json = response.getContentText();
  var data = JSON.parse(json);

}

function displayFXData() {

  // pick up the search term from the Google Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var datetime = sheet.getRange(2,13).getValue();

  var forexArray = [data]

  Logger.log(forexArray);

}

To resume, I'm trying to understand how to 1) fetch only objects with a date_time equal to the date_time in the spreadsheet, 2) Output the Open and Close values for that object in the spreadsheet, and 3) Iterate for every non-empty line. I apologize in advance if this question comes off as too basic. I'm still a beginner with Javascript but I've been trying for days to try to understand how to solve this, to no avail. I appreciate any tips you might give me.

As a side note, I wonder if it's possible to modify the URL directly so that it only outputs objects with a given date_time, but I don't think it's possible.

Thank you for reading,

David.-

Upvotes: 2

Views: 784

Answers (1)

Cooper
Cooper

Reputation: 64032

This one uses your filter value

function getDataDT1(filter) {
  var sr=3
  var filter=filter||'';//You can test this function by providing a default here like '2019-04-03 20:28:00' 
  var r=UrlFetchApp.fetch("http://www.datadt.com/api/data/AUDCAD/1/20190403/20190404?api_token=s3MoVn4GAJRJhKcdNJ6zZugFN2C92SBv");
  var data=JSON.parse(r.getContentText("UTF-8"));
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.clearContents();
  var pair='USDJPY';
  var a=[['','','Without V8','',''],['Date','Time','Pair','Open','Close']];
  if(filter) {//if the filter is null then no filtering takes place
    var dat=new Date(filter);
    var dtv=new Date(dat.getFullYear(),dat.getMonth(),dat.getDate(),dat.getHours(),dat.getMinutes()).valueOf();
  }
  data.forEach(function(r,i){
    var dt=r.DATE_TIME.split(' ');
    var sd=new Date(r.DATE_TIME);
    var sdv=new Date(sd.getFullYear(),sd.getMonth(),sd.getDate(),sd.getHours(),sd.getMinutes()).valueOf();
    if(sdv==dtv || !filter) {//no filtering if filter is null
      var d=dt[0].split('-');
      var t=dt[1].split(':');
      var ds=Utilities.formatString('%s/%s/%s',d[1],d[2],d[0]);
      var ts=Utilities.formatString('%s:%s',t[0],t[1]);
      a.push([ds,ts,pair,data[i].OPEN.toFixed(3),data[i].CLOSE.toFixed(3)]);
    }
  });
  if(a) {
    sh.getRange(sh.getLastRow()+1,1,a.length,a[0].length).setValues(a);
  }else{
     SpreadsheetApp.getUi().alert('No items found');
  }
}

Upvotes: 2

Related Questions