If you’ve landed on this post, I’m assuming you spend a considerable amount of time collecting data from various systems and preparing that data for your next status report or big meeting? If so, you’re in the right place. In this guide, we’ll show you how to use Google Sheets for automated reports.
How, you ask? Kloudio.
Kloudio is a self-service reporting tool that integrates with your favorite spreadsheet tool (like Microsoft Excel or Google Sheets). Think of it as a spreadsheet view of any data source (i.e., MySQL, PostgreSQL, Amazon Redshift, and Oracle) or as using Google Sheets as a database.
With Kloudio, it’s possible to define and acquire your own metrics (independent of your data team or external data resources). Moreover, you can set your report to be automatically delivered to you instead of relying on a developer’s roadmap.
You can also schedule data refreshes to your spreadsheet when you need it—monthly, weekly, daily, or hourly, in the form of an automated report.
How to Use Google Sheets Automated Reports with Kloudio
1. Install the Google Sheets add-on.
Click Allow to finish the installation. Congratulations! You’ve added Kloudio to your Google Sheets.
2. Set up your database connection.
Next, open a new Google Sheets document and navigate to Add-ons > Kloudio > Run Report.
This will open the Kloudio sidebar. Click Sign-in with Kloudio to log into your account, or create a new account to get started.
This will navigate you to another window in which you can access your Kloudio account. After signing in or signing up, click Connections in the sidebar. This is where you can set up database connections.
In this window, you can designate the database from which you’d like to extract your data. Click the on the right and enter the details of your database in the pop-up window.
Add a Connection Name and designate the Database Type. Add information about your Username, Password, Host, Port, and Database.
Finally, click Save. Kloudio will use these stored credentials to securely execute your configured Kloudio reports and send them to Google Sheets, thus connecting and using Google Sheets as a database. Furthermore, you can use Sheets for downloading data and writing data back to your database(s).
3. Create your first automated report.
After connecting your database or data source, it’s time to run a report. Your report is essentially a specification of database table columns and filter criteria based on what data you’d like to extract each time you run the report.
In your Kloudio account, click Reports in the sidebar and tap the button. Select the connection you created in the previous step, and choose the Scheme/Database name from the second list of values.
Click Search to generate the list of tables from which you can choose. (If you know the table you want to include in your report, type the name or the first few letters in the next text box and click Search. Pick your table from the results.
Name your Kloudio report. Click Columns select all or individual columns for the report. If you want to apply a filter to a certain column, click Add Filter; switch to the Filters tab to apply that filter.
Click Enable Offline Refresh in the Settings tab to ensure that Auto-Refresh is turned on. Finally, click Save in the upper right-hand corner.
4. Choose a schedule for auto-refresh.
Now that you’ve created a report, you can run it anytime to view live data. You may also schedule it to run automated reports in Google Sheets.
Open your Google Sheets document. Click Add-ons > Kloudio > My Reports. Select your report from the drop-down. Next, enter your filter criteria and click Run. This will execute the report. Leave the data in the Google Sheet.
Then, go to Add-ons > Kloudio > Schedule Reports. Enable Auto-Refresh in the pop-up, then choose your desired frequency—when you’d like your sheet to be refreshed from the database. (You can even choose to have it emailed to you when it’s ready.)
Finally, click Update Schedule. Kloudio uses this schedule to update your Google Sheets automated reports with fresh data, even when offline.
Over to You
With the Kloudio add-on for Google Sheets automated reports, you can add any number of custom formula columns or charts to better analyze your data.
Kloudio will simply refresh the columns mapped to the database as defined by your schedule. Note: Leave the additional user-entered columns untouched so you don’t have to redo the analysis each time.
Kloudio’s report scheduling feature is brilliant for not only connecting your Google Sheet to your databases but also for using Google Sheets as a database itself. From the savviest to the most novice data user, Kloudio makes it easy to extract any data for analysis or reporting.
What do you find useful when extracting data periodically from a database or data warehouse?