Complex Google Workspace/Spreadsheet Workflow - Seeking Advice on Optimization

[Coupland Signs]

Hi everyone,

I’ve developed a complex Google Workspace/Spreadsheet system for my small business, and I’m looking for advice on whether I’m approaching it the right way, or if there are better methods to optimize and streamline this workflow. I've run this through AI to hopefully make it more readable.

Project Scope:

• Main purpose: It manages our quoting system, creating and sending quotes to customers (as PDFs) and generating CSV files for direct import into QuickBooks Desktop.

• Scale: Over the past few years, it has grown into a huge project—currently around 8,000 lines of code, with multiple users working with these spreadsheets daily.

• Data volume: We process over 100 quotes weekly, each containing detailed customer and order data, generating 75-column CSV files for every approved job.

System Structure:

• Main spreadsheet: Users interact with a spreadsheet that has 38 hidden sheets and 4 visible ones, handling all logic for quoting, line items, and customer data.

• 7 database spreadsheets: These manage data like users, customers, inventory, and pricing. Some are read-only via importRange(), others are written to using SpreadsheetApp.openByUrl(), and a few are used both ways.

• Multiple users: Each user in the office has their own copy of the main spreadsheet. I roll out a new version every few months. Users select their details using a dropdown, which are then stored for use during the quoting process.

• UI: I use a mix of sidebars, modal dialogs, and standard spreadsheet cells with dropdowns. Most user interaction happens on a single sheet, avoiding the need for scrolling.

Key Workflow:

1. Users create or select customers and line items, pulling data from linked databases.

2. Quotes are generated, emailed to customers via GmailApp(), and saved as CSV files (75 columns) to Google Drive. Each quote is logged in a database with its status as "Quote."

3. Once a quote is approved, the data is re-imported for final production notes and resaved as needed. The status is updated to "In Production" in the log.

4. After production, the CSV file is imported into QuickBooks Desktop via a 3rd party app (Google Drive for Desktop conveniently syncs the files to a local folder).

Challenges:

1. Script authorization: Each time a new spreadsheet version is rolled out, users have difficulty with authorizing the script and initializing importRange().

2. Spreadsheet breakage: Despite locking down most sheets, users sometimes break their copies by dragging cells or overwriting formulas, requiring a replacement spreadsheet.

3. Speed: Performance is a significant issue. For example, opening a file takes about 7 seconds, and initiating production takes around 16 seconds. These delays, while manageable, can become frustrating during busy periods.

About Me:

I’m not a professional developer—this project has grown organically as I’ve learned Google Apps Script and adapted the system to fit our evolving needs. My main concerns are scalability, long-term maintainability, and optimization (I’m at the mercy of Google).

I’d love to hear if this is the best way to manage a project of this size. Ideally, I’d like to convert it into a full-fledged web app hosted on my own domain, but for now, Google Apps Script is extremely convenient due to its integration with Drive, Gmail, etc.

[Keith Andersen]

Sounds pretty impressive if you ask me. Lots of work there for sure. Obviously there are better database systems but they would require you to learn additional languages such as Python or MySQL etc. or hiring someone to create it. So, staying with the current eco system I would suggest the following;

Instead of user spreadsheets, create a web app in app script and deploy it incorporating a login process for your specified users only. The coding required for this is fully within apps, script and very similar to your sidebar scripting and modal dialogue scripting. So the learning curve isn't significant and there are many tutorials on how to do this on YouTube. Additionally, there are many that could assist you with this less expensively because it is a fairly easy process within apps script itself. Certainly less expensive than some other programming languages and trying to incorporate them into a database system.

This would completely eliminate breakage of spreadsheets as well as users having to give permissions to use both the sheet and import range.

Saleability? Don't buy a trucking company to accommodate a volume that UPS could handle. How much bigger do you anticipate getting? Google has a per spreadsheet cell limit of 10,000,000 cells. Will any one database exceed this hard stop?

Multiple open shared spreadsheets with multiple users accessing the same data ... Google gets a little wonky. This functions better with a web app scenario.

Your code might be able to be optimized - without seeing it it's impossible to know. Obvious point is to make sure calls to the sheet(s) are kept to a minimum and. This is a balance between hard coding variables and dynamically needed variables. Also, which iterating process (loops) you use could affect time significantly. Also, too many importranges can really stall a sheets performance.

Not intimately knowing your process or data extent and volume - I can only generalize. Forgive me if you've already addressed some of my points. I do believe that any time/ energy you commit to an app script web app will pay significant dividends in the short term for you.

Hope that helps some.

Cheers
Keith

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/16ab4535-2229-495d-bfdb-9b7378fb7895n%40googlegroups.com.

[Coupland Signs]

Thank you so much for your reply, I appreciate the thought you put into your response.

A nice Apps Scripts Web App does sound like a good idea. A serious overhaul, but within my abilities. How would you suggest I stored the quote data as it was being built (before saving to CSV)? HTML data-attributes / tables? I know a little about PHP, and love the concept of "Session data". Is there an equivalent that could be used here? Currently, the users spreadsheet is being used to hold that information and it's very easy to wrangle up when needed.

As for size, currently our biggest database (Line Item Log) contains 21, 000 lines, with 75 columns (~1.6m cells), and that covers about 3 years of orders. This log is used for Sales Analytics, and is browsable through a dialog that allows searching and adding items to the current quote. There's room for growth, and with some revisions, I think I can make it work for us, for a long while. But I have considered selling this system to other businesses like ours though. If that ever did happen, I'm not certain it could exist as-is. 

And lastly, I've done my best to minimize calls, harness the power of loops and optimize my functions. It's ever changing. I do think that if I could eliminate some of the dynamically needed variables it could help a lot. I thought about utilizing PropertiesService.getUserProperties(); and guiding each user through a process for setting those up when receiving a new Spreadsheet. Doing so would effectively hard-code those values, correct? 

[Coupland Signs]

Oh, and I just thought about it. The PDF's we send out are constructed in a sheet, and collected using UrlFetchApp.fetch(url + exportOptions, params).getBlob();

I can easily build an HTML version of the quote, but I'll need to find a way of saving it and sending it, which I am currently unfamiliar with.

[Keith Andersen]

Sounds like scalability isn't an issue at present and separating data is an option for future consideration. I.e. current and archived.

As to storage - I would prefer Spreadsheet for ease of use/access. Perhaps csv for archiving or backups.

Excellent idea on PropertyServices for variables. Couple ideas on that.

Let's say you need values from cells A2, B6, G7, K12 and Q10 to be used in various functions and calculations. Instead of a getRange("").getValue() for each, create a helper cell that joins them all in one cell. I.e. =join(",", A2, B7, G7, K12,Q10). Then make one call to this helper cell and gather them all at once into an app script variable and turn it into an array for easy reference in any function.

Return it to a string and store one user variable in userPropertysservice containing all variables to be used in functions.

This has an added benefit of helping not reaching limits on spreadsheet calls.

I'm gonna think a little on the HTML vs PDF question.

That's the thoughts for now.

Cheers
Keith

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/776a398a-7adb-49fd-bd9d-b502e57aed2fn%40googlegroups.com.

[Coupland Signs]

I really like the idea of joining variables into a single cell to reduce the calls, great suggestion! 

[George Ghanem]

Hi,

Some alternative suggestions for you.

To help with users overwriting protected cells and messing with the spreadsheets, look at doing a sidebar for data entry. This would ensure they don't touch the spreadsheet itself, but let the software enter the data in the right place.

For performance, it's a complicated subject. Data size does impact it, as well as Keith said the number of calls you make to Google services. Each call can take up to 2-3 seconds and can impact the performance severely if you have many of them inside a loop.

On the subject of creating new spreadsheets, my question is why? Can you not keep the data in same spreadsheet instead and just have a view to filter the right info for you? The proliferation of spreadsheets would be problematic over the longterm.

One other thought for you to think about is how your software is structured, are you cloning code(bad), do you have software layers and separation of responsibility to make code more modular. As code size increases, if code is not modular, it will be almost impossible to maintain..

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/05e51ab6-9f64-49b8-b49e-ba25ae68c358n%40googlegroups.com.

[Edward Friedman (Eddie)]

Here are a few relevant resources for you to consider as well. Kanshi Tanaike has looked at some of the problems you referenced, such as the IMPORTRANGE authorization, and handling lots of spreadsheets feeding into a dashboard sheet. Check out his work in these articles:

• https://tanaikech.github.io/2024/02/25/allowing-access-by-importhtml-importdata-importfeed-importxml-and-importrange-on-google-spreadsheet-with-google-apps-script/
  
• https://tanaikech.github.io/2024/09/17/simplifying-spreadsheet-management-introducing-a-google-apps-script-automation/
  

There's definitely some inspiration there for making your system more manageable.

[Coupland Signs]

I had once rejected the sidebar for user input because I like the instant calculation of using the spreadsheet cells. But I realize now that I can program the HTML/JS to do just about anything I need it to do. This may be a way forward for the spreadsheet. I have since already begun work on a Web App though.

As for creating new spreadsheets. I only create a new spreadsheet as a way of "releasing" updates. Sometimes the "UI", which is how I see the main spreadsheet, undergoes radical changes. The easiest way to update everyone is to just make a copy of the "beta" sheet that I have been working on. The outdated versions are unshared to the users, and deleted. 

I have done my best to create modular code. I have obsessed over efficiency, but I am limited in knowledge. I would like to make a video to demonstrate the usage of the system to better communicate what's going on. It's hard to find time to do that. 

Thank you for your reply.

[Coupland Signs]

Those links from Kanshi Tanaike are VERY interesting. Definitely relevant to my situation. Thank you for sending those! 

[George Ghanem]

If you want, I would be happy to sit through a video session with you to see what performance issues you have and give some pointers.

Feel free to take me up on the offer.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9f41e48f-1188-4031-8020-0cc710f69444n%40googlegroups.com.