I'm currently creating a script that can import xlsx file stored from gdrive folder to spreadsheet, may I know if someone have reference or sample that I can explore I've already do some research on my end but some of them are in CSV process.

CodePudding user response：

Firstly: this not some giveaway service. Without a code sample and specific question we cannot help out.

But i have this laying around to get you started. Enable advanced drive services. The trick is to first convert to sheets and then get the data.

function getExcelData() {
  //The excel file id -> Open with spreadsheetApp and copy the id from url
  const excel = DriveApp.getFileById("excelfileif");
  //ID of the folder where the tempfile can be placed.
  const tempFolder = "tempFolderId"
  const tempFile = Drive.Files.insert(
          {title: "TempFile", parents: [{"id": tempFolder}]},
          excel.getBlob(),
          {convert: true}
        );
  const tempID = tempFile.getId();
  const source = SpreadsheetApp.openById(tempID);
  //The sheetname of the excel where you want the data from
  const sourceSheet = source.getSheetByName("sheetname");
  //The range you want the data from
  const sourceValues = sourceSheet.getRange("A1:G20").getValues();
  
  const target = SpreadsheetApp.getActiveSpreadsheet();
  //The targetsheetname
  const targetSheet = target.getSheetByName("TargetSheetName");
  
  targetSheet.getRange(targetSheet.getLastRow() 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
  
  DriveApp.getFileById(tempID).setTrashed(true);
  
}

CodePudding user response：

You can try this script wherein the excel file is converted into a blob and then saved the blob as another file with a google spreadsheet format:

function convert() {
  var excelFileName = "Test File.xlsx"; //File name of the file to be converted
  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);
  console.log(spreadsheet.id); //Displays the file ID
}

Please take note that you also need to enable the Drive API in the services section of Google Apps Script. Also, the file that I used for this test case was saved in the main directory of my google drive and not placed within any folder. Hence, the output file should also be saved in the main google drive directory as well.

For more information, you may visit this site:

• Automatically convert Excel spreadsheets to Google Sheets using Apps Script