Augmenting SmartSheet

Hi there SmartSheet Community,

We are using an existing system for job management and for accounting, and have introduced SmartSheet for the management of project plans. In order to meet the requirement for consolidated reporting of project financials and capacity, we have carried out a deal of augmentation around SmartSheet. Data are extracted from our job management/account system and from SmartSheet, and these are fed into Excel workbooks for health checking, capacity planning, project time analysis and project financial analysis (all of which are vital management tools).

The key additions to SmartSheet were to:

· Create a master template from which every plan sheet is created so that every plan sheet has the same structure of locked rows and locked columns (to prevent formulas from being modified by non-admin users).

· Automatically nominate whether a row is a Deliverable, Milestone, Task or Comment by inspecting the value of the Duration field and determining whether the row has child rows beneath it.

· Automatically calculate the status of a task (i.e. Not Due To Start, Not Yet Started, In Progress Early, In Progress, Overdue, Completed Recently, Completed or Unscheduled) from its start date, end date and today’s date.

· Automatically calculate cost budgets based on who is assigned to a task and how must effort is estimated for that task (and then total these budgets up for the whole plan).

· Validate all data being entered to pick up errors (e.g., the percentage complete on a milestone may only be blank or the numbers 0% or 100%).

I’m wondering who else out there is doing something similar so that we may be able to share ideas…

Find more posts tagged with

Formulas

Reports

Project Management

Comments

J. Craig Williams

Rob,

I can't see the image.

Are you doing the automation items you list in Smartsheet, Excel, both, in between?

Craig

Rob Hagan

Hi Craig,

First, apology for the rubbish stuff intruding into my original post and here is the pic (the purple is JobBag, the orange is SmartSheet, the aqua is Excel, and people are people). JobBag is a job management and accounting system.

All of the automation is being carried out in SmartSheet itself, except the “automation” in the Health Checker (that is implemented in Excel and takes an export of all rows in all plans from SmartSheet as input - currently some 12,000 rows). The Health Checker detects situations like #REF or a missing formula (where SmartSheet’s auto-fill has failed to act). It also highlights project management risks like “no effort or assignee for a task that is due to commence in 4 business days”.

I maintain sets of tasks that represent common actions and seek to reuse these in project after project. Over some eight months or so, 14 of these “fragments” have been identified, implemented and refined, and that has significantly reduced the time to create plans from proposals and also reduced error rates.

One sticking point, however, is that it is quite tricky to introduce these fragments into a plan from the “fragments” sheet and I would dearly love the following addition to SmartSheet that would get me around this one troublesome piece.

<spec>

Allow a normal user (i.e. a non-admin user) to copy and paste whole rows within a sheet, or from one sheet to another, including updating the contents of locked columns and adjusting the references in formulas.

</spec>

Just putting it out there... Your thoughts?

Cheers,

Rob.

Melbourne, Australia

Screen Shot 2016-12-15 at 8.18.43 am.png

J. Craig Williams

Well done.

I am not familiar with JobBag. Every day is a new reference to some tasking tool, CRM, SaaS, or app that is one someone's must-have list. My brain is full.

I'd really like something along the lines of your spec. I keep running into the copy rows without formulas problem and am looking for a work-around in the API.

8 months is impressive. I keep getting approached from customers that think something like this is a long week and then vacation time!

I'd love to work for an org that no only identified those 14 things but then went ahead and got them assigned and implemented. I have painful stories from my past on how NOT to do that. Stories like yours are refreshing.

Thanks for sharing.

Craig

Rob Hagan

Ahh, a work around in the API. Interesting. How would that operate I wonder?

I do have a procedure that I follow (manually) to do the transfer of a fragment from the central fragments sheet (the source sheet) into another sheet (the target sheet). Note that each fragment has a green coloured name/description row and a yellow coloured usage instructions row followed by the real body of the fragment.

The procedure may seem laborious, but I can carry it out in under 2 minutes, and the payoff is that the predecessors and all other relevant data are retained. Again, a focus on reduction in error rates, reduction in effort and encouraging re-use of best practice.

All plan sheets (the target sheets) have rows 1 and 2 locked. Every column mentioned in step 10 is also locked. Row 1 is used to contain metadata (things like a [Number of Tasks] column containing the formula =COUNTIF([Row Type]:[Row Type], "Task")) as well as the formulas that are copied down through every used row in a sheet - these being the ones nominated in this procedure. Anyhoo, read on...

-------------------cut here

Instructions for copying one item from this source sheet to a target sheet (and you must be an admin user).

Repeat these instructions for each item to be copied. If you have many items to copy to the same target sheet and you are very careful, you may defer doing steps 9, 10 and 11 until after the multiple copies are complete. The danger is that you may forget to do steps 9, 10 and 11 at all.

In this source sheet:

1) Move the desired item to start at row 3 (keeping it collapsed).

2) Fully expand this chosen item.

3) Select all rows of the body of the item (i.e. omit the green and yellow header rows from your selection, but do note any relevant "Usage:" instructions for later application).

4) From the right click menu, select "Copy to Another Sheet..." and choose the target sheet (filtering in the target sheet selection window is very useful).

5) Copy the Predecessors of the whole of the item (i.e. from row 3 onwards). They will stay in the paste buffer until needed in step 8.

6) Exit without saving.

(immediately open the target sheet)

In the target sheet:

7) Move the newly inserted item to start at row 3 (you will move it into its final position later).

8) Paste into [Predecessors]3 (i.e. row 3 of the Predecessors column) - this will recreate all of the predecessors that were lost during the copy from the source sheet.

9) Unhide all columns in the target sheet.

10) In order to recreate formulas in the item that have been lost during the "Copy to Another Sheet...", do the following copy/pastes:

10.1) Copy [Row Type]1 and paste it from row 3 to the final row of the newly inserted item.

10.2) Copy [Task Status]1 through [Error Message(s)]1 and paste them from row 3 to the final row of the newly inserted item.

10.3) Copy [Project Type]1 through [Task and Effort]1 and paste them from row 3 to the final row of the newly inserted item.

10 <alternate> Instead of steps 10.1 through 10.3, you can Expand All on the target sheet and do these same copy/pastes from row 1 down to the whole depth of the target sheet. This will recreate all "whole of column" formulas. Formulas that exist only in row 1 do not need to be recreated.

11) Re-hide all columns in the target sheet from [Admin Comment 1] to the right hand end of the sheet.

12) Move the newly inserted item to your desired location.

13) Apply any relevant "Usage:" instructions that you noted back in step 3.

14) Indent the item as is appropriate for its new location.

You may like to take a copy of the newly inserted item and lock all of its rows so that you don't have to repeat this exercise (for that particular target sheet). Keep this locked version under the Templates deliverable in row 3 (create this if it is not already present).

As the deployed item uses the formulas from the target sheet, these fragments are version independent.

-------------------cut here

Cheers,

Rob.

J. Craig Williams

Nice.

The API can not update data in a Predecessors column.

There will still be some manual work.

We also can't toggle formulas, or find & replace in them.

For some customers I have created new sheets with the formulas as != instead of =

and then at the end of the process find & replace != with = and the formulas "come to life", but there's no going back and only works for new sheets, not existing ones with formulas already active.

Well done on the doc. Lots to think about.

Craig

Rob Hagan

Cute trick with the != construct. I understand the limitations, but worth having in the kit bag. Every bit helps...

BTW, What language do you write in, or what environment do you use, with the API? So far, I've been fine with the Export function from SmartSheet and then importing into Excel (though recently I've discontinued using INDIRECT() for that, bitten the bullet and done the whole macro "copy/paste" thing).

Rob.

J. Craig Williams

I took over some API code written in php - I can fix other people's code (and find bugs) without being overly proficient at the language. I dabbled in php quite a while ago.

The code was horrible, even for php.

Now I'm working in Python and testing things as I go.

Currently, this is mostly to give me more tools, I can do most of what I want using Zapier or Appsheet, but want to control the security more -- I trust me.

Depending on your plan, have you looked at the ODBC connector?

https://www.smartsheet.com/apps/smartsheet-odbc

Craig

Rob Hagan

OK, I could do Python. Would be fun. Thx.

One thing to mention is that I have set up a separate sheet for each of the main formulas (that I use in our plan sheets) to do comprehensive regression testing. The [Error Message(s)] formula, the largest, runs to 3,859 characters (just under the 4,000 character field size limit) and I have over 100 test cases to ensure that I don't get any "egg on face". Just because SmartSheet looks and feels like a spreadsheet doesn't mean that we shy away from proper Software Engineering ;-)

Ahhhh, the wonderful, ubiquitious ODBC connector. Yup, would be great, but we run a Mac shop. We received confirmation from SmartSheet just a couple of days ago that support for the ODBC adventure is not for the likes of us.

Rob.

J. Craig Williams

I recommend Crash Course by Eriic Matthes

https://www.nostarch.com/pythoncrashcourse

The Learning Python tome (1594pages!) published by O'Reilly is next on my list.

Comes recommended by a friend who programs Python for a living.

One can't go wrong with an O'Reilly.

I'm setting up my unit testing for my new environment over the holidays.

I shifted to Spyder because of the IPython console. I may go back to UltraEdit/Studio or Eclipse, but I don't know (yet) how well they handle Python. Spyder was recommended by the course instructor I took on-line.