Architecture of a company-wide Google Apps Scripts

Question

In our company we use Google spreadsheets heavily. I would like to automate some things, for instance every minute I would like to call our bank's API and save all the new transactions into a spreadsheet.

What I'm missing in the official documentation is some major architecture best practices.

First part of the question: Who should be the owner/runner of such user-neutral company-wide script? I would not like to bind it to my personal account. I want it to run seamlessly even after I leave the company some day. Is some artificial technical user needed?

If it makes any difference we plan on moving to team drives in future.

Second part of the question: I would like the scripts to have persistent storages (in the example above - for the record of transactions). I'm considering to be a rule that every script is bound to a spreadsheet which acts as its data store. Is it a good idea?

Answer 1

I generally recommend that clients provide me with an "artificial technical user", as you say, to keep that owner alive and away from my personal account. Which also avoids using up my daily quota especially for regular triggers.

I'd avoid binding the scripts to a GSheet as you'll then get issues with potentially using GitHub or creating add-ons in the future. I generally make all scripts in a separate library (Bruce McPherson did various tests and never found a performance hit) and then link them with a very small script to a script contained in a GSheet if needs be. This allow eases separate development whilst the script is being used live.

GSheets do provide a fair amount of storage (2 million cells) but if you reach that - and the sheets do tend to slow down - take a look at using Firebase for which you get 1GB free and there is a great Firebase library.

There are various general recommendations for Apps Script development. I link a few here.

Happy scripting!