GAS method for Efficiently writing data to a custom bound form in Google Sheets-CodePudding

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
}

I'm looking a method to accelerate the processing speed. The whole source spreadsheet is 56 columns of data with records of personal data in each row. The search from one sheet and write to the search results sheet runs pretty quickly. The REALLY slow part of the process is writing the data from the search results sheet to the form (you'll note that the order on the destination form is not the same sequence as the sheet data). Generating the array of data (read from the sheet record) proceeds at a decent speed. Writing the array data to the form (all 56 fields a few constant values I use for other operations) measured at 54 seconds. As I understand it, the 'batch' method is only good for string data (there are few fields of numerical data and dates in the records). If you can get me pointed in the right direction on this problem, I would appreciate it. Thanks for any help in advance.

//This function copies the data of the current Google Sheets record to the User Form //** Abbreviated version of the function I'm using to copy data from a Google sheet to a custom form (created on another Google sheet) in the same spreadsheet. */

CodePudding user response：

I believe your goal is as follows.

You want to reduce the process cost of your script.

In this case, I would like to propose using Sheets API and the range list for your script. When your script is modified, please modify it as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services. Ref

From:

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();
}

To:

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');

Modified script:

From:

To:

References: