How can I use Google Apps to create a weekly report form, and then display the answers to that form in a nice format to outside parties?

I would like to use Google Apps to support the following workflow. I’m a Google Apps newbie, so I’m reaching out for a little help. If Google Apps isn’t the right solution, let me know, but my colleagues and I thought we should give it a try. It would be nice if this resulted in a central place where all of our work plans are stored so we avoid sending tons of work plans as e-mail attachments.

We want to have some of our employees fill out a work report once per week. Just a quick thing with three free-text sections: “Tasks worked on this week,” “Tasks for next week,” “Ongoing projects." We would like to display this in a useful format to people outside of our network. So the people who end up reviewing the information entered will not have a Google account in our apps domain, they'll just have a link.

I figured out how to create a form that submits the free text data to a Google spreadsheet. But I'm stuck on creating a nice weekly view of the data for our end-users who are not in our apps domain. Is there a way to create a webpage or a script that will grab the data for a certain week from the spreadsheet and displayed to the end-user and a nice format? The "Summary" view doesn't do what I want, because it crams all of the answers together instead of breaking them out by user and by week. I need to create a view that displays each user's answer for that week individually. So if they wanted to see responses for March 26, the user would see four separate reports for the week of March 26: one for each employee who filled out the form. Or if we just put the most recent submissions at the top of the page, that would work too. I imagine the workflow would go: 1) End user clicks link 2)Chooses week to view 3) Sees form responses broken out by user. Or it could go 1) End user receives link for this week 2) Link takes them directly to the form responses., though I'll try any workflow that you can suggest too.

A bonus question: it would be nice if the form were e-mailed to the employees to fill out every week at the same time, and the link with the responses were emailed to the end users automatically too. Is there a way to automate sending a Google form to a list of recipients?

Google Apps Scripts can have scheduled triggers (e.g.) and can send emails. Apps scripts can be embedded in spreadsheets or in Google Sites (either of which can be made visible to your target users).

I'm sure you can do a weekly view, but I'm not 100% sure how. Possibly by reading and writing from your spreadsheet. I also wonder if you could do something with Google Fusion Tables, which I seem to recall can pull data from a Google Spreadsheet.posted by idb at 11:31 AM on March 14, 2012

Tags

Share

About Ask MetaFilter

Ask MetaFilter is a question and answer site that covers nearly any question on earth, where members help each other solve problems. Ask MetaFilter is where thousands of life's little questions are answered.