Google-sheets – Is duplication of Structure and Behaviour a ‘fact of life’ in the world of Google Sheets. Is it possible to reduce that duplication further

google sheets

Please note, I am not asking how to remove duplicates from a column of data.

We are transitioning to a relational database system to store data. But, in the mean time we are using Google Sheets to store data.

Let's say we have 20 customers and each of our customers has two Google Sheets that we share to them and ask for it to be filled in and we have one Sheet per client which translates the data into a format that is useful to us. (A kind of Translation Layer pattern or Anti-Corruption Layer)

As you can see in my example folder on Google Drive, and in the image below.

We have one Google Drive folder per client (Client Folder). E.g. ClientX

The Client Folder contains, say three Google Sheets:

  • ClientOutlineData – Lists Client's products and other Client Data.
  • ProductDesignClientInterface – One tab per client product for them to insert product data.
  • ProductDesignsTranslated – One tab per client product. Based on data in ProductDesignClientInterface and ClientOutlineData to create data in a format that matches the import format of a software application that we use.

enter image description here

Since this set of sheets is repeated for each client. Where each tab has the same column structure, conditional formatting rules and formulas as the other sheets – this huge amount of duplication just does not feel right.

Let's say we want to alter the formula to generate ProductDesignsTranslated column. We would have to go through and edit all tabs in the ProductDesignsTranslated Google Sheet for each client. Which could be, say, (20 Products) * (20 Clients) = 400 steps per change (plus testing).

Even Google Apps scripts appear to have to be attached to each spreadsheet independently.

Question: Is this kind of duplication of Structure and Behaviour a 'fact of life' in the world of Google Spreadsheets, or are there any ways to reduce the duplication further?

As I say, perhaps Google Sheets are not appropriate for this kind of problem. But, I would be interested to understand one technique to reduce duplication of Sheet Structure and Behaviour in this specific scenario, or if is is possible at all.

I am a programmer familiar with OOP, Design Patterns, Relational Databases, etc. But, not so familiar with spreadsheets.

Best Answer

The kind of duplication described on the question in a broad sense isn't a "fact of life" in the world of Google Sheets as there are alternatives about how to handle Google Apps Script code like:

  1. To have code Google Apps Script attached to each spreadsheet: Create a Google Sheets add-on. Another possible path is to change the logic to have a master spreadsheet to host the code, create Google Apps Script web application, or if you are using a G Suite Business account (or similar) create an app by using Google App Maker.
  2. To edit formulas manually: Use Google Apps Script to update the formulas

Other features like spreadsheet settings (region, timezone, etc.), formulas, named ranges, data validation, conditional formatting, etc. should be updated on all the spreadsheets either manually or by code.

Related Topic