i am fairly new to coding in google scripts so the you will probably look at the code below and shake your head with its current inefficiency haha. in short this code is checking the value of cells A5 to D5 and seeing if its in the master list with the same values of E5 and F5. if its not it then copies A5-D5 to two different lists on another workbook. the code works at the moment but i was wondering if there is anyway to speed it up? its going to be mainly triggered by the IOS sheets app on an Ipad.(hence why i had to use onEdit instead of a button to trigger). when its checking to see if its already on the list, its on a local sheet in the same workbook that updates via a formula from the external workbook. Any help you could provide would be greatly appreciated. like i said though I'm new to coding i managed to piece this one together from the help of google when i ran into an issue.
function onEdit(e) {
var sheet1 = SpreadsheetApp.getActive().getSheetByName("sheet1")
var SiteName = sheet1.getRange("A5").getValue()
var Type = sheet1.getRange("B5").getValue()
var Fleet = sheet1.getRange("C5").getValue()
var Rego = sheet1.getRange("D5").getValue()
var Inside = sheet1.getRange("E5").getValue()
var Outside = sheet1.getRange("F5").getValue()
var User = sheet1.getRange("A3").getValue()
var master = SpreadsheetApp.getActive().getSheetByName("Master Wash List");
var True = true
var False = false
var mastermaster = SpreadsheetApp.openById("links to external workbook2").getSheetByName("master")
var mastertolocal = SpreadsheetApp.openById("links to external workbook2").getSheetByName("MasterToLocal")
var sheet = SpreadsheetApp.getActive().getSheetByName("Master Wash List");
var lastrow = sheet.getLastRow()
var SubmitButton = SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("G5").getValue()
if(Inside == True) {
for(var x = 1; x < lastrow+1;x++) {
var cell = master.getRange(x,3).getValue()
var masterinside = master.getRange(x, 4).getValue()
var masteruser = master.getRange(x,6).getValue()
if(Fleet == cell && masterinside != "N/A"){
SpreadsheetApp.getActive().getSheetByName("sheet1").insertRowAfter(6)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange(7,1).setValue([cell + " has already been Internally washed on " + masterinside + " by " + masteruser])
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("G5").setValue("false")
}
}
}
if(Outside == True) {
for(var y = 2; y < lastrow+1;y++) {
var cell = master.getRange(y,3).getValue()
var masteroutside = master.getRange(y, 5).getValue()
var User1 = master.getRange(y, 6).getValue()
if(Fleet == cell && masteroutside != "N/A"){
SpreadsheetApp.getActive().getSheetByName("sheet1").insertRowAfter(6)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange(7,1).setValue([cell + " has already been externally washed on " + masteroutside + " by " + User1])
SpreadsheetApp.getActive().getSheetByName("sheet1").deleteRows(17,2)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("F5").setValue("false")}
}
}
if(SubmitButton == True){
if(Inside == True && Outside == True && Fleet != "" ) {
mastermaster.appendRow([SiteName, Type, Fleet, Rego, new Date(), new Date(), User,])
mastertolocal.appendRow([SiteName, Type, Fleet, Rego, "N/A", new Date(), User])
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("E5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("F5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("G5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("A5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("B5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("C5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("D5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").insertRowAfter(6)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange(7,1).setValue([Fleet + " has successfully been Washed Internally & Externally."])
SpreadsheetApp.getActive().getSheetByName("sheet1").deleteRows(17, 2)}
else if(Outside == True && Fleet != "" ) {
mastermaster.appendRow([SiteName, Type, Fleet, Rego,"N/A", new Date(), User])
mastertolocal.appendRow([SiteName, Type, Fleet, Rego, "N/A", new Date(), User])
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("F5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("G5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("A5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("B5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("C5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("D5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").insertRowAfter(6)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange(7,1).setValue([Fleet + " has successfully been Washed Externally."])
SpreadsheetApp.getActive().getSheetByName("sheet1").deleteRows(17, 2) }
else if(Inside == True && Fleet != "") {
mastermaster.appendRow([SiteName, Type, Fleet, Rego, new Date(), "N/A", User])
mastertolocal.appendRow([SiteName, Type, Fleet, Rego, new Date(), "N/A", User])
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("E5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("G5").setValue("false")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("A5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("B5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("C5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("D5").setValue("")
SpreadsheetApp.getActive().getSheetByName("sheet1").insertRowAfter(6)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange(7,1).setValue([Fleet + " has successfully been Washed Internally."])
SpreadsheetApp.getActive().getSheetByName("sheet1").deleteRows(17, 2)}
else if(Inside != True && Outside != True && Fleet != "") {
SpreadsheetApp.getActive().getSheetByName("sheet1").insertRowAfter(6)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange(7,1).setValue(["Please select if " + Fleet + " Has been washed Internally, Externally or Both"])
SpreadsheetApp.getActive().getSheetByName("sheet1").deleteRows(17, 2)
SpreadsheetApp.getActive().getSheetByName("sheet1").getRange("F5").setValue("false")}
}
}