javascript - Convert excel files to Google Spreadsheet and replace existing spreadsheet files automatically

Question

The code provided convert files from Excel to Google Sheet. The code from here but it does not overwrite/replace current existing spreadsheet files in the destination folder. Is it possible to convert everything including the ones inside the subfolders altogether and replace any existing Google Spreadsheet files with the same name?

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))
}

Answer 1

1. There are Excel files (filename with the extensions of .xlsx or .xls) in a folder with several subfolders.
2. There are Spreadsheet files (filename without the extensions of .xlsx or .xls) in a folder without subfolders.
3. You want to overwrite the existing Spreadsheet files with the converted Spreadsheet from Excel files.
4. Number of Spreadsheet and Excel files are the same.

From your question and comments, I could understand like above.

At first, I tested to update files by the batch request. As the result, it seems that the update of file cannot be achieved by the batch request, when the file blob is used for updating. About this, if I found the workaround for this situation, I would like to update my answer.

So in this sample script, I propose the method for using Drive API of Advanced Google Services for above situation.

When you use this script, please enable Drive API at Advanced Google Services and API console. You can see about this at here.

Flow:

The flow of this script is as follows.

1. Retrieve files in the source and destination folder.
2. When the filenames in the source folder are existing in the destination folder, those files overwrite the existing Spreadsheet files.
3. When the filenames in the source folder are not existing in the destination folder, those files are converted to Spreadsheet as new files.

Sample script:

Before run the script, please set sourceFolderId and destinationFolderId.

function myFunction() {
  var sourceFolderId = "###"; // Folder ID including source files.
  var destinationFolderId = "###"; // Folder ID that the converted files are put.

  var getFileIds = function (folder, fileList, q) {
    var files = folder.searchFiles(q);
    while (files.hasNext()) {
      var f = files.next();
      fileList.push({id: f.getId(), fileName: f.getName().split(".")[0].trim()});
    }
    var folders = folder.getFolders();
    while (folders.hasNext()) getFileIds(folders.next(), fileList, q);
    return fileList;
  };
  var sourceFiles = getFileIds(DriveApp.getFolderById(sourceFolderId), [], "mimeType='" + MimeType.MICROSOFT_EXCEL + "' or mimeType='" + MimeType.MICROSOFT_EXCEL_LEGACY + "'");
  var destinationFiles = getFileIds(DriveApp.getFolderById(destinationFolderId), [], "mimeType='" + MimeType.GOOGLE_SHEETS + "'");
  var createFiles = sourceFiles.filter(function(e) {return destinationFiles.every(function(f) {return f.fileName !== e.fileName});});
  var updateFiles = sourceFiles.reduce(function(ar, e) {
    var dst = destinationFiles.filter(function(f) {return f.fileName === e.fileName});
    if (dst.length > 0) {
      e.to = dst[0].id;
      ar.push(e);
    }
    return ar;
  }, []);
  if (createFiles.length > 0) createFiles.forEach(function(e) {Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, parents: [{id: destinationFolderId}], title: e.fileName}, DriveApp.getFileById(e.id))});
  if (updateFiles.length > 0) updateFiles.forEach(function(e) {Drive.Files.update({}, e.to, DriveApp.getFileById(e.id))});
}

Note:

• When you have many files for converting and the execution time of the script is over, please divide the files and run the script.

References:

• Advanced Google Services
• Drive API