Import xlsx file from folder-CodePudding

I have script that perfectly work on google sheet and import files from folder to one file. But when i want import files xlsx-format from folder to one file it gives: "Exception: Service Spreadsheets failed while accessing document with id 1mvo7w1k320cqYqf5sK2YQrDqCRIt59iM"

What the problem whith script

function Import_Kredo() {
  var arr = [];
  var files = DriveApp.getFolderById("13kymSiqX0-L6a9ev4It1_BEjH-xM8rnc").getFiles();

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var copySheet = ss.getSheetByName("Table 1")
  copySheet.getRange('A2:G').clear();

  while (files.hasNext()) {
    var file = files.next();

    var ss1 = SpreadsheetApp.openById(file.getId())
    var sheets = ss1.getSheetByName("Table 1")
    var sheetsRange = sheets.getDataRange()
    var sheetsValues = sheetsRange.getValues().filter(r => r[1]).filter(e => e)
    arr = arr.concat(sheetsValues.slice(1))
  }

copySheet.getRange(5, 1, arr.length, arr[0].length).setValues(arr)
}

I tried to inport files from folder to one file, but in geves mistakes

CodePudding user response：

Unfortunately, in the current stage, the values cannot be directly retrieved from the XLSX data using the Spreadsheet service (SpreadsheetApp). In order to retrieve the values from XLSX data using Google Apps Script, in this answer, I would like to propose the following flow.

When the file is XLSX data, convert XLSX data to Google Spreadsheet. When the file is Spreadsheet, your script is used.
Retrieve the values from converted Spreadsheet.
Put the values to the sheet you expect.
Remove the converted Spreadsheet.

When this flow is reflected in your script, it becomes as follows.

Modified script:

Before you use this script, please enable Drive API at Advanced Google services.

function Import_Kredo() {
  var arr = [];
  var files = DriveApp.getFolderById("13kymSiqX0-L6a9ev4It1_BEjH-xM8rnc").getFiles();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Table 1");
  copySheet.getRange('A2:G').clear();
  while (files.hasNext()) {
    var file = files.next();
    var mimeType = file.getMimeType();
    var ss1 = null, id = null;
    if (mimeType == MimeType.MICROSOFT_EXCEL) {
      id = Drive.Files.copy({ mimeType: MimeType.GOOGLE_SHEETS }, file.getId()).id;
      ss1 = SpreadsheetApp.openById(id);
    } else if (mimeType == MimeType.GOOGLE_SHEETS) {
      ss1 = SpreadsheetApp.open(file);
    }
    if (!ss1) continue;
    var sheet = ss1.getSheetByName("Table 1");
    if (!sheet) continue;
    var sheetsRange = sheet.getDataRange();
    var sheetsValues = sheetsRange.getValues().filter(r => r[1]).filter(e => e);
    var v = sheetsValues.slice(1);
    console.log([file.getName(), v])
    arr = arr.concat([[file.getName(), ...Array(v[0].length - 1).fill(null)], ...v]);
    if (id) DriveApp.getFileById(id).setTrashed(true); // or Drive.Files.remove(id);
  }
  copySheet.getRange(5, 1, arr.length, arr[0].length).setValues(arr);
}

When this script is run, when the file is XLSX data, it is converted to Google Spreadsheet. And, when the file is Google Spreadsheet, your script is used.

Reference:

Files: copy