在谷歌电子表格中导入在线Excel文件

Question

我已经尝试了好几次，只是从这个在线excel文件中获取数据;但到目前为止我还没有做到。我基于此链接，将 excel 文件转换为电子表格 https://gist.github.com/azadisaryev/ab57e95096203edc2741 但不允许在此工作区访问 DriveApp。

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);
}  

错误 ： "不支持从应用程序/vnd.ms-excel 转换为文本/csv。 "该网站不提供 CSV 文件。

Answer 1

那么函数是：

/**
 * 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());
}

重要的一点是将正确的参数传递给函数。请注意：

       @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

对于第一个必需参数Blob，您可以使用如下内容：

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
}

对于第二个必需参数，它只是上传驱动器上文件名的string。其余的不是必需的参数。您可以暂时跳过它们。

要使代码正常工作，您必须在脚本和开发者控制台中的高级 Google 服务中启用云端硬盘 API（有关详细信息，请参阅如何启用高级服务）。