CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Petrus
Petrus

Reputation: 321

What is the most efficient way to convert a .XLS to Google Sheets in App Script?

I download a .XLS file automatically to a Google drive weekly. I would like to automatically convert the latest downloaded .XLS file every week to Google sheets format.

Thus go to specific Google drive folder. Find the latest or unconverted .XLS file. Convert to Google sheets format and save in the same folder.

Upvotes: 2

Views: 2630

Answers (3)

Oniy16
Oniy16

Reputation: 1

I thought this would help you to convert for single/specific file by ID from MS Excel file to GS and return the new sheet Id converted to Google sheet.

function testConvertExcelToGS(fileId = '*your file id*') {
  
  var file = DriveApp.getFileById(fileId);
  Logger.log('file id %s',file.getName());
  let blob = file.getBlob();
  Logger.log('bolb file: %s:',blob);
  let config = {
    title: file.getName(),
    parents: [{id:'*The folder id you want to copy/create GS version of MS Excel*'}],
    mimeType: MimeType.GOOGLE_SHEETS,
    convert: true
  }; 
  Logger.log('config title:  %s', config.title);
  let spreadsheet = Drive.Files.insert(config, blob);
  Logger.log('The new spread sheet id:  %s',spreadsheet.id);
  return spreadsheet.id;
}

Upvotes: 0

Tanaike
Tanaike

Reputation: 201683

  • You want to convert one .xls file to Google Spreadsheet.
    • The .xls file has 60 kBytes in the file size.
    • The .xls file is put in the specific folder.
  • You want to put the converted Google Spreadsheet in the same folder with the .xls file.
  • You want a simple script for this situation.

If my understanding is correct, how about this sample script? In this sample script, the .xls file is converted by files.copy method of Drive API v2.

When you use this script, please enable Drive API at Advanced Google Services.

Sample script:

var folderId = "###"; // Please set the folder ID including the .xls file.

var files = DriveApp.getFolderById(folderId).getFilesByType(MimeType.MICROSOFT_EXCEL_LEGACY);
if (files.hasNext()) Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: folderId}]}, files.next().getId());

Note:

  • This sample script is for the .xls file. If you want to use .xlsx file, please modify from MimeType.MICROSOFT_EXCEL_LEGACY to MimeType.MICROSOFT_EXCEL.

References:

Upvotes: 1

Eyal Abadi
Eyal Abadi

Reputation: 69

Does something like this work for you?


  try {

    fileName = fileName || "microsoft-excel.xlsx";

    var excelFile = DriveApp.getFilesByName(fileName).next();
    var fileId = excelFile.getId();
    var folderId = Drive.Files.get(fileId).parents[0].id;  
    var blob = excelFile.getBlob();
    var resource = {
      title: excelFile.getName(),
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{id: folderId}],
    };

    Drive.Files.insert(resource, blob);

  } catch (f) {
    Logger.log(f.toString());
  }

}

thanks to ctrlq.org/

Upvotes: 1

Related Questions