Question 212451 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Is it possible to convert a .txt file to a google doc in an apps script? I keep throwing errors and I'm wondering what I'm missing

I commented out my attempt as it keeps failing… any thoughts?

// script to be run on below noted folder and output to second specified folder in same drive
// converts spreadsheets to TXT files

function convertSpreadsheetsToDocs() {
  const mainFolderName = "Test Spreadsheets"; // Replace with your main folder name
  const subFolderName = "Subfolder5"; // Replace with your subfolder name

  const mainFolder = DriveApp.getFoldersByName(mainFolderName).next();
  const subFolder = mainFolder.createFolder(subFolderName); // Create a subfolder

  const spreadsheets = mainFolder.getFilesByType(MimeType.GOOGLE_SHEETS);

  while (spreadsheets.hasNext()) {
    const spreadsheet = spreadsheets.next();
    const spreadsheetName = spreadsheet.getName();
    const docName = spreadsheetName + " (Converted)";
    const doc = DocumentApp.create(docName);
    
    const sheet = SpreadsheetApp.openById(spreadsheet.getId()).getActiveSheet();
    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    dataRange.setNumberFormat("@"); // Set cell format as plain text
    
    let text = values.map(row => row.join("t")).join("n"); // Convert data to tab-delimited     text
    // Replace carriage return with page break
    text = text.replace(/n/g, "nn");
    // Replace tab with carriage return
    text = text.replace(/t/g, "n");

    const blob = Utilities.newBlob(text, MimeType.PLAIN_TEXT);
    //   const blob = Utilities.newBlob(text, MimeType.GOOGLE_DOCS);
    const docFile = subFolder.createFile(blob.setName(docName + ".txt"));

    // File.setContentType(MimeType.GOOGLE_DOCS);
    DriveApp.getFileById(doc.getId()).setTrashed(true); // Delete the temporary Google Doc
  }
  
  Logger.log("Spreadsheets converted to text file successfully.");
}

I can create the .txt file from a spreadsheet but fail when trying to make it a google doc. In the end I need to add some formatting… remove the first five or so lines and tag a font to it to make it pretty

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Here's what worked and note above in comment back to Miguel, has an error but it runs so I'm running with it.

    function copyDataToGoogleDoc() {
  var sourceFolderName = "Test Spreadsheets"; // Replace with the name of your source folder
  var targetFolderName = "Test Target 5"; // Replace with the name of your target subfolder

  var sourceFolder = DriveApp.getFoldersByName(sourceFolderName).next();
  var targetFolder = sourceFolder.createFolder(targetFolderName);
  var files = sourceFolder.getFiles();

  while (files.hasNext()) {
    var file = files.next();
    var ss = SpreadsheetApp.open(file);
    var sheet = ss.getActiveSheet();
    var lastRow = sheet.getLastRow();

    // Create a new Google Doc for each sheet file
    var newDoc = DocumentApp.create(file.getName());

    // Loop through each row and copy data from specified columns
    for (var i = 2; i <= lastRow; i++) {
      var rowData = sheet.getRange(i, 1, 1, 6).getValues()[0];
      var combinedData = rowData[0] + " - " + rowData[1] + " - " + rowData[3] + " - " + rowData[4];

// Append the combined data in bold font as a new paragraph in the Google Doc
      newDoc.getBody().appendParagraph(combinedData).setBold(true);

      // Append the long text string from column 5 on a new line (skip a line)
      newDoc.getBody().appendParagraph("");
      newDoc.getBody().appendParagraph(rowData[5]).setBold(false);

      // Append a new paragraph before pasting the next row's data
      newDoc.getBody().appendParagraph("");
    }

    // Move the new Google Doc to the target subfolder
    var newFile = DriveApp.getFileById(newDoc.getId());
    targetFolder.addFile(newFile);
    sourceFolder.removeFile(newFile);
  }
}


  2. 0

    Based on this and this, it does not seem createFile is able to create files of the Google Docs mime type.

    As an alternative, you can create a new (blank) doc file (via the DocumentApp or advanced Drive API), and then append elements from the spreadsheet to that document. Depending on how you want things formatted in the final product, this could be quite tedious, but in the simplest case you could do something like so:

    // script to be run on below noted folder and output to second specified folder in same drive
// converts spreadsheets to TXT files

function convertSpreadsheetsToDocs() {
  const mainFolderName = "Test Spreadsheets"; // Replace with your main folder name
  const subFolderName = "Subfolder5"; // Replace with your subfolder name

  const mainFolder = DriveApp.getFoldersByName(mainFolderName).next();
  const subFolder = mainFolder.createFolder(subFolderName); // Create a subfolder

  const spreadsheets = mainFolder.getFilesByType(MimeType.GOOGLE_SHEETS);

  while (spreadsheets.hasNext()) {
    const spreadsheet = spreadsheets.next();
    const spreadsheetName = spreadsheet.getName();
    const docName = spreadsheetName + " (Converted)";
    // const doc = DocumentApp.create(docName);
    
    const sheet = SpreadsheetApp.openById(spreadsheet.getId()).getActiveSheet();
    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    dataRange.setNumberFormat("@"); // Set cell format as plain text
    
    let text = values.map(row => row.join("t")).join("n"); // Convert data to tab-delimited     text
    // Replace carriage return with page break
    text = text.replace(/n/g, "nn");
    // Replace tab with carriage return
    text = text.replace(/t/g, "n");

    const driveFile = Drive.Files.insert({mimeType: MimeType.GOOGLE_DOCS, title: docName, parents: [{id: subFolder.getId()}]});
    const docId = driveFile.id;
    const doc = DocumentApp.openById(docId);
    // Either run the above three lines with the advanced Drive API enabled or the next two lines
    // const doc = DocumentApp.create(docName); 
    // DriveApp.getFileById(doc.getId()).moveTo(subFolder);

    const body = doc.getBody();

    body.appendParagraph(text)
    doc.saveAndClose();
  }
  
  Logger.log("Spreadsheets converted to text file successfully.");
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search