谷歌电子表格设置基于笔记的行背景颜色 [英] google spreadsheet set row background color based on note

问题描述

我有一个Google电子表格，里面有客户联系信息。

我试图找到一种突出显示用户自动编辑的方式，而不必更改我想要做的是用户编辑一个单元格时，用户的电子邮件地址添加一个笔记到该单元格中。然后检查它是否添加到该单元格中的注释等于某个电子邮件地址，以将该行的背景颜色设置为我指定的值。

以下将用户电子邮件地址添加到编辑的单元格中，并将编辑单元格的背景颜色设置为红色而不是整行。

关于如何操作的任何想法这是，如果有更好的方式来实现我想要做的。

  function onEdit（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 
 var cell = ss.getActiveCell（）; 
 var range = ss.getActiveRange（）; 
 var note = cell.getNote（）; 
 var user = Session.getUser（）; 
 
 note = user; 
 cell.setNote（note）; 
 
 
 
 if（note =[e-mail]）{
 range.setBackgroundRGB（255，0，0）; 
} 
 
}; 
  

解决方案

要获取整行，假设它从1开始，并使用 Range.getLastColumn（）方法查找该行的右侧范围。 （如果你的行是错开的，这可能会突出显示任何一端的空单元格，但检查它会进一步减慢函数的执行速度。）

  function onEdit（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 
 var cell = ss.getActiveCell（）; 
 var range = sheet.getRange（cell.getRow（），1,1，sheet.getDataRange（）。getLastColumn（））; 
 var note = cell.getNote（）; 
 var user = Session.getUser（）; 
 
 note = user; 
 cell.setNote（note）; 
 
 if（note =[e-mail]）{
 range.setBackgroundRGB（255，0，0）; 
} 
}; 
  

您还问过一个更好的方法。这里有一些改进，有一些改进。

• 它使用触发器事件，而不是依靠容器绑定脚本。 （请参阅了解活动）。这使得它更具可移植性，当然，但它也意味着您可以免除对电子表格服务的一些（缓慢）调用，因为您可以免费获得信息。

  
  
  

  要测试此功能，请使用我如何测试GAS中的触发器功能？。
  

• 您的原始脚本包含 if-then 来检查哪个用户进行了更改，代码块会随着您添加更多用户而增长。这个采用数据驱动的方式，使用 const userColors 。然后，我们可以在比较中检查用户是否被知道，并对此作出反应。

  
  
  

  你可以通过管理电子表格或其他地方的用户颜色进一步改进它 - 甚至让它动态地学习新用户并为其分配自己的颜色。无论如何，这可以让你轻松地添加更多的用户&颜色组合，而不需要改变函数中的逻辑。

非常关心表现。 （这个例子很短，这不是一个真正的问题，但仍然是一个好习惯。）这个版本有一些改变来解决这个问题。首先，检查是否需要设置颜色 - 如果正在编辑的单元由同一用户最后编辑，则不需要执行其他任何操作。其次，一些调用Spreadsheet Services的操作已被移动，因此它们只在需要时执行。


更新的脚本：

  function onEdit（event）{
 const userColors = {
'[e-mail]'：'red'， 
'[e-mail]'：'blue'，
'[e-mail]'：'＃ff00ff'
}; 
 
 var note = event.range.getNote（）; 
 var user = new String（Session.getUser（））; 
 
 //更新注释和颜色（如果新编辑器）
 if（note！= user）{
 var sheet = event.range.getSheet（）; 
 var range = sheet.getRange（event.range.getRow（），1,1，sheet.getLastColumn（））; 
 
 event.range.setNote（user）; 
 
 if（userColors中的用户）range.setBackground（userColors [user]）; 
 //不着色未知用户
 else range.setBackground（'white'）; 
} 
}; 
  

I have a Google spreadsheet that has client contact information in it.

I am trying to find a way to highlight edits users make automatically without them having to change the row color to say that they edited it.

What I was thinking of doing is when a user edits a cell is to add a note to that cell with the users email address. Then have it check to see if the note that was added to that cell equals a certain email address to set the background color of the row to whatever I specify.

The below adds a note of the users email address to the edited cell and sets the background color of the edited cell to red but not the entire row.

Any ideas on how to do this and if there is a better way to accomplish what I am trying to do.

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var cell = ss.getActiveCell();
  var range = ss.getActiveRange();
  var note = cell.getNote();
  var user = Session.getUser();

  note = user;
  cell.setNote(note);



  if(note = "[e-mail]") {
    range.setBackgroundRGB(255, 0, 0);
  }

};

解决方案

To get the entire row, assume it starts at 1, and use the Range.getLastColumn() method to find the right-extent of the row. (If your rows are staggered, this may highlight empty cells at either end - checking for that, though, will slow down the function further.)

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();

  var cell = ss.getActiveCell();
  var range = sheet.getRange(cell.getRow(),1,1,sheet.getDataRange().getLastColumn());
  var note = cell.getNote();
  var user = Session.getUser();

  note = user;
  cell.setNote(note);

  if(note = "[e-mail]") {
    range.setBackgroundRGB(255, 0, 0);
  }
};

You also asked about a "better way". Here's a variation with a few 'improvements'.

• It uses the trigger Event, rather than relying on being a container-bound script. (See Understanding Events.) That makes this more portable, sure, but it also means that you can eliminate some (slow) calls to Spreadsheet services, since you're handed information for free.

  To test this function, use the technique described in How can I test a trigger function in GAS?.

• Your original script contained an if-then to check which user had made a change, the beginning of a code block that would grow as you added more users. This one takes a data-driven approach, with const userColors. Then, we can check whether the user is known by an in comparison, and act on that.

  You could further improve it by managing the user colors in the spreadsheet, or elsewhere - and even having it dynamically learn new users and assign them their own colors. At any rate, this allows you to easily add more user & color combinations, without needing to change the logic in the function.

• In onEdit() functions, you should be very concerned about performance. (This example is so short, it's not really an issue, but still a good habit.) This version has a couple of changes to address that. First, there is a check to see if we need to set color at all - if the cell being edited was last edited by the same user, there's no need to do anything else. Second, a number of operations that invoke Spreadsheet Services have been moved so they only execute when needed.

Updated script:

function onEdit(event) {
  const userColors = {
    '[e-mail]' : 'red',
    '[e-mail]' : 'blue',
    '[e-mail]' : '#ff00ff'
  };

  var note = event.range.getNote();
  var user = new String(Session.getUser());

  // update note and color if new editor
  if (note != user) {
    var sheet = event.range.getSheet();
    var range = sheet.getRange(event.range.getRow(),1,1,sheet.getLastColumn());

    event.range.setNote(user);

    if (user in userColors) range.setBackground(userColors[user]);
    // don't color unknown users
    else range.setBackground('white');
  }
};

这篇关于谷歌电子表格设置基于笔记的行背景颜色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！