UrlFetchApp: database optimisation

[Andrew Apell]

I have created a web app that contains database information for my users.

I expect each user to access this database hundreds of times a day as the use my add-on.

How do I set it up my code so that they are not using my quota and read speeds are good?

EDIT:

The database is a spreadsheet and my standalone script is being deployed as an add-on. If there is a way of allowing all the users to access this spreadsheet, then all the above isn't necessary.

[ChiefChippy2]

Cache Service might be useful in certain cases...

You can also do XmlHttp requests from the client side.

[ChiefChippy2]

On Thursday, September 26, 2019, Andrew Apell wrote:

You can make the spreadsheet "everyone with link can edit" but I don't think that would be very secure :3 

[Andrew Apell]

You know what? I have actually been thinking of doing that given how much everything else has either failed or been inefficient.

I does seem to be very insecure though....

[ChiefChippy2]

Another solution would be to use JDBC to connect to a SQL database, but if you are using google sheets....

 Or you can put it to everyone w/link can view, and to edit the database, you do a UrlFetchApp request to edit it.

[Andrew Apell]

My issue here is with UrlFetchApp

My current setup is with the web app I'm querying is:

• Execute app as ME

I read somewhere that this permission means everyone accessing the web app is using my UrlFetchApp quota.

So now I'm looking for a way around this

[Romain Vialard]

No, it should be good.

When your users will use your add-on, they will use their own Urlfetch quota to call your web app. If I understand well, the code of your web app doesn't have urlfetch calls in it (instead you read / write a spreadsheet), so your own urlfetch quota won't be used.

[Andrew Apell]

True, the web app itself is simply reading the spreadsheet though SpreadsheetApp.openById() wrapped inside doGet()... it has no UrlFetch call.

Thanks for the clarification Romain. This makes things a lot easier.

[Andrew Apell]

Now that that issue is resolved, the next is this:

Does my UrlFetchApp code need to be wrapped between waitLock() and releaseLock()?

Plus Google says the CacheService is good for such calls? Anyone here experience real world advantages with it?

[ChiefChippy2]

I used CacheService a few times, and it is best used for requests that doesn't necessary need to be refetched every second - i.e I used to UrlFetchApp the html of a google drive embedded folder because I want to customize it ( with my own CSS ), and I used CacheService to cache it, so it wouldn't be more than 1 request per 1/2 hour ( the folder rarely updates and even if it does nothing inside is extra important, so it wasn't necessary to refetch everytime).
A bad use of CacheService is storing mass amounts of data : I tried to store data:image/jpeg base64 images ( about 2 mb ) and it worked poorly ( took a long time to set it and to get it).
I suppose, you can cache results of a database for a short time ( if a user action fetches the same database a lot of times without editing it ).

[Andrew Apell]

I'm using the CacheService for speed purposes. The documentation says that it is faster than UrlFetchApp so I decided to take their word at it.

Of course if a user makes a change that updates the database, then they will have to wait for a few minutes before they see any results.... It is my hope that this doesn't bother them too much.

[ChiefChippy2]

Just delete the cache when a user updates the database, or make it something only donators get.

On Thursday, September 26, 2019, Andrew Apell wrote:

[Andrew Apell]

I need to extend this topic rather than creating a new one and this might require some Apps Script ninjutsu.

I have a function in my add-on that looks like this:

function myFunction{
var a=UrlFetchApp...

// Other stuff 

}

Now imagine a scenario where this function is in about 1000 cells. How do I prevent 1000 UrlFetchApp calls from being made?

MORE:

What if I come out and simply remove the whole UrlFetchApp line and do this instead:

function myFunction{
var a= SpreadsheetApp.openByUrl(...).getSheets()[0].getDataRange().getValues();
// Other stuff
}

 

Would distributing that inside an add-on have any problems and how would you suggest avoiding it?

I'm basically looking for the best way to use Google Sheets as a database without worrying about user access being limited.

If any of you know how to do this, then please help me out here.

[Alan Wells]

If the UrlFetch call always results in the same returned value/object, then put the value into cache, and check cache first.  If the value returned from UrlFetch could change, then you'll need to determine whether it changes on a certain interval of time, or some known event happening.  If it's random, and you can't know for sure whether the value returned from UrlFetch will be different or not, then you can't avoid making a UrlFetch call every time, unless the users know that they might not get the exact results that they expect every time.

Could you put a value into a cell somewhere, and then reference the value in the cell?  I don't work with cell formulas.  I don't know if a conditional test can be put into a cell formula that either gets the value from another cell, or calls a custom function.

As far as using SpreadsheetApp instead of UrlFetch, have you run a test with just a few cell formulas and tried to notice a difference?

[Andrew Apell]

I haven't tried SpreadsheetApp yet because I don't know how it would affect my scopes and how permissions would be handled.

Otherwise, about the cache... which do you think is better: ScriptCache or UserCache?

[Alan Wells]

It depends on your needs.  Script caches are common to all users of the script.  If the values are different for each user, and one user could overwrite the values of another user, then don't use script cache.  If the values will always be the same, regardless of the user, and one user can not corrupt the data of another user, they you could use script cache.  I don't know if there is a performance advantage of using one or the other.

[Andrew Apell]

I'm thinking about it in terms of the cache.

If I use the ScriptCache, then everyone gets a communal cache. That might reduce the number of UrlFetchApp calls as opposed to leaving it to everyone to call if and when their private caches expire. At least that is what I think

[Andrew Apell]

I wish there was a way of setting the cacheservice and lockservice so that only one UrlFetchApp call is made but the rest of the calls get the cache, regardless of how close the requests are to one another.

[Andrew Apell]

I think this might work:

function myFunction{
Utilities.sleep(1000);

var a=UrlFetchApp...

// Other stuff
}

So if there are multiple requests, they will be delayed by a second. Hopefully, by the time they get passed that point, the cache is already set so the subsequent requests read from it rather than calling UrlFetchApp.

What do you all think?

[Steven Bazyl]

:(

Putting a sleep in there doesn't help and defeats one of the main purposes of having the cache in the first place. The only time you'd want to sleep (and you don't) would be if there's a cache miss *and* there's some indication that another task is already trying to populate it. In either case, since you don't know how long you need to wait, to avoid waiting too long you'd need more of a spin lock (repeatedly check the lock) with a very short, if any, sleep time.

Guaranteeing that the fetch will only happen once is hard. The problem it sounds like you're trying to avoid is known as cache stampede (lots of requests simultaneously missing the cache and doing the expensive thing you're trying to avoid.) There are various ways to approach it, but it's a hard problem.

--
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/e51a4c81-ac7a-4e57-85a5-1618bb59ba56%40googlegroups.com.

[Andrew Apell]

Cache stampede eh?

I decided to run Utilities sleep with a random number between 1 and 1.5 seconds.

So, my hope is the code will reach the core at different times, forcing it to check if the cache is available or not rather than rushing to call UrlFetchApp.

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

[ChiefChippy2]

What if you create a trigger  that triggers every hour( or longer/shorter ) that updates the cache?

On Thursday, September 26, 2019, Andrew Apell wrote:

[Andrew Apell]

Triggers have been unkind to me

I'm thinking of going back to the LockService... just don't know if it can slow down/manage the cache stampede

[ChiefChippy2]

Maybe you can do something like : 

var cache = CacheService.getScriptCache()

if(cache.get("CachedData")==null){

Utilities.sleep(100)

if(cache.get("CachedData")==null){

//Fetch data but first set cache 

cache.put("CachedData","Fetching",10000)

var data = UrlFetchApp.fetch("https://secret.data/server")

//If after 10 seconds the fetch hasn't finished another server will take care of it.

cache.put("CachedData",data,1000*60*10)

}

}

while(true){

if(cache.get("CachedData")=="Fetching"){

Utilities.sleep(100)

}else{

break;

}

}

return cache.get("CachedData")

[Andrew Apell]

That is some serious ninjutsu there!

Let me look through it carefully and see how/if it will work

[ChiefChippy2]

I forgot to add a fail-safe ( i think ).

ALSO - You can randomize all the sleep delays ( I set it to 100 but it'd best be from 50-200 I think )

On Wednesday, October 2, 2019 at 5:54:09 PM, ChiefChippy2 wrote:

Maybe you can do something like : 

function getData(){ 

var cache = CacheService.getScriptCache()

if(cache.get("CachedData")==null){

Utilities.sleep(100)

//Just to make sure it is still "null" after 0.1 seconds since putting cache can take a tiny amount of time 

if(cache.get("CachedData")==null){

//Fetch data but first set cache 

cache.put("CachedData","Fetching",10000)

var data = UrlFetchApp.fetch("https://secret.data/server")

//If after 10 seconds the fetch hasn't finished another server will take care of it so if the fetching failed not everything fails.

cache.put("CachedData",data,1000*60*10)

//Put Data into cache for 10 minutes  

}

}

while(true){

if(cache.get("CachedData")=="Fetching"){

//Check if the fetch is done or not. Maybe you should find a derivative to a while true loop. 

Utilities.sleep(100)

}else{

break;

}

} 

if(cache.get("CachedData")==null){

//The Fetching encountered an error, there is no fetched content. Rerun the function  

return getData()