App-script to pull data from email attachment (text file) to Google Sheet?-CodePudding

Hi I wrote this script to pull data from an email attachment into a Google Sheet and although it seems to work, it stops after writing the data into the range with an exception error that the columns don't match the range. Even though I logged it and it does.

3:16:02 PM Info 6065.0

3:16:02 PM Info 33.0

3:16:22 PM Error
Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 33.

function myFunction() {
  
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
      threads = GmailApp.getUserLabelByName('Example').getThreads() ,
      message = threads[0].getMessages()[0],
      attachment = message.getAttachments()[0],
      date = Utilities.formatDate(new Date(), "GMT 1", "MM/dd/yy");

  var data = attachment.getDataAsString().split(/\n/).map(r => r.split(/\t/));
  Logger.log(data);
  
  var sheet = spreadsheet.getSheetByName('Sheet1');
  sheet.clearContents();
  var row1 = data.length;
  var col1 = data[0].length;
  Logger.log(row1);
  Logger.log(col1);

  sheet.getRange(1,1,row1,col1).setValues(data);
}

CodePudding user response：

Try

  var data = attachment.getDataAsString().replace(/([ ]{2,})/gm, "\t").split(/\n/).map(r => r.split(/\t/));
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = spreadsheet.getSheetByName('Sheet1');
  sheet.clearContents();
  for (var i = 0; i < data.length; i  ) {
    sheet.getRange(i   1, 1, 1, data[i].length).setValues(new Array(data[i]));
  }

CodePudding user response：

Is this any help?

function myFunction() {
  
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
      threads = GmailApp.getUserLabelByName('Example').getThreads() ,
      message = threads[0].getMessages()[0],
      attachment = message.getAttachments()[0],
      date = Utilities.formatDate(new Date(), "GMT 1", "MM/dd/yy");

  var data = attachment.getDataAsString().split(/\n/).map(r => r.split(/\t/));
  Logger.log(data);
  
  var sheet = spreadsheet.getSheetByName('Sheet1');
  sheet.clearContents();
  var row1 = data.length;
  var col1 = data[0].length;
  Logger.log(row1);
  Logger.log(col1);

  /* ↓↓↓ */
  const hasEqualColumns = data.every(row => row.length === data[0].length)
  if (!hasEqualColumns) {
    data = data.map(row => {
      if (row.length !== data[0].length) {
        return [...row, ...Array(data[0].length-row.length).fill(``)]
      } else { return row }
    })
    sheet.getRange(1,1,row1,col1).setValues(data);
  } else {
    sheet.getRange(1,1,row1,col1).setValues(data);
  }

}

I'm going to guess there's a row in the array that is not equal to the range's column width. This snippet will check if there's an inequality of column lengths, and append empty cells to make any 'uneven' rows fit the range.