google-sheetsgoogle-apps-scriptgoogle-drive-api

Extracting data in Excel inside Google Drive using App Script


I've been trying to extract data from Excel file stored in Google Drive. Here's what I've done so far:

function readExcelFromDrive(file_id='1_m2AR8UVOpn9XABPz_WvyPkLzz9OgyPs',debug=true) {  
  let file = DriveApp.getFileById(file_id);
  console.log(`File ${file_id} found.`)
  let blob = file.getBlob();
  let config = {
    title: "[Auto Generated Google Sheets] " + file.getName(),
    parents: [{id: file.getParents().next().getId()}],
    mimeType: MimeType.GOOGLE_SHEETS
  };
  let spreadsheet = DriveApp.createFile(blob).setSharing(DriveApp.Access.ANYONE,DriveApp.Permission.EDIT)
  console.log(`Temp spreadsheet created. Id: '${spreadsheet.getId()}'`)
  let ss = SpreadsheetApp.openById(spreadsheet.getId())
  console.log(`Temp spreadsheet opened.`)
  let data = ss.getActiveSheet().getDataRange().getValues()
  console.log(data)
  }

I get the following error enter image description here

how do i resolve this?

Thanks in advance


Solution

  • Modification points:

    When these points are reflected in your script, how about the following modification?

    Modified script:

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

    function readExcelFromDrive((file_id='1_m2AR8UVOpn9XABPz_WvyPkLzz9OgyPs',debug=true) {
      let file = DriveApp.getFileById(file_id);
      console.log(`File ${file_id} found.`);
      let blob = file.getBlob();
    
      // --- I modified the below script.
      let config = {
        name: "[Auto Generated Google Sheets] " + file.getName(),
        parents: [file.getParents().next().getId()],
        mimeType: MimeType.GOOGLE_SHEETS
      };
      const { id } = Drive.Files.create(config, blob);
      let spreadsheet = DriveApp.getFileById(id).setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.EDIT);
      // ---
    
      console.log(`Temp spreadsheet created. Id: '${spreadsheet.getId()}'`);
      let ss = SpreadsheetApp.openById(spreadsheet.getId());
      console.log(`Temp spreadsheet opened.`);
      let data = ss.getActiveSheet().getDataRange().getValues();
      console.log(data);
    }
    

    Reference: