Write data to BigQuery from Looker Studio

Question

I have a table in BigQuery that I use as data source for my Looker Studio reports, and it comes from the aggregation of some external tables in a database (Odoo). Because the database is updated quite often, the query that creates my table needs to be run ideally every time one opens the reports, to make sure the info is updated. Basically, the query in question takes the tables from the external database and aggregates them, all in one go.

I know this could be done with a personalized query directly in Looker Studio, without even saving my table in BQ. But that's almost too much! In that case the query would run not only every time the user opens or refreshes the report, but also whenever they click on a menù or a filter (even though some caching will happen). I tested it and the feeling is that it slows down the fruition of the report because queries take longer respect to having the main table saved in BQ.

Therefore, I would like to enable the user of my reports to push a button and update the table stored in BQ (so literary run a "create or replace table" with a button). In this way they can update the data whenever they like, which is stored in BQ, and after that they can play with the report with more agility.

Is this even remotely possible you think?

Because of costs, I would like to avoid scheduling queries every 5 min or similar (In this sense the personalized query is more efficient).

Thanks.

Answer 1

You could create a cloud function that uses ```HTTP triggers ``. Insert the trigger URL as a button on your Data Studio report, button action type: Navigation, button link URL is static.

You can have the query/code that updates your BQ table in the cloud funtion. When a user clicks on the button on data studio, it triggers the cloud funtion and then updates the table.