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 G Suite Business, Enterprise, or Education account, and a Google Cloud Platform account and project.
- 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.
Set up a Google Cloud Platform project
- Sign in with your G Suite Business, Enterprise, or Education Account credentials.
- In the GCP Console, select or create a new GCP project.
Create a templatized slide deck
- Make a copy of the template slide deck here.
- Identify the unique ID of your Slides document. The ID can
be derived from the URL:
Set up the configuration spreadsheet
- Make of copy of the template spreadsheet here.
- From the spreadsheet, open the script editor by selecting Tools > Script editor.
- Copy and paste your Slides document ID into line 1 of
YOUR_SLIDES_IDand maintaining the quotes.
- Save the changes by navigating to File > Save.
Generate a new report
- Navigate to the Generator tab of your spreadsheet.
- Choose an Account Name and Region in drop-down cells.
- Navigate to the Data Results tab and click Refresh in the bottom left-hand corner of the spreadsheet grid.
- 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.
- When prompted, click the Review permissions button.
- Select your G Suite account from the list.
- Click the Allow button.
- 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!
Were you able to get the solution up and running?
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.