当T:T包含“已复制"时，清除S:S中的内容.Google脚本 [英] Clear content in S:S when T:T contains "Copied" Google Script

问题描述

我对简单脚本一见钟情.

I am stuck with, at first sight, simple script.

当T的值为复制"时，我想从单元格S中清除内容.

I want to clear a content from cell S when T has value "Copied".

此刻我是这样的:

function onEdit(e) {
  if(e.range.columnStart === 20) { 
    e.range.offset(0,-1).clearContent();
  }
}

我不确定如何添加IF.另外，请记住，T列有一个公式，所以我不手动编辑它，并且使用此脚本，它不起作用.

I am not sure how to include IF. Also, bear in mind that T column has a formula, so I don't edit it manually, and with this script, it doesn't work.

它不必是OnEdit，我可以设置一个触发器来每分钟运行一次脚本，这甚至更好，但是重要的是使用Copied值对其进行过滤.

It doesn't have to be OnEdit, I can set a trigger to run the script every minute which is even better, but it is important to filter it by the value Copied.

进一步说明我的文件如何工作(示例):

To explain a bit more how my file works (example):

1)我在单元格S5中添加一条注释.

1) I add a comment in the cell S5.

2)我的第二个脚本每分钟运行一次，它将值从S列复制到V列.

2) My second script runs every minute where it copies values from column S to column V.

3)在T列中，我有公式(= IF(V5<>"，IF(RegExMatch(S5，V5)，复制"，")，")))，这意味着V5列中存在该值，然后在T5单元格中添加Copied.

3) In the column T, I have the formula (=IF(V5<>"",IF(RegExMatch(S5,V5),"Copied",""),"")), which means if the value exist in the column V5 add Copied in cell T5.

4)我正在寻找一种解决方案，当单元格T:T具有已复制"时，删除单元格范围S:S

4) I am looking for a solution that when cell T:T has "Copied", delete the cell range S:S

谢谢您！

推荐答案

@TheWizEd指出T中的值取决于另一个单元格中的结果.但是，OnEdit函数不一定必须响应所做更改的范围.我已经使用此代码使用OnEdit事件来评估T列中的值，然后对S列中的值进行相关更改.

As @TheWizEd points out the value in T is dependant on the result in another cell. However an OnEdit function does not necessarily have to respond to the range where the change was made. I've used this code to use the OnEdit event to evaluate the values in Column T and then make the relevant change to values in Column S.

列T使用 for循环遍历各行，但相关值被压入数组.这允许在函数末尾执行单个 setValues .

Column T uses a for loop to go through the various row, but the relevant value is pushed to array. This allows a single setValues to be executed at the end of the function.

该功能应分配给电子表格的 OnEdit 触发器.

The function should be assigned to the OnEdit trigger for the Spreadsheet.

function so_53469142() {

    // Setup spreadsheet and target sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("so_53469142");

    // get the last row of data
    var lastRow = sheet.getLastRow();

    // get range for Column  S & T
    var values = sheet.getRange(1, 19, lastRow, 2).getValues();

    // set counter variable
    var i = 0;
    var dataArray = [];
    var masterArray = [];


    // start loop
    for (i = 0; i < lastRow; i++) {
        // Logger.log("i="+i+", S = "+values[i][0]+", T = "+values[i][1]);//DEBUG

        // empty the array
        dataArray = [];

        // test value of first row in T
        if (values[i][1] === "Copied") {
            // If value = "Copies then push blank onto array for Column S
            dataArray.push("");
        } else {
            // else push existing value for column S
            dataArray.push(values[i][0]);
        }

        // make the array 2D
        masterArray.push(dataArray);

    }

    // Update values in Column S
    sheet.getRange(1, 19, lastRow).setValues(masterArray);

}

这篇关于当T:T包含“已复制"时，清除S:S中的内容.Google脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！