CODEWITHSUNDEEP
google-apps-script
salmon petrus
salmon petrus

Reputation: 13

Is there any way to select first row of filtered(visible) entries and store it's row number into a cell via script?

I've a google sheets table in range A22:T2000, with header at row 22 When it filtered (By a criteria of C column - Image),

  1. How to select first row (visible) and store it's row_number into cell A1. (row 515 - image)
  2. How to move to the next visible row It must done in one sheet named "Entri_Meter"

My plan is to use OFFSET function to get the data place in a row above the table (Row 21) and set to print area (C1:H20). OFFSET function require "Row Number". Thank you for any help.

enter image description here

I'm newbie in Google App Script From Excel VBA, now learning Google Sheet App Script

Upvotes: 1

Views: 437

Answers (1)

Cooper
Cooper

Reputation: 64040

First Unfiltered Row

function firstrownotfiltered() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const hr = sh.getFilter().getRange().getRow();//gets row where filter starts
  const rg = sh.getRange(hr + 1, 1, sh.getLastRow() - hr);
  const vs = rg.getDisplayValues();
  var vrow;
  for (let i = 0; i < vs.length; i++) {
    if (!sh.isRowHiddenByFilter(i + hr + 1)) {
      vrow = i + hr + 1;
      ss.toast(`First Row Not Filtered: ${vrow}`);
      return vrow;
    }
  }
}

Upvotes: 0

Related Questions