制表符分隔TXT文件转Google Sheets脚本报错求助：数据列数与范围列数不匹配

The error you’re hitting happens because your script assumes every row in the TXT file has the same number of columns as the first row—but there are likely empty lines or rows with fewer columns in your file. When those lines get split, they create arrays with fewer elements than the first row, causing a mismatch when writing to the sheet.

Here’s a revised script that handles these edge cases, just like the manual import process does:

function convert_txt_gsheets(){ 
  var file = DriveApp.getFilesByName('file.txt').next(); 
  var body = file.getBlob().getDataAsString().split(/\n/); 
  
  // Remove empty or whitespace-only lines to avoid single-column rows
  var nonEmptyLines = body.filter(line => line.trim() !== '');
  
  // Split each line into columns and find the maximum number of columns across all rows
  var result = nonEmptyLines.map(r => r.split(/\t/));
  var maxColumns = result.reduce((currentMax, row) => Math.max(currentMax, row.length), 0);
  
  // Pad shorter rows with empty strings to match the maximum column count
  var normalizedRows = result.map(row => {
    while (row.length < maxColumns) {
      row.push('');
    }
    return row;
  });
  
  // Write the cleaned, normalized data to the sheet
  var targetSheet = SpreadsheetApp.getActive().getSheets()[0];
  targetSheet.getRange(1, 1, normalizedRows.length, maxColumns).setValues(normalizedRows); 
}

Key Fixes Explained: #

• Filter Empty Lines: We strip out any lines that are blank or only contain whitespace—these were the main culprits creating single-column rows that caused the mismatch.
• Dynamic Column Count: Instead of hardcoding the column count to match the first row, we calculate the maximum number of columns present in any row of your TXT file.
• Normalize Row Lengths: Shorter rows get padded with empty strings to ensure every row has the same number of columns, so the sheet range and data array always align.

This should resolve the error and automate the conversion just like your manual import works.

内容的提问来源于stack exchange，提问作者BigBenne