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 access to the DriveApp is not allowed on this workflow.

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

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 of the file name on the uploading drive. The rest are not required parameters. You can skip them for the time being.