如果输入的日期已经在Google表格中的受保护范围内，如何避免执行Apps脚本代码块 [英] How to avoid a block of Apps Script codes execution if an input date is already there in a protected range in Google Sheets

问题描述

我必须满足两个条件才能执行代码块:

I have to satisfy two conditions to execute a block of codes:

1. 如果没有该特定日期的数据，它将不会执行突出显示的代码块.帖子中的最后30行代码]；条件是if(dates.indexOf(startDate)！= -1){并且工作正常.
2. 如果Google表格中受保护的数据范围中已经存在输入日期，则它也不会执行相同的突出显示的代码块.

输入日期始终是当前日期，因此，我认为应该仅在最后一个受保护的数据范围内进行搜索.

The input dates are always the current date and therefore, I think it should be searched only in the last protected range of data.

如果refreshSheet()和onePeriod()将一个接一个地执行，如何构建第二个条件并将其添加到第一个条件?还要检查附件中的图像以更好地了解问题.

How to build the second condition and add it to the first condition provided that refreshSheet() and onePeriod() will execute one after another? Check the attached image also to know the issue better.

    function onePeriod(){
      // For a single Period Class
      var spreadsheet = SpreadsheetApp.getActive();
      var dashboard = spreadsheet.getSheetByName("Dashboard");
      var sheetName = dashboard.getRange("A4").getValue();
      //retrieve the start date to use as desired
      var startDate = dashboard.getRange("C4").getDisplayValue();
      var endDate = dashboard.getRange("D4").getDisplayValue();
      var sheet = spreadsheet.getSheetByName(sheetName);
      //chose the range within the specified dates, for this first locate the date column
      var startRow = 2;
      var dateColumn = sheet.getRange(startRow,1,sheet.getLastRow(), 1);
      var dates = dateColumn.getDisplayValues().flat();
      var firstRow = dates.indexOf(startDate)+startRow;
      var lastRow = dates.lastIndexOf(endDate)+startRow;
      //now get the range between (and including) those rows
      var range = sheet.getRange(firstRow, 1, lastRow-firstRow+1, sheet.getLastColumn());
      
      //Sorting and removing duplicates
      // You need to specify by which column you want to sort your data, in this sample it it column 3 - that it column C  
      

**if(dates.indexOf(startDate) != -1){  
        var column = 3;
        range.sort({column: column, ascending:true});
        range.removeDuplicates([column]);
      
        //now delete empty rows if any
        var deleteRows = 0;  // <--- Added
        for (var i = range.getHeight(); i >= 1; i--){
            if(range.getCell(i, 1).isBlank()){
               sheet.deleteRow(range.getCell(i, 1).getRow());
               deleteRows++;
            }
        }
        
        //Protecting data 
        var timeZone = Session.getScriptTimeZone();
        var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
        var me = Session.getEffectiveUser();
        var description = 'Protected on ' + stringDate + ' by ' + me;
        var height = range.getHeight();
        var newHeight = height+1;
        var newRange = sheet.getRange(firstRow, 1, newHeight-deleteRows, sheet.getLastColumn());
      
        var protection = newRange.protect().setDescription(description);
        newRange.getCell(newHeight-deleteRows, 2).setValue(height-deleteRows + ' Students, Signed by ' + me).offset(0, -1, 1, 6).setBackground('#e6b8af');
        //protection.setDomainEdit(false);
        protection.addEditor(me);
        protection.removeEditors(protection.getEditors());
        if (protection.canDomainEdit()) {
          protection.setDomainEdit(false);
        }  
      }**
}

推荐答案

• 您要创建一个附加条件来运行代码块
• 条件是range没有受到保护
• 要评估这种情况，您可以使用
  • You want to create an additonal condition for running a code block
  • The condition is that range is not protected
  • To assess this condition, you can retrieve all protected ranges of the sheet with sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE), loop through them and verify either they overlap with the current range (e.g. contain startRow)
  • 如果存在对range的保护，请将布尔变量设置为true
  • 在if条件中使用布尔值
  • Set a boolean variable to true if a protection for range exists
  • Use the boolean inside your if condition

  示例:

  ...
    var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    var protected = false;
    for (var k = 0; k < protections.length; k++){
      var protection = protections[k];
      if(firstRow >= protection.getRange().getRow() && firstRow <= protection.getRange().getLastRow()){
        //range is already protected
        protected = true;
        break;
      }
    }   
    if(dates.indexOf(startDate) != -1 && protected == false){
  ...
  }
  

  这篇关于如果输入的日期已经在Google表格中的受保护范围内，如何避免执行Apps脚本代码块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！