如何在Google Sheets中通过URL导入xlsx文件(更新)？ [英] How to import XLSX file (which gets updated) via URL in Google Sheets?

问题描述

我想将一个xlsx文件导入到Google Sheets中。我试着在网上找它，但什么都没有用。 例如，我想导入此URL：http://www.sportsbookreviewsonline.com/scoresoddsarchives/nba/nba%20odds%202015-16.xlsx

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

此URL会更新，因此我希望Google Sheets中的文件也会自动更新。

谢谢！

推荐答案

您应该尝试使用DriveApp，而不是理解xlsx格式

作为一种简单的方法，您可以将您的xlsx文件导入到您的Drive中，然后通过Google Apps脚本中的Drive API 2每X分钟更新一次，作为高级服务，我的方法如下：

code.gs

function updateXLSX() {
  // This URL should always be the XLSX
  var xlsx = UrlFetchApp.fetch("http://www.sportsbookreviewsonline.com/scoresoddsarchives/nba/nba%20odds%202015-16.xlsx");

  // If file doesn't exist create a new one
  var file = DriveApp.getFilesByName("XLSX test NBA");
  if(!file.hasNext()) {
    var newFile = DriveApp.createFile(xlsx.getBlob()).setName("XLSX test NBA");

    return;
  }

  // Otherwise override the content
  var driveFile = file.next();
  Drive.Files.update({}, driveFile.getId(), xlsx.getBlob(), {
    mimeType: 'application/vnd.google-apps.spreadsheet'
  });
}

此代码将下载xlsx文件，然后更新该文件。如果未创建该文件，您以前可以创建名为"；xlsx test NBA"；的Google Sheets并删除行5-11，它将覆盖xlsx格式并将其视为Google Sheets格式。

基于时间的触发器

我发现这种方法更有效，但不是很理想，因为它没有在每个单元格中使用循环来理解Microsoft Excel Open XML格式的电子表格文件。这是一个ZIP压缩的基于XML的电子表格文件，所以我个人会避免使用它。

引用

• File
• Import data to Sheets
• Drive API via Google Apps Script
• Import to Google Docs types

这篇关于如何在Google Sheets中通过URL导入xlsx文件(更新)？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！