如何将数据从Google驱动器中的xlsx文件复制到Google工作表? [英] How do I copy data from an xlsx file in my google drive to a google sheet?

问题描述

我在Google驱动器上保存了一个Excel文件(.xlsx).我想将数据从那里复制到我已经创建的Google工作表文件的标签中.我有下面的代码运行.但是我不知道究竟如何指定Google表格(和标签)以粘贴excel数据?

I have an excel file (.xlsx) saved on my google drive. I want to copy the data from there into a tab in a google sheet file i have already created. I have the following code that runs. But I don't know exactly how to specify the Google Sheet (and tab) to paste the excel data in?

``

function run() {
  try {

    fileName = fileName || "G:\Shared drives\ExchangeData\DailyVolumes.xlsx";

    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;  
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName().replace(/.xlsx?/, ""),
      key: fileId
    };
    Drive.Files.insert(resource, blob, {
      convert: true
    });

  } catch (f) {
    Logger.log(f.toString());
  }

}

推荐答案

• 您要将".xlsx"文件的值复制到现有的Google电子表格中.
• 您的Google云端硬盘中有".xlsx"文件.
• 您想使用Google Apps脚本实现这一目标.

如果我的理解是正确的，那么这个答案如何?请认为这只是几个可能的答案之一.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

• 为了从".xlsx"文件转换后的Google Spreadsheet中检索值，它使用从Drive.Files.insert()返回的文件ID.
• 在您的脚本中，需要声明目标Google电子表格，该电子表格将复制".xlsx"文件中的值并使用它.
• In order to retrieve the values from the Google Spreadsheet converted from the ".xlsx" file, it uses the file ID returned from Drive.Files.insert().
• In your script, it is required to declare the destination Google Spreadsheet which copies the values from the ".xlsx" file and to use it.

修改脚本后，请进行以下修改.在运行脚本之前，请设置变量destSpreadsheetId和destSheetName.

When your script is modified, please modify as follows. Before you run the script, please set the variables of destSpreadsheetId and destSheetName.

fileName = fileName || "G:\Shared drives\ExchangeData\DailyVolumes.xlsx";

var excelFile = DriveApp.getFilesByName(fileName).next();
var fileId = excelFile.getId();
var folderId = Drive.Files.get(fileId).parents[0].id;  
var blob = excelFile.getBlob();
var resource = {
  title: excelFile.getName().replace(/.xlsx?/, ""),
  key: fileId
};
Drive.Files.insert(resource, blob, {
  convert: true
});

到:

var destSpreadsheetId = "###";  // Added
var destSheetName = "###";  // Added

fileName = fileName || "G:\Shared drives\ExchangeData\DailyVolumes.xlsx";
var excelFile = DriveApp.getFilesByName(fileName).next();
var fileId = excelFile.getId();
// var folderId = Drive.Files.get(fileId).parents[0].id; // This is not used in your script.
var blob = excelFile.getBlob();
var resource = {title: excelFile.getName().replace(/.xlsx?/, "")};  // Modified
var sourceSpreadsheet = Drive.Files.insert(resource, blob, {convert: true});  // Modified

// Also I added below script.
var sourceSheet = SpreadsheetApp.openById(sourceSpreadsheet.id).getSheets()[0];
var destSheet = SpreadsheetApp.openById(destSpreadsheetId).getSheetByName(destSheetName);
var values = sourceSheet.getDataRange().getValues();
destSheet.getRange(destSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

注意:

• 在您的问题中，我不确定".xlsx"文件是否包含多个工作表以及您要放置值的范围.因此，在此修改中，作为示例，转换后的电子表格中的第一个选项卡的值将复制到目标电子表格中的最后一行.
• 在此经过修改的脚本中，它假定您已经在高级Google服务中启用了Drive API.

Note:

• In your question, I'm not sure whether the ".xlsx" file has several sheets and the range you want to put the values. So in this modification, as a sample, the values of the 1st tab in the converted Spreadsheet are copied to the last row in the destination Spreadsheet.
• In this modified script, it supposes that you have already enabled Drive API at Advanced Google services.
• 文件:插入
• openById(id)
• getSheets()
• getDataRange()
• getValues()
• setValues(values)
• Files: insert
• openById(id)
• getSheets()
• getDataRange()
• getValues()
• setValues(values)

如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

If I misunderstood your question and this was not the direction you want, I apologize.

这篇关于如何将数据从Google驱动器中的xlsx文件复制到Google工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！