App Script running slow.

48 views
Skip to first unread message

E Browning

unread,
Jun 24, 2022, 8:03:45 AMJun 24
to Google Apps Script Community
Hi, I have a script to move information from an input form to a data tab within a google sheet. At a very high level, the user will input the information into the form and they press a button to save the data. At that point, the script will do a number of validation checks such as checking for required fields. The script then copies specific cells from the form and pastes it into the data tab. It will then reset the form by deleting the inputs and adding back any formulas or formatting that might have been deleted (there are many users). The script is running quite slow (4-5 seconds) and I am looking for help to optimize it. Any thoughts on where I might speed this up? I appreciate any guidance or help on this.



//Function to validate the required fields
function validateData(){

  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet();
  var inputFormSheet = myGooglSheet.getSheetByName("InputForm");
  var range = inputFormSheet.getRange('C13');
  var numbOfGuests = range.getValue();
  var ui = SpreadsheetApp.getUi();

  if(inputFormSheet.getRange('C4').isBlank() == true){
    ui.alert("Please enter a date");
    inputFormSheet.getRange('C4').activate();
    return false;
  } 
  else if(inputFormSheet.getRange('C5').isBlank() == true){
    ui.alert("Please enter a time");
    inputFormSheet.getRange('C5').activate();
    return false;
  }
  else if(inputFormSheet.getRange('D5').isBlank() == true){
    ui.alert("Please select PM or AM");
    inputFormSheet.getRange('D5').activate();
    return false;
  }
  else if(inputFormSheet.getRange('C6').isBlank() == true){
    ui.alert("Please enter a valid name or choose one from dropdown");
    inputFormSheet.getRange('C6').activate();
    return false;
  }
  else if(inputFormSheet.getRange('C9').isBlank() == true){
    ui.alert("Please select a number of Adult Members from dropdown");
    inputFormSheet.getRange('C9').activate();
    return false;
  }
  else if(inputFormSheet.getRange('C10').isBlank() == true){
    ui.alert("Please select a number of Dependent Members from dropdown");
    inputFormSheet.getRange('C10').activate();
    return false;
  }
  else if(inputFormSheet.getRange('C13').isBlank() == true){
    ui.alert("Please select a number of Guests");
    inputFormSheet.getRange('C13').activate();
    return false;
  }
  else if(numbOfGuests != 0 && inputFormSheet.getRange('C14').isBlank() == true){ 
    ui.alert("Please select a valid type of Guest and enter at least Guest 1 Name");
    inputFormSheet.getRange('C14').activate();
    return false;
  }
  else if(numbOfGuests != 0 && inputFormSheet.getRange('C15').isBlank() == true){ 
    ui.alert("Please enter at least Guest 1 Name");
    inputFormSheet.getRange('C15').activate();
    return false;
  }
  return true;
}

//Funtion to save the data
function save()
{
  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 
  var inputFormSheet = myGooglSheet.getSheetByName("InputForm"); //delcare a variable and set with the InputForm worksheet
  var signInSheet = myGooglSheet.getSheetByName("SignInSheet"); ////delcare a variable and set with the SignInSheet worksheet

  //To copy paste formulas
  var dateFormula = inputFormSheet.getRange('F4:G19');
 
  if(validateData() == true){
    
    //Validating the entry. If validation is true then proceed with transferring the data to Database sheet
    var blankRow = signInSheet.getLastRow()+1//identify the next blank row

    signInSheet.getRange(blankRow1).setValue(inputFormSheet.getRange("C4").getValue()); 
    signInSheet.getRange(blankRow2).setValue(inputFormSheet.getRange("C5").getDisplayValue());
    signInSheet.getRange(blankRow3).setValue(inputFormSheet.getRange("D5").getValue()); 
    signInSheet.getRange(blankRow4).setValue(inputFormSheet.getRange("D6").getValue()); 
    signInSheet.getRange(blankRow5).setValue(inputFormSheet.getRange("C6").getValue()); 
    signInSheet.getRange(blankRow6).setValue(inputFormSheet.getRange("C9").getValue()); 
    signInSheet.getRange(blankRow7).setValue(inputFormSheet.getRange("C10").getValue());
    signInSheet.getRange(blankRow8).setValue(inputFormSheet.getRange("C11").getValue());
    signInSheet.getRange(blankRow9).setValue(inputFormSheet.getRange("C13").getValue());
    signInSheet.getRange(blankRow10).setValue(inputFormSheet.getRange("C14").getValue());
    signInSheet.getRange(blankRow11).setValue(inputFormSheet.getRange("C15").getValue());
    signInSheet.getRange(blankRow12).setValue(inputFormSheet.getRange("C16").getValue());
    signInSheet.getRange(blankRow13).setValue(inputFormSheet.getRange("C17").getValue());
    signInSheet.getRange(blankRow14).setValue(inputFormSheet.getRange("C18").getValue());
    signInSheet.getRange(blankRow15).setValue(inputFormSheet.getRange("C19").getValue());

    //Clearing the data from the Data Entry Form

    inputFormSheet.getRange("C4:C19").clear(); //Data
  
    //Copy and paste formulas, etc from a "clean" section - fix any formulas that might have been deleted
    dateFormula.copyTo(inputFormSheet.getRange('C4:D19'));

    //Set every field with font size 18 after clear the fields
    inputFormSheet.getRange("D5").setFontSize(18); //pm / am
    inputFormSheet.getRange("C5").setFontSize(18).setNumberFormat('##":"00'); //Time
    inputFormSheet.getRange("C6:C19").setFontSize(18);//Mamber name to Guest name
  }

}

Edward Ulle

unread,
Jun 25, 2022, 7:55:52 AMJun 25
to Google Apps Script Community
The best way to improve performance is to minimize getValue()/setValue() calls.  When possible use getValues()/setValues().  I typically make on call to get all the values from a sheet var values = inputForm.getDataRange().gatValues();.  I know which row and column I want so I index into the array values[row-1][column-1].

I'm not able to test the following script but it shows how I would merge getValue()/setValue() calls.

//Function to validate the required fields
function validateData(){

  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet();
  var inputFormSheet = myGooglSheet.getSheetByName("InputForm");
  // Row 1 = index 0, Row 13 = index 12
  // Column A = index 0, Column C = index 2
  var values = inputFormSheet.getDataRange().getValues();  // values[Row][Column]
  var numbOfGuests = values[12][2];  // C13
  var ui = SpreadsheetApp.getUi();

  if( values[3][2] == "" ) {
    ui.alert("Please enter a date");
    inputFormSheet.getRange('C4').activate();
    return false;
  } 
  else if( values [4][2] == "" ) {
    ui.alert("Please enter a time");
    inputFormSheet.getRange('C5').activate();
    return false;
  }
  else if( value[4][3] == "" ) {
    ui.alert("Please select PM or AM");
    inputFormSheet.getRange('D5').activate();
    return false;
  }
  else if( values[5][2] == "" ) {
    ui.alert("Please enter a valid name or choose one from dropdown");
    inputFormSheet.getRange('C6').activate();
    return false;
  }
  else if( values[8][2] == "" ) {
    ui.alert("Please select a number of Adult Members from dropdown");
    inputFormSheet.getRange('C9').activate();
    return false;
  }
  else if( values [9][2] == "" ) {
    ui.alert("Please select a number of Dependent Members from dropdown");
    inputFormSheet.getRange('C10').activate();
    return false;
  }
  else if( values[12][2] == "" ) {
    ui.alert("Please select a number of Guests");
    inputFormSheet.getRange('C13').activate();
    return false;
  }
  else if( ( values[13][2] == "" ) && ( numbOfGuests != 0 ) ) { 
    ui.alert("Please select a valid type of Guest and enter at least Guest 1 Name");
    inputFormSheet.getRange('C14').activate();
    return false;
  }
  else if( ( values[14][2] == "" ) && ( numbOfGuests != 0 ) ) { 
    ui.alert("Please enter at least Guest 1 Name");
    inputFormSheet.getRange('C15').activate();
    return false;
  }
  return true;
}

//Funtion to save the data
function save()
{
  var myGooglSheet = SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 
  var inputFormSheet = myGooglSheet.getSheetByName("InputForm"); //delcare a variable and set with the InputForm worksheet
  var signInSheet = myGooglSheet.getSheetByName("SignInSheet"); ////delcare a variable and set with the SignInSheet worksheet

  //To copy paste formulas
  var dateFormula = inputFormSheet.getRange('F4:G19');
 
  if(validateData() == true){
    
    var values = inputForm.getDataRange().gatValues();
    //Validating the entry. If validation is true then proceed with transferring the data to Database sheet
    var blankRow = signInSheet.getLastRow()+1; //identify the next blank row
    var valueC5 = inputFormSheet.getRange("C5").getDisplayValue();
    let row = values.map( (row,index) => { if( ( index > 12 ) && ( index < 19 ) ) row[2] } );  // This will get C13:C19
    // [C4,C5,D5,D6,C6,C9,C10,C11].concat([C13,C14,C15,C16,C17,C18,C19])
    row = [values[3][2],valueC5,values[4][3],values[5][3],values[5][2],values[8][2],values[9][2],values[10][2]].concat(row);  

    signSheet.getRange(blankRow,1,1,values.length).setValues([row]);

E Browning

unread,
Jun 25, 2022, 4:12:28 PMJun 25
to Google Apps Script Community
This is a great suggestion. Thank you! I will work on making these changes!

Again, I really appreciate your help
Reply all
Reply to author
Forward
0 new messages