MIT app inventor and google sheets

[yunana]

Hi! I’m currently building a bookkeeping app, and one of its features is a proceeds report
that comes with a user account system. I’m planning to use Google Sheets for generating and storing these proceeds reports.

My question is:
Is it possible to automatically create a new Google Sheet (or tab) per user account, based on a template sheet that already contains formulas, so that each user gets their own copy (with the same formulas), rather than all accounts sharing the exact same sheet?

Basically, I want to duplicate a template sheet for every new account — is this doable within MIT App Inventor + Google Sheets integration? If so, what’s the best approach?

[ABG]

According to
https://www.google.com/search?q=google+sheets+limit+on+number+of+sheets
you only can have 200 sheets per spreadsheet.

As an alternative, consider adding an extra column to the account activity sheet identifying the user account id.

Then filter your queries.

There might be other limits on Google Sheets that will cap your volume and speed.

[Still_Learning]

Yes possible. But imagine what will happen if you have more than 100 subscribers... How could you manage? Your sheet may take lot of time to load.

Think of ABG advise. Use limited sheet with query option

[ABG]

You probably need more advanced techniques from

FAQ Section: Google Sheets

Starter Guides Download a Google Sheet as CSV for the Media Folder and load it at startup - ABG AI2 Google Sheets Component AI2 Spreadsheet component help page - MIT Creating a Credential for Google Sheets - @Gordon_Lu Google link to service account creation May 2023 Peek inside a Service Account .json file - ABG Error reading credentials from stream, 'type' field no specified How To HowTO: Log DateTime Values - PU discussions HOWTO: Get A Range from a Google Sheet - TimAI2 (Also see Web…

[ABG]

After some study of
http://ai2.appinventor.mit.edu/reference/components/storage.html#Spreadsheet

I would consider setting up your sheets with the year as the sheet name, forcing transactions into shards.

This affords you 200 years of transaction storage.

If you encounter slowdowns in December, further thought would be needed for smaller shards in the current month.

In bookkeeping, this is called "closing the books".

[TIMAI2]

Yes, you could create a completely separate templated spreadsheet for each user/subscriber.

To do this from AppInventor, you would need to use suitable google drive/sheets extension/s.

For example:

[FREE] GS Connected - A Swiss Army Knife for Google Sheets

[gsConnected] This is an offshoot from my GDConnector extension, and could be considered to be the big brother of my Google Sheets for AppInventor extension. With this extension, the blocks and the google apps script will only use the google drive and google sheets stored on the developers google drive - the one who sets up the apps script. This removes the need for any authentication, but does have privacy issues for users. You will be able to perform some basic drive functions to create fol…

This would all have to happen on your your own google account (suggest you get a paid one for features and bandwidth)

[TIMAI2]

You may need a separate gas script to copy and existing template.

Let me know how your get on.