The ways we have become smarter by using Smartsheet

The Ways We Have Become Smarter Using Smartsheet

Session proposed by: Lily Lee, Program Manager, School of Humanities & Sciences

Ways we’ve used Smartsheet in H&S IT:

• Project Management
  • Built out a project management solution with a Smartsheet vendor
  • Project plans
  • Weekly project status update form
  • Dashboard used by project managers
  • Portfolio dashboard and reports for managers
• Return to Campus Exception Request form
  • Forms submitted by department staff
  • Use workflow to route to Dean’s Office staff for approval
• COVID Building Density and Occupancy Dashboard
  • Display the guidelines and definitions related to building occupancy during COVID
  • We update the guidelines based on changes to University and County rules
  • We used row-level access to only show users their buildings and rooms
• COVID Building Usage vs Density Dashboard
  • For Facilities team - compare the allowable density of a building to the actual usage based on Lenel data
  • Weekly manual export of Lenel data
• Course Kit Request form
  • Form for faculty to request course kits
  • Used workflow to route to Dean’s Office staff for approval
  • Built a dashboard to display the financial impact of these requests
• PPE Requests and Inventory Tracking dashboard 
  • Faculty who return to campus can request a PPE kit
  • Displays metrics about requests, inventory of available kits, etc.
• Return to Campus Schedule Tracking
  • Tracking form for individuals who are approved to come onto campus for a specific department
  • Tracked their planned visit, we did not have to collect data about their actual visit
• System Access Requests form
  • Used by our IT team to manage access for our various systems
  • One form per system
  • Completed form is routed for approval, then confirmation emails are sent to the requester and their manager
• Field Research Approval and Travel Exception Requests
  • Available through Cardinal Recovery site
  • Form used by individuals who need to travel or complete field research
  • Built out workflow for approval routing
• Integration with ScheduleOnce
  • Used Zapier to link Smartsheet and ScheduleOnce (time reservation tool)
  • Pull data from ScheduleOnce into Smartsheet to display it on a dashboard for the user
  • Used to verify that individuals who are scheduled to come to campus for a specific department
• Other smartsheets created
  • Equipment ordering and tracking forms
  • Facilities request form
  • Course completion acknowledgement form for a faculty member
  • HR processes - replace paper forms, automate the workflow process, and track the status of outstanding items
  • IT EDP forms and workflows
  • POC-IT event registration form and workflow

Creating a form and a workflow

• Example: creating an order form for food that requires approval
• We use Lucidchart to map out the request/workflow process
• Create a sheet to collect your data
  • Email column - must be in the format of Contact List
• Convert the sheet into a form
  • Can remove any fields you want
  • Can add additional fields
• Add logic to the form
  • If fruit is selected, show the What Type field
• Go back to the sheet and set up an automation
  • Can automate the request for updates - to provide the status or comments, to fill out additional fields, etc
• In the sheet, create an approval workflow
  • Choose the fields to display
  • Add conditions (approve, deny)
  • Choose who to notify
• Once the form is submitted, the workflow process is kicked off
• Approver receives an email, can click a button to approve or deny
• Workflow process continues, sends email to specified users with the approve/deny status, etc
• Automated reminder emails: Add a column to your sheet to calculate the reminder date and trigger it to send an email if the form hasn’t been filled out by that date

Managing projects in Smartsheet

• We built out a project management solution with a Smartsheet vendor
  • Project plan - uses our own template, can be viewed in Gantt chart, sheet, or card view
  • Weekly project status update form
  • Dashboard used by project managers - status metrics, weekly summary, project status history, upcoming tasks, Raid Log metrics, open raid items, PO tracking (manual), recent invoices 
  • Portfolio dashboard and reports for managers
  • Financial tracking sheet for project managers - manually tracking our POs and invoices
• 10000ft resource planning software - Smartsheet is now integrated with 10000ft - https://app.10000ft.com/ 
  • Provides a high level view of team member’s availability and workload
  • Integration shows up in Smartsheet, can connect projects between the two systems and feed updated data back and forth
  • Contact Sean Brandt (seanbra@stanford.edu) to learn more about how we use this integration. There will also be a presentation on this at Smartsheet Day.
• Talk to Lydia Barrett (lbarrett@stanford.edu) or Dave Bunger (dbunger@stanford.edu) about how the PMO is using Smartsheet to manage their projects

Upcoming events

• Stanford Smartsheet Day
  • Virtual event in February 2021

Misc notes:

• Dave Bunger is working with Smartsheet to get it approved to use High Risk data
• We aren’t using integrations to PeopleSoft, Oracle, etc.  This requires us to import/update data on a regular basis or know when changes are made.  Our highest priorities for this are:
  • Org data
  • People data
  • Location (building) data
• Users don’t need to log into Smartsheet to fill out a form
  • You can require people to log into Smartsheet to access this form, option under Security
  • We typically include a name field and an email address field to collect this data on the form
  • We have also added validation to verify that the email address is a Stanford address
• Users must log into Smartsheet with Single Sign On in order to access a sheet, dashboard, report, etc.

Questions and feedback:

• These look better and more user-friendly than using Excel, it isn’t just a glorified spreadsheet