The Google Apps Script uses the Advanced Drive API to covert Microsoft Excel files (XLS, XLSX) into CSV files and saves them into a specific Google Drive folder. The Excel sheets are deleted after the CSV files are saved in Drive.
Also see: Convert Google Sheets to PDF Files
The conversion engine may timeout if you have too many XLS/XLSX files in a Google Drive and in that case, you’d need to include the time check to ensure that the script doesn’t exceed the execution time limit.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
function convertXLSFilesToCSV() { var oauthToken = ScriptApp.getOAuthToken(), sourceFolder = DriveApp.getFolderById(SOURCE_XLS_FOLDER), targetFolder = DriveApp.getFolderById(TARGET_CSV_FOLDER), mimes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY]; /* Written by Amit Agarwal */ /* email: amit@labnol.org */ /* website: www.ctrlq.org */ for (var m = 0; m < mimes.length; m++) { files = sourceFolder.getFilesByType(mimes[m]); while (files.hasNext()) { var sourceFile = files.next(); // Re-upload the XLS file after convert in Google Sheet format var googleSheet = JSON.parse(UrlFetchApp.fetch( "https://www.googleapis.com/upload/drive/v2/files?uploadType=media&convert=true", { method: "POST", contentType: "application/vnd.ms-excel", payload: sourceFile.getBlob().getBytes(), headers: { "Authorization": "Bearer " + oauthToken } } ).getContentText()); // The exportLinks object has a link to the converted CSV file var targetFile = UrlFetchApp.fetch( googleSheet.exportLinks["text/csv"], { method: "GET", headers: { "Authorization": "Bearer " + oauthToken } }); // Save the CSV file in the destination folder targetFolder.createFile(targetFile.getBlob()).setName(sourceFile.getName() + ".csv"); // Delete the processed file sourceFile.setTrashed(true); } } } |