Skip to content

Instantly share code, notes, and snippets.

@tanaikech tanaikech/submit.md
Last active Apr 12, 2019

Embed
What would you like to do?
Converting Many Files to Google Docs using Google Apps Script

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().

// 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) {
      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 + "\r\n";
      return s;
    }, "--" + boundary + "\r\n");
    var params = {
      method: "post",
      contentType: "multipart/mixed; boundary=" + boundary,
      payload: payload,
      headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true,
    };
    var req = UrlFetchApp.getRequest("https://www.googleapis.com/batch", params);
    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);
}

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.