Google Script - arrayofarrays - incorrect range width

Question

I have a Google Script that imports a TSV file and copies the contents into a Google Spreadsheet (code isn't mine, I copied it off someone else's project after googling a bit).

After some tinkering, the script works great but I keep getting a "Incorrect range width, was 1 but should be 6 (line 46, file "Test")" error.

Now, the TSV file getting imported is basically a list consisting of 6 columns, ie:

fruits vegetables cars countries colors names
pear   carrot     ford nicaragua yellow frank

After doing some reading up on how arrayofarrays works and googling for the same error, I've concluded that the problem is that somewhere in the importing/outputting process, a final empty row gets added. ie: instead of having 2 rows of 6 columns, I have 2 rows of 6 columns and a 3rd row with 1 empty column.

Apparently arrayofarrays needs all rows to be the exact same width.

As far as I can tell, this extra column is not present in the source file. I also cannot modify the source file so any fix needs to be done by the script itself.

Like I said, the script works but I'd like to a) stop getting emails about errors in my script, and b) understand how to fix this.

Any suggestions?

Thanks in advance.

Here's the code (there's some commented lines where I tried some fixes which clearly didn't work but I'm leaving them there for the sake of completion).

function getFileIDFromName(fileName) {
  // Return the file ID for the first
  // matching file name.
  // NB: DocsList has been deprecated as of 2014-12-11!
  // Using DriveApp instead.
  var files = DriveApp.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    if (file.getName() === fileName) {
      return file.getId();
    }
  }
  return;
}
function getTsvFileAsArrayOfArays(tsvFileID) {
  // Read the file into a single string.
  // Split on line char.
  // Loop over lines yielding arrays by
  //  splitting on tabs.
  // Return an array of arrays.
  var txtFile = DriveApp.getFileById(tsvFileID),
      fileTextObj = txtFile.getAs('text/plain'),
      fileText = fileTextObj.getDataAsString(),
      lines = fileText.split('\n'),
      lines2DArray = [];
  lines.forEach(function (line) {
                  lines2DArray.push(line.split('\t')); 
               });
  return lines2DArray;
}
function writeArrayOfArraysToSheet(tsvFileID) {
  // Target range dimensions are determine
  // from those of the nested array.
  var sheet = SpreadsheetApp.getActiveSheet(),
      arrayOfArrays = getTsvFileAsArrayOfArays(tsvFileID),
      dimensions = {rowCount: Math.floor(arrayOfArrays.length),
                 colCount: Math.floor(arrayOfArrays[0].length)},
      targetRng;
  sheet.clearContents();
  targetRng = sheet.getRange(1, 1, 
                             dimensions.rowCount, 
                             dimensions.colCount);
  //                           .setValues(arrayOfArrays);
  //targetRng = sheet.getRange(1, 1, arrayOfArrays.length, 6).setValues(arrayOfArrays);
  //targetRng.setValues(arrayOfArrays);
  targetRng.setValues(arrayOfArrays);
}
function runTsv2Spreadsheet() {
  // Call this function from the Script Editor.
  var fileName,
      fileID,
      file;
    //fileName = Browser.inputBox('Enter a TSV file name:',
     //                        Browser.Buttons.OK_CANCEL);
    //fileID = getFileIDFromName(fileName);
    //Logger.log(fileID)
    fileID = "0B-9wMGgdNc6CdWxSNGllNW5FZWM"
  if (fileID) {
    writeArrayOfArraysToSheet(fileID);
  }
}

Jason Allshorn · Accepted Answer · 2016-09-18 10:08:55Z

1

Don't know if this is helpful. But, have you tried the appendRow() method for Google sheets? You can manipulate your array into the item format you want, then loop through it appending into the next empty row in the sheet.

In the past I looped though arrays trying to figure out the positions, but then started using the appendRow() method, and it made things easier for me.

answered Sep 18, 2016 at 10:08

Jason Allshorn

1,6451 gold badge18 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google Script - arrayofarrays - incorrect range width

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related