如何在谷歌应用程序脚本中更改导入excel文件ID而不是excel文件名?

093gszye  于 2023-01-18  发布在  其他
关注(0)|答案(1)|浏览(163)

下面的代码对我来说运行得很完美,但我想更改为通过ID而不是Excel文件名导入Excel文件。2我是新手,所以不知道如何编码。3谢谢。
下面是我的代码:

function main() {
  importData();

function importData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //Clear all content
["order"].forEach(function (s) {
var first = ss.getSheetByName(s);
first.clear();
})
  var xlsxName = "0 - all order - business.xlsx"; //Change source file name accordingly
  var convertID = convert(xlsxName).toString();
  var xLSX = SpreadsheetApp.openById(convertID).getSheetByName("order");
  var ss = SpreadsheetApp.openById("1ROxxxxxxxxxxxxxxxxxx").getSheetByName("order"); //Change output sheet ID
  var lastColumn = xLSX.getLastColumn();
  var lastRow = xLSX.getLastRow();
  ss.getRange(1, 1, lastRow, lastColumn).setValues(xLSX.getDataRange().getValues()); //Sets values from converted xlsx data to output sheet
  DriveApp.getFileById(convertID).setTrashed(true); //deletes temporary file
}

function convert(excelFileName) {
  var files = DriveApp.getFilesByName(excelFileName);
  var excelFile = (files.hasNext()) ? files.next() : null;
  var blob = excelFile.getBlob();
  var config = {
    title: "[Converted File] " + excelFile.getName(), //sets the title of the converted file
    parents: [{ id: excelFile.getParents().next().getId() }],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  var spreadsheet = Drive.Files.insert(config, blob);
  return (spreadsheet.id); //Returns the ID of the converted file
}

}

我希望得到一个修改代码,导入Excel文件的ID,而不是Excel文件名在谷歌应用程序脚本。

y1aodyip

y1aodyip1#

我相信你的目标如下。

  • 您希望通过修改脚本使用XLSX文件的文件ID而不是文件名。

在这种情况下,下面的修改怎么样?

发件人:

var xlsxName = "0 - all order - business.xlsx"; //Change source file name accordingly
var convertID = convert(xlsxName).toString();

收件人:

var xlsxFileId = "###"; // Please set your XLSX file ID.
var convertID = convert(xlsxFileId);

还有

发件人:

function convert(excelFileName) {
  var files = DriveApp.getFilesByName(excelFileName);
  var excelFile = (files.hasNext()) ? files.next() : null;

收件人:

function convert(excelFileId) {
  var excelFile = DriveApp.getFileById(excelFileId);

注:

  • 当上述修改反映在脚本中时,它将变为如下所示。
function main() {
  importData();

  function importData() {
    var ss = SpreadsheetApp.getActiveSpreadsheet(); //Clear all content
    ["order"].forEach(function (s) {
      var first = ss.getSheetByName(s);
      first.clear();
    })

    var xlsxFileId = "###"; // Please set your XLSX file ID.
    var convertID = convert(xlsxFileId);

    var xLSX = SpreadsheetApp.openById(convertID).getSheetByName("order");
    var ss = SpreadsheetApp.openById("1ROxxxxxxxxxxxxxxxxxx").getSheetByName("order");
    var lastColumn = xLSX.getLastColumn();
    var lastRow = xLSX.getLastRow();
    ss.getRange(1, 1, lastRow, lastColumn).setValues(xLSX.getDataRange().getValues());
    DriveApp.getFileById(convertID).setTrashed(true);
  }

  function convert(excelFileId) {
    var excelFile = DriveApp.getFileById(excelFileId);
    var blob = excelFile.getBlob();
    var config = {
      title: "[Converted File] " + excelFile.getName(),
      parents: [{ id: excelFile.getParents().next().getId() }],
      mimeType: MimeType.GOOGLE_SHEETS
    };
    var spreadsheet = Drive.Files.insert(config, blob);
    return spreadsheet.id;
  }
}

相关问题