google sheets and kloudio

How to Run Automated Reports with Google Sheets

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.

Try Google Sheets automated reports at no cost with a free Kloudio account.

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.

First, install the Kloudio add-on for Google Sheets, which you can find here. Click Install > Continue and then choose which Google account to which you want to install the add-on. 

google sheets automated reports

Click Allow to finish the installation. Congratulations! You’ve added Kloudio to your Google Sheets.

google sheets automated reports

2. Set up your database connection.

Next, open a new Google Sheets document and navigate to Add-ons > Kloudio > Run Report.

google sheets automated reports

This will open the Kloudio sidebar. Click Sign-in with Kloudio to log into your account, or create a new account to get started. 

google sheets automated reports

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.

google sheets automated reports

In this window, you can designate the database from which you’d like to extract your data. Click the Screen Shot 2017-03-23 at 4.57.40 PM.png on the right and enter the details of your database in the pop-up window.

google sheets automated reports

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.

Report creation.gif

In your Kloudio account, click Reports in the sidebar and tap the Screen Shot 2017-03-23 at 4.57.40 PM.png 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.

Report running.gif

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-upthen 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 ScheduleKloudio uses this schedule to update your Google Sheets automated reports with fresh data, even when offline.

Report refresh 2.gif

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?

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Kloudio empowers you to do more with your data

Recent Posts

elt vs etl

ETL vs ELT: Which Is Better? [+ 5 Pros and Cons]

The main difference between ETL vs ELT is the process order and transformation stage, as well as the time it takes to perform both of these aspects. It’s up to you to decide what process is best for your data needs. This guide will help.

Gut Feel vs. Data Driven Decisions: Which Is Better?

“Trust your gut" is the trademark advice of mentors and grandmothers alike, and it remains a trusted mantra for many successful entrepreneurs. When making data driven decisions is preferred, is trusting your gut still a good idea?
All articles loaded
No more articles to load
Scroll to Top