使用GAS batchUpdate复制Sheet自定义表单单元格到另一行时ValueRange报错求助

• 你调用Sheets.Spreadsheets.Values.batchUpdate时传入的data参数不符合API格式要求：该接口要求data是由ValueRange对象组成的数组，每个ValueRange必须包含range（目标写入区域）和values（二维数组格式的写入内容）两个字段，而你直接将收集到的单元格原始值一维数组传给了data，类型完全不匹配，因此抛出了类型错误。

不需要换回逐个单元格复制的方案，以下两种优化方案的执行速度都远快于5秒的逐个写入方案，通常耗时可控制在1秒以内。

方案一：使用SpreadsheetApp原生方法（推荐） #

不需要启用Sheets高级服务，代码更简洁，出错概率更低：

// 从用户表单指定单元格复制数据到数据表指定行
function Ex_UpdateRecord(){
  var Ss = SpreadsheetApp.getActiveSpreadsheet();
  var ShUserForm = Ss.getSheetByName("User Contact Info Form");
  var DataSheet = Ss.getSheetByName("VolunteerListTbl");
  var DatabaseRow2Update = ShUserForm.getRange("A11").getValue();
  // 待读取的表单单元格列表
  var RangesToUpdate = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14", "F15", "I10", "I12", "I14", "I16", "F17", "B19", "F20", "I20", "F22", "I22", "F24", "C27", "B22", "C23", "B24", "B26", "F28", "C29", "B28", "B34", "B32", "G32", "G36", "C36", "B40", "B42", "E40", "H40", "E42", "H42", "B44", "E44", "H44", "E46", "H46", "C48", "B52", "B54", "F52", "B56", "F54", "F56", "F11", "C17",  "D19", "D20"]; 

  var FormValuesArray = [];
  for (var j=0; j<RangesToUpdate.length; j++){
    FormValuesArray.push(ShUserForm.getRange(RangesToUpdate[j]).getValue());
  }
  // 将一维数组转为二维数组（单行结构），批量写入目标行
  DataSheet.getRange(DatabaseRow2Update, 1, 1, FormValuesArray.length).setValues([FormValuesArray]);
}

方案二：修正batchUpdate参数格式 #

如果需要使用Sheets高级服务的batchUpdate接口，需要按照API要求构造参数：

function Ex_UpdateRecord(){
  var Ss = SpreadsheetApp.getActiveSpreadsheet();
  var ShUserForm = Ss.getSheetByName("User Contact Info Form");
  var DataSheet = Ss.getSheetByName("VolunteerListTbl");
  var DatabaseRow2Update = ShUserForm.getRange("A11").getValue();
  var RangesToUpdate = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14", "F15", "I10", "I12", "I14", "I16", "F17", "B19", "F20", "I20", "F22", "I22", "F24", "C27", "B22", "C23", "B24", "B26", "F28", "C29", "B28", "B34", "B32", "G32", "G36", "C36", "B40", "B42", "E40", "H40", "E42", "H42", "B44", "E44", "H44", "E46", "H46", "C48", "B52", "B54", "F52", "B56", "F54", "F56", "F11", "C17",  "D19", "D20"]; 

  var FormValuesArray = [];
  for (var j=0; j<RangesToUpdate.length; j++){
    FormValuesArray.push(ShUserForm.getRange(RangesToUpdate[j]).getValue());
  }

  // 构造符合API要求的参数
  var data = [{
    range: `VolunteerListTbl!A${DatabaseRow2Update}:${columnToLetter(FormValuesArray.length)}${DatabaseRow2Update}`,
    values: [FormValuesArray]
  }]
  Sheets.Spreadsheets.Values.batchUpdate({data: data, valueInputOption: "USER_ENTERED"}, Ss.getId());
}

// 辅助函数：列索引转列字母
function columnToLetter(column){
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

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