Generate custom sales reports automatically

Connect to your sales data warehouse and automatically generate presentations for particular customers.

Last updated: December, 2019

Annual and quarterly reports are a standard part of business for many enterprises. Luckily, automation can help eliminate the repetition inherent in these periodic obligations. If you find yourself needing to analyze and present findings from large sets of stored data, consider using automation to help streamline your reporting.

This solution creates a tool that connects to sales data in BigQuery, an analytics data warehouse, from directly within Google Sheets. A configuration sheet allows a user to provide parameters for the report, such as the Account Name and Region. With the click of a button, a customized report with the latest sales data is automatically created in just a matter of seconds!

Note: This solution requires a Google Workspace Enterprise account and a Google Cloud Platform account and project.

Technology highlights

• Uses the Sheets data connector for BigQuery to access tables in a data warehouse from directly within Google Sheets.
• Uses Google Slides to create a templatized report.
• Uses Apps Script to create a chart in Google Sheets, and merge it, along with sales data, into the template report.

Try it

Set up a Google Cloud Platform project

This solution requires a Google Cloud Platform account and project. The service used in this solution, BigQuery, has a sandbox environment that you can use to test this solution.

1. Sign in with your Google Workspace Business, Enterprise, or Education Account credentials.
2. In the GCP Console, select or create a new GCP project.

Create a templatized slide deck

1. Make a copy of the template slide deck here.
2. Identify the unique ID of your Slides document. The ID can be derived from the URL: https://docs.google.com/presentation/d/slideId/edit

Set up the configuration spreadsheet

1. Make of copy of the template spreadsheet here.
2. From the spreadsheet, open the script editor by selecting Tools > Script editor.
3. Copy and paste your Slides document ID into line 1 of Constants.gs replacing YOUR_SLIDES_ID and maintaining the quotes.
4. Save the changes by navigating to File > Save.

Generate a new report

1. Navigate to the Generator tab of your spreadsheet.
2. Choose an Account Name and Region in drop-down cells.
3. Navigate to the Data Results tab and click Refresh in the bottom left-hand corner of the spreadsheet grid.
4. Return to the Generator tab and click on the large Generate button at the bottom of the spreadsheet grid to initiate the creation of the report.
5. When prompted, click the Review permissions button.
6. Select your Google Workspace account from the list.
7. Click the Allow button.
8. Once the script finishes executing, navigate to Google Drive and click on Recent in the left-side navigation bar. Your newly minted report will be at the top of the list!

Next steps

To learn more about how a similar solution was built, check out this blog post. You can also view the full source code of this solution on GitHub to learn more about how it was built.

You can read more about BigQuery in the product documentation, and learn how to load your own data directly or through solution providers.

Feedback

Were you able to get the solution up and running?

It worked!

Great! Check out the "Next steps" section above to learn more.

There was a problem

Bummer, let us know what went wrong. If you have found a bug in the code, report the issue on GitHub or submit a pull request.

If you have an idea for another solution you'd like to see featured in our gallery submit a request on the GitHub issue tracker.