Import online excel file in google spreadsheet?

Question

I've tried several times, to simply get the data out of this online excel file ; but so far I've not been able to. I've based myself on this link about converting excel files to spreadsheets https://gist.github.com/azadisaryev/ab57e95096203edc2741 but acces to the DriveApp is not allowed on this workfloor.

function importDayAhead(){

  var params = {
    "method":"POST",
    "contentType": 'application/vnd.ms-excel',
  };
var response = UrlFetchApp.fetch("http://www.belpex.be/wp-content/uploads/marketdata/dam/public/results_dam_be/Belpex_Daily_Market_Results.xls", params).getBlob();
var responseCSV = response.getAs(MimeType.CSV);
  Logger.log(responseCSV);
var data = Utilities.parseCsv(responseCSV, ",");
  Logger.log(data);
var ss = SpreadsheetApp.getActive() ; 
var sh = ss.getSheetByName("Sheet8").getRange(1, 1, data.length, data[0].length).setValues(data);
}  

Error : " Converting from application/vnd.ms-excel to text/csv is not supported. " the website does not offer a csv file.

Answer 1

Well the function is:

/**
 * Convert Excel file to Sheets
 * @param {Blob} excelFile The Excel file blob data; Required
 * @param {String} filename File name on uploading drive; Required
 * @param {Array} arrParents Array of folder ids to put converted file in; Optional, will default to Drive root folder
 * @return {Spreadsheet} Converted Google Spreadsheet instance
 **/
function convertExcel2Sheets(excelFile, filename, arrParents) {

  var parents  = arrParents || []; // check if optional arrParents argument was provided, default to empty array if not
  if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not

  // Parameters for Drive API Simple Upload request (see https://developers.google.com/drive/web/manage-uploads#simple)
  var uploadParams = {
    method:'post',
    contentType: 'application/vnd.ms-excel', // works for both .xls and .xlsx files
    contentLength: excelFile.getBytes().length,
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    payload: excelFile.getBytes()
  };

  // Upload file to Drive root folder and convert to Sheets
  var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);

  // Parse upload&convert response data (need this to be able to get id of converted sheet)
  var fileDataResponse = JSON.parse(uploadResponse.getContentText());

  // Create payload (body) data for updating converted file's name and parent folder(s)
  var payloadData = {
    title: filename, 
    parents: []
  };
  if ( parents.length ) { // Add provided parent folder(s) id(s) to payloadData, if any
    for ( var i=0; i<parents.length; i++ ) {
      try {
        var folder = DriveApp.getFolderById(parents[i]); // check that this folder id exists in drive and user can write to it
        payloadData.parents.push({id: parents[i]});
      }
      catch(e){} // fail silently if no such folder id exists in Drive
    }
  }
  // Parameters for Drive API File Update request (see https://developers.google.com/drive/v2/reference/files/update)
  var updateParams = {
    method:'put',
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()},
    contentType: 'application/json',
    payload: JSON.stringify(payloadData)
  };

  // Update metadata (filename and parent folder(s)) of converted sheet
  UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);

  return SpreadsheetApp.openById(fileDataResponse.id);
}

/**
 * Sample use of convertExcel2Sheets() for testing
 **/
 function testConvertExcel2Sheets() {
  var xlsId = "0B9**************OFE"; // ID of Excel file to convert
  var xlsFile = DriveApp.getFileById(xlsId); // File instance of Excel file
  var xlsBlob = xlsFile.getBlob(); // Blob source of Excel file for conversion
  var xlsFilename = xlsFile.getName(); // File name to give to converted file; defaults to same as source file
  var destFolders = []; // array of IDs of Drive folders to put converted file in; empty array = root folder
  var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);
  Logger.log(ss.getId());
}

The important point is to pass the proper parameters to the function. Please note:

       @param {Blob} excelFile The Excel file blob data; Required
     * @param {String} filename File name on uploading drive; Required
     * @param {Array} arrParents Array of folder ids to put converted file in; Optional, will default to Drive root folder
     * @return {Spreadsheet} Converted Google Spreadsheet instance

For the first Required parameter Blob, you can use something like:

function Blob() {
  var doc = DocumentApp.openByUrl("https://docs.google.com/document/d/[My document ID]/edit");
  var docContentBlob = doc.getBlob();

 //You can also do something like

  var copyofDoc = DocsList.getFileById(doc.getId()).makeCopy('new copy of '+doc.getName());// this will create a copy (google doc format) // Optional
  var newDocfromBlob = DocsList.createFile(docContentBlob);// this will create a pdf version of your doc //Optional
}

For the second required parameter, it is simply a string the file name on the uploading drive. The rest are not required parameters.You can skip them for the time being.

For the code to work, you must enable Drive API in Advanced Google Services in your script and in Developers Console (see How to Enable Advanced Services for details).