如果列具有一定的值，请使用google脚本更改同一行中单元格的值 [英] Change value of cell in same row with google script if column has certain value
问题描述
我正在为使用Google表格的产品开发简单的跟踪工具。对于产品，最上面一行的组件范围从1到13。当所有组件检查到达时，该行自动将列R中的就绪状态更改为就绪。灰色区域用于确定哪些组件不属于某些产品。
这个工作正常，但是我希望一旦就绪状态发生变化就会出现时间戳。
我找到了一段代码并对其进行了编辑，几乎可以满足我的需求。
function onEdit（event）{
var eventRange = event.range;
if（eventRange.getColumn（）== 18）{// 18 ==列R
var columnARange = SpreadsheetApp.getActiveSheet（）。getRange（eventRange.getRow（），19，eventRange.getNumRows ），19）; // 19 ==列S
var values = columnARange.getValues（）;
for（var i = 0; i< values.length; i ++）{
values [i] [0] = new Date（）;
}
columnARange.setValues（values）;
$ b $ p
$ b 问题在于它是一个OnEdit函数，我有如果我想要显示时间戳，手动编辑指定的范围。在上面链接的例子中，R列中的1产生时间戳，但是一旦所有空白框被划掉，将公式单元格更改为就绪的公式不会。
是否有任何方法可以编辑函数以查看工作表，并且如果在R列中找到单词Ready，时间戳会在S列中出现在它旁边？
这个论坛已经帮了我很多次了，为此我感谢每个人的贡献。
更新
我在这里找到了这段代码 https ：//productforums.google.com/forum/#！topic / docs / 3Iz9y9OSLMk
函数emailSendingTrigger（） {
var sheet = SpreadsheetApp.getActiveSheet（）;
if（sheet.getName（）==Sheet Name）{
var activeCell = sheet.getRange（F21）;
if（activeCell.getValue（）<500）{
emailBALANCETE（）;
}
}
}
是否可以更改它，以便它查看范围内的每个单元格，并在单元格值更改为就绪时触发时间戳记？
类似于
对于范围
中的每个单元如果值Ready
Offset.1 select.cell
set.value新日期（）
现在我知道这绝对不是正确的，但也许给我一些洞见，我要去的。
Totti
解决方案公式和脚本的结合使用意味着它不像它那样简单。如果您使用脚本执行所有更改，则只需在脚本的其余部分运行时添加时间戳即可。实际上，一种解决方案是每隔几分钟运行一次脚本以检查列'R'是否具有值'Ready'和列'S'没有时间戳，如果这是真的=添加当前的时间戳。
一个简单但不雅的方法是：
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（））;
$ / code $ / pre
遍历电子表格中的所有行，并检查是否需要时间戳。然后，您可以设置时间触发器来运行每过一段时间。
这对于小型电子表格可以很好地工作，但几分钟后我就将它作为解决方案放在一起，因此如果您打算使用它使用它几千行。
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.
Here is the document.
https://docs.google.com/spreadsheets/d/18iX6Yp8SgfPaDRJC8L6f4xU1U9Gw8eiBINNFPT9r0Ow/edit?usp=sharing
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);
}
}
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.
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.
UPDATE
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"?
Something like
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.
Totti
解决方案 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.
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.
这篇关于如果列具有一定的值，请使用google脚本更改同一行中单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
