Capture historical data for KPI dashboard

Having real-time data available for our dashboards and reports has improved our management capabilities. I want to be able to share how we have been doing over time, as well. I want to capture and report KPIs over time. I have a report that can capture the information I need but have been trying to figure out how to capture this data on a recurring (e.g. Weekly) basis. I can send a report as an attachment on a scheduled basis, but then I have to get the data back into the Smartsheet environment. Anyone have ideas to share?

Find more posts tagged with

Reports

Dashboards

Importing and Exporting

Comments

J. Craig Williams

Brian,

You'll need a third party tool (I prefer Zapier www.zapier.com for low sheet count systems) or the API. Set up a Date column to be used for a trigger (Sunday's usually work) and have it capture the data to another sheet.

I have successfully done this (using Zapier) for burn-down charts.

Craig

Brian Hacker

Thanks for the quick reply, Craig. I was looking over Zapier and another similar service. I

I'll follow your suggestion and give it a try.

-Brian

Brian Hacker

Craig,

I've looked at Zapier and Smartsheet. I didn't see the collection of actions and tests between both tools to get through your suggested recipe. Can you help me understand which tool and which function you would use for each:

Trigger on a day of the week?

Add a new row to a sheet?

I assume links (in Smartsheet) are used to fill in the columns.

thanks, Brian

J. Craig Williams

Brian,

Short version (as I'm behind in other work):

1. Zapier Zap #1:

A Zap to force updates daily

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

2. Smartsheet side:

new column for day of week

=WEEKDAY(TODAY())

3. Smartsheet side

create new sheet for historical capture

4. Zapier Zap #2

trigger on changes to sheet, filter on the ones you want, and action copy row to new sheet (sometimes add row is better)

Some known issues:

Zapier (and other 3rd party tools) do not scale well. More sheets needs same functionality points to do-it yourself API work (though I have thoughts on that but no budget to implement)

Zapier may complain at first because so many rows are being (potentially) updated. This is resolved by contacting them with the finished Zap and explaining that the filters you have set up will prevent the Zaps from successfully finishing (the copy/add row action) except for a limited number.

An example, I had setup a recurring task list for a customer with 100 or so tasks, some daily, some weekly, some monthly, etc...

Zapier was used to create a new instance of the recurring task. Each day, the 100 or so rows would be updated and next instances of a smaller set (less than 10) might get created (passed to a different sheet). But it only worked after confirming with Zapier that I knew what I was doing.

Craig

(that was longer than I expected)

smartsheet_pgh

You can also just use Zapier's copy row event and schedule it daily.

Sign in to join the conversation!

Quick Links

Trending discussions

Smartsheet access
A collogue of mine had been using Smartsheet and is no longer with the Org. Im the administrator and can't find where the timelines and Gaant charts are. Is there someone at Smartsheet who could help me?
Is Smartsheet HIPAA Compliant?
I work for a behavioral healty practice and we are interested in using this tool to manage several projects NON related to any patient or confidential information. We would have to integrate those record keeping systems for this to become an issue with compromising that type of personal data?
Mr
hi would like to create a simple patient tracking system for our small clinic where i can record the time & duration a patient appeared in our establishment. Using a central sheet where it records all the data to be accessible by the admin. There will also be Four (4) forms to be accessible by the three department. Concept…