在执行Google表格的应用脚本代码时遇到的问题 [英] Facing issues while executing the App Scripts code for Google Sheets

问题描述

以下脚本遇到了一些问题.工作表名称为仪表板". refreshSheet()下的第一段工作不正常.问题是:

I am facing few issues with the following scripts. Here the sheet name is 'Dashboard'. First paragraph under refreshSheet() is not working fine. The issues is:

每次运行代码时，以下代码都会对A1:K4产生两种保护(仪表板！A1:K4和仪表板！A1:K4).每次应该只做一个保护.

The following code is producing two protections of A1:K4 each time I run the code (Dashboard!A1:K4 and Dashboard!A1:K4). Should do only one protection each time.

请检查代码如下:

   function onEdit(e){  
      if (e.range.getA1Notation() === 'C6' && e.range.getValue() === "Start 1-Period") {  
        refreshSheet();
        onePeriod();  
      }
      if (e.range.getA1Notation() === 'C6' && e.range.getValue() === "Start 2-Period") {    
        refreshSheet();
        twoPeriod();  
      }   
    }
    
    function refreshSheet() {   
      //For protecting dashboard while scripts running
      var spreadsheet = SpreadsheetApp.getActive();
      var dashboard = spreadsheet.getSheetByName("Dashboard");
      var rangem = dashboard.getRange('A1:K4');   
      var protectionm = rangem.protect();
      var me = Session.getEffectiveUser();
      protectionm.addEditor(me);
      protectionm.removeEditors(protectionm.getEditors());
      if (protectionm.canDomainEdit()) {
          protectionm.setDomainEdit(false);
      }
     
      // Other codes are here which are working fine        
    
      protectionm.remove();
 }

推荐答案

您同时具有多个触发器runnig-这就是refreshSheet()运行多次的原因.

首先:

You have several triggers runnig simultaneously - this is the reason why refreshSheet() is running several times.

First of all:

请勿同时使用简单且可安装的onEdit触发器-这将导致重复执行和冲突.

do NOT use the simple and installable onEdit trigger simultaneously - this leads to double execution and conflicts.

• 在您的特定情况下，简单的onEdit触发器无法正确执行(因为它没有必要的权限).

• In your specific situation the simple onEdit trigger does not execute correctly (because it does not have the encessary permissions).

但是您应该删除简单的触发器-通过将function onEdit(e)重命名为不是保留关键字的任何其他名称即可轻松完成-例如function Edit(e).

You should however remove the simple trigger - this can be easily done by renaming function onEdit(e) to any other name that is not a reserved key word - e.g. function Edit(e).

您可安装的触发器仍将运行-因为您已将其手动绑定到该功能.

You installable trigger will still run - since you manually bound it to the function.

从执行日志来看，可安装的onChange trigger似乎与onEdit触发器同时运行，我可以从中假设您将其绑定到相同的功能-这会使您的代码执行两次.

From your execution logs it seems that the installable onChange trigger runs at the same time like the onEdit trigger from which I can assume that you bound it to the same function - this makes your code execute twice.

相反，如果要实现多个触发器，则应将每个触发器绑定到不同的函数.

Instead, in case of implementing multiple triggers, you should bind each to a different function.

从您提供的代码片段中，我看不到您需要使用onChange触发器-也许您是以前方法的其余部分.

From the code snippet you provided I cannot see your need to use an onChange trigger - maybe you it is a remainder from previous approaches.

请继续执行Edit-> Current Projetc's triggers，查看所有已安装的触发器soyu，删除不必要的触发器，并确保其余触发器已绑定到不同的功能.

Please go on Edit-> Current Projetc's triggers , review all trigger soyu have installed, delete the unnecessary ones and make sure that the remaining ones are bound to different functions.

第二:

• 要验证是否重复执行代码部分是由于函数的两次调用-通过从脚本编辑器手动运行功能进行测试.
• 为此，只需从下拉菜单中选择正确的功能(在您的情况下为refreshSheet()，然后单击运行"按钮(三角形)即可.

• To verify either the double execution of a code part is due to a double call of a function - test by running the funciton manually from the script editor.
• For this, simply chose the correct funciton from the dropdown menu (in your case - refreshSheet() and click on the run button (the triangle).

这篇关于在执行Google表格的应用脚本代码时遇到的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！