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.

I let ai create a script that converts
.txt files to Google-Docs
and .csv to Google-Sheets

YOU CAN PLACE THIS SCRIPT IN
https://script.google.com/
=> Change folder ID to your folder
=> You need to give permission to run the script

function convertFilesToGoogleWorkspace() {
// Record the start time to prevent 6-minute timeout crashes
var startTime = Date.now();
var maxExecutionTime = 5.5 * 60 * 1000; // 5.5 minutes in milliseconds

// Your new folder ID
var folderId = 'YOUR-FOLDER_ID_HERE';
var folder = DriveApp.getFolderById(folderId);

// Get ALL files in this folder
var files = folder.getFiles();
var txtCount = 0;
var csvCount = 0;
var skipCount = 0;

while (files.hasNext()) {
// Check how long the script has been running
if (Date.now() - startTime > maxExecutionTime) {
Logger.log('Approaching Google's 6-minute time limit. Stopping safely. Please click RUN again to continue!');
break; // Exit the loop safely
}

var file = files.next();
var fileName = file.getName();
var mimeType = file.getMimeType();

// Immediately skip files that are already native Google Docs or Sheets
if (mimeType === MimeType.GOOGLE_DOCS || mimeType === MimeType.GOOGLE_SHEETS) {
  continue;
}

// ==========================================
// 1. CONVERT .TXT TO GOOGLE DOCS
// ==========================================
if (fileName.toLowerCase().endsWith('.txt')) {
  var newDocName = fileName.replace('.txt', '');
  
  // CHECK: Does a file with this new name already exist in the folder?
  var existingFiles = folder.getFilesByName(newDocName);
  if (existingFiles.hasNext()) {
    skipCount++;
    continue; // Skip to the next file
  }
  
  // Create the new Doc
  var content = file.getBlob().getDataAsString();
  var newDoc = DocumentApp.create(newDocName);
  newDoc.getBody().setText(content);
  newDoc.saveAndClose();
  
  // Move to folder and trash original
  var newDocFile = DriveApp.getFileById(newDoc.getId());
  newDocFile.moveTo(folder);
  file.setTrashed(true); 
  
  txtCount++;
  Utilities.sleep(500); // Reduced to 0.5 seconds
}

// ==========================================
// 2. CONVERT .CSV TO GOOGLE SHEETS
// ==========================================
else if (fileName.toLowerCase().endsWith('.csv')) {
  var newSheetName = fileName.replace('.csv', '');
  
  // CHECK: Does a file with this new name already exist in the folder?
  var existingFiles = folder.getFilesByName(newSheetName);
  if (existingFiles.hasNext()) {
    skipCount++;
    continue; // Skip to the next file
  }
  
  // Create the new Sheet
  var content = file.getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(content);
  var newSpreadsheet = SpreadsheetApp.create(newSheetName);
  var sheet = newSpreadsheet.getActiveSheet();
  
  if (csvData.length > 0 && csvData[0].length > 0) {
    sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  }
  
  // Move to folder and trash original
  var newSheetFile = DriveApp.getFileById(newSpreadsheet.getId());
  newSheetFile.moveTo(folder);
  file.setTrashed(true);
  
  csvCount++;
  Utilities.sleep(500); // Reduced to 0.5 seconds
}

}

// Log the final results
Logger.log('Session Complete.');
Logger.log('Converted ' + txtCount + ' .txt files.');
Logger.log('Converted ' + csvCount + ' .csv files.');
Logger.log('Skipped ' + skipCount + ' already converted files.');
}