Use connections as data sources

Best practice

Description

Use security filters

Include AppSheet expressions in the security filters, which will be translated to BigQuery SQL and executed on the server before the data is downloaded to the app.

Note: Security filters are pushed to the SQL Provider only for AND(), IN(), NOT(), and OR() expressions. Other expressions aren't supported for Provider-level querying before the row limit is applied. For BigQuery, the row limit of 100K is applied on that Provider query, so you can get too few rows if you use a security filter that isn't purely composed of these expression.   

Examples:

• Filter by USEREMAIL() to get rows owned or viewable by the currently logged-in app user.
• Filter by DateTime values in the last week or month to get only the most recent data. For example, assuming there is a column in the dataset called DateCreated, you could use security filter expressions like:
  • For the last week: [DateCreated] >= TODAY() - 7
  • For the last month: [DateCreated] >= TODAY() - 30

• Use a combination of user settings and security filters to allow your app user to limit the data. For example, using the user setting column named Year and a column in the dataset called StartTime, you can get data from the year 2018 or later (assuming the user setting Year is set to 2018), as follows: [StartTime] >= DATETIME("1/1/" & USERSETTINGS("YEAR"))

Use BigQuery Views (read only)

You can create specific views for AppSheet in BigQuery that have potentially complex SQL queries to create a limited number of rows from the dataset. When setting-up the BigQuery data source as a table in your app, select from the list of views in your project as well as from the base datasets.

Note: BigQuery views are read-only so changes made in AppSheet will not sync back to the BigQuery dataset.

Create a new BigQuery Table

BigQuery allows you to set up a copy of a table that can be re-created periodically based on a scheduled query. This is similar to a view, but it is a physical table that contains the subset of data. This can also be used to reduce the number of columns in order to simplify the table structure.