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

Reputation: 117

Convert text file into SpreadSheet Google Apps Script

I'm looking for a way to convert a brut file into .txt then into spreadsheet using Google Apps Script.

I've already seen a way to convert xls into Google SpreadSheet with this script (using Drive API V2) :

function convert_XLS_To_SpreadSheet(){
  var folder = DriveApp.getFoldersByName('RootFolder').next().getFoldersByName('depository').next();
  var txtFile = folder.getFilesByName('file.xls').next();
  Logger.log('Text File : ' + txtFile);

  
  var resource = {
    title : txtFile.getName(),
    mimeType : MimeType.GOOGLE_SHEETS,
    parents: [{id : folder.getId()}],
  }

  Drive.Files.insert(resource, txtFile.getBlob(),{
    convert: true
  });

  txtFile.setTrashed(true);
}

I tried to do the same by replacing the file I wanted into txt format but it's giving me a Google Doc even if I provide the correct MimeType.

Upvotes: 2

Views: 1382

Answers (1)

Cooper
Cooper

Reputation: 64100

Column Delimiter: \t

Line Delimiter: \n

Code:

function bf2ss() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const file = DriveApp.getFileById('fileId');
  let lA = file.getBlob().getDataAsString().split(/\n/);
  let fA = lA.map(r => r.split(/\t/));
  sh.clearContents();
  sh.getRange(1,1,fA.length,fA[0].length).setValues(fA);
}

Sheet1:

0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9
0 1 2 3 4 5 6 7 8 9

Upvotes: 1

Related Questions