CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
Admin
Admin

Reputation: 1

Error : I want to convert the file from XLSX to csv using google script

Hi Facing error while running code of Coverting excel to csv. Please guide what to edit to solve the query.

 function makeCSV() {
 var SourceFolder = DriveApp.getFolderById("1aoonhCebvI5DddvJVGTzBvWs2BPn_yXN")
 var DestinationFolder = DriveApp.getFolderById("1LB0Em4vYFJV8vJIEiLt6Tg5pnzPATZEj")
 var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + 
  MimeType.MICROSOFT_EXCEL_LEGACY + "'";
 var sourceFiles = SourceFolder.searchFiles(searchQuery);

 var now = new Date();
var properties = PropertiesService.getScriptProperties();
 var cutoff_datetime = properties.getProperty('last_execution_time');
if(cutoff_datetime)
 cutoff_datetime = new Date(cutoff_datetime);
while (sourceFiles.hasNext()){
var sourceFile = sourceFiles.next();

 if(!cutoff_datetime || sourceFile.getDateCreated() > cutoff_datetime){
  var fileId = sourceFile.getId();

var Spreadsheet = Drive.Files.copy({mimeType: MimeType.csv, parents: 
[{id:"1LB0Em4vYFJV8vJIEiLt6Tg5pnzPATZEj"}]}, fileId);
}
}
properties.setProperty('last_execution_time',now.toString());
}

Upvotes: 0

Views: 1616

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Unfortunately, XLSX data cannot be directly converted to CSV data using Google Apps Script. And, Drive.Files.copy can convert non-Google Docs files to Google Docs files. Please be careful about this. I think that this is the reason for your current issue. In order to convert XLSX data to CSV data using Google Apps Script, it is required to do the following flow.

  1. Convert XLSX data to Google Spreadsheet.
  2. Convert Google Spreadsheet to CSV data.
  3. Remove Google Spreadsheet.

When this flow is reflected in your script, it becomes as follows.

Modified script:

function makeCSV() {
  var SourceFolder = DriveApp.getFolderById("1aoonhCebvI5DddvJVGTzBvWs2BPn_yXN");
  var DestinationFolder = DriveApp.getFolderById("1LB0Em4vYFJV8vJIEiLt6Tg5pnzPATZEj");
  var searchQuery = "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'";
  var sourceFiles = SourceFolder.searchFiles(searchQuery);
  var now = new Date();
  var properties = PropertiesService.getScriptProperties();
  var cutoff_datetime = properties.getProperty('last_execution_time');
  if (cutoff_datetime) cutoff_datetime = new Date(cutoff_datetime);
  var token = ScriptApp.getOAuthToken();
  while (sourceFiles.hasNext()) {
    var sourceFile = sourceFiles.next();
    if (!cutoff_datetime || sourceFile.getDateCreated() > cutoff_datetime) {
      var fileId = sourceFile.getId();
      var ssId = Drive.Files.copy({mimeType: MimeType.GOOGLE_SHEETS, parents: [{ id: "1LB0Em4vYFJV8vJIEiLt6Tg5pnzPATZEj" }]}, fileId).id;
      var url = "https://docs.google.com/spreadsheets/export?exportFormat=csv&id=" + ssId;
      var blob = UrlFetchApp.fetch(url, {headers: {authorization: "Bearer " + token}}).getBlob();
      DestinationFolder.createFile(blob.setName(sourceFile.getName().split(".")[0] + ".csv"));
      DriveApp.getFileById(ssId).setTrashed(true);
    }
  }
  properties.setProperty('last_execution_time', now.toString());
}
  • When this script is run, the XLSX data is converted to Google Spreadsheet, and Google Spreadsheet is converted to CSV data. The CSV file is created to DestinationFolder folder.

Note:

  • This script used Drive API. So, please enable Drive API at Advanced Google services.

References:

Upvotes: 4

Related Questions