0

I have an excel spreadsheet with the following piece of code. I want to convert and upload this spreadsheet to a google drive spreadsheet. Can anyone help me with converting the VBA code to google apps script? Thanks!

Sub Filter1()

'Assigned to Keyboard Shortcut Ctrl + Q
ActiveSheet.AutoFilterMode = False 'Initializes the Filter
ActiveSheet.Range("$D$2:$AZ$500").AutoFilter Field:=ActiveCell.Column - 3, Criteria1:="<>" 'Applies filter and criteria (blanks)

End Sub
user2292449
  • 3
  • 2
  • I'm not familier with VBA, please can you add an explanation of what it does. [The Google Spreadsheet Class Document](https://developers.google.com/apps-script/reference/spreadsheet/) is your friend, it explains the methods that will help you. – dev Apr 17 '13 at 22:50
  • Hey vletech. Thanks for the comment. Here's the explanation - Data is contained in the range "D2:AZ500" with headers in the range "D2:AZ2". The data contains numbers and blanks. When the code is executed, 1) It applies a filter on the range and 2) it filters out blanks from the column that contains the active cell............... For instance, if cell K50 is selected and the code is executed, it applies the filter on D2:AZ500 and filters out blanks from column K. Hope I'm making sense. If not, I can make a small video showing what the code does. Thanks! – user2292449 Apr 18 '13 at 00:59

1 Answers1

0

Two aspects of the VBA script example you've shared are not possible to implement in apps-script:

  • While Google Spreadsheets do have an auto-filter feature that is similar to Excel, there is no API to control it from apps-script.
  • There is no support for keyboard shortcut programming (you didn't include the code that maps Ctrl + Q to Filter1(), but it's mentioned in comments).

There are alternatives you could consider. For instance, you could create a menu-driven function that would produce a filtered version of the source data on a separate sheet. The user would navigate to the cell in the source data that is of interest, invoke the "filter" function from the custom menu, and that function would proceed with filtering the data based on the currently "active" cell.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275