Web Apps, Spreadsheet as Database, and performance with a lot of rows

210 views
Skip to first unread message

Anchal Nigam

unread,
Jun 21, 2019, 10:50:31 AM6/21/19
to Google Apps Script Community
So Google Apps Script is quite powerful and you can do a lot with it. I have an idea for building a web-app using a Spreadsheet as the DB back-end. From a technical perspective I know everything would work. What I am not sure is how performance would be.

I am wondering if anyone has done anything similar to this and if so, did they notice any major issues/concerns, or performance problems?

For the sake of explanation, I'm simplifying my web-app use-case to a more trivial example:
  • Web-app will be for different users to submit engagement requests to our team
  • A Spreadsheet will be used as the DB, with each tab/sheet being a different "table"
  • Different "tables"/tabs/sheets will have different type of data -- like request details, comments, activity log, tasks, etc...
  • There will be a tab for permissions with a list of user e-mail addresses and what they can do (i.e. submit a request, view requests, edit requests, etc...)
  • And the web-app would tie all of these together with a web front-end for the users
I think all of this will technically work. I'm worried about performance. After enough folks submit requests, there will be a lot of rows in the sheets/tabs/tables. With any DB driven web-app there is a need to be able to select rows from a table that match a criteria. For example, find all of the engagements submitted by a specific user.

Since Sheets does not have a built-in way to select rows from a sheet that match some criteria my thought is to:
  1. Get the values in the column I want to search on
  2. Go through the array to find the value, this would give me the row number
  3. For each found row, get the row range and values
var searchQuery = "some string";
var searchColumn = 3;

var foundRowValues = [];

var columnRange = sheet.getRange(2, searchColumn, sheet.getLastRow() - 1);
var columnValues = columnRange.getDisplayValues();

columnValues.forEach(function(rowColumnValue, rowIndex) {
    if(rowColumnValue[0] == searchQuery) {
        var foundRowRange = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn());
        foundRowValues.push(foundRowRange.getDisplayValues()[0]);
    }
});


I know I could get all the values of the sheet but that might be a lot of rows and I think it would be very slow.

Thoughts?

Bauke de Kroon

unread,
Jun 21, 2019, 3:23:13 PM6/21/19
to google-apps-sc...@googlegroups.com
Hi,

Eric Koleda created an article "Why you shouldn’t use Google Sheets as a database" that explains when to consider Google Sheets as a database and when it might be better to consider another option. I think that will be helpful to you.


Kind regards, Met vriendelijke groeten,

Bauke de Kroon

G-workplace | Makes Google work for you | www.g-workplace.com
Email: bauke.d...@g-workplace.com | Mob.: (+31) (0)6 10733013





--
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.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/672e94bc-f691-4bd5-8061-c51bf3ee27bc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.


Disclaimer: This email and any files transmitted with it, are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify G-workplace and destroy this message. G-workplace B.V. has it registered offices in The Hague, The Netherlands and is registered with the Chamber of Commerce under no. 52204316.


Anchal Nigam

unread,
Jun 22, 2019, 12:30:47 AM6/22/19
to Google Apps Script Community
Thank you!


On Friday, June 21, 2019 at 3:23:13 PM UTC-4, Bauke de Kroon wrote:
Hi,

Eric Koleda created an article "Why you shouldn’t use Google Sheets as a database" that explains when to consider Google Sheets as a database and when it might be better to consider another option. I think that will be helpful to you.

Kind regards, Met vriendelijke groeten,

Bauke de Kroon

G-workplace | Makes Google work for you | www.g-workplace.com
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Clark Lind

unread,
Jun 22, 2019, 6:52:47 AM6/22/19
to google-apps-sc...@googlegroups.com
I would just add one more scenario for using sheets as a DB: your company/organization didn't purchase online DB (Firebase, etc) as part of the package, and Sheets is all you have to work with.

Faustino Rodriguez

unread,
Jun 22, 2019, 11:33:25 AM6/22/19
to Google Apps Script Community
From my personal experience about good performance, while working from a web app while having the data in a spreadsheet
- append to a datasheet if quite fast
- updating a row in a data sheet, assuming you already has the row data and want to change some values, it is also fast
- querying a datasheet using the Google visualization query also works very good (it also works great from a GAS code)

Otherwise, I would consider Firebase as a Database alternative

Steve Webster

unread,
Jun 22, 2019, 11:41:36 AM6/22/19
to google-apps-sc...@googlegroups.com
To add to the nice list from @Faustino, is binary search for 1,000+ rows. Here is some code:
//
// Utility function for binary searches ( e.g. find a specific row, fast )
//
// https://stackoverflow.com/questions/29345356/google-apps-script-fastest-way-to-find-a-row?rq=1
/**
 * Performs a binary search on the provided sorted list and returns the index of the item if found. If it can't be found it'll return -1.
 *
 * @param {*[][]} list Items to search through.
 * @param {*} item The item to look for.
 * @return {Number} The index of the item if found, -1 if not.
 */
function binarySearch(list, item,column) {
    var min = 0;
    var max = list.length - 1;
    var guess;
    var column = column || 0
    while (min <= max) {
        guess = Math.floor((min + max) / 2);
        if (list[guess][column] === item) {
            return guess;
        } else {
            if (list[guess][column] < item) {
                min = guess + 1;
            } else {
                max = guess - 1;
            }
        }
    }
    return -1;
}
// Example called function
function myFunction() {
  var customerId = 11359;
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getDataRange();
  var customers = range.getValues();  
  var index = binarySearch(customers, customerId,0);
  Logger.log(index+1); // This is the Row in the spreadsheet.
}


Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows


--
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.

Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/09e78859-5107-4da8-ad46-059d645be394%40googlegroups.com.

Adam Morris

unread,
Jun 22, 2019, 2:05:48 PM6/22/19
to google-apps-sc...@googlegroups.com
Also be aware that there is a maximum cell count, which you'll hit faster than you think if you're using spreadsheets as a database.


————————————————————————————

Adam Morris | IT Systems & English Teacher | IGB International School
Jalan Sierramas Utama, Sierramas,
47000 Sungai Buloh, Selangor DE, Malaysia

t    +60 3 6145 4688
f    +60 3 6145 4600
w   www.igbis.edu.my
e    adam....@igbis.edu.my

————————————————————————————


On Fri, Jun 21, 2019 at 4:50 PM IMTheNachoMan <imthen...@gmail.com> wrote:
--
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.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/672e94bc-f691-4bd5-8061-c51bf3ee27bc%40googlegroups.com.

Steve Webster

unread,
Jun 22, 2019, 2:47:32 PM6/22/19
to google-apps-sc...@googlegroups.com
Correct on maximum characters quota within a cell. I believe it's 50,000 character count per cell.
2 million cells per spreadsheet -- exception is the new big query connector.


Kind Regards,

Steve Webster
SW gApps LLC, President 
Google Product Expert in: Google Apps Script, Drive, and Docs 
Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 
Add-ons: Text gBlaster and Remove Blank Rows

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFgZO_PKWNoFf2nmTMhtXYV%2B%3DemJNt6_uYuWVJByiGXsns-OYQ%40mail.gmail.com.

Dimu Designs

unread,
Jun 22, 2019, 3:51:27 PM6/22/19
to Google Apps Script Community
Its been bumped up to 5 million cells per spreadsheet.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
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-community+unsub...@googlegroups.com.

Anchal Nigam

unread,
Jun 24, 2019, 10:22:04 PM6/24/19
to Google Apps Script Community



On Friday, June 21, 2019 at 10:50:31 AM UTC-4, IMTheNachoMan wrote:

Wilson Galter

unread,
Jun 25, 2019, 2:52:24 PM6/25/19
to Google Apps Script Community
Hi
IMTheNachoMan, 
Thank you for sharing the link, it's very useful to me.
Just for the sack of sharing, I have been using appscript and google spreadsheets as a database and performance is definitely an issue when using one spreadsheet as the source and queries. But one work around I found is to use multiple spreadsheets. Being one spreadsheet for the raw submitted/edit or deleted data and other spreadsheets and various sheets for the queries by using the importrange formula. I understand that this may lack of sophistication but it works for now.

This retrieves the raw data as other users submit new data.(it seems to perform well)
=query(IMPORTRANGE("spreadsheet key";"sheet name!A3:U"))

Subsequently I have the hard coded queries in separate spreadsheets or sheets. Like so:
=sort(query(NotAgregated!A3:AE; "select D,E,C,X,L,M,N,O,P,Q,R where G contains '"&A3&"' and I contains '"&B3&"'and J contains '"&C3&"'and L contains '"&D3&"'and M contains '"&E3&"'and E contains '"&H3&"' and H contains '"&I3&"' and D >= date"""&text(F4;"yyyy-mm-dd")&""" and D <=date"""&text(G4;"yyyy-mm-dd")&""" "))

In this example the user can submit and display data back to the user interface. I have tested this in spreadsheets with thousands of rows and it's working so far. I know that I will eventually reach google sheets limitations.


Client side form;
<!DOCTYPE html> <html> <style> <?!= include('Stylesheet'); ?> <?var url = getScriptUrl();?> </style> <head> <base target="_top"> </head> <body> <navthree class="container" > <a class="buttons" tooltip="SALVAR" onclick="reloadTable()" ></a> <a href='<?=url?>?page=controlPanel' class="buttons" tooltip="CANCELAR" onclick="startLoader()" ></a> <a href='<?=url?>?page=Relatorio1html' class="buttons" tooltip="RECARREGAR" onclick="startLoader()"></a> <a href='<?=url?>?page=controlPanel' class="buttons" tooltip="RETORNAR" onclick="startLoader()"></a> <a class="buttons" tooltip="MENU DE EXECUCAO" ></a> </navthree> <div class="bg"></div> <div class="bg bg2"></div> <div class="bg bg3"></div> <div class="contentbg"> <script> function openQuickLinkBar() { document.getElementById("QuickLinkBarId").style.width = "450px"; } function closeQuickLinkBar() { document.getElementById("QuickLinkBarId").style.width = "20px"; } </script> <fieldset> <!-- Side opacity button--> <div id="hiddenOpacitySidePopup" class="hiddenOpacitySidePopup"> <a onmouseover="openQuickLinkBar()"id="QuickLinkHiddenButton">Quick Link >>>>>>>>>>></a> </div> <div id="QuickLinkBarId" onmouseover="openQuickLinkBar()" onmouseout="closeQuickLinkBar()" class="QuickLinkBar"> <a href='<?=url?>?page=RequisicaoNewAddItem' onclick="startLoader()">Retornar a Requisicao</a> <a href='<?=url?>?page=ProdutoAjustment' onclick="startLoader()" >AJUSTE DE ESTOQUE</a> <a href='<?=url?>?page=FornecedorEdit' onclick="startLoader()" >FORNECEDOR</a> <a href='<?=url?>?page=ProductExistingEdit' onclick="startLoader()" >EDITAR ou LANCAR PRODUTO</a> </div> <h1>LISTA DE ALTERACOES E SUBMICOES DE PRODUTOS</h1> <p>PENDENTE A APROVACAO DO MANE!<p> <form action="action_page.php" class="fancy-form" name="form" id="form"> <table> <tr> <td>Produto<br> <input list="ProdutoNameList" id="Produto" name="Produto"type="text" value="" onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"></td> <datalist id="ProdutoNameList" > <option value="">NEUTRO </option> </datalist> <td>FORNECEDOR<br> <input list="FORNECEDORList" id="FORNECEDOR" name="FORNECEDOR"type="text" value="" onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"></td> <datalist id="FORNECEDORList" > <option value="">NEUTRO </option> </datalist> <td>NF<br> <input id="NF"name="NF"type="text" value="" onchange="formgetValuesToSheetProdutoReportOne()"></td> </tr> </table> <table> <tr> <td>GRUPO<br> <input list="GRUPOList" id="GRUPO"placeholder="GRUPO..."onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"name="GRUPO"type="text" value=""></td> <datalist id="GRUPOList"> <option value="">NEUTRO </option> </datalist> <td>SUB-GRUPO<br> <input list="SUBGRUPOList" id="SUBGRUPO"onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"name="SUBGRUPO"type="text" value="" ></td> <datalist id="SUBGRUPOList"> <option value="">NEUTRO </option> </datalist> <td>LUMINOSISDADE<br> <input size="15" list="LUMINOSISDADEList" id="LUMINOSISDADE"onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"name="LUMINOSISDADE"type="text" value="" ></td> <datalist id="LUMINOSISDADEList" > <option value="">NEUTRO </option> <option>NAO SE APLICA</option> <option>SOL PLENO</option> <option>MEIA SOMBRA</option> <option>SOMBRA</option> </datalist> <td>ONDE ENCONTRAR<br> <input list="ONDEENCONTRARList" id="ONDEENCONTRAR"onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"name="ONDEENCONTRAR"type="text" value="" ></td> <datalist id="ONDEENCONTRARList" > <option value="">NEUTRO </option> <option>VIVEIRO 01</option> <option>VIVEIRO 02</option> <option>VIVEIRO 03</option> <option>VIVEIRO 04</option> <option>VIVEIRO 05</option> </datalist> </tr> </table> <table> <tr> <td>DATA INCIAL<br> <input id="DATAINICIAL"name="DATAINICIAL"type="date" value="" onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"></td> <td>DATA FINAL<br> <input id="DATAFINAL"name="DATAFINAL"type="date" value="" onchange="formgetValuesToSheetProdutoReportOne(),setTimeout(reloadTable,2000)"></td> </tr> </table> <script> function onSuccessProdutoNameList(values) { var input = document.getElementById("ProdutoNameList"); var options = values; //Two dimensional array var options = values; for(var i = 0; i < options.length; i++) { var opt = options[i]; var el = document.createElement("option"); el.textContent = opt; el.value = opt; input.appendChild(el); } } google.script.run.withSuccessHandler(onSuccessProdutoNameList) .ProdutoNameList(); function onSuccessProdutoFORNECEDORList(values) { var input = document.getElementById("FORNECEDORList"); var options = values; //Two dimensional array var options = values; for(var i = 0; i < options.length; i++) { var opt = options[i]; var el = document.createElement("option"); el.textContent = opt; el.value = opt; input.appendChild(el); } } google.script.run.withSuccessHandler(onSuccessProdutoFORNECEDORList) .ProdutoFORNECEDORList(); function onSuccessProdutoSUBGRUPOList(values) { var input = document.getElementById("SUBGRUPOList"); var options = values; //Two dimensional array var options = values; for(var i = 0; i < options.length; i++) { var opt = options[i]; var el = document.createElement("option"); el.textContent = opt; el.value = opt; input.appendChild(el); } } google.script.run.withSuccessHandler(onSuccessProdutoSUBGRUPOList) .ProdutoSUBGRUPOList(); function onSuccessProdutoGRUPOList(values) { var input = document.getElementById("GRUPOList"); var options = values; //Two dimensional array var options = values; for(var i = 0; i < options.length; i++) { var opt = options[i]; var el = document.createElement("option"); el.textContent = opt; el.value = opt; input.appendChild(el); } } google.script.run.withSuccessHandler(onSuccessProdutoGRUPOList) .ProdutoGRUPOList(); </script> </form> </fieldset> <br> <script type="text/javascript"> function formgetValuesToSheetProdutoReportOne() { google.script.run.getValuesToSheetProdutoReportOne(document.forms[0]); } </script> <fieldset> <legend>Resultado de busca no parimetro selecionado!</legend> <br> <table> <tr> <div id="tableOfItems"></div> </table> </fieldset> <script> function reloadTable() { google.script.run.withSuccessHandler( function (table) { document.getElementById("tableOfItems").innerHTML = table; } ).getTableRelatorio1(); } </script> </div> </body> </html>



Serverside:

// function ProdutoNameList() { var s1 = SpreadsheetApp.openById('1-rflA03ymqLUC8oGJua-L1AWzYsDYqK_2I35DAoFmuU').getSheetByName('NotAgregated'); var lastRow = s1.getLastRow(); var range = s1.getRange(3,8,lastRow).getValues(); Logger.log(range) return range; } function getValuesToSheetProdutoReportOne(form){ var GRUPO = form.GRUPO, SUBGRUPO = form.SUBGRUPO, FORNECEDOR = form.FORNECEDOR, LUMINOSISDADE = form.LUMINOSISDADE, ONDEENCONTRAR = form.ONDEENCONTRAR, DATAINICIAL = form.DATAINICIAL, DATAFINAL = form.DATAFINAL, NF = form.NF, Produto = form.Produto, sheet = SpreadsheetApp.openById('1-rflA03ymqLUC8**********').getSheetByName('Sheetname'); var cell = sheet.getRange(3, 1,1,9); //row2, col1, for 1 row, for 6 columns cell.setValues([[FORNECEDOR,GRUPO,SUBGRUPO,LUMINOSISDADE,ONDEENCONTRAR,DATAINICIAL,DATAFINAL,NF,Produto]]); //please note the double [[ ..... ]] } function getTableRelatorio1() { var sheet = SpreadsheetApp.openById('1-rflA03ymqLUC8oGJua-*********').getSheetByName('Sheetname'); var lastRow = sheet.getLastRow(); var data = sheet.getRange(6,1,lastRow,11).getDisplayValues(); var table = "<table>"; for( var i=0; i<data.length; i++ ) { table = table.concat("<tr>"); for( var j=0; j<data[0].length; j++ ) { table = table.concat("<td>"); table = table.concat(data[i][j].toString()); table = table.concat("</td>"); } table = table.concat("</tr>"); } table= table.concat("</table>"); Logger.log(table); return table; } 

Bruce Mcpherson

unread,
Jun 25, 2019, 2:57:52 PM6/25/19
to google-apps-sc...@googlegroups.com
I did this several years ago .. a nosql database abstraction, some years ago and one of the supported backends was sheets. 
A big issue with sheets is locking for multi user changes, another is performance for anything more than a trivial amount of data. It’s all possible though, but there are a host of better solutions.


--
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.

Anchal Nigam

unread,
Jun 25, 2019, 7:25:18 PM6/25/19
to Google Apps Script Community
This is a clever idea. Have you thought of cleaning this up and posting as a solution on GitHub?

Anchal Nigam

unread,
Jun 25, 2019, 7:25:56 PM6/25/19
to Google Apps Script Community
Agreed but when we don't have access to a DB we have to work with what we have. :)

I'm hoping to use a Sheet until I can convince the powers that be to get me a DB. :)
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

Bruce Mcpherson

unread,
Jun 26, 2019, 4:01:54 AM6/26/19
to google-apps-sc...@googlegroups.com
https://github.com/brucemcpherson/cDriverSheet  

you'll find drivers for other databases here too https://github.com/brucemcpherson

as I say, i did this 4 or 5 years ago and would do it differently now. 
you could use firebase, google cloud sql, mongolab db  and others . many of which have generous free tiers.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
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.

Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9a66e9b0-89c7-4c32-8c78-ef4cff68a2da%40googlegroups.com.

Davis Jones

unread,
Jun 26, 2019, 11:18:43 AM6/26/19
to Google Apps Script Community
FWIW I've built our team's automation hub using a Spreadsheet as the database, and it works well. When my app writes 4-5 rows of content (~20 cells) to 2 sheets within a spreadsheet and does some querying, it usually takes the app ~12 seconds to execute. 

I do plan to migrate to a proper DB solution soon though, and the approach definitely is more cumbersome if you want multiple users to be able to write to the same "database."

Anchal Nigam

unread,
Jun 26, 2019, 11:19:52 AM6/26/19
to Google Apps Script Community
It's not the cost that is a problem. None of those are approved for use at my company. Not yet anyway. 

I will check out your libraries.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
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-community+unsub...@googlegroups.com.

Clark Lind

unread,
Jun 26, 2019, 3:05:14 PM6/26/19
to Google Apps Script Community
I'm in the same situation. Online DBs are not (yet) available, and our data cannot go outside our domain.

Davis Jones

unread,
Jun 28, 2019, 1:27:56 PM6/28/19
to google-apps-sc...@googlegroups.com
This isn't a database solution, but could the Properties Service meet your needs?

Each script has a 500kb native properties datastore, which, by my calculations, can store ~7,500 key:value pairs / script.

Here's a short tutorial on using the key functions.

Bruce Mcpherson

unread,
Jun 29, 2019, 6:31:37 AM6/29/19
to google-apps-sc...@googlegroups.com
I made a driver for the property service for my noSQL dbabstratction library some years ago - there are 10 or so drivers using the same front end query language, and proprties service was one of them
https://github.com/brucemcpherson/cDbAbstraction  
although many of them are now deprecated service such as parse.com and importio
 
There are quite a few restrictions on property services, and aside from the limited capacity, you still have to solve the problem of locking https://github.com/brucemcpherson/cNamedLock and sharing across multiple scripts (which you can do by sharing the properties service of a shared library)

You can help mitigate the overall capacity by compressing, and the single item capacity of 9k by spreading an item across several keys, as described here
http://ramblings.mcpher.com/Home/excelquirks/gassnips/squuezer , and by using the user property service when the data is specific to a given user.

So there are lots of issues with properties service as a 'database', and its purpose is really to hold 'secret' data specific to your app or your user, but of course you can make query access to properties simple with some of the techniques above - which is what I used it for.

As I mentioned previously, I don't use any of the above any more, except for lsome egacy apps - but at the time a migration path between back ends without needing to change the front end app was handy given the deprecation of so many services.

My recommendation is to look at firebase or cloudsql, or even google spanner if you can, and want to stick to google cloud solutions.

A good solution, if you want to run your own database but not expose it for SQL access externally, is cockroachdb https://github.com/cockroachdb/cockroach/ on kubernetes cluster via a graphQL API ingress. This is globally scalable, free/cheap, and works really well from cloud functions, client apps and even apps script. If anybody is interested I can give you some more info on this approach.




 

On Fri, 28 Jun 2019 at 18:27, Davis Jones <da...@eazl.co> wrote:
This isn't a database solution, but could the Properties Service your needs?
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
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.

--
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.

Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/1ec838dc-1f25-4182-afc0-dfee08a4bcdf%40googlegroups.com.

Anchal Nigam

unread,
Jun 29, 2019, 2:46:03 PM6/29/19
to Google Apps Script Community
Yeah, I use properties services for internal things and it works out well enough.

Davis Jones

unread,
Jul 1, 2019, 10:47:43 AM7/1/19
to Google Apps Script Community
Bruce:

I'm interested in learning more about your cockroachDB approach if it's not much of a hassle for you to share.

I'm going to keep working on a Properties-based solution for us, and I think we might have to migrate to a proper DB solution in the near future, anyway.

If you're interested in our use case, I'm trying to store automation information in the DB / Properties store.

Specifically, things like:
  • Enroll this user in an automated series of events (e.g. calendar invites, emails, etc.)
  • Delete this part of the automation and replace it with this new component
So, we're not needing a full-scale DB for the storage of a bunch of data, but rather somewhere we can store details about operational "tags" if you like, recall them, and replace them.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

--
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-community+unsub...@googlegroups.com.

--
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-community+unsub...@googlegroups.com.

Bruce Mcpherson

unread,
Jul 1, 2019, 12:01:06 PM7/1/19
to google-apps-sc...@googlegroups.com
Hi

actually I'll likely be doing a totally unscripted broadcast on that topic as ive had a bit of interest - its for big complex problems that need scalability so I dont think its what you need anyway.

if the data you are storing is user specific, then each user has an complete properties service allocation inidivdually using the UserProperties store - so that can give you a lot of storage space for tags in any case - why cant you simply store some replaceable JSON data against each key in any case? 

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

--
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.

--
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.

--
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.

Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/71241d98-e788-4baa-916f-22d475be1762%40googlegroups.com.

Davis Jones

unread,
Jul 1, 2019, 1:22:59 PM7/1/19
to google-apps-sc...@googlegroups.com
Bruce:

RE: Totally Unscripted -- great! I'll look forward to that.

RE: Properties as a user data store. That's what I'm working on at the moment. I'm basically creating a synthetic little "mini database" by storing a few different little pieces of data (e.g. UTC time codes, email IDs) between hashtags in a string (e.g. #UTC#utcTimeCode) and then using some parsing functions to retrieve the strings and convert the longer string (with multiple pieces of useful data) into individual useful bits, like the UTC code etc.

If you've got any tips to share, I'm all ears!

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

Adam Morris

unread,
Jul 1, 2019, 6:52:07 PM7/1/19
to google-apps-sc...@googlegroups.com
Thanks for this thread. It's good to know there are others with the same problem. I'm hoping the community can establish go-to libraries so that we don't keep reinventing the wheel. There are those out there but discovering them is currently difficult, although I am hopeful that the next iteration of app scripts will bring a more modern toolchaining of some sort 

I've written a library that allows us to interface with property services and supports json values:

I also wrote a library to interface with advanced spreadsheets service with the goal of using for full database-like utility, but then AppMaker came along and negated my own need to do this. You write code where the programmer defines sessions and updates happen at the end of a session.


Adam

————————————————————————————

Adam Morris | IT Systems & English Teacher | IGB International School
Jalan Sierramas Utama, Sierramas,
47000 Sungai Buloh, Selangor DE, Malaysia

t    +60 3 6145 4688
f    +60 3 6145 4600
w   www.igbis.edu.my
e    adam....@igbis.edu.my

————————————————————————————

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGPa%2BS1eDVBMmyYXOjXSGJ9_evFqsgnQpuHo71K51MRrpsQ%2BzQ%40mail.gmail.com.
Reply all
Reply to author
Forward
0 new messages