Project Management Solution

Video of the system in action

This is a workflow drawing outlining the process, and showing a snapshot of a project owner's tab from the sheet as well as the status sheet which allows one to view all the projects of a certain status, using the dropdown menu.

Overview of Projects by owners and status

This chart is generated by the overview sheet, showing a summary of projects.

Using a combination of Forms, Sheets, Slides, Docs, Form Publisher, and Data Studio, this solution helps organizations keep their Projects up to date.

All data entry is done with a form, so all data is in the sheet
On the sheet, projects can be viewed by owner or status
Form submit creates a new google doc
Form Edit updates the google doc
Docs can be found and viewed from the sheet
Projects/docs are updated with a Form edit URL
A slide deck can be maintained with project information for summary of projects in meetings
Data can be viewed on the sheet, slide, doc, and in Data Studio
The Data Studio view allows for easy filtering, sorting , and updating the projects.

Use this form to add new projects. Upon Form submission, a new document is generated with the submitted information,

All files generated by Form Publisher are in this folder. When a project is updated using the Form Edit URL, the document is updated with the new information.

This is the Sheet that collects the data from the form, then disaggregates the data into different summary sheets by status, and owner, for easy access to see the projects. Using this sheet, choose a project by selecting an owner tab, or a status tab. Open the Document by clicking the link. (It will open in a new tab) Then going back to the tab with this web page, open the Form Edit link. This will open in a new tab also. Changes something on the form, submit, then quickly change to the document (you have opened this already in a different tab) and watch as the document magically gets updated with your edited information.

This animated GIF shows how quickly you can update projects, and everyone can see the updates on the google doc which is more easily readable.

Twelch FSO Projects (Responses)

This is the Data Studio view of the system, used to easily sort and filter the data, and see another summary view of the projects.

Use this Slide deck to quickly see all the projects by status (as the projects grow and data is added, you need to adjust the range on the slide deck, it is a link to a tab on the sheet)

Projects Summary

Notes about Mail Merge Add-ons

I use three different add ons to write form submissions to a google doc or sheet. They are Form Publisher, Document Studio, and Autocrat. The only free version is autocrat, but over the years I have found it to be the buggiest and has the slowest, hardest to use interface.

Form Publisher ($79) is an add-on to the Form, so the only data that can be merged is what is submitted on the form. For this use case, Form Publisher is the one that rewrites the form data to the SAME DOCUMENT so when you edit the form, the data flows to the document, which is therefore updated. All the changes that are made to the document are recorded in version history.
Document Studio, ($29) in my opinion has the best interface, it runs in a sidebar and is very easy to work with. It is an add on to the sheet, so you can do fancy things on the form submission sheet like include vlookups, and such so that you can merge data that is not actually submitted on the form. DS also updates the document when you use the form edit URL, but it instead creates a new document with each edit. This will create a lot of versions of a project in the folder, however, it also can be setup to automatically merge the documents into a folder for each owner, so that the project documents are automatically filed according to owner (or any other field such as status).
Autocrat (Free) is also an add on to the sheet, so like DS it can merge data that is not submitted. It has in my opinion the clumsiest interface, which forces you to go through the entire setup to get to one part you want to change. It is also the slowest interface to launch.