SQLite - can it be used with Apps Script?

[Michael O'Shaughnessy]

I apologize upfront for a lengthy post however I feel a little background info is necessary.

I am an educator who works for an educational service center that serves all the school districts in the county.  I have helped many schools develop solutions using Workspace (formerly GSuite) apps.  As my skills have improved I have been asked to develop increasingly more complex solutions.  What I have always thought was missing from the Workspace apps is a the replacement app for Microsoft Access.  This is a great program that allows for file based relational databases to be created and used.  I was so excited about AppMaker seeing how it could use "tables" that were stored and accessed through GDrive, but when it was made available to educators that support was removed and as we all know now, AppMaker is no more.

I have seen Android solutions using SQLite as the database and I thought, "Why can this not be used with Apps Script?"  Being able to use a "file based" database is perfect for schools.  There will be no need for a "database manager" and student data will be protected under the "Workspace for Education" umbrella.  Also, it will be easy for others to use.

I have searched for an API or library that I could use with my Apps Scripts programs to connect to an SQLite database stored in GDrive but I cannot find it.

So, here are may questions:

1. Does such an API or Library exist and if so where can I find it?
2. Can creating or building such a library be done, if so, where would I even begin?
3. Is there another "file based relational database" solution I can use with my Apps script projects?

Thank you in advance for any and all support and information anyone can provide!

Thanks,
Michael

[frank 6000]

Hi Michael, back in the day I was very much into MS Access (IMHO one of Microsoft's best apps in Office) together with VBA.

But of course, Access was a local desktop application and when users demanded internet availability, I realised that Access had had it's day and (somewhat reluctantly) moved to PHP and mySQL as the back end database.

I had learned other languages which included JavaScript so when Google Sheets and App Script came my way, I quickly realised the potential.

However, Sheets is not designed for projects that interact with a large number of indexed rows. Therefore connecting to a 'proper relational DB makes a certain amount of sense.

This is all a preamble to say try using Sheets and mySQL as a back end - here's a link that tells you all you need to know: https://www.cdata.com/kb/tech/mysql-cloudhub-apps-script.rst

Good luck!

--
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/da77f8ef-a332-4afa-8839-0d0e3c704ccdn%40googlegroups.com.

[Michael O'Shaughnessy]

Wow!  CData looks pretty good!  And the link you gave is a great step by step approach.  I too really got into coding with VBA and Access... many, many, many years ago!!! The issue would be the cost of Cloud Hub... small schools could not afford that.  Also, this would put student data a a "third party server" correct?  This would not be good.

Access is great for those small databases like household inventory and collections.  The types of solutions I would be using a database for would be for a similar amount of data.  Possibly a student table of less than 1000 students, a classes table of maybe 100 and then throw in a teacher table of maybe 200.  Very small compared to what SQL server or MySQL would normally have.

So, thank you for the info and I will investigate it further but unfortunately I don't think it is a solution for me.

[Adam Morris]

1. Does such an API or Library exist and if so where can I find it?

Cloud-based databases in general are ample. While it's not a relational database, it's nonetheless a very good database: Firebase

https://github.com/RomainVialard/FirebaseApp

1. Can creating or building such a library be done, if so, where would I even begin?

Yes. You simply need to see how the API works, and use UrlFetchApp.fetch to interact with it. Not trivial, but doable. The nice thing about making libraries is that once someone has done it, others can benefit.

1. Is there another "file based relational database" solution I can use with my Apps script projects?

Use Firebase, it's probably more than enough.

--

[Michael O'Shaughnessy]

Thank you Adam for the resources and links!  I have done a very brief look into Firebase but have not gone more than reading some articles.  I will take a look at the FirebaseApp you linked to on GitHub.

But correct me if I am wrong, the data for Firebase is stored on Google's Cloud Servers somewhere, right?  If this is the case then a school will not allow that.  Student data cannot be stored on a cloud based server that is not under direct control of the school or the IT company they use.  This is why I was so excited about AppMaker and "drive tables".  All the data stays in the Workplace domain which would make the school district happy.  But sadly, it is no more....

Again, thanks for the input, but I still am looking for a solution...  

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BTMY_THvqS0b73J_nm-dwCCSsbQ8qFeVOjq56or7MmtuA%40mail.gmail.com.

[Alan Wells]

Firebase is not a relational database.  I have very little experience with it, but from what I've read, and experienced, I'm guessing that it won't be what you want if you want to structure you're data in different tables, and be able to link the data.  Firebase is a Google product, and basically runs through your Google Cloud Platform as far as I know.  So, I don't think that it's out of your domain.  I'd think that it would be considered data that is in your domain.  That's about all I know.  As far as other alternatives go, I don't have any info on that.

[Andrew Roberts]

I'm certainly intrigued by the niche that you have found - a SQL DB that resides "locally" on the GDrive - and taking a deeper dive! 

I wonder if the various vanilla JS libraries used in sheet2sqlite could be useful. I'll definitely be exploring that further.

And then we could always code an Apps Script library from scratch that respected the SQLite file format in a GDrive text file!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a08ddc07-61fe-4e0d-bfb9-982b2b52fe0fn%40googlegroups.com.

[Alan Wells]

One potential problem with reading/writing data to a text file in GDrive using Apps Script is service quota limitations,

depending upon how much usage it gets.

You can hit short term quota limits, and daily limits.

It's more likely that you'd hit short term limits.

There is nothing that you can do to increase the Apps Script quota limits.

You can't buy more Apps Script quota.

You can't change an account configuration setting to scale up.

Text files do count against the GDrive storage limit.

I think it's a great idea to store data in your GDrive with some type of database.

Google probably won't like you if you're taking away business from Google Cloud.

[dimud...@gmail.com]

SQLite makes for an interesting solution. But it looks like you'd have to roll your own SQLite implementation from scratch as Andrew suggested. 

However, there are solutions native to Google Cloud. As Adam stated Google Cloud has a number of its own database solutions. Including:

• Cloud SQL (with supports relational databases under the hood such as  MySQL, MariaDB and PostgreSQL)
• Cloud Firestore (Firebase's native NoSQL database)
• Big Query

There are a few others but those are probably the most accessible from Apps Script. They are not free, but they may not be as expensive as you

might think (but getting schools to buy-in will be a hurdle in of itself). 

With Apps Script libraries available that support Firebase/Firestore you might want to start there. The following YouTube playlists should give you a good run-down of what Firebase and Firestore are capable of:

https://www.youtube.com/playlist?list=PLl-K7zZEsYLmOF_07IayrTntevxtbUxDL

https://www.youtube.com/playlist?list=PLl-K7zZEsYLluG5MCVEzXAQ7ACZBCuZgZ

Alan touched on the limitations of Apps Script which I've found to be a major bottleneck when integrating database solutions. I've often ended up leaning into other cloud-based services such as Cloud Functions and other Google Cloud APIs (such as Cloud Pub/Sub) to offload the work, sometimes cutting Apps Script out of the equation entirely. 

[Michael O'Shaughnessy]

Thank you everyone for your input!  I came across this idea when I was messing around APIs using a Flask website on PythonAnywhere.  I was following info on this site: https://programminghistorian.org/en/lessons/creating-apis-with-python-and-flask#implementing-our-api .  When I got to the SQLite database I realized it is just a text file that is on the webserver and was accessed by Python code.... I thought "why can't I store the db file in Drive,  make a Google Apps Script Webapp that can "read and write" from the file using the same functions as the Python SQLite3 library does??

However this is where I am no WAY over my head!  I can "see" what could be done BUT have no idea where to start!

Alan, yes, I am sure quotas will have to be investigated.  As far as the amount of data however we are talking small scale.  Maybe a table of no more than 1000 rows of student data (name, id, grade) a courses table of maybe 500 rows (course id, course name) and maybe a schedule table that combines students with courses.  BUT this is where a relational database would come in handy!

Andrew, I will look at the links you shared... maybe this can get me moving.

I just think if this can be done, it will be the "Access" replacement that I have been looking for!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1930a4cc-8130-45f7-a5a6-766878798fb6n%40googlegroups.com.

[Kim Nilsson]

Just another voice on the topic of "Student data cannot be stored on a cloud based server that is not under direct control of the school or the IT company they use."

You already do that every day with Google Workspace (G Suite for Education).

Firebase is just another Google Cloud product, just like GSFE/Workspace is, as it too is run on Google's own cloud.

Your account, your data, you're in control. Just as much, or as little, as you are with GSFE. No difference.

[Andrew Roberts]

@Michael That was the kind of thing I was thinking of. I was wondering if any of those vanilla client-side JS files would be helpful. Came across http://alasql.org/ too.

@Kim Good point. I guess it depends on the schools policies, but as you say it's all up on the cloud!

--

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/b9885c48-7d2c-418a-bed8-698aaf07dbd7n%40googlegroups.com.

[Adam Morris]

Hi Michael,

There is probably a technical way to get a public-facing SQlite server on an ubuntu box set up, and then use AppScripts to connect to that server. Databases aren't usually public though, but there are ways to protect them, but not sure if traffic from appscripts comes from some static domain. If it did, it would be simple to make iptables (a sysadmin thing) to lock it down a bit.

I agree with the sentiment that the requirements that the school house/own the database is kinda misplaced, given how modern cloud infrastructure works. 

Besides, if you did decide to go your own route you’d not be building a stack that is better locked down and protected from prying eyes. I wouldn’t want to be that sysadmin!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLh%2BafkR%2BPwd%3D9%2BuiKY86U-pfAw-XY7jUtF5jL%3DUPH1dPw%40mail.gmail.com.

--

http://classroomtechtools.com

[Alan Wells]

If you are going to use text files as the storage files, then you'll need to use the Google Drive API.

What you need to do is have a restricted scope that doesn't give the app broad access to the users Drive.

For example, the permission to permanently delete every file in their Drive without sending files to the trash folder.

You could put data into Google Docs, which are seen as zero byte files and don't go against the storage quota limit.

But I don't know if there is a restricted permission for Document Service, and users would wonder why they need to approve

permission to access their Docs for an app that isn't visibly doing anything with their Google Docs.

The scope you need to use is:

"https://www.googleapis.com/auth/drive.file"

And set it in the appsscript.json file.

There is an unfortunate problem with the Advanced Drive Service,

it can't retrieve the contents of a text file.

It can save content to a text file, but it can't read data from a text file.

What that means is that you need to use the REST API to get contents of a text file.

And if you need to use the REST API, then the user must give permission to make external requests.

This is assuming that the reading and writing of the data is being done from the server side code, and not the client side code.

That's different.  But if you're going to read/write data from the client side then you''d need to use OAuth I believe.

Firebase is basically designed to work from the client side.

Getting from "here to there" isn't a straight line.  Hopefully you can avoid hitting a dead end before you've put lots of work into it.

[Kim Nilsson]

Soon (mid 2021?) even Google's own file types will count towards storage.

https://workspaceupdates.googleblog.com/2020/11/changes-to-google-workspace-storage.html

[dimud...@gmail.com]

You can use application-specific storage with the Drive API as detailed in the guide linked below:

Store application-specific data

It uses a scope that doesn't appear to be restricted (though it may be a sensitive scope). I've experimented with it in the past to write config settings in JSON format to a text file in the user's appdata folder and it seems to be viable. Last time I tested it I only needed the following scopes:

• https://www.googleapis.com/auth/drive.file 
  
• https://www.googleapis.com/auth/drive.appdata
  

[Michael O'Shaughnessy]

Fantastic conversation everyone!!

I do agree that schools are putting info into the "cloud" BUT a lot of control of this data is protected by being in the Workspace domain which can be controlled from the Admin console.  As you all know, you can "lock" Workspace for Education Drive to NOT allow anyone to share anything outside the domain.  This is the "protectection" that I would feel OK with IF I can get an SQLite db text file stored in drive.  Please correct me if I am wrong but I don't know of any settings in the admin console that will allow me to control access to Firebase.  This is why that is a "non starter".

As an example, in Ohio every student is assigned a unique State Student ID (SSID).  If you try to share a Google Sheet that contains a student's SSID OUTSIDE of your domain you will get a WARNING stating what you are about to do.  This is the security and control that I am talking about IF it can be applied to an SQLite file.

@dimudesigns - interesting link... do you happen to have a "prototype" or just a basic use example that you could share?

@Kim - yes, we will be hit with quotas for storage now... however I have not seen anything that applies to an Education domain.... have you?  I am curious if Google will NOT apply quotas to Edu domains.

So, I will keep experimenting and searching... please keep this conversation going if anyone finds anything that may be of help.

Also, it does not have to be SQLite, I am just in search of the "Microsoft Access" replacement.  A locally controlled, file based, relational database.  Not asking much, right...?  😁

Again, thank you for all who have contributed!

--

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/35aacad9-1ae8-4f80-83d4-162417750786n%40googlegroups.com.

[Zeeple]

Hi there.  I am also an IT admin at a school in WA.  We use GSuite for Ed and I also disable the ability for drive items to be shared outside the domain.  I am unfamiliar with Firebase but I also do not know of any setting in the admin console that would control access to it.  If it were a webapp I'd recommend something like cleanbrowsing.org, which I also use.  Good luck and I am looking forward to seeing your progress on this issue!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLiumZts6nCSu7QSBMLoG6LoHEr%3DEz08NhXdEUnjm2kGNw%40mail.gmail.com.

[Kim Nilsson]

@Michael, yes, Education will continue to have unlimited storage, even without Enterprise licences.

Also, I'm pretty sure that you have control over who can access your Firebase database. 

[Kim Nilsson]

Firebase access is, of course, controlled in Firebase. 

[Alex]

There is just an another point.

As GAS is a microsystem (don't throw me under the bus) which dies every 6 mins I think JS dbs have chance to live.

You can learn AlaSQL https://github.com/contributorpw/alasqlgs Be ware you have to dump your DB every your app is ran.

[Adam Morris]

Oh that's an exciting share there. I can confirm this is a stable way of using relative databases. You can use google spreadsheets to keep the data. So imagine you have spreadsheets, with a table for students, and another table for parents, and they're linked by IDs. You can use this tool for the joins.

I've CC'd the author. I forked it and got it working by updating the submodule to the latest pull request. Does he intend to maintain it?

--

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/58e388ee-512e-4e59-97f1-79c3c90e3726n%40googlegroups.com.

[Michael O'Shaughnessy]

Oh my goodness!  I remember looking at AlaSQL a long time ago... but I just was not ready to "dive" in.  I do think it is time to revisit!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BQLVDhnvv77WLKDMAZn3PeDeBAxerzhXJhCPKY3atM4DQ%40mail.gmail.com.

[Clark Lind]

In response to you and Kim, I too am behind a GSuite domain and at the mercy of the organization. Firebase isn't an option for me since they didn't buy it as part of the package. So I am stuck trying to find workarounds like Michael. and you guys. If you have Firebase as part of your Suite, definitely use it! :) Otherwise consider purchasing it as part of the package. Then get your folks using it instead of trying to use Sheets as a substitute for a database table.. lol  (like we do!)

[Kim Nilsson]

?

Firebase has a Free Tier, last I checked?

I'm definitely not paying for my Firebase database.

Yup, just checked, I only use within the free quota.

Kim

[Clark Lind]

Thank you Kim, I'll check it out and see just how much access I have.

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/XEmz6SCIkRc/unsubscribe.
To unsubscribe from this group and all its topics, 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/CACK38a5VkeRZ-0cND1ivxH6%3DELP6tk7_xo5%2BfYSVGhjZcvPj0w%40mail.gmail.com.

[Clark Lind]

Nope... It is blocked at the GCP level. My organization has disabled all GCP functionality, making it very difficult to do anything beyond basic GSuite interactions.

On Wed, Nov 18, 2020 at 9:48 AM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/XEmz6SCIkRc/unsubscribe.
To unsubscribe from this group and all its topics, 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/CACK38a5VkeRZ-0cND1ivxH6%3DELP6tk7_xo5%2BfYSVGhjZcvPj0w%40mail.gmail.com.

[Kim Nilsson]

Ah, that's a shame.

You should befriend your superadmin. 😎👍

[Michael O'Shaughnessy]

Kim, you have me intrigued...  I have a Workplace training domain where I am the superadmin but I cannot find anywhere in the Admin Console where I can change settings for Firebase.  I can get to the "Google Cloud Platform" settings BUT it states at the top of the page that GCP is "not covered by the G Suite Agreement ''.  Because of this, schools will not allow it to be used....  

Am I missing something? If so please let me know!

On Wed, Nov 18, 2020 at 2:25 PM 'Kim Nilsson' via Google Apps Script Community <google-apps-sc...@googlegroups.com> wrote:

Ah, that's a shame.

You should befriend your superadmin. 😎👍

--

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/CACK38a71M830Bwx2v4V%2BzpFgGJsUkjkmsgGZjdaNo%2BNtxSUVRA%40mail.gmail.com.

[Adam Morris]

This thread has been really interesting actually. Thanks so much for everyone's contributions.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHNYQLhW1meZ4b2f1EVcppSf8rpcV0B7_f6VPR5pf_3xdtP-Lw%40mail.gmail.com.

[Kim Nilsson]

Michael, the organisation is free to allow use of any services. They will just have to process it like any other non-G Suite service. Just like they did when allowing GSFE. Before they allowed GSFE to be used in the organisation it was forbidden. Now it is allowed. 

I would be ready to bet a billion whatever that GSFE isn't the only app, service or extension you use in the organisation. So you already know how to and have already allowed non-GSFE services. So, just allow Firebase, based on the legal documentation of the service, just like you do with all other services.

/Kim 

[Michael O'Shaughnessy]

OK, did some experimenting.... sadly I cannot seem to find a way to get SQLite to work... but I have not given up yet.

I did however revisit AlaSQL that Alex reminded me about.  I found this video: https://www.youtube.com/watch?v=0KuSD5PGQF8 and started messing with it.

I then put together a "proof of concept" spreadsheet.  The spreadsheet has a tab of student info, a tab of studentIDs matched to schools and a results tab.  There is a menu function (DB Functions)  that you can select students by period and you can get student info and schools with another function.  I tried to follow "good" programming practices.  I have a name space for "settings", a name space for "DataConversion", a class for working with sheets/tabs (thank you Bruce McPherson - https://ramblings.mcpher.com/apps-script/apps-script-v8/multiple-script-files/ ) and the function that "loads up" the database is all on its on.

Here is a link that will ask you to make a copy https://docs.google.com/spreadsheets/d/1SGkpSiWl5QJ4budQWN_W_a7kC9lY2KUDUV6F_gV7IV4/copy

I do have some issues that I am not sure how to address and I do believe there is(are) a better way(s):

1. The function "ldDbs" is called every time a function is run!  I do not know how to "persist" the database info... or is this even possible with Google Apps Script?

2. Should the "ldDbs" function be better written as a "class" or....?

3. The creation of the tables is very repetitive in the "ldDbs" function... should I abstract the "table creating" to its own function (or even class) and just send it the info it needs?

Right now I am happy with the "speed" of the app.  It doesn't take any longer than a "Query" cell function to run.  BUT I am not sure where (or how to test) when the amount of data slows everything down....

So, have a look and let me know what you think.

Also, thank you in advance for any guidance, support and helpful hints anyone passes on to me!!

--

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/CACK38a6NBjehurWAa5qYp_YQv0exd8wzmG%2BFycxxkkVf5wP_tg%40mail.gmail.com.

[Kim Nilsson]

Hmmm, but are you using AlaSQL as the database, or are you using Sheets as the database?

The definition of database being where the actual data is held.

Kim

[Adam Morris]

This is super interesting, keep sharing!

I think one of the strengths and advantages of alaSQL is that you can read in from the spreadsheet and thus use that to persist, but alaSQL can provide the query language to do things like joins, which is not simple to implement on the spreadsheet side (and would repeat data anyway)

--

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/CACK38a4L%2Bw1UdeseDfB33x_sVFg1tErf4DCHN40cqSRnj6TD1g%40mail.gmail.com.

[Michael O'Shaughnessy]

Well... to Kim's point... good question!!  The data is stored and maintained in the tabs of the spreadsheet.  Thinking back to my days of using Access, I am picturing the tabs would be like tables in Access.  Now, when the functions from the menu are run, AlaSQL is used to create a database, create the tables (right now just 2) and the tables are filled with data from the spreadsheet tabs.  The AlaSQL database is then used to execute the queries and the result is returned to the "results" tab of the spreadsheet.  All of this leads me to.....

The database is created, used and "lost" as soon as a function is done.  It would be great to be able to "persist" the database until the spreadsheet is closed.  But I do not know if that is even possible.  So creating the db, creating the tables, loading the tables EVERY time is what I am worried about becoming a "bottleneck" at some time.

However, with my limited work with accessing online databases, you have to make a connection to the db, prepare the statement to be executed on the db, execute it, deal with the result and then close the connection.  So, really, what I have done is not that different....

Adam - yes, being able to write a "proper" SQL query with "JOINS" and "FROM" with aliases... I have to "relearn" all my SQL stuff from my old days with Access and  ADO.NET!!!

I think I am going to try to get some random customer data with some fake order info and see if I can get to a "performance" degradation...  Any suggestions on sites that offer "dummy" data?  I wonder if Microsoft still uses the "Northwinds" company as a sample database.....😁

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHQVb%2BTgtRgJNttp%3DxP3qQVV4r0vk9n6vTf2FSp%2BFqzj5bh8AQ%40mail.gmail.com.

[dimud...@gmail.com]

I wonder if there is a market for an add-on that allows users to upload and manage a SQLite database from a Google Sheet?

[Alex]

Well

When it comes to loading a database every time AlaSQL does it very quickly. I also want to note that JavaScript is generally not the best language for working with persistent cache and runtime memory.

If I'm not mistaken then all modern systems work through a similar bottleneck. Another thing is that AlaSQL has significant restrictions in working with memory it's limited by the current environment.

As for SQLite. You need any server with REST to create a database interface. Don't forget about asynchronous tasks. Perhaps even Google Cloud Functions is suitable for this if your database is small.

[dimud...@gmail.com]

Let me rephrase the question. Assuming all the technical hurdles could be overcome, is there a market for a Sheets Add-on that allows users to manage a SQLite database?

[Adam Morris]

Yes. It’s called Google Data Studio :)

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d92a188f-23ec-4eec-a8f0-8e129cf53cdan%40googlegroups.com.

--

http://classroomtechtools.com

[dimud...@gmail.com]

@Adam So a GDS community connector for SQLite. I like it!

I've been futzing around with integrating an SQLite database engine with GAS for several months now and I think I found a way to make it viable. 

Figured I'd monetize my efforts and provide a sheets add-on, but a GDS connector may be the better option.