42

Is it possible, when exporting a Google Spreadsheet into a CSV, to apply this operation only to filtered set of rows?

I need to get in CSV exactly the rows which are visible on the sheet after a filter is applied to it.

1
  • Some answers to this question are the same for those who use IMPORTRANGE.
    – Rubén
    Jan 20, 2020 at 22:42

6 Answers 6

9

Apparently not. I've just wasted half an hour trying to figure out a way to save filtered data, to no avail. It always saves all rows, regardless of the filter selection.

1
  • 4
    There is a way. Check out the answer from user29020. Feb 4, 2019 at 17:28
49

Create a new sheet (Sheet2) within the spreadsheet.

Set the A1 cell to be =filter(Sheet1!A:X, Sheet1!A:A>1). (See docs on the filter function.)

You should then be able to save or export Sheet2 as CSV with only the filtered values.

8
  • 10
    I joined this community specifically to upvote your answer. Thank you so much! Jan 25, 2018 at 19:39
  • 1
    But you still have to specify the filter again in the function? It doesn't to take the filtered conditions from the source sheet's columns - it still shows all rows by default. I have a sheet with 20 columns, 6 of them filtered in various ways, how can you specify multiple filters like that?
    – scipilot
    Nov 4, 2019 at 4:36
  • 1
    Besides exporting as CSV this also is helpful for those who use IMPORTRANGE, connect the spreadsheet to Google Data Studio, etc.
    – Rubén
    Jan 20, 2020 at 22:43
  • 1
    @scipilot Yes, you will have to specify the filter again - see my next comment..
    – vstepaniuk
    Apr 14, 2020 at 18:27
  • 2
    Enclose the separate conditions in parentheses: (range:one>5)*(range:two<10) and not range:one>5*range:two<10.
    – vstepaniuk
    Apr 14, 2020 at 19:42
35

Old-school way:

  • select filtered set of rows
  • copy to clipboard
  • create new sheet
  • paste (or paste special -> paste values only)
  • download this new sheet as CSV
3
  • This indeed works for Copy and Paste. But somehow does not limit to only the filtered rows when using Cut and Paste. As a workaround one can Copy, Paste and then delete the values of the filtered rows.
    – Arjan
    Jan 9, 2015 at 9:42
  • 2
    This answer is WRONG. Copy and Paste includes not only the filtered/excluded rows, but also all hidden columns.
    – Tim Mackey
    Mar 14, 2020 at 0:09
  • This worked for copy and paste for me, excluding the hidden rows as desired. Mar 29 at 1:04
5

The best way to do this is to just select the cells you want to save as CSV, paste it into notepad, then find/replace tab with a comma.

1
  • When you copy data from Google Sheets it knows and does what needs to be done to protect the data - like enclosing the multiline cell contents in quotes, escaping quotes inside those cells etc.
    – vstepaniuk
    Jan 3, 2022 at 7:10
3

You can make a Pivot table for that, inserting the desired filter. Then just export it as CSV.

-2

Open a Gdocs spreadsheet, look at the menu item: 'Tools::Script Gallery', do a search for csv, and you'll find a script that takes an arbitrary named range, converts it to to csv and emails the result to you. I can't vouch for the script at all, but it seems to work.

If you look at it in the Gdocs Script Editor, you can see that it builds a CSV by actually parsing the spreadsheet data rather than using an internal export function, so it may not work perfectly for all cases. If it doesn't do what you want out of the box, it looks like this would be pretty easy to modify. I haven't tried it on filtered data, but it gets its data from a call to 'SpreadsheetApp.getActiveSpreadsheet()' which has a '.getActiveSelection()' method, so you might be able to bypass the whole named range thing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.