如何在Google工作表编辑器脚本中循环getRange("B" +行+“:" +"J" +行) [英] How to loop getRange("B" + row + " :" + "J" + row) in google sheet editor script

问题描述

function onEdit(evt) {

var range = evt.range;

showAutoValue(range);

}

function showAutoValue(range){

  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = spreadSheet.getActiveSheet();

  var row = range.getRow();

  var column = range.getColumn();

  if(column == 1 && row >= 1){

    var Avalue = activeSheet.getRange("A" + row).getValue();

    var B_Jrange = activeSheet.getRange("B" + row + ":" + "J" + row);

    if(String(Avalue).trim()){
      for(var i = 0; i < B_Jrange.length; i++){
        if(String(B_Jrange[i].getValue()).trim().toLowerCase() == "change moi"){
          B_Jrange[i].setBackground(Avalue);
        }
      }
    }
  }

}

推荐答案

使用onEdit()选择和突出显示范围

好吧，我想了更多，我决定将columnA设为白色，黄色，浅绿色，浅蓝色的数据验证列表，因为您必须牢记要生成onEdit触发器，您实际上必须进行一些更改.因此，在这种情况下，您要将范围e.range.getSheet().getRange(e.range.rowStart，2,1,8)的背景颜色更改为在该行的A列中选择的值.因此，无需循环即可完成此操作.

Okay I thought about this a bit more and I decided to make columnA a data validation list of white,yellow,lightgreen,lightblue because you must keep in mind that in order to generate the onEdit trigger you must actually change something. So in this case you're changing the background color of the range e.range.getSheet().getRange(e.range.rowStart,2,1,8) to the value that's selected in column A of that row. So no loop is required to accomplish this.

function onEdit(e){
  var sh=e.range.getSheet();
  if(e.range.columnStart==1){
    if(String(sh.getRange(e.range.rowStart,1).getValue()).trim()){
      sh.getRange(e.range.rowStart,2,1,8).setBackground(sh.getRange(e.range.rowStart,1).getValue()); 
    }else{
      sh.getRange(e.range.rowStart,2,e.range.rowEnd,8).setBackground('white').getValue(); 
    }
  }
}

如果您选择了A列的所有内容并按回车键，则此版本也会清除整个范围，它将A列设置为无选择，并且if/else语句的第二部分将清除整个范围的背景.

This version also clears the entire range if you select all of column A and hit return it sets column A to no selection and the second part of the if/else statement clears background of the entire range.

这篇关于如何在Google工作表编辑器脚本中循环getRange("B" +行+“:" +"J" +行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！