CODEWITHSUNDEEP
google-sheets
Ian
Ian

Reputation: 39

Subset of Google Sheet

I have a club membership database held in a google sheet which is accessible to the committee. A subset of the data is available to members via another sheet, populated using importrange.

What I need to be able to do is to withhold some rows of data from the members sheet, depending on a flag (eg a cell in the committee sheet).

It's important that when the "flag" is set, the whole record (row) in not shown in the members sheet.

I have tried some gs scripts and also =QUERY(IMPORT..) to no avail, so suggestions welcome.

Thanks

Ian

Upvotes: 0

Views: 2586

Answers (2)

player0
player0

Reputation: 1

if you have two spreadsheets (two Google Sheets files) you need to use:

=IMPORTRANGE("ID or URL"; "'Sheet1'!A1:Z")

  • Sheet1 - a name of the sheet from which you want to import
  • A1:Z - the range you want to import

  • "URL" - whole URL address of the sheet from which you want to import

    example: "https://docs.google.com/spreadsheets/d/10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4/edit#gid=1873076661"

  • "ID" - part of the URL address of the sheet from which you want to import

    example: "10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"

and then you can use FILTER, QUERY or both like:

=FILTER(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!A1:Z"); 
        IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!C1:C") = 
        "COMPLETE")

=QUERY(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!A1:Z"); 
 "select * where Col3 = 'COMPLETE'"; 0)

=FILTER(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!A1:Z"); 
  QUERY(IMPORTRANGE("10asxBh4bicmqqGdIIZEguD9ApcJk4GnF82ZiS8MBkq4"; "'Sheet1'!C1:C"); 
  "select *"; 0) = "COMPLETE")

side note: Col3 - column strong text (eg. 3rd column)

Upvotes: 1

mik
mik

Reputation: 3905

For query inputs that are not ranges, use Col1, Col2, etc. as column names. In your case the formula should look like:

=query(importrange(...),"select * where Col5 = 'N'")

if the hide flag is in 5th column and has Y/N values. Be also sure that you include the flag in the importrange.

Upvotes: 2

Related Questions