Screen Shot 2020-05-13 at 08.52.39

How to build an automated report using Google Sheets and Kloudio

Are you tired of spending your time collecting data from various systems and websites, then preparing that data for your next status report or big meeting with leadership?

Do you want to define and acquire your own metrics independently and to have the report automatically delivered to you instead of relying on a developer’s roadmap? Kloudio’s scheduled reports feature does exactly that… with no coding involved! See how to build an automated report with Google Sheets here.

For anyone that is new to Kloudio, we’re essentially a self-service reporting tool on top of Google Sheets. Think of it as a spreadsheet view of any data source: (MySQL, PostgreSQL, Amazon Redshift, Oracle, Microsoft SQL Server, and more).

In addition, you can even schedule data refreshes to your sheets where and when you need it. Whether hourly, daily, weekly or monthly in the form of an automated report. It’s pretty easy, too.

Here’s a step-by-step guide on how to create and schedule your data refresh:

Step 1: Install Kloudio’s Google Sheets add-on

First, you will need to install the Kloudio add-on. You can find it here. You will need to sign-in using your G-Suite or Gmail id.  Click the FREE button to get started.

Screen Shot 2017-03-21 at 7.19.55 PM.png

Step 2: Setup your database connection

Once the Kloudio add-on is installed, go to Add-ons > Kloudio > My Reports. Click Login to Kloudio button on the sidebar.

Screen Shot 2017-03-21 at 7.26.09 PM.png

Screen Shot 2017-03-21 at 7.27.30 PM.png

This will take you to the Kloudio portal where you can sign up using your G-Suite or Gmail account. After signing up, click Connections from the sidebar. This is where you can set up database connections.

Screen Shot 2017-03-21 at 7.34.53 PM.png

In other words, here is exactly where you tell Kloudio the specifics of your database that you’d like to extract data from. 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 window that pops up.

Screen Shot 2017-03-21 at 7.56.20 PM-1.png

Give your connection a name, then enter the database typeusernamepasswordhostport and database name (if you have one).

After all, fields are filled, click Save. Kloudio will use these stored credentials to execute your configured Kloudio reports securely and send it to Google Sheets. This step lets you connect Google Sheets to a database and you can use it for downloading data and writing data back to the database.

Step 3: Create your first Kloudio report

After establishing a connection,  it’s time for you to create a report. This is basically a specification of database table columns and filter criteria based on which that you’d like to extract data each time you run the report.

Report creation.gif

  1. Click Reports from the sidebar and click onScreen Shot 2017-03-23 at 4.57.40 PM.png button.
  2. Select the connection you just created in the previous step from the sidebar.
  3. Choose the schema/database name from the second list of values.
  4. If you know the table from which you wanted to include columns in your report, type the name or the first few letters in the next text box and click Search.
  5. Pick your table from the results.
  6. Select all or individual columns in the report by clicking on the column name.
  7. On the right side, give a name to your Kloudio report.
  8. Both the Columns and Filters tabs help you review options for your report.
  9. Ensure that Auto-Refresh is enabled in the Settings tab. If it’s not, you will need to click Enable Offline Refresh button.
  10. Save your work.

Step 4: Choose a schedule for auto-refresh

Now that you’ve created a report, you can execute it to view live data anytime. You may also schedule it at your convenience. Go to Google Sheets, Add-ons > Kloudio > My Reports > Select your report from the drop-down > Enter filter criteria > Click Run. This will execute the report.

Report running.gif

Thereafter, go to Add-ons > Kloudio > Schedule Reports. Enable Auto-Refresh in the pop-upthen choose your desired frequency i.e. when you’d like your sheet to be refreshed from the database – Hourly, Daily, Weekly or Monthly. 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 sheet with fresh data even when it is offline.

Report refresh 2.gif

Step 5: Sit back and relax…

cat relax.gif

You can add any number of custom formula columns or charts to your sheet to visualize your data. Kloudio will simply refresh the columns mapped to the database as defined by your report. Leaving the additional user-entered columns untouched so you don’t have to re-do the analysis each time.

You can see how Kloudio’s scheduled reports feature lets you not only connect your Google Sheets to any database. Extracting any much-needed data but also delivers it where you need it without any coding or IT involvement necessary!

The importance of self-service business intelligence cannot be overstated in today’s information economy but automated refreshes take it one step further by enabling anyone to be both effective and efficient with respect to critical data analysis.

It’s very popular among our global customers, saving them hours upon days of time, along with the costs associated with query creation, data extraction, consolidation, and distribution. With the help of just Google Sheets and Kloudio, teams can now simply view data and collaborate on it to ultimately make better decisions.

What do you find useful when extracting data periodically from a database or data warehouse? Kloudio can help you extract data in real-time or scheduled data extractions. Try Kloudio today

Share this post

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

Analyze your data in your favorite spreadsheet in less than 5 minutes

Recent Posts

A Data Lake is not a Data Warehouse 2.0

Data lakes are gaining an increasing amount of traction as more organizations invest in them for their business intelligence needs. However, approaching a data lake…
All articles loaded
No more articles to load
Scroll to Top