2

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.

migrated from superuser.com Nov 12 '18 at 22:43

This question came from our site for computer enthusiasts and power users.

  • Welcome. You could use a single sheet to handle all the products and create an add-on to make easier to have the script available for all the spreadsheets that need it. By the way, the question looks to be too broad for this site. Please edit it to make it specific. – Rubén Nov 13 '18 at 15:48
  • Thanks. I have added more detail and reduced the scope of the question. Let me know if any more edits are recommended. – John Walker Nov 14 '18 at 14:58
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.