无论如何，以加快以下在Google表格脚本中的代码? [英] Anyway to speed up the following code in google sheets script?

问题描述

我对使用Google脚本进行编码非常陌生，因此您可能会看下面的代码，并因其当前的效率低下而摇头.简而言之，这段代码正在检查单元格A5至D5的值，并查看其在主列表中是否具有与E5和F5相同的值.如果不是，则将A5-D5复制到另一个工作簿上的两个不同列表中.该代码目前有效，但我想知道是否还有任何方法可以加快速度?它主要由Ipad上的IOS表格应用程序触发(因此为什么我必须使用onEdit而不是按钮来触发).当检查它是否已经在列表中时，它在同一工作簿中的本地工作表上，该工作表通过外部工作簿中的公式进行更新.您能提供的任何帮助将不胜感激.就像我说的，尽管我是编码的新手，但是当我遇到问题时，我还是通过Google的帮助将其拼凑在一起.

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

推荐答案

Google提供了

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. 通过将返回值放入变量来减少调用SpreadsheetApp.getActive()或SpreadsheeApp.openById()的次数.

var ss = SpreadsheetApp.getActive();
var externalSS = SpreadsheetApp.openById("links to external workbook2");

通过使用已经创建的变量sheet1减少调用SpreadsheetApp.getActive().getSheetByName("sheet1")的次数.您可以使用find&全部替换以解决此问题.

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.

使用

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

true和false是javascript中的常量，因此无需将其放入变量True&中. False.

通过将其放置在变量中来减少调用new Date()的次数；

true and false are constants in javascript, so no need to place them into variables True & False.

Reduce the number of times you call new Date() by placing it in a variable;

var now = new Date();

简化到第3点，改为使用

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.

这篇关于无论如何，以加快以下在Google表格脚本中的代码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！