How to save the content of 50 rows in Google Sheets as txt. file with Google Apps Script

user_11675380 0
Viewed 358

I am looking at a table with 2,000-10,000 rows and need to save the content of 50 rows in a txt file, each line in the txt. file should be 1 row. In addition the the delimiter needs to be a "|".

Table given:

A1  T   B1  C1  ...

A2  T   B2  C2  ...

saved in txt. file like:

A1|T|B1|C1|...

A2|T|B2|C2|...

How do I need to loop through the table to create for 50 rows a new file including the content?

function getTxtFiles(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getSheetByName("Test");

    var folderName = 'Test Folder';
    var folderID = 'Test Folder ID';
    var documentName = 'Test_1.txt'
    var folder = DriveApp.getFolderById(folderID);

  var data = sheet.getDataRange();
  var values = data.getValues();
  var rows = "";   

for (var a = 0; a < data[a].length; a++) {
  for (var i = 0; i < values[i].length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j]) {
        rows = rows + values[i][j];
          }
    rows = rows + "|";
        }
      }
    }

    var sss = DriveApp.createFile(documentName,rows,MimeType.PLAIN_TEXT);
    var file = DriveApp.getFileById(sss.getId());
    var parents = file.getParents();
    parents.next().removeFile(file);
    folder.addFile(file); 

}

I would like to achieve that the script is creating from a table with2,000 rows and with 40 columns, 40 x txt. files and each file includes the content of 50 rows, each row as 1 line in the txt. file.

google-drive-api google-apps-script google-sheets loops
2 Answers

Try the following code:

function txtFileCreator(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues(); // will give you all data cells in a 2D array
  var text = "";
  var count = 0;
  var flag = false;

  for (var i = 0; i < data.length; i++){
    flag = true;    
    for (var j = 0; j < data[0].length; j++)
      text += data[i][j] + " | ";

    text += "\n";

    if ((i+1) % 50 == 0){
      count++;
      createF(text, count);
      text = "";
      flag = false;
    }
  }

  if (flag)
    createF(text, ++count);
}

function createF(content, count){
  DriveApp.createFile("File " + count, content, MimeType.PLAIN_TEXT);
}

It worked perfectly for me, I tried with a test sheet with your kind of ranges and it creates the right files with just 50 lines each.

user_115818300

Flow:

  • values is a 2D array. Join the inner array using | and outer array with \n
  • Use str.match(regex) to get a array of 50 rows each
  • Create file for each string in the array.

Snippet:

var folder = DriveApp.getFolderById('[OUTPUT_FOLDER_ID]')
values.map(function(row){return row.join('|')})
  .join('\n')
  .match(/((?:.*\n){50}|(?:[\s\S]+$))/g)
  .forEach(function(str,i){ folder.createFile('textFile'+ i, str})

References:

user_84044530
Related