How to change the import excel file ID instead of excel file name in google app script?-CodePudding

the following code run perfectly for me but I would like to change to import excel file by ID instead of excel file name. I am newbie so not sure how to code. Thanks.

Here is my code :

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
}

}

I hope to get a modify code that import excel file by ID instead of excel file name in google app script.

CodePudding user response：

I believe your goal is as follows.

You want to use the file ID of the XLSX file instead of the filename by modifying your script.

In this case, how about the following modification?

From:

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

To:

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

And,

From:

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

To:

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

Note:

When the above modification is reflected in your script, it becomes as follows.

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