Sheet Historian - grab snapshots of a sheet and append them to a Data Table

Jake Taylor

I'd like to be able to grab copies of a sheet and append them to rows in a datatable so I can build a "historian" over time. This is the use case: I have a sheet that all our project managers use to capture the Cost To Complete (CTC) for all our projects. Each week the PMs are asked to update this sheet with their current forecasts. I'd like to be able to trend this data over time. I've tried a Bridge workflow to copy the rows into another sheet, which works on smaller sheets, but our master CTC sheet contains over 9k rows and the Bridge 'copy rows' tool doesn't seem to be able to handle that many rows. Also we'd hit the row limit pretty quickly if we simply copied the data from one sheet to another, which is why I think a DataTable would be the best place to house the data.

I've tried using DataShuttle to offload snapshots to a CSV and then upload them to a DataTable. I'm simulating my workflow by running a DataShuttle that imports the data from the CSV into DataTable, which works the first time, but when I go to update 'forecast' time, and offload and re-import the CSV, DataShuttle doesn't append the data, it only updates, which is not what I want.

Each week I want the snapshot of our CTC to be appended to the bottom of a DataTable, even if all the data is the same.

Can someone help me with this?

Thank you,

Lindsay Whitbread

Hi Jake,

How did you go?

If you're still having trouble, try making a column formula with a unique key that uses a row_id from an auto-column and then combines that with the TODAY() function.

Cheers,

Lindsay

James Keuning

Trying to understand why the data shuttle does not work. Do your rows have a unique key column? Can you exclude that from the export, or map it somewhere else on import? It seems like if the append does not work, it's because it's seeing that key column and updating the matching records.

Jake Taylor

Perhaps I'm not using the correct column for a unique key. I had a key that was unique for every row in our Cost To Complete, sheet but I think I needed to create a key that was unique for every row in the historian. I'll try that.

Lindsay Whitbread

Hi Jake,

How did you go?

If you're still having trouble, try making a column formula with a unique key that uses a row_id from an auto-column and then combines that with the TODAY() function.

Cheers,

Lindsay

Jake Taylor

Thank you Lindsay! I did essentially that. I was able to come up with a column that contained a unique key for every row...the trick was coming up with a column that would be unique for each snapshot that would be taken. Lindsay's suggestion to use a column with "=Today()" is essentially what I did.

Thank you all!