Build automated reports using your BigQuery data

[khrystyna_grynko]

Business Benefits

Decrease the amount of time you spend creating reports and charts.

---

Open your Google Sheet and click on Data > Connect to BigQuery.

Google BigQuery connects naturally with Google Sheets if you’re a G-Suite or Workspace client, otherwise use a free add-on like OWOX BI BigQuery Reports Add-on. If you want to use Excel, you can connect it to your BigQuery data using ODBC Driver.

Choose the Google Cloud project you want to work with in your Google Sheet.

Choose your dataset and table, or write a custom query to get, for example, only the columns you need for your analysis.

Use a custom query if you need only specific fields and not the whole table. For example, to get data from two columns, product and price, you could use:

SELECT product, price FROM orders.table
```![](https://lh4.googleusercontent.com/nWFiD7E-hDLHSiH294ciyNueQSOr9203kJe2556AXN_HJFDu_Dvq6ZlFzgZKOwIpd7CnH6Y4En2W50edthtS5erYSQiewnoFfq-sIzTXWxl0coBm7iSThI9U7YzPot3Y3r7mp6sr)

After you click on Connect, you will see a pop-up that says that your data is connected.

![](https://lh4.googleusercontent.com/BLGix8iZS6liSggAJpTeUYCEYR6tKnVP0yKd674DkkZ4BqTnIfPTBotQas2j45vM-pdBM2eJTbxABc_0OIfsdv_jaYtn4ICD_8_A0JV-ln4TQnGDNKv9Im6Dd8U4x2lkeYQSMtHc)


## Explore the table that is displayed and check if you have all the fields you need for your analysis.

![](https://lh6.googleusercontent.com/ZPaUrHdw_PgChQO4h66aAgETeeIaMHdhZ8hiLKMFV4Wu1V8s-_JuoC8optryYSI7ac0nDSP3JAVOnzkYGuxKh3u7xMllsAvIhct_qv0A5czUvLB1KKNdCeS4I17u2PNZMIHWMQmL)

![](https://lh6.googleusercontent.com/ZPaUrHdw_PgChQO4h66aAgETeeIaMHdhZ8hiLKMFV4Wu1V8s-_JuoC8optryYSI7ac0nDSP3JAVOnzkYGuxKh3u7xMllsAvIhct_qv0A5czUvLB1KKNdCeS4I17u2PNZMIHWMQmL)


## Complete your table with calculated fields, if you need to add extra information based on your data.

For example, you can extract the year from the date to add to a ***Year*** column using `=year(date))`, or you can calculate VAT from the price for a ***Taxes*** column: `=price*0.2`.

![](https://lh4.googleusercontent.com/O2hkPSxAfNGfp1SblmJG7b4Ri60why0dO268yZ_nqizbTmIO7dP_NaK4R3-a90anUEhGEx6Op34oXxT6N4sVFygxGWIh36wHlLwBTamQ7Pt1Tx0o254uI4K7lAkAqnSIzF43Y2HI)


## Create a pivot table based on your data.

A pivot table summarizes the data of a more extensive table. For example, if you want to analyze the sales per city per month, best practice would be to have cities as Y axis lines, and months as X axis columns.

![](https://lh6.googleusercontent.com/KRvJfi3ztB8eYGz8TXv6-wTYBNpPoqYBE5CscWfr4sys6VuunF5ra6393ODXxKYoHxm9WB1L4BsTloeof9Vs6Lj6twYE0cB8i_gAlnz6cRMDhxbgetxLlUdUyaieD-AfXRIIy1wa)

![](https://lh6.googleusercontent.com/2Brpw8tBVKpHiComBxmamb65GHDEBh54F76kmG8jnV0qc55gKNtDnljBAOdDlD3qPm0muZB0ghq1PQ6w8ZEO1kmg-pJ4AqKJK0S0_qcNrNz4D6oBb_a_dHGLs2wNdpqmkeVyNOLn)


## Based on your pivot table or other data, create a chart to communicate important changes.

![](https://lh6.googleusercontent.com/sjm7RBT7b8fnJyGGjP9c1nw455p7pABVgZY1aJfK6pk4DkENKAqCpAQQgqlyjLjzQCV8asafVk3L2K1edPLQHs9ya-lFelA87xBmQhbrqydL6GcyCViLZpmQVhSU9XqG9Hg3IW_c)

![](https://lh4.googleusercontent.com/pnzXq_4ucrKC8wK8afKLahTj5Ym_9Md2tTpiwDca8pXDklVqrztLbeiwwYkpJQP8P3PXd-hZMi6c85pdXaXK6dbA6jQiISqIID7lGtmgw03gxJk_mWz7FnoAURkRilfhN8n2MzUn)

Last edited by @hesh_fekry 2023-11-14T15:25:42Z