AppSheet does now have a direct, in-platform connection to BigQuery through the BigQuery data source, which is now in PREVIEW status (as of 4/28/2021). You can also review this community announcement.
Please read the linked PDF Using the BigQuery Datasource - Preview User Guide.pdf (1.6 MB) for all the details, including licensing requirements (Enterprise plans required), Limitations (like Read-only, partitioning not supported, and 100K rows max), and a full set of instructions with screenshots for getting your first BigQuery dataset added as an AppSheet table, including how to create a Google Cloud Service Account and Key.
This is a PREVIEW release, so please do not use this data source for production use cases until it is in GA status. We will announce GA in 2-3 months after we have user feedback and have fixed any reported issues.
Where do I find the BigQuery data source?
Go to “My Account” / “My Account” / and select the “Sources” tab. You can add a new data source of type “Cloud Database”. When you click on the dropdown selection, you will see “BigQuery” as a new type of cloud database to choose from. Please follow the instructions in the PDF to get the required inputs to authenticate. If you don’t see it yet, please check back soon, it should be rolling out to all users today.
Please feel free to send us your use cases! I would love to hear about what you plan to do with BigQuery data in your AppSheet apps or automations. You can fill out this Google Form with your information!
If you have a lightweight use case, and need to have production support right away, or have a lower license than the Enterprise Standard plan, you can also use BigQuery data in the following way as well, through Sheets instead of the new BigQuery data source.
Customers who desire to see BigQuery data inside of their AppSheet apps can follow the steps outlined in this G Suite blog post which we also outline below.
1. There is a 10,000 row limit on extracting data from BigQuery into Google Sheets.
2. This will work for read-only use cases from BigQuery into a Google Sheet and then onward to an AppSheet App. Any data that is written by the App to the specific tab in the sheet which is connected to Big Query will be overwritten by the next BigQuery refresh, which would cause data loss for the app user.
CONNECT GOOGLE SHEETS TO GOOGLE BIGQUERY
Google Sheets now has a new data connector for BigQuery. General usage of this feature is outlined in this blog post.
A detailed step by step guide is available here.
Additional information and row limits is here.
Currently available to G Suite Enterprise and G Suite Enterprise for Education customers.
Not available to G Suite Basic, G Suite Business, G Suite for Education, and G Suite for Nonprofits customers
When you harness up a Google Sheet to a BigQuery table in your Google account, you end up with a very special type of tab/sheet with a “database” icon:
However, this table cannot be connected to from within AppSheet, and the following type of error will be displayed:
What you will need to do is set up a refresh schedule in your Google Sheet. In the following screenshot:
Click the three dots next to (1), which brings up
Refresh options as well as
A scheduler for the refresh
You will also need to set up an extraction of the tab/sheet - in our example we have a special tab called “Schedules” and another tab called “Extract 1” - this second one is the data that you can connect to with AppSheet. The following screenshot shows:
Specify a new or existing sheet as your extraction target
CONNECT APPSHEET TO GOOGLE SHEETS
If you have configured all of the above, AppSheet can now use your "extract" tab/sheet as a Google Sheets Data Source:
Remember, you should mark your connection as "read only" in Appsheet as there is no ability to write back to BigQuery with this configuration:
And if you have configured a schedule, e.g.:
Each time the schedule runs it will refresh both of the two tabs. Each time, BigQuery will overwrite any changes made to the Extract table.
This is one of the reasons why this connection from Appsheet should be considered a Read-Only connection.
The BigQuery to Sheets connection currently has a row limit of 10,000 rows. You may want to consider writing specific BigQuery queries to achieve one of the following characteristics:
A row limiter by date to less than 10k rows.
A row limiter by some other dimension (geospatial, product, region, etc) to less than 10k rows.
An aggregate query which rolls up information to less than 10k rows.