I'm trying to get the middle initial from the middle name. Cell C12 in my script loads the middle name, while cell E12 loads the middle initial. To determine whether the text in cell C12 contains two words, I used the script chckWrd.includes(" ").
Example: Cell C12 is equals to "Dela Cruz", cell E12 must load the text "DC" upon entering.
Then I used 'chckWrd == ""' to determine whether cell C12 was empty. If it is empty, then cell E12 must likewise be empty.
The script runs fine, however it loads slowly. Is it possible to load it quickly or is there a shorter way for me to execute the script below?
function onEdit(e) {
var ss = e.source;
var cell = e.range;
// STUDENT MIDDLE NAME
if(cell.getA1Notation() === "C12" && ss.getActiveSheet().getName() == "UserForm"){
var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm = myGoogleSheet.getSheetByName("UserForm"); //declare a variable and set with the User Form worksheet
var chckWrd = shUserForm.getRange("C12").getValue();
if (chckWrd == "") { // check if cell is empty
shUserForm.getRange("E12").clear();
shUserForm.getRange("E12").setBackground('#FFFFFF').setFontFamily('Roboto').setFontSize('12').setHorizontalAlignment("center");
} else { // check if cell is not empty
if (chckWrd.includes(" ")) { // check if cell includes space
var wordArray = shUserForm.getRange("C12").getValue().split(" ");
var first = wordArray[0].substring(0,1);
var second = wordArray[1].substring(0,1);
var middleInitial = first + second;
Logger.log(middleInitial);
shUserForm.getRange("E12").setValue(middleInitial);
} else { // middle name has one word
var wordArray = shUserForm.getRange("C12").getValue().substring(0,1);
var first = wordArray[0].substring(0,1);
Logger.log(first); // first word
shUserForm.getRange("E12").setValue(first);
}
}
}
}
In general, to improve the performance of a Google Apps Script, you should try to reduce the number of calls to Google Apps Script methods.
In the specific case of onEdit simple trigger, you might reduce the execution time by taking advantage of the edit event object properties.
source: Active Spreadsheetrange: Edited rangevalue: The edited value. Note this property is undefind when the edited cell is cleared.Undocumented but known from a long time:
Examples:
Case 1
cell.getA1Notation() === "C12"
you might use
e.range.rowStart === 12 && e.range.columnStart === 3
Case 2
Instead of
var chckWrd = shUserForm.getRange("C12").getValue();
Consider using
e.value
There is no need to declare a variable; read the value.
Case 3
Instead of
chckWrd == ""
consider to use
e.value === undefined
Also, you might look for better alternatives for some methods, i.e., instead of
shUserForm.getRange("E12").clear();
shUserForm.getRange("E12").setBackground('#FFFFFF').setFontFamily('Roboto').setFontSize('12').setHorizontalAlignment("center");
consider to use
e.range.offset(0,2).clearContent();
The above option clears the cell content only, making it unnecessary to apply the cell background color, font settings and alignment.
I ran a pseudo benchmark (only one execution for each function). An accurate benchmark requires running hundreds of times, among other things, but this might be OK as a proof-of-concept.
| Function name | Execution Time (console.time()/console.timeEnd() |
|---|---|
| original | 1193 ms |
| proposal | 2 ms |
Note: The above times don't include the "transport time", the time it takes for Google servers to push the change to the user-on-keyboard web browser and the "repaint time", the time that it takes to the web browser to update the screen.
Code.gs
Change the index on const fn = name[1] to control which function runs.
function onEdit(e) {
const name = ['original', 'proposal']
const fn = name[1];
console.time(fn);
this[fn](e);
console.timeEnd(fn);
}
Original.gs
Only the function name was changed for benchmarking purposes.
function original(e) {
var ss = e.source;
var cell = e.range;
// STUDENT MIDDLE NAME
if(cell.getA1Notation() === "C12" && ss.getActiveSheet().getName() == "UserForm"){
var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet
var shUserForm = myGoogleSheet.getSheetByName("UserForm"); //declare a variable and set with the User Form worksheet
var chckWrd = shUserForm.getRange("C12").getValue();
if (chckWrd == "") { // check if cell is empty
shUserForm.getRange("E12").clear();
shUserForm.getRange("E12").setBackground('#FFFFFF').setFontFamily('Roboto').setFontSize('12').setHorizontalAlignment("center");
} else { // check if cell is not empty
if (chckWrd.includes(" ")) { // check if cell includes space
var wordArray = shUserForm.getRange("C12").getValue().split(" ");
var first = wordArray[0].substring(0,1);
var second = wordArray[1].substring(0,1);
var middleInitial = first + second;
Logger.log(middleInitial);
shUserForm.getRange("E12").setValue(middleInitial);
} else { // middle name has one word
var wordArray = shUserForm.getRange("C12").getValue().substring(0,1);
var first = wordArray[0].substring(0,1);
Logger.log(first); // first word
shUserForm.getRange("E12").setValue(first);
}
}
}
}
Proposal.gs
function proposal(e) {
// STUDENT MIDDLE NAME
if(e.range.rowStart === 12 && e.range.columnStart === 12 && e.source.getActiveSheet().getName() == "UserForm"){
const target = e.range.offset(0,2);
if (e.value === undefined) { // check if cell is empty
target.clearContent();
} else { // check if cell is not empty
if (e.value.includes(" ")) { // check if cell includes space
var wordArray = e.value.split(" ");
var first = wordArray[0].substring(0,1);
var second = wordArray[1].substring(0,1);
var middleInitial = first + second;
target.setValue(middleInitial);
} else { // middle name has one word
var wordArray = e.value.substring(0,1);
var first = wordArray[0].substring(0,1);
target.setValue(first);
}
}
}
}