如何在Google表格中将setValue仅用于过滤后的行范围(对于未隐藏的单元格，请使用getRange)? [英] How to use in Google Sheets setValue only for range of filtered rows (getRange for not hidden cells)?

问题描述

我想知道，如果有已过滤的行，那么如何使用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)尚未运行.复制的值始终插入在那里.另外，如果可能的话，我想在没有辅助单元的情况下进行复制.可以复制用于复制粘贴功能(setValue)的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.

    这篇关于如何在Google表格中将setValue仅用于过滤后的行范围(对于未隐藏的单元格，请使用getRange)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！