1

Hi im absolute novice with google script in general, ive been tweaking around with this script i found from Bulk convert csv files in Google drive to Google Sheets:

function convert() {
      var folder = DriveApp.getFolderById('folder id here');
      var files = folder.getFiles();
      while (files.hasNext()) {
        var file = files.next();
        Drive.Files.copy({}, file.getId(), {convert: true});
      }
    }

and it works like a charm. the problem is that the this code indiscriminately convert every file in the said folder, including the converted file and source file. so if i run the script for the 2nd i would end up with a new copy of the converted file and another copy of the source file.

is there any way to work around this? i was thinking something along the lines of moving the converted files into a different folder and allow file overwriting to avoid copies.

thank you so much in advance.

gmann
  • 35
  • 1
  • 4

1 Answers1

1

How about this modification?

In this modification, I would like to propose the following workaround.

Workaround:

In this workaround, the mimeType of the source file is checked, and also, the description is given the copied source file. The description is used as the checker whether the file had been copied. By this, when the script is run for the 1st time, the CSV source files are converted to Google Spreadsheet. At that time, the description is added to the source files as copied. And, in the 2nd run, the files with the mimeType of CSV and the description has no value of copied are copied and converted. By this flow, the files which has been copied are not used after 2nd run.

When above workaround is reflected to your script, it becomes as follows.

Sample script:

function convert() {
  var folder = DriveApp.getFolderById('folder id here');
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    if (file.getMimeType() == MimeType.CSV && file.getDescription() != "copied") {  // Added
      file.setDescription("copied");  // Added
      Drive.Files.copy({}, file.getId(), {convert: true});
    }
  }
}

Note:

  • When you have already used the description of the file, you can also use the property of the file.

  • If you want to copy the source CSV files every run, you can also use the following modified script. In this case, only the mimeType is checked.

      function convert() {
    var folder = DriveApp.getFolderById('folder id here');
    var files = folder.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      if (file.getMimeType() == MimeType.CSV) {  // Added
        Drive.Files.copy({}, file.getId(), {convert: true});
      }
    }
  }

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • i think the only problem with this one is that i cannot run the script for the 2nd time. but that should be fine since this only need to be a one time process generally! i just need to be careful to not delete the gsheet files. again thank you! – gmann Jun 25 '20 at 06:20
  • @gmann Thank you for replying. I'm glad your issue was resolved. As an additional information, for example, when the CSV file is copied, when you want to put the converted Google Spreadsheet to the specific folder, please modify `Drive.Files.copy({}, file.getId(), {convert: true});` to `Drive.Files.copy({parents: [{id: "###folderId###"}]}, file.getId(), {convert: true});`. By this, the Google Spreadsheet is put to the specific folder. – Tanaike Jun 25 '20 at 06:25
  • 1
    brilliant i'll try to implement that as well thank you! – gmann Jun 25 '20 at 07:25