Using Google Scripts to Query Multiple Google Sheets to A View

0
votes

I have a Google Spreadsheet that stores employee availability information for my workplace. Every Sheet has the format shown in the example below, with the date fields changing based on the week for which I am collecting availability. Employees submit availability every week by submitting a Google Form that is linked to these Sheets on the backend. My workplace leadership has directed that I limit each Sheet to contain at most one week of data.

09 Jul - 13 Jul Responses

Timestamp         | Email Address   | What is your name? | [Mon Jul 2018] | [Tue Jul 10 2018] | ... | [Fri Jul 13 2018] | Comments
7/9/2018 16:30:48 | [email protected] | John Doe           | Available      | Not Available     | ... | Available         | Must leave by 3:30 p.m.
7/9/2018 16:32:51 | [email protected] | Jane Doe           | Not Available  | Available         | ... | Available         | None

The problem I am encountering is that my boss wants me to generate a clean view of employee availability covering an arbitrary date span. In other words, he wants to have the option of selecting a date span and seeing all the employees who are available on each day in that date span. This is problematic for generating, say, a monthly view of availability because it involves parsing multiple sheets each covering a single week.

My intuition says I need to create a Google Web App that allows a user to query my Spreadsheet with two fields: StartDate and EndDate. I'd need some code that then iterates over every record in every spreadsheet to determine who's available on every day in that date span, push those records in object format to a list, and then use those objects to generate an HTML output on the Web App page. This would allow multiple users to generate whatever availability display they want.

What is the best way to allow a user to enter a date span and output all the employees who are available in that date span using Google Scripts?

1
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Let the form push data to only one workbook, and then use a weekly trigger to create a worksheet that has the appropriate subset of the response data. Then your boss can view the weekly sheets for weekly info, and you have a single endpoint to query for your view data. (The recommendation to use an actual db is of course superior, but as soon as bosses see $ where they saw 0, it's an uphill fight.) - tehhowch
@tehhowch "but as soon as bosses see $ where they saw 0, it's an uphill fight" So true... However OP might be able to get away with using Cloud Datastore and/or Cloud Firestore (free quota of 50,000 reads and 20,000 writes per day and 1GB of storage). - TheAddonDepot
I'm inclined to stay away from a separate database because this is something I need to be able to leave behind for my coworkers when I'm no longer here. Even if there are inefficient algorithms to generate my view, I must use them to accomodate less savvy programmers. I know how ridiculous this sounds, but that's the hand I've been dealt. - RouteMapper
@RouteMapper Such is life. If you end up going with the one workbook approach be sure to make regular backups (dumps) of your ad-hoc database (CSV exports should suffice). Google Sheets have been known to become unstable (sometimes unrecoverable) once they start approaching their storage limits. - TheAddonDepot

1 Answers

2
votes

Your current setup where you have records spanning multiple sheets is inefficient from a query standpoint and is going to be difficult to maintain as the data scales up.

I'd move all that data into a true database (google cloud has a number of storage options) and then leverage Apps Script to query said database using a date range and populate a sheet with the results.

Google Sheets are a viable storage solution for relatively small non-relational data sets. But as you scale up and you want to execute more complex queries its limitations become more evident. They can still serve as a platform to display the results of a query, but with respect to storage that needs to scale I'd recommend offloading that to a proper database solution.