google-apps-script - 使用 GAS batchUpdate 将数据从 Google 表格自定义表单上的特定单元格复制到同一电子表格中不同工作表上的特定行

Question

我又在折腾了。最后一个问题涉及从电子表格写入值的加速：工作表行到自定义表单（源自工作表）上的特定单元格。这一次，我试图做相反的事情......将表单上单元格列表中的数据复制到数据表上的特定行。将数据“推送”到数组的语句起作用。然后，当我运行无法弄清楚的 batchUpdate 代码（如下）时出现错误。任何帮助，将不胜感激。...或者我应该恢复到从数组到数据表的逐项复制（约 5 秒）？

“错误
GoogleJsonResponseException：对 sheet.spreadsheets.values.batchUpdate 的 API 调用失败并出现错误：'data[0]' (type.googleapis.com/google.apps.sheets.v4.ValueRange) 处的值无效，“5693123-Q5aa” 'data[1]' 处的值无效 (type.googleapis.com/google.apps.sheets.v4.ValueRange), "Fredi" 'data[2]' 处的值无效 (type.googleapis.com/google.apps. sheet.v4.ValueRange), "Yeast" ...后面是每个数据单元格的另外 53 行类似消息。”enter code here

//** Function to copy data from the specific cells of the User form ("User Contact Info Form") to the spreadsheet sheet ("VolunteerListTbl") into Row# (DatabaseRow2Update) */
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 LastColNumber = DataSheet.getLastColumn(); 

  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"]; //List of cells containing data to be updated on the "VolunteerListTbl" sheet

  //Routine to create the array of values to be used to update the sheet's row values
  var FormValuesArray = [];
  for (var j=0; j<RangesToUpdate.length; j++){ //this loop will add each successive value for the cells listed above...to fill the array (1x56 values)
    FormValuesArray.push(ShUserForm.getRange(RangesToUpdate[j]).getValue());
    
    Logger.log("Array value for j= " + j + "; is " + ShUserForm.getRange(RangesToUpdate[j]).getValue() + " for the range " + RangesToUpdate[j])
  }
  
  DataSheet.getRange(DatabaseRow2Update,1,1,LastColNumber).activate();//Added this to specify the row where the data was to be updated
  Sheets.Spreadsheets.Values.batchUpdate({data: FormValuesArray, valueInputOption: "USER_ENTERED"}, Ss.getId());

  return  
}

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-api

Answer 1

我相信你的目标如下。

• RangesToUpdate您希望将“用户联系信息表单”工作表中的范围内的值复制到工作表“VolunteerListTbl”中的相同范围内。
• 您想使用 Sheets API 来实现这一点。

当我看到你的脚本时，FormValuesArray.push(ShUserForm.getRange(RangesToUpdate[j]).getValue());是循环使用的。在这种情况下，处理成本变高。为此，我想建议使用 Sheets API 检索值。

而且，关于你的这个脚本Sheets.Spreadsheets.Values.batchUpdate({data: FormValuesArray, valueInputOption: "USER_ENTERED"}, Ss.getId());，它似乎FormValuesArray是一个像["value1", "value2",,,]. 电子表格.values.batchUpdate 方法的请求体是{"data": [{"range": "", "values": []}]}. 参考我认为这可能是您的问题的原因。在您的请求正文中，需要设置范围和值属性。

当以上几点反映到您的脚本时，它变成如下。

修改后的脚本：

function Ex_UpdateRecord() {
  var Ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = Ss.getId();
  var srcSheetName = "User Contact Info Form";
  var dstSheetName = "VolunteerListTbl";
  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"];

  // Retrieve values from "dstSheetName".
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: RangesToUpdate.map(e => `'${srcSheetName}'!${e}`) });
  
  // Create a request body.
  var FormValuesArray = values.valueRanges.map((e, i) => ({ range: `'${dstSheetName}'!${RangesToUpdate[i]}`, values: e.values }));

  // Put the values to "VolunteerListTbl".
  Ss.getSheetByName(dstSheetName).getRange(DatabaseRow2Update, 1, 1, LastColNumber).activate();
  Sheets.Spreadsheets.Values.batchUpdate({ data: FormValuesArray, valueInputOption: "USER_ENTERED" }, ssId);
}

• 在此修改后的脚本中， “用户联系信息表单”表中的范围被复制到“VolunteerListTbl”表中RangesToUpdate的相同范围。RangesToUpdate

参考：

• 方法：电子表格.values.batchGet
• 方法：电子表格.values.batchUpdate

添加1：

从您的以下回复中，

来自源（“用户联系信息表”）和目标的数据字段的数量是相同的。不同的是床单上的位置。例如，表单上的单元格 C11 复制到目标工作表（“VolunteerListTbl”）上的第 1 列，表单上的单元格 F10 复制到“VolunteerListTbl”上的第 2 列......等等。表格上的排列是由于允许有凝聚力显示的分组。RangesToUpdate（源）与目标表上的单元格的顺序相同。感谢您努力澄清。

我以为您想将这些值放在“VolunteerListTbl”表中的一行中。在这种情况下，下面的修改脚本怎么样？

修改后的脚本：

function Ex_UpdateRecord() {
  var Ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = Ss.getId();
  var srcSheetName = "User Contact Info Form";
  var dstSheetName = "VolunteerListTbl";
  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"];

  // Retrieve values from "dstSheetName".
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: RangesToUpdate.map(e => `'${srcSheetName}'!${e}`) });
  
  // Create an array for putting to spreadsheet.
  var FormValuesArray = values.valueRanges.map(e => e.values[0][0]);

  // Put the values to "VolunteerListTbl".
  var dstSheet = Ss.getSheetByName(dstSheetName);
  dstSheet.getRange(1, 1, 1, FormValuesArray.length).setValues([FormValuesArray]);
  // or dstSheet.appendRow(FormValuesArray);
}

• 在此脚本中，检索User Contact Info Form到的值被放入工作表的一行VolunteerListTbl。
• setValues在这种情况下，使用和放置这些值appendRow。
• 当您想将该行附加到目标工作表时，请使用dstSheet.appendRow(FormValuesArray).

添加2：

当我看到您的示例电子表格时，我注意到您的目标工作表名称是VolSearchResult Tbl. 但是您的脚本使用VolunteerListTbl. 我认为这可能是您的问题的原因。而且，我测试了我的示例脚本，反映了我评论中建议的修改，我确信没有发生错误。为此，请测试以下脚本。

示例脚本：

function Ex_UpdateRecord() {
  var Ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = Ss.getId();
  var srcSheetName = "User Contact Info Form";
  var dstSheetName = "VolSearchResult Tbl";
  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"];

  // Retrieve values from "dstSheetName".
  var values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: RangesToUpdate.map(e => `'${srcSheetName}'!${e}`) });
  
  // Create an array for putting to spreadsheet.
  var FormValuesArray = values.valueRanges.map(e => e.values && e.values[0] ? e.values[0][0] : ""); // This modification is from my comment.

  // Put the values to "VolSearchResult Tbl".
  var dstSheet = Ss.getSheetByName(dstSheetName);
  dstSheet.appendRow(FormValuesArray);
}