0

Question: How I can simplify this code for faster processing?

This is working, however it takes time to update the date answered column. If the internet isn't fast, it won't update especially because the original google sheet file has thousands of data in it 8k+.

Also, another thing that makes the original file slow is because of the conditional formatting. Columns C,D,N has different colors depending on what is selected while column E has this custom formula: =COUNTIF($E$1:$E,$E$1:$E)>1 this is so the sheet highlights same URL.

The onEdit is triggered when VALIDATION is changed (see appscript code)

Sheet with app script: https://docs.google.com/spreadsheets/d/1qsL7sDF1K-qX0hb0RsRPFMuFjj3yDJVg4WLVmxDwWxQ/edit?usp=sharing

just backing up the existing code here in case someone edits the script:

FormFormatting.gs

function onFormSubmit(e) { 

AllFormat(e);
Priority();

}

function AllFormat(e){

var SheetURL = "https://docs.google.com/spreadsheets/d/1qsL7sDF1K-qX0hb0RsRPFMuFjj3yDJVg4WLVmxDwWxQ/edit#gid=1406161645";
var ss = SpreadsheetApp.openByUrl(SheetURL).getSheetByName("Sheet1");


//TEXT WRAPPING
var Reason = ss.getRange('F2:F');
Reason.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);

var ReasonToCaseID = ss.getRange('H2:J');
ReasonToCaseID.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);

var CUFLink = ss.getRange('G2:G');
CUFLink.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);

var DateTimeToEmail = ss.getRange('A2:B');
DateTimeToEmail.setNumberFormat("@"); //Convert numbers as plain text
DateTimeToEmail.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);

var row = e.range.getRow();
e.source.getActiveSheet().getRange(row,17).setValue("PENDING");


//TEXT ALIGNMENT
var AllRange = ss.getRange("A2:Q");
AllRange.setVerticalAlignment("middle");

var AllCenter = ss.getRange("A2:A" && "F2:F" && "J2:K" && "M2:M" && "O2:Q");
AllCenter.setHorizontalAlignment("center");

var AllLeft = ss.getRange("B2:E" && "G2:I" && "L2:L" && "N2:N");
AllLeft.setHorizontalAlignment("left");

var CUFRight = ss.getRange('G2:G');
CUFRight.setHorizontalAlignment("right");

}


function Priority() {
  //sort by priority

  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var rows = sheet1.getLastRow();
  var cols = sheet1.getLastColumn();
  //the selected range below doesn't include the header and is working fine
  sheet1.getRange(2,1, rows -1, cols).sort(3);
  
}

DateAnswered.gs

function onEdit(e) {
var row = e.range.getRow();
var ws = "Sheet1";
if(row > 1 && e.source.getActiveSheet().getName() === ws)
{addDateAnswered(e);}
AllFormat(); //on FormFormatiing.gs
}

function addDateAnswered(e){
var row = e.range.getRow();
var validity = e.source.getActiveSheet().getRange(row,14).getValue();

if(validity == "VALID" || validity == "INVALID")
{setDateAnswered(e);}
else if(validity == "DUPLICATE" || validity == "NFA")
{e.source.getActiveSheet().getRange(row,17).setValue(validity)}
else
{e.source.getActiveSheet().getRange(row,17).setValue("PENDING");}

//I think the formatting code below isn't working onEdit
var AllRange = ws.getRange("A2:P");
AllRange.setVerticalAlignment("middle");

var AllCenter = ws.getRange("A2:A" && "F2:F" && "J2:K" && "M2:M");
AllCenter.setHorizontalAlignment("center");

var TestLeft = ws.getRange("O");
TestLeft.setHorizontalAlignment("left");
}


function setDateAnswered(e){

var row = e.range.getRow();
var col = e.range.getColumn();
  //this is how I like the time stamp formatted (this is working):
var TimeStamp = Utilities.formatDate(new Date(), 'GMT+8:00', 'MM/dd/yyyy hh:mm:ss a');

//column 14 "Validity" to set column 16 "Date Answered if valid or invalid only"
if(col === 14 && row > 1)
{e.source.getActiveSheet().getRange(row,17).setValue(TimeStamp);}
}
  • 1
    The question need more more focus as it includes several functions. Choose one, remove the others and add a brief description of your search/research efforts as is suggested in How to Ask – Rubén Jul 26 at 1:41
  • 2
    the original file has a lot of data? Would you please edit your question to provide some specific details to quantify this? – Tedinoz Jul 27 at 5:25
  • How I can simplify this code for faster processing? – KB John Jul 28 at 15:07