如何跨选定的工作表选项卡运行菜单项脚本 [英] how to run a menu item script across selected sheet tabs

问题描述

我一直试图完成仅在某些工作表选项卡上运行此边框脚本的操作，但在所有这些选项卡上调用此操作均未成功.工作表标签如下.当前销售，待处理订单，转包订单，部分发货，报价，已存档的销售.

I have been trying to accomplish running this border script across only certain sheet tabs, and I have been unsuccessful at calling this across them. the sheet tabs are as follows. Current Sales, Pending Orders, Subcontract Orders, Partial Shipped, Quotes, Archived Sales.

function setborders() {
 var menu = [{name: "Borders for nonempty cells", functionName: "borders"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Set Borders", menu);
}

function borders() {
  var ss = SpreadsheetApp.getActive();
  var numsheets = ss.getNumSheets();
  var allsheets = ss.getSheets();
  for (var s in allsheets){
    var sheet=allsheets[s]
    var range = sheet.getRange("A2:V1000");
    var values = range.getValues();
    var offsetRow = range.getRowIndex();
    
    for(var i = 0; i < allsheets.length; i++) 
   if(['Current Sales','Pending Orders','Subcontract Orders','Partial Shipped','Quotes','Archived Sales'].indexOf(allsheets[i].getName())){
  
  if(sheet.getRange(range.getRow(), 1).getValue()) {
    range.setBorder(false, false, false, false, false, false);
    SpreadsheetApp.flush();
    for (var i = 0; i < values.length; i++) {
      if (values[i][0]) { // Only for non-empty A column cells
        sheet.getRange("A" + (i + offsetRow) + ":V" + (i + offsetRow)).setBorder(true, true, true, true, false, false, "black",SpreadsheetApp.BorderStyle.SOLID);
      }
    }
       
    for (i = 0; i < values.length; i++) {
      if (values[i][0] && values[i][14]) { 
        sheet.getRange("O" + (i + offsetRow) + ":O" + (i + offsetRow)).setBorder(true, true, true, true, true, true, "black",SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
    
    for (i = 0; i < values.length; i++) {
      if (values[i][0] && values[i][18]) { 
        sheet.getRange("S" + (i + offsetRow) + ":S" + (i + offsetRow)).setBorder(true, true, true, true, true, true, "black",SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
    for (i = 0; i < values.length; i++) {
      if (values[i][0] && values[i][19]) { 
        sheet.getRange("T" + (i + offsetRow) + ":T" + (i + offsetRow)).setBorder(true, true, true, true, true, true, "black",SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
    for (i = 0; i < values.length; i++) {
      if (values[i][0] && values[i][20]) { 
        sheet.getRange("U" + (i + offsetRow) + ":U" + (i + offsetRow)).setBorder(true, true, true, true, true, true, "black",SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
    for (i = 0; i < values.length; i++) {
      if (values[i][0] && values[i][21]) { 
        sheet.getRange("V" + (i + offsetRow) + ":V" + (i + offsetRow)).setBorder(true, true, true, true, true, true, "black",SpreadsheetApp.BorderStyle.SOLID_THICK);
      }
    }
  }
}
}
}

推荐答案

版本5

修复"无效，因为代码嵌套了for，它们都迭代了所有工作表.内部为建议条件，外部为没有条件.

Revision 5

The "fix" didn't work because the code has nested fors, both iterating through all sheets. The inner for has the suggested condition but the outer for not.

要将代码的某些部分的执行限制为特定的电子表格，您可以使用类表"的getName()方法，并将其与有效表的列表进行比较.一种方法是创建一个使用indexOf的有效工作表名称的数组.

To limit the execution certain parts of the code to specific spreadsheet you could use the getName() method of Class Sheet and compare it with the list of the valid sheets. One way of doing this is creating an array of the valid sheet names an use indexOf.

假设有效的工作表名称为Sheet1和Sheet3，并且sheets是由类似var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();之类的结果产生的所有工作表的集合，那么下面的代码将遍历所有工作表，但仅执行有效工作表中的一部分代码:

Let say that the valid sheet names are Sheet1 and Sheet3, and that sheets is a collection of all the sheets resulting from something like var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();), then the following code will iterate over all the sheets, but only execute one part of the code on the valid sheets:

for(var i = 0; i < sheets.length; i++) 
   if(['Sheet1','Sheet3'].indexOf(sheets[i].getName())){
     //Add here the code to be executed on the valid sheets
   }
}

这篇关于如何跨选定的工作表选项卡运行菜单项脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！