title: Enhancing Performance in Student Info App
body: <p>I've built a simple Student Information platform using <code>Google Sheets.</code> It allows the user to query, update and create new student info on a user interface. Please refer to <a href="https://drive.google.com/open?id=1PVoMoz9MDTH5TI9pYS90aIbFh8PR2sVfja86XCtK3uU" rel="nofollow noreferrer">this sheet</a> to see how it works. </p>
  
  <p>The functions to <code>Refresh/Update/Save</code> are inside the Actions button on a menu bar. Everything seems to work well however when the number of records increases, say above 100 records, all the functions slow down and it gets extremely slow with 200+ records. </p>
  
  <p>Appreciate if anyone could help to take a look at the scripts as I suspect they need to be optimized. </p>
  
  <p>Many thanks in advance!</p>
  
  <pre><code>function UpdateDataIntoMaster() { //This script is used in the SAVE button in UPDATE sheet)
  
  /*Get data from UPDATE Sheet*/
    var ss = SpreadsheetApp.openById("11Djp9UmXbtWv7VitZFfo0X4Ctet3O8Amh4xADNKOZgY");
    var sheet = ss.getSheetByName('UPDATE');
    var range = sheet.getRange("D30:AE30"); //All data transposed into this line. MUST be updated if more fields are added into the Data sheet
    var values = range.getValues();
    var rangeForKey =  sheet.getRange("D30") //Student Name is used as the 
  key identifier
    var keyValue = rangeForKey.getValue(); 
  
  /*Pass in keyValue(identifier = Student Name)
    and all data in the function below in order 
    to update master data sheet*/
    updDbase(keyValue,values);
  
  function updDbase(keyValue,values) {
    var ss = SpreadsheetApp.openById("11Djp9UmXbtWv7VitZFfo0X4Ctet3O8Amh4xADNKOZgY")
    var sheet = ss.getSheetByName('Data');
    var data = sheet.getDataRange().getValues();
    var noOfRow = values.length
    var noOfCol = values[0].length
  
      for (var i=0; i &lt; data.length; i++) { // going through all the rows in Data sheet
          var keyData = ss.getSheetByName("Data").getRange(i+1,1).getValue(); //Get the Student Name from Data sheet
          if (keyData == keyValue) {      
       //   for (var j=0; j &lt; data[i].length; j++) { // this is going through all the cell of a row
             var row = Number(i)+1;
  
             var sh = SpreadsheetApp.getUi();
             var response = sh.alert("Update Information","Are you sure you want to update the student information?", sh.ButtonSet.YES_NO);
             if (response == sh.Button.YES)
              {
                var sheets = ss.getSheetByName("Data").getRange(row,1,noOfRow,noOfCol).setValues(values);      
              }//If  response == YES
  
     }           
    }        
   }  
  }
  
  function CreateNew() {
  
  /*Get data from Inquiry Sheet*/
    var ss = SpreadsheetApp.openById("11Djp9UmXbtWv7VitZFfo0X4Ctet3O8Amh4xADNKOZgY");
    var sheetNew = ss.getSheetByName('Create New');
    var range = sheetNew.getRange("D30:AZE30"); //All data transposed into 
  this line
    var values = range.getValues();
    var rangeForKey =  sheetNew.getRange("E30") //Using Student ID as key identifier
    var keyValue = rangeForKey.getValue(); 
    var noOfRow = values.length
    var noOfCol = values[0].length
    var sheetData = ss.getSheetByName('Data');
    var lastRow = sheetData.getLastRow();
    var data = sheetData.getDataRange().getValues();
  
      for (var i=0; i &lt; data.length; i++) { // going through all the rows in Data sheet
          var keyData = sheetData.getRange(i+1,2).getValue(); //Get the Student ID from Data sheet
          if (keyData == keyValue) {      
             AlertBox();//If Student ID is found, to prompt Student ID already 
  exist
             return; 
           } //If
       }    //For
  /*Confirming with user whether to proceed to create new entry*/       
         var sh = SpreadsheetApp.getUi();
         var response = sh.alert("Create New Record","Are you sure you want to 
  create new student information?", sh.ButtonSet.YES_NO);
         if (response == sh.Button.YES){
           if (keyValue == ""){ 
               var response = sh.alert("Create New Record","Unable to proceed 
  because Student ID is empty", sh.ButtonSet.OK);
               return;}
            else {
                //var response = sh.alert("Create New Record","Unable to 
  proceed because Student ID is empty", sh.ButtonSet.OK);
                var sheets = 
  sheetData.getRange(lastRow+1,1,1,noOfCol).setValues(values) 
                 }
           }//If
  }  
  
  function EditStudentInfo() {
   var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inquiry");  
   //var ss = SpreadsheetApp.getActive();
    var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i &lt; protections.length; i++) {
      var protection = protections[i];
       if (protection.canEdit()) {
         protection.remove();
       }
  }
  }
  
  function EditContent() {
  
       var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Update");
  
       var rangeContentCol1 = s.getRange("E3:E23");
       var CopyContentCol1  = s.getRange("E3:E23").getValues();
       var rangeContentCol2 = s.getRange("I3:I23");
       var CopyContentCol2  = s.getRange("I3:I23").getValues();
  
       rangeContentCol1.clearContent();
       rangeContentCol2.clearContent();
  
       var PasteContentCol1  = 
  s.getRange("E3:E23").setValues(CopyContentCol1); 
       var PasteContentCol2  = s.getRange("I3:I23").setValues(CopyContentCol2); 
  }
  </code></pre>