Error when filtering dates in .CSV file for Google Sheets through Google App Script

Question

Until last week the script was working perfectly, but something happened that when trying to import the .CSV data it returns totally blank, I couldn't find where the fault is.

Note: The script filters date data to collect future data, current and past data is only collected from yesterday.

function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Master');
  var rg=sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns());
  rg.clear({contentsOnly:true});
  var url="https://projects.fivethirtyeight.com/soccer-api/club/spi_matches_latest.csv";
  var data=UrlFetchApp.fetch(url).getContentText();
  var csvA=Utilities.parseCsv(data);
  var now=new Date(new Date().getFullYear(),new Date().getMonth(),new Date().getDate());
  var daysAgo=new Date(now.getFullYear(),now.getMonth()+1,now.getDate()-1);
  var daysLater=new Date(now.getFullYear(),now.getMonth()+1,now.getDate()+15);
  var values=csvA.filter(function(e, i) {
    if(i>0) {
      var tA=e[0].split('-');
      var temp=new Date(tA[0],tA[1],tA[2]);
      return temp.getTime() >= daysAgo.getTime() && temp.getTime()<=daysLater.getTime();
    }else{
      return true;
    }
  });
}

Did you notice that your var daysAgo=new Date(now.getFullYear(),now.getMonth()+1,now.getDate()-1); is in the future not the past — Cooper
In fact daysAgo is greater than daysLater so your filter will never work — Cooper

Cooper Cooper · Accepted Answer · 2020-06-15T20:14:26

It works now:

The big problem was the index for e in this line var tA=e[1].split('-');//modified

and subtracting 1 from getMonth() in this line: var temp=new Date(tA[0],tA[1]-1,tA[2]).valueOf();//modified

function myFunction() {
  //removed unnecessary junk up here
  var url="https://projects.fivethirtyeight.com/soccer-api/club/spi_matches_latest.csv";
  var data=UrlFetchApp.fetch(url).getContentText();
  Logger.log(JSON.stringify(data));
  var csvA=Utilities.parseCsv(data);
  var dt=new Date();
  var now=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate());
  var daysAgo=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()-1).valueOf();//modified
  var daysLater=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()+15).valueOf();//modified
  var values=csvA.filter(function(e, i) {
    if(i>0) {
      var tA=e[1].split('-');//modified
      var temp=new Date(tA[0],tA[1]-1,tA[2]).valueOf();//modified
      return temp>=daysAgo && temp<=daysLater;//modified
    }else{
      return true;
    }
  });
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(JSON.stringify(values)).setWidth(1200).setHeight(500), "Display Results");
}
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(JSON.stringify(values)), "Display Results");
}

Here's what it returned:

[["season","date","league_id","league","team1","team2","spi1","spi2","prob1","prob2","probtie","proj_score1","proj_score2","importance1","importance2","score1","score2","xg1","xg2","nsxg1","nsxg2","adj_score1","adj_score2"],["2020","2020-06-14","1874","Swedish Allsvenskan","IK Sirius","Djurgardens IF","22.74","48.83","0.1765","0.5836","0.2399","0.9","1.84","","","0","2","","","","","",""],["2019","2020-06-14","1846","German 2. Bundesliga","Karlsruher SC","VfB Stuttgart","29.35","49.81","0.2142","0.5365","0.2493","1.1","1.87","85.0","99.8","2","1","1.09","1.98","1.12","1.83","2.1","1.05"],["2019","2020-06-14","1846","German 2. Bundesliga","SV Darmstadt 98","Hannover 96","33.43","38.7","0.3588","0.3577","0.2835","1.36","1.36","0.1","0.0","3","2","0.57","1.7","0.67","1.65","3.15","2.1"],["2019","2020-06-14","1846","German 2. Bundesliga","FC St. Pauli","Erzgebirge Aue","29.9","26.51","0.4543","0.2549","0.2908","1.46","1.02","13.2","0.0","2","1","1.88","2.28","1.24","1.82","2.1","1.05"],["2019","2020-06-14","1869","Spanish Primera Division","Athletic Bilbao","Atletico Madrid","71.79","83.78","0.2302","0.4752","0.2946","0.82","1.31","4.4","86.5","1","1","0.41","1.26","0.75","0.76","1.05","1.05"],["2019","2020-06-14","1837","Danish SAS-Ligaen","AaB","FC Midtjylland","45.27","61.72","0.2291","0.5102","0.2607","0.99","1.61","0.0","37.4","0","2","","","","","",""],["2020","2020-06-14","1874","Swedish Allsvenskan","IFK Goteborg","IF Elfsborg","35.71","29.51","0.5019","0.2418","0.2563","1.69","1.1","7.9","11.1","0","1","","","","","",""],["2020","2020-06-14","1874","Swedish Allsvenskan","IFK Norrkoping","Kalmar FF","47.52","18.17","0.7781","0.0578","0.1641","2.26","0.45","","","2","1","","","","","",""],["2019","2020-06-14","1845","German Bundesliga","Mainz","FC

Actually I get a lot more but it's too much to post.

Error when filtering dates in .CSV file for Google Sheets through Google App Script

1 Answers