There is no point in describing in this post how this connection is done, as\u00a0Google documentation<\/a>\u00a0does that already. But there are several tips worth sharing here.<\/p>\n First of all, is the use of\u00a0parameterized queries<\/b>. The most straightforward way of using Connected Sheets is creating a direct connection to a single Big Query table, and so, to all its rows.<\/p>\n But, perhaps we need some level of aggregation for the data we fetch? Or perhaps the data must come from several different tables?\u00a0Or, even if all aggregation and analysis calculations are going to be done in the spreadsheet, maybe we don\u2019t need all rows, just a specific range? Or perhaps we need all of these?<\/p>\n In all of the cases aforementioned, the\u00a0Write Custom Query<\/i>\u00a0option is what needs to be used. The\u00a0Write Custom Query<\/i>\u00a0option allows you to specify which data must be fetched from BigQuery using an SQL query.<\/p>\n As well as the\u00a0Write Custom Query<\/i>\u00a0option, for further flexibility, it\u2019s possible to parametrize your query with values from selected cells in the spreadsheet.<\/p>\n In the image below we can see an example of a parameter configuration:<\/p>\n (Note<\/b>: when creating the query parameter, the selected cell must already contain a value). Once the value of any of these parameters is changed, the cells that depend on the affected connection query can be updated by just clicking their popups\u00a0refresh<\/i>\u00a0text. Or go to the\u00a0Data -> Data connectors -> Refresh options<\/i>\u00a0to update a specific or just all connections in the spreadsheet.<\/p>\n A very important factor related to query parameters is to know how tables are\u00a0partitioned<\/b><\/a>\u00a0and\u00a0clustered<\/b><\/a>\u00a0in BigQuery. To make a query more efficient, it is important to use, if possible, a\u00a0where<\/i>\u00a0clause, using columns that the searched table uses for partitioning or clustering. This allows the BigQuery query engine to restrict the data it needs to scan in order to process the query and thus improving performance.<\/p>\n The use of partitioning or clustering not only improves BigQuery usage performance but IMPORTANTLY reduces costs; as Google charges for BigQuery usage based on the amount of scanned data, a wise use of partitions and clusters can reduce costs, a lot.<\/p>\n <\/p>\n The lesson here is that when linking Sheets with BigQuery, try to use partitioning and clustering as much as possible. If the data tables are given and it\u2019s not possible to change its schema, try to use their partition and cluster columns as parameters for the spreadsheet connecting queries. If you can design the tables schemas, set their partitioning and clustering based on the parameters the spreadsheet requires. That way, BigQuery\u2019s performance, and cost will be optimal.<\/p>\n Another interesting option related to Connected Sheets is\u00a0Scheduled refresh<\/b>. It allows you to set the refresh timing and frequency of connected sheets and related objects. If we add to the mix the\u00a0Publish to the web<\/b>\u00a0option, then Connected Sheets become a way to create quite capable web applications.<\/p>\n <\/p>\n Google documentation lists\u00a0Business planning<\/i>,\u00a0Customer service<\/i>,\u00a0Sales reports<\/i>\u00a0as possible use cases for Connected Sheets. We found another possible use:\u00a0Invoicing<\/i>.<\/p>\n This is especially salient when the source for such invoices is millions of data rows, for example, for\u00a0large accounts<\/b>\u00a0in the telecom sector. These kinds of customers usually have invoicing requirements that are very different from normal subscribers and require a high grade of flexibility.<\/p>\n Manually invoicing shouldn\u2019t be an option, as it is prone to errors and mistakes. Connected Sheets are a very handy tool to use in this case. They are highly configurable and flexible, which allows them to conform to the customer billing requirements and once the spreadsheet is set up, invoice generation becomes an automatic or semi-automated process where human errors are taken out of the scene.<\/p>\n Also, the fact that the invoicing data comes from BigQuery and is available to other tools, like Data Studio, makes it possible to give the customer additional information related to their invoices, such as reports and dashboards. All this with the confidence that, as all of them use the same\u00a0Single Source of Truth<\/i>, they are totally consistent with each other.<\/p>\n","protected":false},"excerpt":{"rendered":" (or: Saving $$$ doing Invoice Calculation and Generation using Google BigQuery and Google Sheets). Since VisiCalc and Lotus 1-2-3 popularized spreadsheet programs, they have become an ubiquitous tool. Spreadsheet\u2019s tabular format and dynamic calculations make them a perfect fit for accounting and data analysis. But like multifunctional \u201cswiss army knives\u201d, spreadsheets have some rough edges. […]<\/p>\n","protected":false},"author":21,"featured_media":42869,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[848],"tags":[65,145],"class_list":["post-48029","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-cloud-en","tag-bigquery","tag-google-sheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/posts\/48029","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/comments?post=48029"}],"version-history":[{"count":0,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/posts\/48029\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/media\/42869"}],"wp:attachment":[{"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/media?parent=48029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/categories?post=48029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.makingscience.com\/us\/wp-json\/wp\/v2\/tags?post=48029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"https:\/\/www.makingscience.co.uk\/wp-content\/uploads\/2021\/09\/e1ff2392-captura-de-pantalla-2021-08-13-a-las-10.05.48.png\")
You can pick a cell in any of the existing sheets to create a named parameter. The named parameter can then be referenced in the query using the\u00a0\u201c@PARAMETER_NAME\u201d<\/i>\u00a0notation. In the example we are showing above, we have named the parameters @START_DATE and @END_DATE and they have been related to cells C2 and D2 respectively.<\/p>\nInvoicing; an Interesting Use Case<\/b><\/h2>\n