2

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.

Cooper
  • 59,616
  • 6
  • 23
  • 54
Petrus
  • 309
  • 4
  • 12
  • 1
    Can you provide your current script? Because I thought that there might be the possibility that your script might have already been most efficient way to convert XLSX format to Google Spreadsheet. And can I ask you about the number and maximum file size of files you want to convert? These information is important for creating the high efficiency script. – Tanaike May 19 '19 at 22:11
  • The file size is usually 60KB. Currently I'm using the solution here: https://stackoverflow.com/questions/49246890/google-apps-script-creates-sheets-version-of-excel-file#comment93718159_53421856 – Petrus May 21 '19 at 20:58
  • Thank you for replying. I apologize for my poor English kill. Also can I ask you about the number of files you want to convert? And can you update your question including the information? By the way, can I ask you about your issue of the sample script of the thread you are using? – Tanaike May 21 '19 at 23:19
  • It's only one file of 60KB that I need to convert every week. The sample script is working, just thought there is perhaps a shorter version of code with the same effect... – Petrus May 22 '19 at 20:00
  • Thank you for replying. I thought that I could correctly understand your situation. So I proposed a sample script as an answer. Could you please confirm it? This sample script is for your situation as mentioned above. If I misunderstood your question and that was not the result you want, I apologize. – Tanaike May 22 '19 at 22:27

3 Answers3

1

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/

Eyal Abadi
  • 69
  • 1
  • 9
0
  • 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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • @Petrus Thank you for replying. I'm glad your issue was resolved. – Tanaike May 25 '19 at 23:11
  • Hi Tanaike, can this script also be used to convert all XLS files in a directory? Thus multiple files? – Petrus Oct 03 '22 at 18:43
  • @Petrus About `can this script also be used to convert all XLS files in a directory? Thus multiple files?`, I would like to support you. But your new issue is different from your question. So can you post it as new question? Because when your initial question is changed by comment, other users who see your question are confused. By posting it as new question, users including me can think of it. If you can cooperate to resolve your new issue, I'm glad. Can you cooperate to resolve your new question? – Tanaike Oct 03 '22 at 22:13
0

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;
}
Oniy16
  • 1
  • 1