时间戳

首页 > 解决方案 > 用于在 Google 表格中将数据高效写入自定义绑定表单的 GAS 方法

google-apps-script - 用于在 Google 表格中将数据高效写入自定义绑定表单的 GAS 方法

问题描述

function Ex_WriteVolDataToForm(){

  var Ss=SpreadsheetApp.getActiveSpreadsheet();
  var ShUserForm=Ss.getSheetByName("User Contact Info Form");//User Interface Form
  var Search_str = ShUserForm.getRange("C5").getValue();//Searches for Last Name (Entered in 'C5' on the user form)
  var SearchRowDisplayed = ShUserForm.getRange("A13").getValue();//The row # of the "VolSearchResult Tbl" record that is currently displayed on the form
  var NumberOfSearchFindsLeft = ShUserForm.getRange("C10").getValue();//=n if there are 'n' records to display from the search function; ="0" if NO search Records
  var SearchResultDatasheet=Ss.getSheetByName("VolSearchResult Tbl");//Destination Table of data for Name search
  var LstSearchColNumber = SearchResultDatasheet.getLastColumn(); 
  var start = new Date(); //This line before operation begins

  ResetDataForm(); //Clears all data from 'User Contact Info Form'

  //Re-enter the 'hidden' data cleared by the 'ResetDataForm' fcn
  ShUserForm.getRange("C10").setValue(NumberOfSearchFindsLeft);//Re-enters in C10 the number of records found from the "VolSearchResult Tbl" or "" [Null] if NOT from "VolSearchResult Tbl" 
  ShUserForm.getRange("A13").setValue(SearchRowDisplayed);//Re-enters number of the row displayed from the "VolSearchResult Tbl" (to A12) after being cleared by ResetDataForm function
  ShUserForm.getRange("C5").setValue(Search_str);//Re-enters Last Name searched (to C5) after being cleared by ResetDataForm function


  var FormRangesToSetValues = ["C11", "F10", "B10", "B14", "B16", "B12", "C17", "F14"]

  var RowValues = SearchResultDatasheet.getRange(SearchRowDisplayed,1,1,LstSearchColNumber).getValues();//Creates an Array 1x56 items 

  for (var i=0; i<LstSearchColNumber; i++) { //adjusted for the starting column is '0', so the last column (index) is LstColNumber-3 NOTE: Excludes cells 'BB3' and 'BC' from routine
    ShUserForm.getRange(FormRangesToSetValues[i]).setValue(RowValues[0][i]);// Sequentially sets values in form cells from corresponding values in the 'RowValues' array

    if (RowValues[0][i]!="") { //True if there IS a value
      ShUserForm.getRange(FormRangesToSetValues[i]).setBackground('#dff3ef'); //Sets Cell Bkg color for all item Not equal to "" to lt. green (ALL form cells with data turn Lt. Green)
    }
    SpreadsheetApp.flush();
  }
ShUserForm.activate();//Takes user to the 'Volunteer Information Form'
return //Exit if only one record found
}

我正在寻找一种加快处理速度的方法。整个源电子表格有 56 列数据，每行记录个人数据。从一张表中搜索并写入搜索结果表的速度非常快。该过程中真正缓慢的部分是将数据从搜索结果表写入表单（您会注意到目标表单上的顺序与表单数据的顺序不同）。生成数据数组（从工作表记录中读取）以相当快的速度进行。将数组数据写入表单（所有 56 个字段 + 我用于其他操作的一些常数值），测量时间为 54 秒。据我了解，“批处理”方法仅适用于字符串数据（记录中的数字数据和日期字段很少）。如果你能让我在这个问题上指出正确的方向，我会很感激。感谢您提前提供任何帮助。

//此函数将当前 Google 表格记录的数据复制到用户表单 //** 我用来将数据从 Google 表格复制到自定义表单（在另一个 Google 表格上创建）的函数的缩写版本同一个电子表格。*/

标签： google-apps-scriptgoogle-sheets

解决方案

我相信你的目标如下。

  • 您想降低脚本的处理成本。

在这种情况下，我想建议为您的脚本使用 Sheets API 和范围列表。当您的脚本被修改时，请按如下方式进行修改。

修改后的脚本：

在使用此脚本之前，请在高级 Google 服务中启用 Sheets API。参考

从：

for (var i=0; i<LstSearchColNumber; i++) { //adjusted for the starting column is '0', so the last column (index) is LstColNumber-3 NOTE: Excludes cells 'BB3' and 'BC' from routine
  ShUserForm.getRange(FormRangesToSetValues[i]).setValue(RowValues[0][i]);// Sequentially sets values in form cells from corresponding values in the 'RowValues' array

  if (RowValues[0][i]!="") { //True if there IS a value
    ShUserForm.getRange(FormRangesToSetValues[i]).setBackground('#dff3ef'); //Sets Cell Bkg color for all item Not equal to "" to lt. green (ALL form cells with data turn Lt. Green)
  }
  SpreadsheetApp.flush();
}

至：

var data = [];
var rangeList = [];
for (var i = 0; i < LstSearchColNumber; i++) {
  data.push({range: `'User Contact Info Form'!${FormRangesToSetValues[i]}`, values: [[RowValues[0][i]]]});
  if (RowValues[0][i] != "") {
    rangeList.push(FormRangesToSetValues[i]);
  }
}
Sheets.Spreadsheets.Values.batchUpdate({data: data, valueInputOption: "USER_ENTERED"}, Ss.getId());
ShUserForm.getRangeList(rangeList).setBackground('#dff3ef');

参考：

推荐阅读