Google Apps Script Web App long-polling and simultaneous executions limit

Question

My google script web app is recently hitting qps limits. What would be a better way to improve performance.

I have about 50 active users. I use 15,000 rows google spreadsheet as a database and my app is serving json data requested by users from this spreadsheet. I use long-poll to keep connection alive for 5 min and close it if no update in spreadsheet happens. Then client reconnects. Web App is published to be executed as me.

My polling works like this:

function doGet(e){
    var userHasVersion = e.parameter.userVersion
    while (runningTime < 300001) {
          var currentServerVersion = parseInt(cache.get("currentVersion"),10)
          if(userVersion<currentServerVersion){
            var returndata = []
            for(var i = userVersion+1; i <= currentServerVersion;i++){
              var newData = cache.get(i)
              if(newData!=null){returnData.push(JSON.parse(cache.get(newData)))}
            }
            return ContentService.createTextOutput(JSON.stringify({currentServerVersion,data:returnData })).setMimeType(ContentService.MimeType.JSON); 
          } else {
            Utilities.sleep(20000)
          }
          runningTime = calculateRunningTime()
    }
}

What I have tried so far:

1) I optimized requests with CacheService to reduce calls to Spreadsheet. It helped for few months, but now I'm getting qps errors more and more often.

2) Asking Google team about quotas. They explained me, that there is no published quotas/limits for simultanous executions and they are subject to change without notice. They advised further usage of cacheService and better error handling.

I think to switch from long-polling to short-polling. But it feels like drawback. Should I try to further optimize performance or move to another service?

Would trying to use "execute app as user accessing the app" help? (users should use the same database)

Is Google Script API Executable different from Web App? It looks like it might fit, but I'm not sure if they share the same qps quotas.

I'm also considering GAE service, but I'd like to avoid going over free quota.

Any advice will be much appreciated!

Answer 1

I think that a following part can be improved. When data is retrieved from cache service, getAll() is more effectively than get(). I have ever measured the difference. That is about 890 times faster than get(). If the number of data retrieving from cache service is large, I think that the improvement of this part is important for performance.

Your script :

var returndata = []
for(var i = userVersion+1; i <= currentServerVersion;i++){
  var newData = cache.get(i)
  if(newData!=null){returnData.push(JSON.parse(cache.get(newData)))}
}

Improved script :

var ar = [];
for(var i = userVersion+1; i <= currentServerVersion;i++){
  ar.push([i]);
}
var r = JSON.parse(JSON.stringify(cache.getAll(ar))); // Since key is number, I used this.
var returnData = [r[j] for each (j in r)if (!r[j])];

Since I cannot see your data, I cannot confirm this execution. So if errors occur, please tell me.

If I misunderstand your question, I'm sorry.