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

Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…
How to Share Single Workspace Folder?
I have a workspace that I created with two subfolders. A user needs to have access to only one of them but when I have that folder selected, I can only share the entire workspace with them. Is there a secret way that I can share the parent folder and ensure that any new files created will be shared with the same people? I…
I have a Pro plan but I cannot create ANY reports
I have a Pro plan but I cannot create ANY reports. The system does not allow me to choose a folder, and consequently a sheet to create a row report and suggests an upgrade to Business or Enterprise. I'm quite new to Smartsheet and feel a bit disappointed with this limitation, or maybe I'm just doing something wrong. Alberto