Convert Excel Files to CSV in Google Drive with Apps Script

CodeCanyon · May 2

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.

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: [email protected]  */
  /* 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);
    }
  }
}