如何在 Google Sheets 中仅将 setValue 用于过滤行的范围(getRange 用于未隐藏的单元格)?

2022-01-24 00:00:00 google-sheets google-apps-script range filter javascript

我想知道，如何使用setValue，如果有过滤的行，那么只有显示的行(C1 + 一行到C的最后一行)得到一个值.

I want to know, how to use setValue, if there are filtered rows, so that only the shown rows (C1 + one row down to last row of C) get a value.

x(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  sheet.getRange(C2, lastRow).setValue('x');
}

更新

它可以工作，但速度很慢.我已经测试了以下代码，它运行得很快.它必须从显示的第二行开始.以下解决方案适用于有无过滤器.尚未运行的是第二行 (C2).复制的值总是插入那里.此外，如果可能的话，我想不使用辅助单元进行复制.复制粘贴功能(getValue)是否可以复制setValue?

Update

It works, but very slowly. I have tested the following code and it works fast. It must start in the second shown row. The following solution works both with and without filter. What is not yet running is the second row (C2). The copied value is always inserted there. In addition I would like to do without an auxiliary cell for copying if possible. Is it possible to copy setValue for the copypaste function (getValue)?

function x() {
  var spreadsheet = SpreadsheetApp.getActive();
  var lastRow = spreadsheet.getLastRow();
  spreadsheet.getRange('C2:'+'C'+lastRow).activate();
  spreadsheet.getRange('C1').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

目标是在 C 列的当前可见(不是隐藏或不可见的过滤)单元格中放置一个 x.为此，我只需要知道如何将第二个可见单元格指定为 getRange 值(偏移量为例如)，因为其余的(结束单元格:lastRow)正在工作(正确的选择和输入，每次只有 C2，如果我使用这个脚本，C2 斧头中有):

The goal is to put an x in the currently visible (not the hidden or non-visible filtered) cells of column C. For this I just need to know how to specify the second visible cell as getRange value (with offset for example), because the rest (end cell: lastRow) is working (correct selection and input, only C2, everytime, if i'm using this script, there is in C2 a x): 

spreadsheet.getRange('C2:'+'C'+lastRow).activate();

第一行是固定的.如何将第一个可见的不固定行(第二行)用于getRange?如果最后一行被隐藏并且使用了脚本，则没有设置 x，可能是因为 'C'+lastRow.这行得通.只有 C2 受到影响.

The first row is fixed. How to use the first visibile not fixed row (second row) for getRange? If the last row is hidden and the script is used, no x is set there, probably because of 'C'+lastRow. This works. Only C2 is affected.

var s = SpreadsheetApp.getActive().getActiveSheet();

function x() {
  var lastRow = s.getLastRow();
  for(var row = 2; s.isRowHiddenByFilter(row); ++row);
  var range = s.getRange('C'+row+':C'+lastRow);
  s.getRange('F1').copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}

推荐答案

  • 您希望使用 setValue 将值放入过滤列(C"列)的显示行中.
  • 过滤器是基本过滤器.

    • You want to put the value using setValue to the showing rows of the filtered column (column "C").
    • The filter is the basic filter.

      • 如果我的理解是正确的，那么这个答案呢?请认为这只是几个可能的答案之一.

      If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

      • 在这种情况下，您可以使用 isRowHiddenByFilter 通过过滤器检索显示和隐藏行的行号.
        • 当 isRowHiddenByFilter 为 true 时，行隐藏.
        • 当 isRowHiddenByFilter 为 false 时，显示行.
        • In this case, you can retrieve the row numbers of the shown and hidden rows by the filter using isRowHiddenByFilter.
          • When isRowHiddenByFilter is true, the row is hiding.
          • When isRowHiddenByFilter is false, the row is showing.
          var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var ranges = [];
for (var i = 0; i < lastRow; i++) {
  if (!sheet.isRowHiddenByFilter(i + 1)) {
    ranges.push("C" + (i + 1));
  }
}
sheet.getRangeList(ranges).setValue('x');

          • 当您运行脚本时，x 的值会被放到C"列的显示行中.
          • 如果将 if (!sheet.isRowHiddenByFilter(i + 1)) { 修改为 if (sheet.isRowHiddenByFilter(i + 1)) {，则值为x 被放入隐藏行.

            • When you run the script, the value of x is put to the showing rows of the column "C".
            • If if (!sheet.isRowHiddenByFilter(i + 1)) { is modified to if (sheet.isRowHiddenByFilter(i + 1)) {, the value of x is put to the hidden rows.

              • isRowHiddenByFilter(rowPosition)李>
              • getRangeList(a1Notations)李>

              如果我误解了您的问题并且这不是您想要的方向，我深表歉意.

              If I misunderstood your question and this was not the direction you want, I apologize.

相关文章