How can the SpreadsheetApp calls be reduced to get all non-hidden rows for a rangelist 10 to 100 times or more faster?

Question

How can the following code (https://stackoverflow.com/a/60823521/8652270) be converted to work similar to https://stackoverflow.com/a/61306738/8652270 with isRowHiddenByFilter and isRowHiddenByUser? For example, 100 rows could be checked in 1 second instead of 30 if all values are cached once before.

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

Answer 1

• Use map to create a new array, where the visible values are mapped to "x" and leave the hidden values as is.

• Use the mapped array to directly setValues on the range.

const getVisibleValuesMarkedWithStr_ = (
  shtName,
  rngString,
  sheet = shtName ? SpreadsheetApp.getActive().getSheetByName(shtName) : null,
  range = sheet ? sheet.getRange(rngString) : null,
  stringToMark
) => {
  return range
    .getValues()
    .map((row, rowIdx) =>
      !sheet.isRowHiddenByFilter(rowIdx + 1) &&
      !sheet.isRowHiddenByUser(rowIdx + 1)
        ? row.fill(stringToMark)
        : row
    );
};

function x() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(`C1:C${lastRow}`);
  range.setValues(getVisibleValuesMarkedWithStr_(null, null, sheet, range, 'x'));
}