CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
user9448640
user9448640

Reputation:

Script to convert Google Sheet to Text Documents

I'm trying to create a custom script in Google Sheets that creates text files in the Google Drive.

The Sheet Name will be the folder name, the value in Column A will be the text file name and the value in Column B will be the text inserted into the text file.

I've had success creating it, but I'm having issues expanding it to create a new file for every row and to do multiple sheets.

How can I adjust the code to create additional folders and files when I add a new sheet or a new row?

Here is what I have so far:

function export() { 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var filename = sheet.getRange(1, 1).getValue();
  var foldername = sheet.getSheetName();
  Logger.log('Filename = ' + filename + '  Foldername = ' + foldername);
  var pFIT = DriveApp.getRootFolder().getFoldersByName(foldername);
  var parentFolder, File ;
  if (pFIT.hasNext())
    parentFolder= pFIT.next();

  if (parentFolder==null) {
    parentFolder = DriveApp.getRootFolder().createFolder(foldername);
  }

  var existing = parentFolder.getFilesByName(filename);
  if (existing.hasNext()) {
    var file = existing.next();
    if (file.getName() === filename) {
      file.setTrashed(true);
    }
  }
  var values = sheet.getRange(1, 2).getValues(); 
  var text = values.map(function (a) {return a.join('\t');}).join('\r\n');
  parentFolder.createFile(filename, text, MimeType.PLAIN_TEXT);
}

Here is what I'm trying to accomplish when I run the script:

Screenshot 1

Screenshot 2

Screenshot 3

Upvotes: 0

Views: 2011

Answers (1)

Tanaike
Tanaike

Reputation: 201553

How about this modification? As the basic direction, I modified your script by checking the duplication of folders and files.

Modification points :

  • Retrieve sheet names and check the duplication of folders.
    • When the folder of the sheet name is not found, the folder is created as new folder.
  • Retrieve filenames of "A2:A" and check the duplication of files.
    • When the file of the filename is not found, the file is created with the data of column B.

Modified script :

function export() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheets = ss.getSheets();
  for (var i in sheets) {
    var name = sheets[i].getSheetName();
    var fo = DriveApp.getFoldersByName(name);
    var folder = fo.hasNext() ? fo.next() : DriveApp.getRootFolder().createFolder(name);
    var values = sheets[i].getDataRange().getValues();
    values.shift();
    var filename, text;
    for (var j in values) {
      [filename, text] = values[j];
      folder.getFilesByName(filename).hasNext() || folder.createFile(filename, text, MimeType.PLAIN_TEXT);
    }
  }
}

Note :

  • This script creates folders when the sheets are added to the spreadsheet.
  • This script creates files if the values are added to the column A.
    • In this case, when you updated the values of column A, the new files with the updated values are created. On the other hand, the existing files are ignored.

If this was not what you want to do, please tell me. I would like to modify.

Upvotes: 1

Related Questions