Converting Many Files to Google Docs using Google Apps Script

submit.md

Converting Many Files to Google Docs using Google Apps Script

This is a sample script for converting a lot of files to Google Docs (Spreadsheet, Document and Slides).

• Batch request can be used for converting files. In this sample script, the files are converted using the batch request.
• Batch request can request 100 API by one API call.
• This sample script uses the fetchAll method. So even if there are over 100 files, this script can process them.

Sample script:

Before you run the script, please set the variables at main(). And, please enable Drive API at Advanced Google services.

// Get file IDs of Microsoft Excel files in a specific folder including subfolders.
function getFileIds(folder, fileIds, q) {
  var files = folder.searchFiles(q);
  while (files.hasNext()) {
    fileIds.push(files.next().getId());
  }
  var folders = folder.getFolders();
  while (folders.hasNext()) {
    getFileIds(folders.next(), fileIds, q);
  }
  return fileIds;
}

// Convert Microsoft Docs to Google Docs
function convertToGoogleDocs(fileIds, dest, to) {
  var limit = 100;
  var split = Math.ceil(fileIds.length / limit);
  var reqs = [];
  for (var i = 0; i < split; i++) {
    var boundary = "xxxxxxxxxx";
    var payload = fileIds.splice(0, limit).reduce(function(s, e, i, a) {
      s += "Content-Type: application/http\r\n" +
        "Content-ID: " + i + "\r\n\r\n" +
        "POST https://www.googleapis.com/drive/v3/files/" + e + "/copy" + "\r\n" +
        "Content-Type: application/json; charset=utf-8\r\n\r\n" +
        JSON.stringify({"parents": [dest], "mimeType": to}) + "\r\n" +
        "--" + boundary + (i == a.length - 1 ? "--" : "") + "\r\n";
      return s;
    }, "--" + boundary + "\r\n");
    var req = {
      url: "https://www.googleapis.com/batch/drive/v3",
      method: "post",
      contentType: "multipart/mixed; boundary=" + boundary,
      payload: payload,
      headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true,
    };
    reqs.push(req);
  }
  return UrlFetchApp.fetchAll(reqs);
}

// Please run this function.
function main() {
  var sourceFolderId = "###"; // Folder ID including source files.
  var destinationFolderId = "###"; // Folder ID that the converted files are put.
  var from = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY]; // Source mimeType
  var to = MimeType.GOOGLE_SHEETS; // Destination mimeType

  var q = from.reduce(function(q, e, i) {return q += "mimeType='" + e + "'" + (i < from.length - 1 ? " or " : "")}, "");
  var fileIds = getFileIds(DriveApp.getFolderById(sourceFolderId), [], q);
  var res = convertToGoogleDocs(fileIds, destinationFolderId, to);
  Logger.log(res);

  // DriveApp.createFile(); // This is used for automatically adding a scope of "https://www.googleapis.com/auth/drive".
}

Note:

• This script uses Drive API. So if the error related to Drive API, please enable Drive API at API console.
• If you report the number of files using this script, I'm glad. Because when the maximum number for converting files by this script is found, it will be useful for other users.
  • I could confirm that 500 files could be converted. But I don't know about the limitation. I'm sorry.

If this was useful for your situation, I'm glad.

Updated:

• 20201206: The specification of batch request was updated. So I modified above script.

• 20230404: The specification of request was updated. So I modified above script.

@tanaikech I tried the above script in order to convert "application/vnd.openxmlformats-officedocument.word" file to MimeType.GOOGLE_DOCS.

I got the "Exception: Attribute provided with invalid value: Header:X-Forwarded-For" on the line of "return UrlFetchApp.fetchAll(reqs);" Any suggestions? Thanks!

Getting the same "Exception: Attribute provided with invalid value: Header:X-Forwarded-For" on the line of "return UrlFetchApp.fetchAll(reqs);"

@zqian and @REVREBEL It's Done. I apologize for the late response.

you're amazing, thank you