Read TXT file in GDrive to put into GSheets new column

[Stefan Cook]

(Noobie) I am trying to take data from an uploaded TXT file [on Google Drive] and then put it into a new column in an existing Google Sheets file by using Apps Script.

The data in this TXT file, for this example, is only a subset of what it will ultimately contain (which will be 27,000+ lines of data), so I am starting small first.  Because this file will eventually contain so many records I would like to have the read / write process as quick as possible.

The spreadsheet for this example just happens to be an empty, new spreadsheet to keep it simple.

I do not know if what I have is efficient or not, but, I am trying to go from a BLOB to the 2D Array needed for the "setValues()" function.  However, I am not parsing out the BLOB correctly so am getting "Cannot convert Array to number[][]".  Looping through an array to populate a 2D array would not be very efficient.

Here is my code with my TXT file attached.

function foo() {

  var folderId = '1f2FB3uCXxIoXM2keJyw1AzpcBAOS1PVH';

  var fileName = 'OPTFLTEST1.TXT';

  // Get file location inside specific folder within Google Drive

  var testFolder = DriveApp.getFolderById(folderId)

  var testFiles = testFolder.getFilesByName(fileName);              // look for file in folder

  if (!testFiles.hasNext()) {                                       // If no file present

    throw new Error('No file with name:' + fileName);

  }

  

  var file = testFiles.next();                                      // take only the first file among all files with such name

  var textBlob = file.getBlob();                                    // grab all data

  var text = textBlob.getDataAsString('utf8');                      // put all data as all one string

  var ss=SpreadsheetApp.getActiveSpreadsheet();

  var s=ss.getActiveSheet();

  var arr = [[],[]];

  arr = text.split('\n');                                          // "parse" 1 string into individual strings

  

  var range = s.getRange(1,1,10,1);        // range for A1:A10

  range.setValues(arr);

}

Secondary, if you have any tips to make something better I would love to hear it.  Thanks

[ChiefChippy2]

On Tuesday, October 8, 2019, Stefan Cook wrote:

(Noobie) I am trying to take data from an uploaded TXT file [on Google Drive] and then put it into a new column in an existing Google Sheets file by using Apps Script.

The data in this TXT file, for this example, is only a subset of what it will ultimately contain (which will be 27,000+ lines of data), so I am starting small first.  Because this file will eventually contain so many records I would like to have the read / write process as quick as possible.

The spreadsheet for this example just happens to be an empty, new spreadsheet to keep it simple.

I do not know if what I have is efficient or not, but, I am trying to go from a BLOB to the 2D Array needed for the "setValues()" function.  However, I am not parsing out the BLOB correctly so am getting "Cannot convert Array to number[][]".  Looping through an array to populate a 2D array would not be very efficient.

Here is my code with my TXT file attached.

function foo() {

  var folderId = '1f2FB3uCXxIoXM2keJyw1AzpcBAOS1PVH';

  var fileName = 'OPTFLTEST1.TXT';

  // Get file location inside specific folder within Google Drive

  var testFolder = DriveApp.getFolderById(folderId)

  var testFiles = testFolder.getFilesByName(fileName);              // look for file in folder

  if (!testFiles.hasNext()) {                                       // If no file present

    throw new Error('No file with name:' + fileName);

  }

  

  var file = testFiles.next();                                      // take only the first file among all files with such name

  var textBlob = file.getBlob();                                    // grab all data

  var text = textBlob.getDataAsString('utf8');                      // put all data as all one string

  var ss=SpreadsheetApp.getActiveSpreadsheet();

  var s=ss.getActiveSheet();

  var arr = [[],[]];

//Google apps script has a function to parse CSV, it should help a lot in this case. 

Utilities.parseCsv(text,"\n")

[Stefan Cook]

Most Excellent!  Yes, that works!  I had tried the parseCsv(csv) method but guess that I never saw the second method variation (DOH).