0

I'm new to the coding game, learning how to code day by day. Recently I'm hooked into Google App Script, learning to make a simple database. I've tried to decode and re-code the script but I just can't get it working. It was supposed to convert and replace the existing google sheet instead the script just convert and duplicate the excel into many versions (1,2,3) of sheets from the original excel file.

// 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 convertCollection1() 
{
  var user = Session.getActiveUser(); // Used for ownership testing.
  var origin = DriveApp.getFolderById("1dPsDfoqMQLCokZK4RN0C0VRzaRATr9AN");
  var dest = DriveApp.getFolderById("1M6lDfc_xEkR4w61pUOG4P5AXmSGF1hGy");

  // 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;
      }
    }
  }
}
Daniel
  • 3
  • 1
  • 5

1 Answers1

0

The logic of the script is spot on. However, there are subtle nuisances in the function .getName() and Drive.Files.insertyou use that is causing the unintended behavior in the code.

folder.getName() gets the full name of the file, which includes the .xls/.xlsx extension. However, when you convert these files with Drive.Files.insert the extension is dropped. So your gsNames object has filenames without extension and but when the code tries to access the specific element using !gsNames[file.getName()] which has the file extension. It always returns undefined, which is evaluated as True.

Hence, you will need to remove the file extension before you try to check if the same file has already been converted. The regex to remove file extension was shamelessly copied from here. Your logic would be modified like so:

if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().replace(/\.[^/.]+$/, "")])

Your file code will be:

function convertCollection1() 
{
  var user = Session.getActiveUser(); // Used for ownership testing.1aJcbdGhwliTs_CZ-3ZUvQmGRDzBM7fv9
  var origin = DriveApp.getFolderById("1dPsDfoqMQLCokZK4RN0C0VRzaRATr9AN");
  var dest = DriveApp.getFolderById("1M6lDfc_xEkR4w61pUOG4P5AXmSGF1hGy");

  // 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;

    Logger.log(JSON.stringify(gsNames))
  }

  // 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().replace(/\.[^/.]+$/, "")])
      {
        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;
      }
    }
  }
  Logger.log(JSON.stringify(gsNames))
}

Note the use of Logger.log(), use this in the future to determine what the program might be accessing vs what you think it is doing.

Cheers!

Jack Brown
  • 5,802
  • 2
  • 12
  • 27