Anyway to speed up the following code in google sh

2019-08-30 17:22发布

问题:

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")}
  }
}

回答1:

Google has provided a Best Practices resource that you should definitely review. From there, the most relevant section is about using batch operations. Not specific to Google Apps Script, but a good practice, is to reduce some of the repetition in your code (see DRY principle). Here are some specific notes that you can apply to your entire script:

  1. Reduce the number of times you call SpreadsheetApp.getActive() or SpreadsheeApp.openById() by placing the returned value into a variable.

    var ss = SpreadsheetApp.getActive();
    var externalSS = SpreadsheetApp.openById("links to external workbook2");
    
  2. Reduce the number of times you call SpreadsheetApp.getActive().getSheetByName("sheet1") by using the variable that you already created sheet1. You can use find & replace-all to fix this.

  3. Reduce the number of times you call getValue() by using getValues() instead and accessing the array values.

    var row5 = sheet1.getRange(1, 1, 1, 7).getValues(); // A5:G5
    var SiteName = row5[0][0]; // A5
    var Type = row5[0][1]; // B5
    var Fleet = row5[0][2]; // C5
    var Rego = row5[0][3]; // D5
    var Inside = row5[0][4]; // E5
    var Outside = row5[0][5]; // F5
    
  4. true and false are constants in javascript, so no need to place them into variables True & False.
  5. Reduce the number of times you call new Date() by placing it in a variable;

    var now = new Date();
    
  6. Similary to point 3, reduce the number of times you call setValue() by instead using setValues().

    row5.setValues([ // row5 previously defined A5:G5
      [""], // A5
      [""], // B5
      [""], // C5
      [""], // D5
      [false], // E5
      [false], // F5
      [false] //G5
    ]);
    

The examples I wrote above are just examples, but they are based on your code. You'll need to go through your entire script and figure out exactly how to apply these principles without breaking the functionality of your script.