IssueTracker

Well, from my understanding, if you are writing info to a sheet and then deleting them in the end, you can write them to an array object. Since the array can live as long as the script is running.
Sorry, if I didn't understood you well..

The tool I am working on is a scheduling spread sheet were each row is a deployment into a controlled environment.  The users want to run a custom menu command to only show those row that are scheduled for the next 7 days. After that script has been run, the user may want to run another script to further reduce the visible rows to only show Production deployments (in the next 7 days). After that script has been run, the user may want to run another script to show just the deployments assigned to a particular implementer (in the next 7 days, in the Production environment).  I have written a script with hard coded date ranges such as today()+7 as well as using the active cell contents to do a pattern match against all cells in the same column.

To make this work without the ability to determine the current visibility status of each row in a sheet, I'm having to write individual row numbers to a different sheet as the scripts determine which rows to hide.  An array would not work as each script will be executed independently and once the first script completes, the array will be lost.  

See discussion here:

http://www.google.com/support/forum/p/apps-script/thread?tid=32be48d119824585&hl=en&fid=32be48d1198245850004a044648ea89d

Google recently added UI support to show/hide rows and sheets. We need API to use these in GAS.

My suggestions:

It would be great to have a hide(), show(), visible() method on the Sheet object.

hide() - Hides the sheet in the UI
show() - Unhides the sheet in the UI
visible() - returns true if the sheet is visible in the UI, false if not.

It would also be great to have a hideRow(n), showRow(n), and hiddenRows() methods too

hideRow(n) - Hides row N in the UI.
showRow(n) - Unhides row N in the UI.
hiddenRows() - returns an array of all hidden rows.

Had to rewrite a big piece of functionality in a script that shows/hides rows based on complicated criteria because it was issuing way too many calls to showrows/hiderows.

Since I couldn't get the visibility of the sheet's rows I had to iterate over each row in the sheet and either hide or show it. So for a sheet with N rows, I had N calls.

If there were a Sheet.getHiddenRows() (and getHiddenColumns too) method, I could have just toggled the rows I needed.

Big thanks to nmagocio for helping me with rewriting my script.

This looks like a pretty old request. I am also looking for a method that can tell me if a row, column or cell is hidden or not.

I am also looking for this functionality.

There is a method Sheet#showColumns and a method Sheet#showRows [1]
I am looking for a way to know if a Column is hidden and another method to know if a row is hidden.

Is it possible to know this from a google-app-script?

Many thanks in advance.
Hugues
[1] https://developers.google.com/apps-script/class_sheet#showColumns

I give my vote for a "getHiddenRows()" and "getHiddenColumns()" feature!

I would love to see this feature be available too!!!!!!!

Signed. Such a functionality would bring me (and many others as well) a great tools to use in my spreadsheet scripts.

Many thanks and good luck to the developers that could be involved in implementing such feature!

Please add this feature, would be a good addition to being able to make rows visible

[Comment deleted]

My workaround was to use a special leading character that would indicate the visibility state of the row/column, it was a backtick (`), but then each show/hide column/row command should be performed from a customized menu.
Details are in the StackOverflow topic:
http://stackoverflow.com/questions/6793805/how-to-skip-hidden-rows-while-iterating-through-google-spreadsheet-w-google-app

Good workaround.  If only I could rely on the guys here to consistently use alternate show/hide commands rather than right clicking and using the context menu to show/hide the rows.  Is there any way to block out the normal show/hide functionality for certain users?

It should be possible with some Browser Extensions, as it's a web application after all
http://img845.imageshack.us/img845/6442/ysy5.png

Good thinking Steven.  I'll add it to the list and see what I can do to implement.  I'll let you guys know if I make any progress.

Another workaround is changing the width of hidden columns to the minimum of 2. You can then detect hidden columns by their width. This would only really work if you do not care about preserving the width of the column before it was hidden. Since you are hiding the column anyways, the column width might not be significant.

I would like to be able to test for the visibility of a row, so that I can avoid duplicating cells from a line but only if the line above is visible.

Unassigning

This feature should really be added, not only for rows, but also for columns!

the feature is needed indeed!

Please add these methods to class Sheet:
getHiddenColumns(columnIndex, numColumns) Boolean[]
getHiddenRows(rowIndex, numRows) Boolean[]
where an entry is true if the corresponding column (or row, respectively) is hidden.

This would be immensely helpful. Several users of my add-on are requesting this feature and at this point I can't help them, which feels awful.

Related:

- http://stackoverflow.com/questions/6793805/how-to-skip-hidden-rows-while-iterating-through-google-spreadsheet-w-google-app
- http://webapps.stackexchange.com/questions/45677/google-spreadsheets-sum-only-shown-rows

It is a total shame for Google for ignoring this feature request for 3 years.

I would really want to know what the hell happens with Google systems development as they put publicly available issue trackers but no one ever interacts decently with public requested problems.

A isVisible() function would be useful also for accounting scripts that consider filtered out rows.

It's because we're the product, not the customer.

Google, please hear us!

Eric Koleda seems to be the one assigned to managing this issues tracker.

Eric - it'd be awesome if you could give us some insight into the development of Google Apps Script. For instance: how many engineers are assigned to the project across Google? How much time is spent developing new features vs fixing bugs? Is there an internal product roadmap for new features that will be implemented, and can you share it with us?

Many of us here, if not all of us, have invested a great deal of time in developing Apps Script projects for critical business applications and production environments, and we plan to continue to, so it would greatly reduce our frustration and increase our confidence in the future of the platform if we could cut through some of the mystery.

I agree with Mike.  Thank you for being kind and polite with Eric.  I too have companies that completely rely upon the sheets for their business processes.  It wasn't until we developed further into the programs that we saw how helpful it would be to know which lines are hidden and which are not.  At this point, I'm forced to remove lines and insert lines for our functions.  Crazy!!!  This would be a great add.  

Hi Everyone, Eric here. I'd like to apologize for how long this feature request has been open, especially since so many of you have requested it and because it's such an obvious compliment the hideRows() and showRows() methods that already exist.

I can fully understand your frustration, but it's not appropriate for me to give you internal information about our team, our time breakdown, or private roadmaps. Certainly one of the challenges we face is balancing the creation of new integrations and opportunities with improving existing ones. Our work on add-ons has brought the power of Apps Script to many, many more users then previously would have wrote their own scripts, but we need to balance these ambitious new projects with the more routine work of improving our core product and keeping up-to-date with the changes in Google Sheets, etc.

I've reached out the team and will make sure that this request gets on their short term roadmap. If you have any other issues you'd like to escalate or other feedback for the team please reach out to me on Google+: plus.google.com/+EricKoleda

Thanks Eric, that's awesome.

And I totally understand that you can't share internal details, but even the fact that you responded here means a lot to us. It reminds us that there ARE real humans just like us over on the Google side, working diligently whether we know about it or not, and that alone helps boost my confidence.

Also, some of us can get a little uneasy about investing time in a platform given how Google has been known to regularly discontinue products and features, forcing people to find alternatives, so it's good to know that Apps Script has a dedicated team working on it and is here to stay for a while.

Thanks again for the response, and have a great weekend!

missed simple isRowHidden(x)

Any news on this? The issue lives 5+ years :D

yeah seriously

Just another up-vote for this feature.

[Comment deleted]

Another vote for this.

I starred this when we needed it 3 years ago, and we still need it. The workarounds I was forced to build at the time have caused some other problems, all in all it has become quite costly for a seemingly simple to solve issue: if Google can decide which rows should be hidden whenever the sheet is displayed on the screen then it seems trivial to propagate these same properties (switches/flags/mask or whatever technique is used) to Apps Script. Alas, apparently it doesn't work that way :/

I personally gave up waiting for this feature.

They released Filter Views recently, so if you put the appropriate data in a column and combine that with Filter Views you achieve a very similar result.

Spam him here:  plus.google.com/+EricKoleda

[Comment deleted]

Please don't :-) I've reached out to our engineering team again to see if they can get this on their short term roadmap.

Thank you!  We look forward to you guys considering this - would be a BIG help!

That would be great, Eric!

Yes, please!  Thanks for advocating for this sorely-needed basic
functionality. As i pointed out in one of my earlier comments, it's
currently impossible to build a reliable application, since hidden rows
will cause some methods to throw an error, but there's no way to check for
it before invoking them.

Gary Whittaker, CHC, CHP, CSCS
Director for Information Management
HIPAA Security Compliance Coordinator
Columbia Co. Dept of Human Services
gary.whittaker@columbiacountyny.com
(518) 828-9446 x1281

On Thu, Apr 21, 2016 at 11:56 AM, <google-apps-script-issues@googlecode.com>
wrote:

Hello. I developed a turnaround based on the idea that a script will only change format of non-hidden rows suggested here:
https://gist.github.com/carlbergman/1a1dd786425bc9180b29

Here is the code. Given at returns a r x 1 array with zeros for hidden rows and the number of row for non hidden rows:

function getVisible() {
  var SS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SS name")
  range = SS.getRange(1, 1, SS.getLastRow())
  var visible = [];
  var oldColors = range.getBackgroundColors();
  var tempColors = [];
  tempColors[0]=oldColors[0];  //assuming the headers should not be considered
  var i
  for (i = 1; i < oldColors.length; i++) {tempColors[i] = ['#fce5cd'];} //starts in 1 assuming the headers should not be considered
  range.setBackgrounds(tempColors)
  var newColors = range.getBackgroundColors();
  range.setBackgrounds(oldColors)
  for (i = 0; i < oldColors.length; i++) {visible[i] = (oldColors[i][0] == newColors[i][0]) ? 0 : i+1;}
  return visible
}

It worked for me, hope it works for someone else.

Best.

That is really clever, Jantonio! I tried it and it works perfectly! I gave you a star for that! :)

Any update on this request?
Can I vote 3000% in favor of this one?

Great hack, Jantonio - thanks for the work you've put in just to give us a way to do this :) I'll see if I can modify what you've done to work for my purposes.. But would still be great to see an official solution.

Any updates on this issue, Eric?

Cheers

This solution doesn't work with Filter Views. Filter Views are too powerful.

Filter views are per client, so the concept of a hidden row is meaningless when they're involved anyways.

Need this badly. Not a small thing.

I just tried Jaunitos work around (#50) and I can not get it to work. I've looked under the hood and it does not appear to be working as it was in 2016.  I tried a test page outside of script and when I change background colors with hidden rowsit in fact changes the colors on the hidden rows too... so kind of validates the fact that that work around is no longer valid.

Is there still any hope to get here a little feature?

getDataRange().getValues()   .. can return for hidden rows null.

Hi,
As I am not able to check if a row is hidden or not my script is becoming very very slow.
I have too much rows to iterate over.
As I cannot see if a row is hidden, I hide them again and that makes my script extremely slow.
Can you please please implement this feature.
It has been asked for for more than 6 years now and it would be good practice to be able to query a property of a column/row if we are able to set its property...
So please make this a priority
Carl

Would have loved to do this in Google App Scripts but after reading how this feature request has languished and I only need this occasionally, I used an ugly manual hack.

Basically exported my sheet to Excel and ran the VBA script from here:
https://www.extendoffice.com/documents/excel/857-excel-delete-hidden-rows-columns.html

TL;DR recipe:

1. Download your Google sheet and import into to Excel
2. ALT-F11 in Excel to bring up a little window with VBA code.
3. Replace that window contents with this snippet (from above link):
  Sub deletehidden()
  For lp = 65536 To 1 Step -1
    If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else
    Next
  End Sub
4. Hit F5 to run it and delete the hidden rows.
5. Save from Excel and reload back into to Google sheets.
6. Hurl.

Use with care, obviously - it only works for 65k rows as written, etc.

Please release this already - I need it too!

You can use the new Google Sheets advanced service to get this information.
See this blog post for more info:
https://gsuite-developers.googleblog.com/2017/04/using-google-sheets-filters-in-add-ons.html

function getIndexesOfFilteredRows(ssId, sheetId) {
  var hiddenRows = [];
 
  // limit what's returned from the API
  var fields = "sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)";
  var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;  
 
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].properties.sheetId == sheetId) {
      var data = sheets[i].data;
      var rows = data[0].rowMetadata;
      for (var j = 0; j < rows.length; j++) {
        if (rows[j].hiddenByFilter) hiddenRows.push(j);
      }
    }
  }
  return hiddenRows;
}

As mentioned in #60, it is possible to access hidden row statuses using the Sheets Advanced Service. The functionality does not exist in the regular Sheets service at this time, so we will keep this feature request open for now.

Can someone please tell me how to get attention on an issue i've created in this same Gsuite Devs > Apps Script category?  

It's a huge bug that is un-assigning script names from buttons/objects.  https://issuetracker.google.com/issues/37172217  Sorry to post this here.. just not sure how else to get anyone to look at this

b

I tried the workaround and does not seem to be working. I have been using ScriptProperties for a simple toggle, but a isColumnHidden() boolean would be very useful!

How do I add myself to this issue?

Good question, i'd like to add myself too

Thanks.

7 years and counting. Probably not going to happen, right? Would be great though!

Don't hold your breath, this is a feature request after all. I'm currently waiting on a fix for a bug that was reported 2 1/2 years ago but is clearly not high on Google's priority list. Link for anyone who is interested: https://issuetracker.google.com/issues/36762588

Wouldn't it be logical? I iterate through thousand of hidden row and it my script hides already hidden row coz I can't check its status. Additional processing time. It takes a few minutes to hide a few rows in a sheet where hundreds are already hidden. Hope we get that soon.

Any chance of getting this before I die??

It would be nice to have this as a feature, I'm iterating through hidden rows when I could be skipping them and saving time sending hideRow() calls and execution time.

One more vote for this feature to be added.

Apparently reusable rockets take less time to be designed and implemented than this feature.

I too would greatly appreciate this feature. Seems grossly overdue.

Would love this feature.

Hello, I wrote a work-around to achieve this functionality:

https://stackoverflow.com/a/48592171/5915860

I know this is a dirty hack but If it ends up being useful to you, I would appreciate an upvote or comment in Stackoverflow, or a star in Gist: https://gist.github.com/esquinas/bdc8de88a41f08cc128f00341857b0e5
Thanks.

Regards,

Another vote.

+1

please add this feature

please work on that, the script is weird when there are hidden rows


2018-07-24 2:27 GMT-04:00 <buganizer-system@google.com>:

+1

+1

I guess commenting and adding myself is a waste of time at this point but it'd be great if this existed.

+1

EIGHT and a half YEARS???
Come on Google... as coders, I think we all agree this is such an easy feature to implement.

Hello. They are too busy turning Google into EvilCorp.

They didn't even made GS compatible with ecmascript (you cannot use `let`, `const` nor `=>`). Shame.

Meanwhile here it is a workaround
https://gist.github.com/esquinas/bdc8de88a41f08cc128f00341857b0e5

+1

+1

+1

+1

This is now available in Google Apps Script.

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#isrowhiddenbyfilterrowposition

How is this the same ?

I don't think this addresses the issue

ri...@gmail.com, isRowHiddenByUser(rowPosition) do the work.

Is not an API and doesn't return an array as je...@gmail.com originally requests but returns a boolean if the row is hidden itself, not by filter i mean.

I haven't tested it yet, but it seems quite costly in terms of execution time if you are going to iterate over a large number of rows. I agree it should be possible to query properties for all rows in a range and store them in an array similar to what happens when we do getValues() from a range.  Perhaps, we should have a Range.getRowProperties() and Range.getColumnProperties() as well.

+1

Please add this feature!

This was launched in 2018 Q4. See https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#iscolumnhiddenbyusercolumnposition for more information.

jb...@google.com  <jb...@google.com>  #97 Mar 13, 2019 10:07PM :
> Marked as fixed.
> This was launched in 2018 Q4.

The original request was about getting an _array_ of hidden rows and columns on a sheet. The three existing Sheet methods only let you find the hidden status of _one_ row or column at a time. They are very slow, taking between 65 and 85 ms per call in a custom function context (your mileage may vary). Very often, a function that uses these methods will time out or throw "internal error executing the custom function". This makes it quite difficult to get an _array_ of hidden rows or columns in a range or sheet.

As pointed out by several posters in this thread, it would seem to make sense to add a Sheet or Range method to get the hidden rows and columns, either as a RangeList or simply as an array of hidden row/column numbers.

Cheers --Hyde

Hyde: I added issue 128600464 for your request, as it is slightly different from this request.

Would be useful indeed

yes please! this seems like a quick fix, and one that has been thoroughly requested!

They don't seem bothered.

please