javascript - How to use in Google Sheets setValue only for range of filtered rows (getRange for not hidden cells)?

Question

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');
}

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);
};

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();

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.

Here is the solution

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);
}

Answer 1

• 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.

Modification points:

• 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.
• The range list is created from the retrieved row numbers and is used for setValue.

Modified script:

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');

• 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.

References:

• isRowHiddenByFilter(rowPosition)
• getRangeList(a1Notations)

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