SubJeff on 14/2/2013 at 21:09

---

This is the most exiting request I've even made on here.


Not really. Its the dullest thing ever - spreadsheets. I need to make a report template for a Google Drive/Docs spreadsheet and I've no idea where to start.

I want to be able to give users a report template to use with a spreadsheet. I've already designed the spreadsheet and the form for entering the data and we currently use a third party propitiatory program for generating the reports. But it's written in Filemaker Pro and is a complete nightmare. It's been the bane of many people's existence for years and years until I had the bright idea of using Memento ((http://mementodatabase.com/)) to collect the data on our phones and then import the csv file into the report program.

At present people use my template to generate the Google Drive template and then get the csv file from there.

I want an all-in-one solution - just create the reports from Google Drive. The reports are just aggregated data from the spreadsheet that need to be delimited by date and whatnot, with simple occurrence counts taken from the columns.

I'm sure you can do this with Google Drive but Google searches are proving pretty unfruitful. Anyone know how to do it?

Fafhrd on 16/2/2013 at 06:54

---

There's a lot of flexibility with the Google Drive spreadsheets, I did a lot of crazy report stuff with them at my old job. If you can host the CSV on a URL that doesn't require a log-in, you can import it directly into the spreadsheet with '=importdata(),' and then you can run queries either on that sheet or another sheet to boil the information down for the report you want.

[edit]It would be a lot easier to help with this if you shared an example of the spreadsheet you were trying to make a report out of.

Fafhrd on 18/2/2013 at 22:14

---

Sort of. Is there any way you can do a spreadsheet with dummy data? I realize you can't share a spreadsheet with any actual patient data, but with only what you've got there to go on, (https://docs.google.com/spreadsheet/ccc?key=0Am7JYeTpuKu8dDNlUW5fbFlpUXUxeHBfWV81ZnU3a0E&usp=sharing) this is the best I can think to come up with. To get the counts for specific date ranges you have to chain an =count or an =countif with an =filter (as I'm doing in column P), and you can have your filter and/or countif criteria be the contents of another cell (which you'd probably want to do if you wanted to get Calendar Year 2012 data, because changing date formatting for the purposes of a filter can get weird), which could be populated by a validated data list so it'll work like a drop down (and that validated data list could even be populated by another cell range, so you could do an =unique on the anaesthetic columns and use the output from that as your validated data array to automatically populate the drop down with the anaesthetics that have been used). Getting the syntax right for really long chains of filter and count calls can be kind of time consuming.

Fafhrd on 19/2/2013 at 02:48

---

They wouldn't necessarily have to copy all your sheets manually. You could have your master data sheet that's already linking to your database and then importrange it into another workbook, set up a sheet on that workbook with the validated data queries and dropdowns and then save that workbook as a Template. Then all they have to do is Create New Spreadsheet From Template and the only interactions they'll need to do are selecting the data they want from the drop downs and it'll spit out their reports.

Fafhrd on 19/2/2013 at 20:00

---

Select a cell, select 'Data->Validation' and the pop-up after that is fairly self explanatory. Set the criteria to 'Items from a List' to have it work like a drop down (you can get the entries by either putting them in manually or selecting a range of cells. And it automatically filters the cell range for unique entries, which I somehow missed).

[edit]I just remembered that there was a weird quirk when using importrange with spreadsheets that are pulling data from external sources: the base spreadsheet needs to be opened every couple of hours or else it won't populate (every morning when I got into the office first thing I had to do was go to Google Docs and open up all of my reports and just leave them open so they'd show up properly wherever they were embedded). So if you have Spreadsheet 1 that is linked to your memento database through an importdata or importhtml or however, and then your Template sheet (and any copies of that sheet that are being used by your clients) that is using importrange to grab the sheet from Spreadsheet 1, if you don't have Spreadsheet 1 open somewhere during the day it won't populate its data and the Template won't see anything. You could probably do the database linking on a hidden sheet of the template to get around this, but since I don't know the specifics of how linking a Memento database works I can't speak to any problems that might cause.