XLS URL

cdenny99  4/22/2017 9:52 PM

Does anyone know how to take the XLS url and import it into excel (or google sheets) so it updates automatically?

sangamc  4/23/2017 10:11 AM

In google sheets use importdata:

=?importdata("http://baseballmonster.com/Daily.aspx?exportcsvH=JqpPjKCdbT401BGg/u/PWSV191ehtTGwSpCpdm7Wp3k=")

Then in File > Spreadsheet settings; change the calculation time to "on update"

This will refresh  at worst every hour. And at best whenever the data on BBM changes. To make it refresh even faster or on command. You can use a script that will simply rewrite the formula to the cell you want forcing it to refresh. For parsehub I use the following script and attached it to an image in my google sheets so that it looks like a refresh button.

function getParse() {
  var queryString = Math.random();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var html = ss.getSheetByName('HTML');
  var parsehub = ss.getSheetByName('Prop');
  
  var cellFunction1 = '=IMPORTDATA("https://www.parsehub.com/api/v2/projects/' + html.getRange('B20').getValue() + '/last_ready_run/data?api_key='+ html.getRange('B21').getValue() + '")';
  var cellFunction2 = '=IMPORTDATA("https://www.parsehub.com/api/v2/projects/' + html.getRange('B20').getValue() + '/last_ready_run/data?api_key='+ html.getRange('B21').getValue() + '&format=csv")';
  parsehub.getRange('i32').setValue(cellFunction1);
  parsehub.getRange('i32').setValue(cellFunction2);

}

sangamc  4/23/2017 10:15 AM

In excel when setting up the get data function, Data tab > connections > conection properties.

You can set the refresh schedule to anything you want

cdenny99  4/23/2017 3:20 PM

Are these instructions for the csv link, or the xls link?

sangamc  4/23/2017 6:24 PM

For the csv link only. I do not think you can auto-download an xls into google sheets or Excel from the web.

cdenny99  4/23/2017 6:41 PM

Ah....bummer.   Well thanks for the help anyway bro!