Status Update
Comments
hg...@gmail.com <hg...@gmail.com> #2
Sorry, if I didn't understood you well..
je...@gmail.com <je...@gmail.com> #3
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.
kt...@gmail.com <kt...@gmail.com> #4
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.
kt...@gmail.com <kt...@gmail.com> #5
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.
jk...@google.com <jk...@google.com>
jm...@omniatravel.be <jm...@omniatravel.be> #6
[Deleted User] <[Deleted User]> #7
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]
om...@gmail.com <om...@gmail.com> #8
te...@gmail.com <te...@gmail.com> #9
av...@gmail.com <av...@gmail.com> #10
Many thanks and good luck to the developers that could be involved in implementing such feature!
de...@warwickshire.gov.uk <de...@warwickshire.gov.uk> #11
ax...@gmail.com <ax...@gmail.com> #12
st...@gmail.com <st...@gmail.com> #13
Details are in the StackOverflow topic:
[Deleted User] <[Deleted User]> #14
st...@gmail.com <st...@gmail.com> #15
[Deleted User] <[Deleted User]> #16
ma...@gmail.com <ma...@gmail.com> #17
na...@gmail.com <na...@gmail.com> #18
rv...@gmail.com <rv...@gmail.com> #20
zt...@gmail.com <zt...@gmail.com> #21
jo...@gmail.com <jo...@gmail.com> #22
getHiddenColumns(columnIndex, numColumns) Boolean[]
getHiddenRows(rowIndex, numRows) Boolean[]
where an entry is true if the corresponding column (or row, respectively) is hidden.
[Deleted User] <[Deleted User]> #23
pr...@gmail.com <pr...@gmail.com> #25
[Deleted User] <[Deleted User]> #26
A isVisible() function would be useful also for accounting scripts that consider filtered out rows.
ch...@gmail.com <ch...@gmail.com> #27
pr...@gmail.com <pr...@gmail.com> #28
mi...@codeandtheory.com <mi...@codeandtheory.com> #29
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.
gg...@gmail.com <gg...@gmail.com> #30
ko...@gmail.com <ko...@gmail.com> #31
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+:
mi...@codeandtheory.com <mi...@codeandtheory.com> #32
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!
kl...@gmail.com <kl...@gmail.com> #33
[Deleted User] <[Deleted User]> #34
jo...@gmail.com <jo...@gmail.com> #35
pr...@gmail.com <pr...@gmail.com> #36
[Deleted User] <[Deleted User]> #37
ja...@xiktam.com <ja...@xiktam.com> #38
[Deleted User] <[Deleted User]> #39
al...@gmail.com <al...@gmail.com> #40
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.
pr...@gmail.com <pr...@gmail.com> #41
jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #42
ko...@gmail.com <ko...@gmail.com> #43
in...@gmail.com <in...@gmail.com> #44
se...@gmail.com <se...@gmail.com> #45
[Deleted User] <[Deleted User]> #46
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:
ja...@xiktam.com <ja...@xiktam.com> #47
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.
[Deleted User] <[Deleted User]> #48
ca...@restauratiemetkwaliteit.be <ca...@restauratiemetkwaliteit.be> #49
Can I vote 3000% in favor of this one?
jf...@webuy.com <jf...@webuy.com> #50
Any updates on this issue, Eric?
Cheers
ar...@gmail.com <ar...@gmail.com> #51
js...@google.com <js...@google.com> #52
br...@bryancanary.com <br...@bryancanary.com> #53
br...@bryancanary.com <br...@bryancanary.com> #54
kl...@gmail.com <kl...@gmail.com> #55
getDataRange().getValues() .. can return for hidden rows null.
ca...@restauratiemetkwaliteit.be <ca...@restauratiemetkwaliteit.be> #56
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
ro...@spiralfx.com <ro...@spiralfx.com> #57
Basically exported my sheet to Excel and ran the VBA script from here:
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.
ro...@spiralfx.com <ro...@spiralfx.com> #58
le...@gmail.com <le...@gmail.com> #59
ek...@google.com <ek...@google.com>
ro...@gmail.com <ro...@gmail.com> #60
See this blog post for more info:
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;
}
ry...@google.com <ry...@google.com> #61
br...@bryancanary.com <br...@bryancanary.com> #62
It's a huge bug that is un-assigning script names from buttons/objects.
b
go...@gmail.com <go...@gmail.com> #63
he...@harmonydispensary.org <he...@harmonydispensary.org> #64
vf...@valeo.com <vf...@valeo.com> #65
ar...@gmail.com <ar...@gmail.com> #66
ch...@outpostcentral.com <ch...@outpostcentral.com> #67
ad...@templetaunton.uk <ad...@templetaunton.uk> #68
ej...@bestandlesstravel.com.au <ej...@bestandlesstravel.com.au> #69
in...@interwebbuilder.co.uk <in...@interwebbuilder.co.uk> #70
[Deleted User] <[Deleted User]> #71
jo...@gmail.com <jo...@gmail.com> #72
sa...@gmail.com <sa...@gmail.com> #73
jo...@gmail.com <jo...@gmail.com> #74
ji...@lairdresearch.com <ji...@lairdresearch.com> #75
es...@googlemail.com <es...@googlemail.com> #76
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:
Thanks.
Regards,
co...@gmail.com <co...@gmail.com> #77
al...@pushentertainment.com <al...@pushentertainment.com> #78
vl...@gmail.com <vl...@gmail.com> #79
tu...@gmail.com <tu...@gmail.com> #80
2018-07-24 2:27 GMT-04:00 <buganizer-system@google.com>:
mj...@gmail.com <mj...@gmail.com> #81
je...@solidliftparts.com <je...@solidliftparts.com> #82
4m...@gmail.com <4m...@gmail.com> #83
oh...@gmail.com <oh...@gmail.com> #84
it...@lisluanda.com <it...@lisluanda.com> #85
Come on Google... as coders, I think we all agree this is such an easy feature to implement.
es...@googlemail.com <es...@googlemail.com> #86
They didn't even made GS compatible with ecmascript (you cannot use `let`, `const` nor `=>`). Shame.
Meanwhile here it is a workaround
ch...@gmail.com <ch...@gmail.com> #87
pi...@gmail.com <pi...@gmail.com> #88
ba...@incubeta.com <ba...@incubeta.com> #89
is...@gmail.com <is...@gmail.com> #90
ri...@gmail.com <ri...@gmail.com> #92
I don't think this addresses the issue
jo...@ascensoreslaplata.com.ar <jo...@ascensoreslaplata.com.ar> #93
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.
it...@lisluanda.com <it...@lisluanda.com> #94
th...@gmail.com <th...@gmail.com> #95
ly...@mgak-12.org <ly...@mgak-12.org> #96
ar...@gmail.com <ar...@gmail.com> #98
> 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
jb...@google.com <jb...@google.com> #99
ka...@gmail.com <ka...@gmail.com> #100
mm...@markj.us <mm...@markj.us> #101
ma...@koplus.co.uk <ma...@koplus.co.uk> #102
[Deleted User] <[Deleted User]> #103
js...@google.com <js...@google.com> #104
This issue is marked fixed because the originally requested functionality is implemented. As several people have observed, getting the "show/hide status of a row" (quoting the issue title, emphasis mine) scales poorly to large sheets.
One of my colleagues already created a follow-up issue to add a bulk method to get hidden rows and columns in a sheet or range:
Our issue tracking system works best when there is a single request per issue. So, if you need any other Apps Script enhancements, please file a new request at
Description
I am creating a spreadsheet tool that will process rows of data based on custom menu functions and change their visibility. Being able to put the show/hide properties of a sheet into an array would be extremely helpful. As a work around, I'm having to write out row numbers that I am making visible to another sheet and then retrieving them later as I employ the logic to show/hide the next set of rows. This has its own problem of making the user see hundreds of sheet changes while the code executes, but that is a separate enhancement request.
Jeff