How to Automate Reports in Google Sheets using Data from Google BigQuery

icon Get in-depth insights

Top 30 handpicked Google Data Studio dashboards for marketers

icon Get in-depth insights

Top 30 handpicked Google Data Studio dashboards for marketers

We continue our series on how to automate regular marketing reports. We’ve already talked about how to automatically update reports and send them by email to Google Analytics 4 and Google Sheets. In this article, you’ll learn how to do the same with reports built in Google Sheets using data from Google BigQuery.

Note: This post was originally published in August 2019 and was completely updated in February 2024 on the Google Sheets report for accuracy and comprehensiveness.

Why Google BigQuery?

Before we talk about settings, a few words about the features and benefits of Google BigQuery for those who aren’t familiar with this cloud storage service.

Advantages of Google BigQuery:

  • Rapid Processing Power: A fast cloud solution that allows you to process terabytes of data in seconds
  • Versatility for All Business Sizes: Suitable for both small and large companies
  • Cost Efficiency and Scalability: Cost-effective and easy to scale
  • Serverless Architecture: Doesn’t require servers, capacity reservations, and maintenance
  • Transparent Pricing: Transparent pricing policy – pay only for the data processed, with no hidden fees
  • Flexible Data Processing: Flexibility in data processing – access tables with data in SQL, and it’s also possible to use JavaScript functions.
  • High Security and Reliability: Reliability and safety are confirmed by numerous certificates – PCI DSS, ISO 27001, SOC 2 & SOC 3 Type II – so you can safely store your customers’ credit card numbers, email addresses, and other personal information.

    Advantage of Connecting BigQuery to Google Sheets

    Connecting BigQuery to Google Sheets enables seamless integration of powerful data analytics and spreadsheet functionality, fostering efficient collaboration and informed decision-making. Here are a few advantages of Connecting BigQuery to Google Sheets:

    Seamless Data Access

    Google BigQuery and Google Sheets integration allows direct access to BigQuery's large datasets within the familiar Sheets interface, eliminating the need for complex data migration or additional tools. This feature simplifies working with big data, making it as straightforward as handling a standard spreadsheet, thereby enhancing productivity and accessibility for all users.

    Real-time Insights

    The capability to refresh data in real time ensures that analyses and reports in Google Sheets are always based on the latest BigQuery data. This is essential for timely decision-making in dynamic business environments, allowing users to rely on up-to-date information for accurate insights.

    Collaborative Analysis

    Combining BigQuery's data storage with Google Sheets' interface promotes collaborative data analysis. Teams can work together in real time, sharing insights and contributing to data-driven projects within a familiar spreadsheet environment, which accelerates decision-making and encourages diverse input.

    Simplified Reporting

    The integration simplifies the reporting process, enabling users to create and automate detailed reports from BigQuery data without needing advanced SQL knowledge. This makes insightful reporting accessible to a wider audience, streamlining the process and democratizing data analysis.

    Visual Data Exploration

    Leveraging Google Sheets' visualization tools, users can generate dynamic charts and graphs directly from BigQuery data, facilitating trend and pattern identification with greater ease. This enhances data analysis by allowing complex insights to be communicated in a visually engaging and straightforward manner.

    No Advanced Skills Required

    Analyzing BigQuery data in Google Sheets does not require advanced SQL or data analysis skills, making big data accessible to a broader audience. This lowers the barrier to entry for big data analysis, empowering more individuals to incorporate data into their decision-making processes and promoting a data-driven culture.

    Easy to automate data exchange

    Another feature of Google BigQuery that’s worth highlighting is its convenient integration with external services. The repository has a lot of ready-made libraries and connectors that make it easy to automate data exchange. For example, with OWOX BI, you can import data from Google Analytics 4, advertising sources, call tracking services, email newsletters, etc., into BigQuery:

    Here are some more useful tools for working with BigQuery:

    Uncover in-depth insights

    BigQuery Budget Forecast Toolkit

    Download now

    Bonus for readers

    BigQuery Budget Forecast Toolkit

    .

    BigQuery Data Transfer Service

    BigQuery introduced a Data Transfer Service tool that automatically delivers data from Google’s advertising services. It currently works with these services:

    • Google Ads
    • Campaign Manager
    • Google Ad Manager
    • YouTube
    • Display & Video 360
    • Google Merchant Center
    • Google Play
    • Search Ads 360
    • YouTube Content Owner

      You can set up the integration in a couple of clicks, after which all information from these services is automatically available to you in BigQuery.

      Combining data in Google BigQuery

      To build reports based on complete data and then automate them, you need to combine data from different sources in BigQuery. Here’s an example of how this can be done:

      1. First, collect data from your site in Google Analytics 4.
      2. Complement it with information from other sources using Google Sheets.
      3. Add cost data from advertising services– for example, through OWOX BI.
      4. Import all this data into BigQuery. If you’re a Google Analytics 360 customer, you can do this using the BigQuery Export feature. If you don’t have Google Analytics 360, you can connect OWOX BI and use it to import data from Google Analytics 4 into Google BigQuery.
      5. Transfer information from your CRM and ERP systems to BigQuery to combine it with the data from your site.
      6. Also, in BigQuery, download more information from Google Sheets of any kind
      7. If you use call tracking, upload call and chat data to BigQuery. OWOX BI has integrations for five call/chat services.
      8. Do the same for email newsletters.
      9. Finally, use the Data Transfer Service, which imports data from Google and YouTube advertising services.

      Measure CPO and ROAS in GA4

      Automatically link your Ad Platforms cost data to Google Analytics 4 conversion data, so you can analyze your marketing KPIs and make fully informed decisions

      Start Free Trial
      Automate your digital marketing reporting

      Working with the OWOX BI BigQuery Reports Extension

      After you’ve combined all the data in Google BigQuery, linked it by a key parameter, and built the necessary reports, you can automate the uploading of these reports to Google Sheets. To do this, use the OWOX BI BigQuery Reports Add-on. It’s similar to the Google Analytics Sheets Add-on but requires knowledge of SQL syntax. To access data in BigQuery, you need to build an SQL query, after which you’ll see the data in the desired structure in Google Sheets.

      How to Create a Report in Google Sheets Based on Google BigQuery Data

      First, install the BigQuery Reports Extension in your Chrome browser. To do this, open a Google Sheets document, and in the Extensions tab, go to the OWOX BI BigQuery Reports tab, and select Add a new report.

      If this is your first time working with this add-on, you’ll need to provide access to your Google BigQuery account.

      After that, specify the project whose data you want to see in the report. Then select an SQL query from the drop-down list (if you created queries earlier) or add a new query by clicking Add new query.

      Now run the query by clicking the Save & Run button.

      At this point, the Extension will access your data in BigQuery and perform calculations. Then, in your table, a separate sheet will appear with the query results.

      Automatically Update Reports Based on BigQuery Data

      To avoid having to run a query manually every time you need data, you can set up a scheduled report. To do this, go to the Extension tab –> OWOX BI BigQuery Reports –> Scheduled Refresh.

      When the window pops up, choose how often and at what time you want the report to be updated, then click on the "Save" button. Then, specify the time to start the SQL query. If necessary, activate an email alert to update the report. Save the settings.

      Done. Now, your report will automatically be updated according to the set schedule.

      Email reports using Google App Script

      Finally, in order not to miss important changes in your KPIs, you can configure the sending of reports by email using Google App Script.

      1. Prepare Data: First, create a Google Sheet and enter the data you want to include in your report.
      2. Open Google Apps Script: Access the Apps Script editor within Google Sheets by navigating to Extensions > Apps Script. Give your project a name in the Apps Script editor.
      1. Write the Script: Develop a script function to retrieve data from the sheet. Format the data for email, either as HTML or plain text. Use MailApp or GmailApp to create a function for sending emails.
      1. Test the Script: Execute the function in the script editor to ensure it runs smoothly. Check the recipient's email to confirm the delivery and formatting are correct.
      2. Automate the Script: Set up a trigger in the Apps Script editor for automatic execution at specific intervals like daily, weekly, etc.
      3. Deploy and Monitor: Deploy the script for regular use and keep an eye out for any errors or issues that may arise during its operation.

      Done! Now reports will come to your email, you won’t miss anything, and you’ll be able to make changes to your marketing activities in time.

      If you have any questions, feel free to send us a message in the support chat, and we’ll be happy to assist you.

      table

      Simplify BigQuery Reporting in Sheets

      Easily analyze corporate data directly into Google Sheets. Query, run, and automatically update reports aligned with your business needs

      Simplify Reporting Now

      FAQ

      Expand all Close all
      • How to Get Data from Google Sheets to BigQuery Automatically?

        Use Google Sheets as a data source in BigQuery via the connected sheets feature. Schedule data transfers in BigQuery to automatically load data from Sheets at regular intervals. This ensures your BigQuery datasets are always up-to-date with the latest data from Google Sheets, facilitating seamless data analysis and reporting.
      • How to Automate Reporting in Google Sheets?

        Leverage Google Apps Script or Google Sheets macros to automate reporting. Scripts can be written to fetch data, generate reports, and even email them on a schedule. Use the Script Editor in Google Sheets to create custom functions that automate repetitive tasks, including data import, analysis, and formatting for reports.
      • Can You Connect BigQuery to Google Sheets?

        Yes, you can connect BigQuery to Google Sheets using the Google Sheets Data Connector for BigQuery. This allows you to directly access and query your BigQuery datasets from within Google Sheets, enabling real-time data analysis and reporting without leaving the spreadsheet environment.
      • How to Save BigQuery Results to Google Sheets?

        To save BigQuery results to Google Sheets, use the BigQuery Data Connector in Google Sheets or export your query results to a Google Sheets document via the BigQuery UI. This process can be automated by scripting with Google Apps Script, allowing for regular updates and analysis within Sheets.
      • How to Automate Data Collection in Google Sheets?

        Automate data collection in Google Sheets by using Google Forms for surveys or feedback, which directly populates Sheets with responses. Additionally, employ Google Apps Script to pull data from APIs, databases, or other sources into Sheets on a schedule, ensuring your data collection is always current and automated.
      • Can You Generate Reports from Google Sheets?

        Yes, you can generate reports from Google Sheets using its built-in features like pivot tables, charts, and conditional formatting to analyze data. For more advanced reporting, use Google Apps Script to create custom reports that can be automatically updated and distributed, making it a powerful tool for data analysis and reporting.
      • Can You export BigQuery to Google Sheets?

        Yes, you can export data from BigQuery to Google Sheets using Google Apps Script. This method involves writing a script in the Apps Script editor within Google Sheets that uses the BigQuery API to run queries and then programmatically inserts the results into a spreadsheet. This approach allows for automation, such as scheduling the script to run at regular intervals, thus keeping your Google Sheets data up-to-date with the latest information from your BigQuery datasets.
      • What are the benefits of automating reports in Google Sheets using Google BigQuery?

        Automating reports in Google Sheets via Google BigQuery saves time, ensures real-time data access, enhances accuracy, enables complex analysis, and fosters collaboration. It streamlines processes, updates reports seamlessly, reduces errors, leverages data power, and facilitates team communication for more informed decision-making.

      icon Get in-depth insights

      Top 30 handpicked Google Data Studio dashboards for marketers

      icon Get in-depth insights

      Top 30 handpicked Google Data Studio dashboards for marketers