需要帮助使脚本适应一周中的多天

Question

我在另一篇文章里找到了这段代码。对于Google，它基本上是在"Status“选项卡中复制颜色格式，在”周一“复制匹配单元格中的颜色。

function colorCodeRevised() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()
  var lr=ss.getSheetByName("Monday").getLastRow() // get last row of sheet1
  var lc=ss.getSheetByName("Monday").getLastColumn() //get last column of sheet1
  var lr1=ss.getSheetByName("Status").getLastRow() // get last row of sheet2
  var lc1=ss.getSheetByName("Status").getLastColumn() ////get last column of sheet2
  var sv=ss.getSheetByName("Monday").getRange(5,2,1,lc-15).getValues() // get vehicles. startrow,startcolumn,numrows to return,numcolumns to return
  var sn=ss.getSheetByName("Monday").getRange(6,1,lr-5,1).getValues() // get names
  var s1=ss.getSheetByName("Status").getRange(2,1,lr1-2,lc1)//exclude legend. squarebackets is sheet numbermm 0=1, 1=2
  var rng1=s1.getValues() // get sheet2 data
  var rng2=s1.getBackgrounds() // get background colors of dheet2 data

var test= sn.length
var test1= sv.length
var test2=rng1[0].length

var col=1 //column for vehicles on sheet1
   for(var m=0;m<sv[0].length;m++){ //for each vehicle
      col=col+1 //add one to vehicle column
  for(var n=0;n<sn.length;n++){ //for each name
  for(var i=0;i<rng1.length;i++){ //loop sheet2 data
  for(var j=0;j<rng1[0].length;j++){
  
    if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name
       var c=ss.getSheetByName("Monday").getRange(n+6, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1
  }}}}}
}

我有一个非常类似的电子表格，但我正在努力使它不仅运行在星期一，但所有的日子。我可以做6个相同的脚本，但我觉得这将是太费劲了，让工作表运行，每次需要更改7个脚本。我想要被着色的细胞和星期一的模式/顺序完全一样。例如，如果周一的C6是红色的，那么周二的C6也应该是红色的。谁能帮我把这个脚本涂成和星期一一样的颜色，而不用花7倍的时间运行呢？

​

Answer 1

对于您的具体问题，我认为您真正需要做的就是添加一个循环，其中包含要修改的工作表列表。从这里开始，你可以把一切都变成一个变量。最后给出了这个解决方案。

不过，在我让您离开之前，我想建议您修改一下^_^

得到代码样本，并使它为您的工作表工作是伟大的！我喜欢这个概念，我在整理所有这些数据的过程中取得了进展。我做了一些改变(我认为)可能会帮你解决问题。

大多数这些更改都是为了将逻辑从脚本中移出并放入工作表中。在表格中使用公式的惊人之处在于，它们被Google很好地优化了。如果你能把你想要做的事情分解成谷歌已经实现的代码片段，事情就会变得更快。

建议的修改：

1. 状态页
   1. 更改为用户和角色矩阵。每个人扮演的角色都有一个x。将来，您可以基于表单输入来填充这个矩阵，如果这是您想要的。
   2. 将A1设置为包含正确为日页上的表着色所需的所有条件格式。
   3. 手动着色角色标头，因为我不想重新执行A1中的所有条件格式，而分层条件格式不是一件事。

​

1. 日页
   1. 颜色表中的每个单元格现在都有一个公式，该公式将根据该行中的用户在状态页上是否有该角色中的x来设置该角色的值。
   2. 根据这些单元格中的文本，对它们进行着色，使类具有选定的文本和背景颜色。

​

1. 脚本
   1. 可以在顶部配置要更改的页面列表和带有格式的单元格的位置。
      • 我希望我能够在脚本中创建格式规则，但是这个功能还没有实现。希望当Google到达本期时，它将被实现。

​

1. Grabs the cell with the formatting
2. Loops through each page
3. Gets the color table
4. Copies the formula (in the script) and the formatting to the table
5. Finally, it adds a custom menu at the top that can be used to call the function named "Script tools".

​

优势

• 您只需要将公式和格式复制到每个工作表一次。
• 当状态发生更改时，它们将立即反映在正确的页面上。

交替解

链接到我的工作表

链接可在驱动器中复制可编辑副本。

/* Loops through all whitelisted sheets and applies a pre-defined format to the table on each.
 * 
 * Michael Kenworthy 12/21/16
*/

//Set-up variables
var whitelist = ["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"]
var colorKeySheet = "Status"
var colorKeyFormatCell = "A1"
var colorTableRange = "B6:M"

var doc = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();

function formatting() {
  statusSheet = doc.getSheetByName(colorKeySheet);
  formatCell = statusSheet.getRange(colorKeyFormatCell);

  for(var i=0; i<whitelist.length; i++){
    sheet = doc.getSheetByName(whitelist[i])
    colorTable = sheet.getRange(colorTableRange);

    cell = colorTable.getCell(1, 1)
    cell.setFormula('if(not(isblank(INDIRECT("Status!R"&MATCH($A6,Status!$A:$A,0)&"C"&MATCH(B$5,Status!$1:$1,0),false))),B$5,)')
    formatCell.copyTo(cell, {formatOnly: true});

    cell.copyTo(colorTable)
  }
}

function onLoad(){
  // Or DocumentApp or FormApp.
  ui.createMenu('Sheet tools')
      .addItem('Apply formatting to day sheets', 'formatting')
      .addToUi();
}

原问题的解决

var whitelist = ["Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"]

function colorCodeRevised() {
  var ss=SpreadsheetApp.getActiveSpreadsheet()


  var lr1=ss.getSheetByName("Status").getLastRow() // get last row of sheet2
  var lc1=ss.getSheetByName("Status").getLastColumn() ////get last column of sheet2

  var s1=ss.getSheetByName("Status").getRange(2,1,lr1-2,lc1)//exclude legend. squarebackets is sheet numbermm 0=1, 1=2
  var rng1=s1.getValues() // get sheet2 data
  var rng2=s1.getBackgrounds() // get background colors of dheet2 data

  for(var sheetNum=0;sheetNum<whitelist.length;sheetNum++){
    var lr=ss.getSheetByName(whiltelist[sheetNum]).getLastRow() // get last row of sheet1
    var lc=ss.getSheetByName(whiltelist[sheetNum]).getLastColumn() //get last column of sheet1
    var sv=ss.getSheetByName(whiltelist[sheetNum]).getRange(5,2,1,lc-15).getValues() // get vehicles. startrow,startcolumn,numrows to return,numcolumns to return
    var sn=ss.getSheetByName(whiltelist[sheetNum]).getRange(6,1,lr-5,1).getValues() // get names

    var col=1 //column for vehicles on sheet1
    for(var m=0;m<sv[0].length;m++){ //for each vehicle
      col=col+1 //add one to vehicle column
      for(var n=0;n<sn.length;n++){ //for each name
        for(var i=0;i<rng1.length;i++){ //loop sheet2 data
          for(var j=0;j<rng1[0].length;j++){
            if(rng1[i][j].indexOf(sv[0][m])>-1 && rng1[i][j].indexOf(sn[n][0])>-1){ //if sheet2 data cell contains vehicle and name
              var c=ss.getSheetByName(whiltelist[sheetNum]).getRange(n+6, col).setBackground(rng2[i][j]) //set color of vehicle and name on sheet1
            }
          }
        }
      }
    }
  }
}