1

I'm developing a Web App using google apps script and a spreadsheet as storage.

Basically, an HTML showing some tables for the different tabs.

From my app, users can add new tasks, edit tasks, and mark tasks as completed. Since there are many users using the app, the data showed on each client will get outdated very fast.

I would like to update the app with new records and changes to the existing ones as soon as possible.

I thought in logging the last edit tab+row in a cell and pull that data from the server every minute, but, what if many entries are added/edited during that minute?

I think WebSocket is not possible. Any other idea?

I'm using JQuery client-side.

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Danielo515
  • 5,996
  • 4
  • 32
  • 66
  • Can you post the code of your app. Code speaks louder than words – Srik Nov 06 '13 at 16:00
  • The proyect is pretty big to post it here. I'll try to set up a little example. Anyway, I'm not asking for help to fix my code, I need an advise to do it or how i should. – Danielo515 Nov 06 '13 at 17:04
  • I implemented a task tracker on top of a spreadsheet, no web app necessary. – Fred Nov 08 '13 at 14:26
  • Hello Fred. Could you be a little more specific? Screenshot or something like that. Thank you very much. – Danielo515 Nov 09 '13 at 11:19

3 Answers3

1

No other way besides polling. You can't have sockets or callbacks from HTML service. You could poll frequently but that may run you out of quotas. If you really want to poll and avoid quotas you can log the last edit on a published public spreadsheet and read it with ajax from the client, however published spreadsheets update every minute only.

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
  • That sounds like a good approach. I'll be running this inside a corporate google domain. How can I manage the public spreadsheet to be available only inside my company domain? Does the quotas applies to this situation? Thank you for your answer. – Danielo515 Nov 07 '13 at 01:57
  • I looked at the quotas and I did not find any related to maximun server calls. Which quota applies? – Danielo515 Nov 07 '13 at 02:43
  • If you use the published (not just public) spreadsheet you wont have quota issues. But once published you cant prevent it from being public for all. – Zig Mandel Nov 07 '13 at 12:53
  • Ok, thank you for your explanation. Anyway, the script will be executed as the user that access. Maybe that way can I avoid the quota problems? I didn't find any information related with number of server calls yet. Do you have any reference? – Danielo515 Nov 07 '13 at 17:02
  • By executing as user you will avoid or reduce quota issues. – Zig Mandel Nov 07 '13 at 17:15
1

To help avoid conflicts, give every task a unique ID. Something like creation time + random string. That way you can look it up in the spreadsheet. Also, I think the Lock Service can prevent concurrent edits temporarily to avoid conflicts: https://developers.google.com/apps-script/reference/lock/

To check for updates, try polling the last edit time of the spreadsheet. If it's greater than the previous poll, fetch updates. https://developers.google.com/apps-script/reference/drive/file#getLastUpdated()

Fred
  • 1,081
  • 12
  • 21
  • Thank you for your answer. I thought in Lock Service, but the google's reference is not enough clear for me. Do you have any reference? The getLastUpdate sounds good. Using it in combination with task ID, I can send the server a list of task that client have and it can reply back with those that they don't have. I think checking every minute when page has focus could be enough. – Danielo515 Nov 09 '13 at 11:15
  • No, I've never needed to use Lock Service, so I don't know how well it works. – Fred Nov 09 '13 at 20:58
0

You could try something like this:

var lock = LockService.getPublicLock();
var success = lock.tryLock(10000);
if (success) {
// check your spreadsheet for lastUpdated or PropertiesService.getScriptProperties();
}
} else {
// do something else (try it again or error msg)
}
lock.releaseLock();

I have found that it works well on my app and I have around 1000 users.

Trevor Iampen
  • 217
  • 2
  • 13