How to open excel files, which are located in a google drive folder with google script?

Question

I need to open excel files in given google drive folder and read its data. For that, I used the following code.

  var folders = DriveApp.getFoldersByName("Test Folder");
  var foldersnext = folders.next();
  var files = foldersnext.getFiles(); // get all files from folder

  while(files.hasNext()) {

    var sheets = SpreadsheetApp.openByUrl(files.next().getUrl());   // Line A     

  }

But it gives an error in "Line A" like this;

Document 1LDVkBTnkcY32ni9WoGDn6xHonPOX87ZV is missing (perhaps it was deleted, or you don't have read access?)

But when Log the IDs of the files using,

var selectedFile = files.next();
Logger.log(selectedFile.getId()); 

It gives me the expecting result. So, I think the error is in converting the file to excel file. Please give me a solution to open multiple excel files in a given folder and read its data...

This project has the following scopes

• https://www.googleapis.com/auth/drive.readonly
• https://www.googleapis.com/auth/spreadsheets

Answer 1

• You want to retrieve values from Excel file on your Google Drive.
• Excel files are put in a folder of "Test Folder".

If my understanding is correct, how about this modification?

Modification point:

• In order to retrieve values from Excel file, at first, the Excel file is required to be converted to Google Spreadsheet. After converted it, the values can be retrieved by Spreadsheet Service.
  • It is considered that the reason of the error is due to that Excel file is tried to be opened by Spreadsheet Service.
• In this modified script, I used Drive.Files.copy() for converting from Excel file to Google Spreadsheet.

Before run the script:

When you run the script, please enable Drive API at Advanced Google Services as follows. This modified script used Drive API of Advanced Google Services.

• On script editor
  • Resources -> Advanced Google Services
  • Turn on Drive API v2

Modified script:

Please modify your script as follows.

From:

while(files.hasNext()) {

  var sheets = SpreadsheetApp.openByUrl(files.next().getUrl());   // Line A     

}

To:

while(files.hasNext()) {
  var file = files.next();
  if (file.getMimeType() == MimeType.MICROSOFT_EXCEL || file.getMimeType() == MimeType.MICROSOFT_EXCEL_LEGACY) {
    var resource = {
      title: file.getName(),
      parents: [{id: foldersnext.getId()}],
      mimeType: MimeType.GOOGLE_SHEETS
    };
    var spreadsheet = Drive.Files.copy(resource, file.getId());
    var sheets = SpreadsheetApp.openById(spreadsheet.id);
  }
}

Note:

• If the Excel file is large, an error might occur.
• If the number of Excel files are large, an error might occur. In this case, please tell me.
• In this sample script, the converted Spreadsheet is created to the same folder of Excel file. If you want to delete the file after the values were retrieved, please use Drive.Files.remove(fileId).

References:

• Files: copy
• Files: delete
• Spreadsheet Service

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

Edit:

From your comment, it was found that my understanding was correct. So as a test case, can you test the following flow?

1. Create new folder.
2. Put an Excel file in the created folder.
   • Please copy the folder ID. This folder ID is used at the script.
3. Please confirm whether Drive API is enabled at Advanced Google Services.

4. Run the following sample script. Before run it, please set the folder ID.

   function sample() {
     var folderId = "###"; // Please set the folder ID.
   
     var folder = DriveApp.getFolderById(folderId);
     var files = folder.getFiles();
     while(files.hasNext()) {
       var file = files.next();
       if (file.getMimeType() == MimeType.MICROSOFT_EXCEL || file.getMimeType() == MimeType.MICROSOFT_EXCEL_LEGACY) {
         var resource = {
           title: file.getName(),
           parents: [{id: folderId}],
           mimeType: MimeType.GOOGLE_SHEETS
         };
         var spreadsheet = Drive.Files.copy(resource, file.getId());
         var sheet = SpreadsheetApp.openById(spreadsheet.id).getSheets()[0];
         var value = sheet.getDataRange().getValues();
         Logger.log(value)
       }
     }
   }
   

5. Run the function of sample() at the script editor.
6. After the script is finished, please check the log.
   • If you can see the values of Excel file at the log, it is found that the script works.