0

I found a solution for my original question in another post Google Apps Script creates sheets version of excel file.

Testing with the code provided in the answer I ran into another issue. Every time I run the script it creates the Spreadsheets version of the .xlsx files again even if they already exist. I have tried modifying the code withing the last If with no results. Then went back to run your code as posted in case I have missed something but it keeps creating versions of the same files.

Any idea of what could I do to fix this will be really appreciated.

The code provided int he answer is the following. 

// Convert the user's stored excel files to google spreadsheets based on the specified directories.
// There are quota limits on the maximum conversions per day: consumer @gmail = 250.
function convertExcelToGoogleSheets() 
{
  var user = Session.getActiveUser(); // Used for ownership testing.
  var origin = DriveApp.getFolderById("origin folder id");
  var dest = DriveApp.getFolderById("destination folder id");

  // Index the filenames of owned Google Sheets files as object keys (which are hashed).
  // This avoids needing to search and do multiple string comparisons.
  // It takes around 100-200 ms per iteration to advance the iterator, check if the file
  // should be cached, and insert the key-value pair. Depending on the magnitude of
  // the task, this may need to be done separately, and loaded from a storage device instead.
  // Note that there are quota limits on queries per second - 1000 per 100 sec:
  // If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
  var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
  while (gsi.hasNext())
  {
    var file = gsi.next();
    if(file.getOwner().getEmail() == user.getEmail())
      gsNames[file.getName()] = true;
  }

  // Find and convert any unconverted .xls, .xlsx files in the given directories.
  var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
  for(var mt = 0; mt < exceltypes.length; ++mt)
  {
    var efi = origin.getFilesByType(exceltypes[mt]);
    while (efi.hasNext())
    {
      var file = efi.next();
      // Perform conversions only for owned files that don't have owned gs equivalents.
      // If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
      // If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
      if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])
      {
        Drive.Files.insert(
          {title: file.getName(), parents: [{"id": dest.getId()}]},
          file.getBlob(),
          {convert: true}
        );
        // Do not convert any more spreadsheets with this same name.
        gsNames[file.getName()] = true;
      }
    }
  }
}
Axel
  • 98
  • 1
  • 9
  • In order to understand correctly about your situation, can I ask you about what you want to do? You want to convert Excel files in ``origin`` folder to Google Spreadsheet and put the converted Spreadsheet to ``dest`` folder. At this time, when the filename of converted file is existing in ``dest`` folder, you don't want to convert it. Is my understanding correct? – Tanaike Nov 22 '18 at 01:00
  • Yes it is, the Excel files are uploaded to the Origin folder and the script creates a Google Spreadsheet version on the Destination folder. The script would be run multiple times in a day since more files are being uploaded to the original folder continuously. The script is intended to convert the Excel files which Don't have a spreadsheet version in the Dest folder yet, but it keeps creating versions for all of the files even if they already have an spreadsheet version created by script before. – Axel Nov 22 '18 at 01:09
  • Thank you for replying. I posted an answer for modifying script. Could you please confirm it? If that was not what you want, please tell me. I would like to modify it. – Tanaike Nov 22 '18 at 01:14

1 Answers1

0
  • You want to convert Excel files in origin folder to Google Spreadsheet and put the converted Spreadsheet to dest folder.
  • When the filename of converted file is existing in dest folder, you don't want to convert it.

If my understanding is correct, how about this modification?

From:

if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName()])

To:

if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().split(".")[0]])

Note:

  • In this modification, when the filename of converted file is found in the dest folder, the file is not converted.
  • When the filename has the extension like ###.xlsx and it is converted to Google Spreadsheet, it seems that the extension is automatically removed. I think that this is the reason that the duplicated files are created. So I used split(".")[0] for this situation.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • I Couldn't thank you enough, this worked just perfectly. I wouldn't be able to figured it out my delft. I guess I need to study more JavaScript and Apps Script itself. I'll study the code you added to try to understand it and check the reference to MDM you added. Thank you very much. – Axel Nov 22 '18 at 02:26
  • @Axel I'm really sorry. I noticed that I had misunderstood about the script. So I updated my answer. Could you please confirm it? By using the object ``gsNames`` in your script, the modification part can be more simple. – Tanaike Nov 22 '18 at 02:57