Import online excel file in google spreadsheet

0 votes

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.

Nov 26, 2022 in Others by Kithuzzz
• 27,740 points
79 views

1 answer to this question.

0 votes

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.

answered Nov 26, 2022 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
718 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 51,240 points
142 views
0 votes
1 answer

Removing specific rows in an Excel file using Azure Data Factory

Under the 'Source' tab, choose the number ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
554 views
0 votes
1 answer

SharePoint - Excel Online - Edit in Excel and Edit in Browser multiple users

Here is the Microsoft article regarding co-authoring https://support.office.com/en-us/article/Collaborate-on-Excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104?ui=en-US&rs=en-US&ad=US When ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 51,240 points
309 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
523 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
471 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,488 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,991 views
0 votes
1 answer

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 51,240 points
1,205 views
0 votes
1 answer

How to fix corrupted imported online excel in google sheet?

Issue and workaround: In the built-in functions for ...READ MORE

answered Nov 26, 2022 in Others by narikkadan
• 51,240 points
64 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP