Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Import xlsx file from folder

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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.

  1. When the file is XLSX data, convert XLSX data to Google Spreadsheet. When the file is Spreadsheet, your script is used.
  2. Retrieve the values from converted Spreadsheet.
  3. Put the values to the sheet you expect.
  4. 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, id;
    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) {
      var sheetsRange = sheet.getDataRange();
      var sheetsValues = sheetsRange.getValues().filter(r => r[1]).filter(e => e);
      arr = arr.concat(sheetsValues.slice(1));
    }
    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:

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading