Google Apps Script复制数据时部分列显示#N/A的问题咨询

我用以下Google Apps Script将HOME工作表的数据复制到HISTORY工作表的最后一行：

function menuItem1() {
  var ss = SpreadsheetApp.getActive();
  var srcSheet = ss.getSheetByName("HOME");
  var dstSheet = ss.getSheetByName("HISTORY");

  var data_range = srcSheet.getRange('O4:FX100'); 
  var data_data = data_range.getValues();
  var data_clean  = data_data.filter(function (r) {return r[0]});

  var F4toF100 = srcSheet.getRange('F4:F100');
  var H4toH100 = srcSheet.getRange('H4:H100');
  var D3toD6 = srcSheet.getRange('D3:D6');

  var text1 = dstSheet.getRange('A2:EY100');
  var date2 = dstSheet.getRange('EZ2:EZ100');
  var text3 = dstSheet.getRange('FA2:FD100');
  var number4 = dstSheet.getRange('FE2:FE100');
  var number5 = dstSheet.getRange('FF2:FF100');
  var number6 = dstSheet.getRange('FG2:FG100');
  var text7 = dstSheet.getRange('FH2:FH100');
  var number8 = dstSheet.getRange('FI2:FJ100');

  var lr = dstSheet.getLastRow(); 

  dstSheet.getRange(lr+1, 1,data_clean.length,166).setValues(data_clean);

  text1.setNumberFormat("@");
  date2.setNumberFormat('dd/MM/yyyy');
  text3.setNumberFormat("@");
  number4.setNumberFormat('#.##');
  number5.setNumberFormat('%');
  number6.setNumberFormat('#.##');
  text7.setNumberFormat("@");
  number8.setNumberFormat('#.##');

  F4toF100.clearContent();
  H4toH100.clearContent();
  D3toD6.clearContent();    
}

源工作表行数为1-100，本次测试用51行数据。运行脚本后数据成功复制，但FF、FG、FJ列显示#N/A，这些列应分别为百分比、数字、数字格式。

有专家指出Range.setValues()需要二维数组，但filter函数将data_clean转为一维数组导致报错。不过我频繁使用该脚本，其他同格式工作表无此问题，想知道原因及解决办法。

原因分析 #

1. filter函数并未生成一维数组：data_data.filter(function(r) {return r[0]})只是过滤掉了O列（源数据第一列）为空的行，返回的仍是二维数组，这不是#N/A的直接诱因。
2. 数据错位或源数据异常：FF、FG、FJ列显示#N/A，大概率是复制时源数据列数与目标列数不匹配导致数据错位，把非数值内容放到了需要数字/百分比的列；或者源数据中对应位置本身就是#N/A错误值。
3. 其他工作表无问题的原因：那些工作表的源数据对应位置无错误值，且源数据列数与目标设置的166列完全匹配，没有出现错位情况。

解决办法 #

1. 修正数据复制的列数匹配问题 #

硬编码166列容易出现不匹配，改用源数据的实际列数设置目标范围：

// 替换原有的setValues行
var targetColCount = data_clean.length > 0 ? data_clean[0].length : 0;
dstSheet.getRange(lr+1, 1, data_clean.length, targetColCount).setValues(data_clean);

2. 处理源数据中的错误值 #

如果源数据存在#N/A，复制前将其替换为有效内容：

var data_clean = data_data.filter(function(r) {return r[0]}).map(function(row) {
  return row.map(function(cell) {
    // 把#N/A错误值替换为空或0，按需调整
    return cell instanceof Error ? '' : cell;
  });
});

3. 精准设置新增行的格式 #

避免对固定范围设置格式，只对新增的行应用格式：

var newRowStart = lr + 1;
// 获取列号，避免硬编码列名
var colFF = dstSheet.getRange('FF1').getColumn();
var colFG = dstSheet.getRange('FG1').getColumn();
var colFJ = dstSheet.getRange('FJ1').getColumn();

// 对新增行设置对应格式
dstSheet.getRange(newRowStart, colFF, data_clean.length, 1).setNumberFormat('%');
dstSheet.getRange(newRowStart, colFG, data_clean.length, 1).setNumberFormat('#.##');
dstSheet.getRange(newRowStart, colFJ, data_clean.length, 1).setNumberFormat('#.##');

4. 核对源数据列数 #

确认HOME工作表中O4:FX100的列数是否为166列，若不一致，调整目标范围的列数参数，确保数据完整对齐。

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