如果列具有特定值，则使用谷歌脚本更改同一行中单元格的值 [英] Change value of cell in same row with google script if column has certain value

问题描述

我正在使用谷歌表格为产品开发一个简单的跟踪工具.顶行包含从 1 到 13 的产品组件.当所有组件都检查到时，该行会自动将 R 列中的就绪状态更改为就绪.灰色区域用于确定哪些组件不属于某些产品.

I am working on a simple tracking tool for products with google Sheets. The top row has components ranging from 1 to 13 for a product. When all the components are checked as arrived the row automatically changes the Ready status in column R to ready. The grey areas are to determine which components do not belong to certain products.

这是文档.https://docs.google.com/spreadsheets/d/18iX8LgfPaDRJC8L6f4xU1U​​9Gw8eiBINNFPT9r0Ow/edit?usp=sharing"?usp=共享

这可以正常工作，但是我希望在就绪状态更改后显示时间戳.

This works ok, however I would like to have a timestamp to appear once the Ready status changes.

我找到了一段代码并对其进行了编辑以几乎满足我的需要.

I found a piece of code and edited it to almost suit my needs.

function onEdit(event) {
var eventRange = event.range;
if (eventRange.getColumn() == 18) { // 18 == column R
var columnARange = SpreadsheetApp.getActiveSheet().getRange(eventRange.getRow(), 19,eventRange.getNumRows(), 19); // 19 == column S
var values = columnARange.getValues();
for (var i = 0; i < values.length; i++) {
   values[i][0] = new Date();
}
columnARange.setValues(values);
}
}

问题是因为它是一个 OnEdit 函数，如果我希望时间戳出现，我必须手动编辑指定的范围.在我在 R 列中的1"上方链接的示例中，生成时间戳，但在划掉所有空框后将单元格更改为就绪"的公式不会.

The problems is since it is an OnEdit function I have to manually edit the range specified if I want the timestamp to appear. In the example I linked above the "1"s in the R-column produce the timestamp but the formula that changes the cell to "Ready" once all empty boxes are crossed out, does not.

有没有办法编辑函数来查看工作表，如果在 R 列中找到就绪"一词，则在 S 列中的旁边会出现一个时间戳?

Is there any way to edit the function to look at the the sheet and if the word "Ready" is found in the R-column, a time stamp would apear next to it in the S-column?

这个论坛已经帮助了我很多次，为此我感谢所有贡献者.

This forum has helped me many times already and for that I thank everyone contributing.

更新

我在这里找到了这段代码 https://productforums.google.com/forum/#!topic/docs/3Iz9y9OSLMk

I found this piece of code here https://productforums.google.com/forum/#!topic/docs/3Iz9y9OSLMk

function emailSendingTrigger() {
var sheet = SpreadsheetApp.getActiveSheet();

if (sheet.getName() == "Sheet Name") {

var activeCell = sheet.getRange("F21");

 if (activeCell.getValue() <500) { 

 emailBALANCETE();



}
}

}

是否可以更改它以查看范围内的每个单元格并在单元格值更改为就绪"时触发时间戳?

Could it be changed so that it looks at each cell in a range and triggers a timestamp if the cell value changes to "ready"?

类似的东西

For each cell in range
If value "Ready"
Offset.1 select.cell
set.value New Date()

现在我知道这绝对是不正确的，但也许可以让我了解我的目标.

Now I know that is definitely not correct but maybe gives some insight as to what I am going for.

托蒂

推荐答案

公式和脚本的结合使用意味着它并不像它想象的那么简单.如果您使用脚本来执行所有更改，则可以在脚本的其余部分运行时添加时间戳.

The combined use of formula and scripts means it's not as straightforward as it could be. If you were using a script to perform all the changes, you could just add a timestamp when the rest of the script runs.

实际上，一种解决方案是让脚本每隔几分钟运行一次，以检查R"列的值是否为就绪"，而S"列的值是否为就绪"，如果为真 = 添加当前时间戳.

As it is, one solution would be to have a script that runs every few minutes to check if column 'R' has the value 'Ready' and column 'S' has no timestamp, and if that's true = Add a current timestamp.

一个简单但不雅的方法是:

A simple but inelegant way to do this would be:

function timestamp(){

  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow()

  for (var i = 1; i <= lastRow; i++){

    var readyRange = sheet.getRange([i], 18);
    var readyValues = readyRange.getValues();

    var timeRange = sheet.getRange([i], 19);
    var timeValues = timeRange.getValues();

    if (readyValues == 'Ready' && timeValues == ''){ // 
      timeRange.setValue(new Date());

      }
    }
}

这将遍历电子表格中的所有行并检查它们是否需要时间戳.然后您可以设置一个时间触发器来运行每过一段时间.

This will run through all the rows in the spreadsheet and check if they need a timestamp. You can then set a time trigger for this to run every once in a while.

这适用于小型电子表格，但我已在几分钟内将其整合为一个解决方案，因此如果您打算将其用于数千行，则需要提高效率.

This works fine for small spreadsheets, but I've thrown it together as a solution in a few minutes, so you'll need to make it more efficient if you intend to use it with several thousand rows.

这篇关于如果列具有特定值，则使用谷歌脚本更改同一行中单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！