Google App Maker updating DB records in batches

Question

We are having a Google App Maker app using Google Cloud SQL tables. Our location is Prague, Central Europe and app.saveRecords() takes incredible 240ms at minimum. We have tried many things including changing the location of the instance but it did not solve the problem.

Fortunately, the solution is to insert new records in batches. This was brilliantly solved by the answer in my previous thread [ Google App Maker saving records to DB is taking 240ms per record ].

We would be so grateful if anyone provided us with a simple code to update records in the DB in batches [server-side scripting]. This way, we not only could insert completely new records in batches, but we could update the data of the records already saved in the DB fast way.

Let's say we have a DB with 3 custom fields:

product code | product availability | price

+ an example array with the data:

ourData[0] = ['pc001','in stock','99'];
ourData[1] = ['pc002','out of stock','49'];
ourData[2] = ['pc003','out of stock','199'];
ourData[3] = ['pc004','in stock','149'];
ourData[4] = ['pc005','in stock','299'];

...and we want to assign the availability and price data to the particular DB line using the key "product code". If the product code is not found in the DB, then we insert a new record to the DB.

Multiple single line inserts/updates take Google App Maker users in the EU too long because the query does go to the US even if the SQL instance is located in the EU. Running this in batches could break today's Google App Maker limitations outside the US. Thank you so much for any help to make Google App Maker a great tool for beginners all around the world.

Answer 1

This issue can be solved by using the proper server scripting and I strongly encourage you to read how server scripting works in the official documentation. Pay close attention at the Querying records example.

So, taking as base the previous solution example, it can be slightly modified to achieve your needs. This is how it should look:

function saveData(){

    //get the data
    var data = getData();

    var totalRecords = data.length;
    var batchLimit = 2000;
    var totalRounds = Math.ceil(totalRecords / batchLimit);
    var round = 1;
    var roundlimit = batchLimit;
    var currentItem = 0;

    do{
        var recordsToSave = [];

        for(var i=currentItem; i<roundlimit; i++){

            var recordData = data[i];
            var productCode = recordData[0];

            //check if the record already exists
            var query = app.models.testDB.newQuery();
            query.filters.productCode._equals = productCode;
            var productRecord = query.run()[0];

            //if non existent, create a new one
            if(!productRecord){
                productRecord = app.models.testDB.newRecord();
                productRecord.productCode = productCode;
            }

            productRecord.availability = recordData[1];
            productRecord.price = recordData[2];
            recordsToSave.push(newProduct);
        }

        if(recordsToSave.length){
            app.saveRecords(recordsToSave);
        }

        currentItem += batchLimit;
        roundlimit += batchLimit;
        round++;

    } while(round <= totalRounds);

}