Limits to application building in Google Apps Script

Question

We are a Google Apps for Your Domain enterprise account, and Real Estate brokerage. We want to build a web application that ties together several Google Apps services. It would be great if we could do it all in Google Apps Script, but at the same time may stretch the limits of what's possible in Google Apps Script. We do not have the time or resources to do full application development using Google Web Toolkit (GWT). Is a framework approach the right solution?

We want to build an application that allows our agents to create a real-estate listing record. Each record is large, with 300 - 400 form fields per record depending on the property type. Many of the fields are 'lookup' fields with specific values in either a select-list format, or multiple checkbox format. (e.g. roof-type = choose one: slate, shingle, rolled-roof; appliances = choose all: refrigerator, stove, dishwasher; etc.)

Each record will also need associated photos in original high resolution format and also smaller resolution sets for display in various contexts. Each record would have 24-50 1MB photos. I'm thinking that we could use and integrate Google Drive for the photos because the process can be simplified for the user to drag and drop a folder from desktop to Google Drive. Having the images stored in Google Drive, and only referenced from the application would solve part of the implied storage question. I read that there is a 200MB ScriptDb Quota in Google Apps Script so I can see that being a potential deal-breaker just for the 'data' alone. I don't have an exact database storage requirement, but I know we'll have 700 records to start and that number will grow to several thousand.

The users of the application are all internal, so GAFYD auth integration is a nice benefit.

There is no form api currently, so how do we create the data entry form in the first place? It appears we would need to manually create the form, or else create a sample spreadsheet to auto-generate the form. But then how do we enhance the form to modify the select lists and attach validation rules, and dynamic form behaviors like creating/showing/hiding additional elements based on user input (e.g. enter # of rooms; then enter dimensions for each room).

Another potential showstopper is the resizing of photos. We want access to original photos in order to create further marketing materials, however in the application UI we would need to use various sized images at smaller dimensions for efficiency. (e.g. show list of properties with one thumbnail to represent each record) I guess there would be methods in the Google Drive api to create sub-folders to store the resized images, but is there access to graphics manipulation software like gd (maybe through an API to picasa)?

A record should be viewable in different layouts and views. For example full record detail, summary view, marketing flyer view.

Once a record is created, messages need to be sent to the agent creating the record, plus an internal team who processes the record for workflows including copy writing/review + marketing. That seems to potentially fit with the new Google Groups? Once the record is 'approved' then the application needs to generate a marketing email to several hundred external recipients; selected based on business rules from various pools. So the application would need additional storage, or possibly address-book integration to be able to manage contacts.

Future edits to records (e.g. price changes, photo changes) need to trigger the review/approval workflow.

Is Google Apps Script capable of handling the size, scope and complexity of this type of application? Or, would the recommended route be using a micro framework such as http://bcosca.github.com/fatfree/ to tie together all the Google Apps components using their respective APIs?

Answer 1

There is no form api currently, so how do we create the data entry form in the first place?

There are two, actually: both UiApp (and the drag-and-drop GUI Builder for it) and HtmlService can show arbitrarily complex forms.

I'm thinking that we could use and integrate Google Drive for the photos because the process can be simplified for the user to drag and drop a folder from desktop to Google Drive.

Drive is integrated with Apps Script.

I don't have an exact database storage requirement, but I know we'll have 700 records to start and that number will grow to several thousand.

You might want to try Google Cloud SQL as your storage, which is 100% natively supported in Apps Script and is a "real" SQL database. However, several thousands records is tiny if you are storing the photos in Drive... ScriptDb could probably scale to several million records in that case.

Google Groups and Contacts are integrated.

Answer 2

Google's documentation can be found here: https://developers.google.com/google-apps/

Google Apps Script is sometimes surprisingly powerful, but it won't be very fast for the amount of data you're implying.

As you said, ScriptDB has a size limit, so it can't store everything. Spreadsheets are limited to 256 columns per sheet and 400000 cells. My way around this is splitting my data into a set of spreadsheets with a set of sheets. If you're in real estate, you can probably split your data by region and neighborhood/area to achieve something similar. If you really want to compact things, you can store a row of data as a stringified JSON object in one cell. However, it will no longer be human-readable.

Unless you're willing to pay extra for storage, it sounds like your pictures will fill up a Drive account fairly quickly. I'm not familiar with images in gadgets, so I'm not sure if they can be embedded from Drive.

I have no experience with Forms, but you can build a gadget using UiApp and call appendRow on a spreadsheet sheet to add the contents of all your fields. And by building your app like that, you can specify valid values for things (and have those read from a "config" spreadsheet).