using cache and avoiding reopening Spreadsheet and sheet and range for every access

[ad...@commet.com]

Question for Bruce - 

Hi Bruce, I looked at your caching code. Looks interesting but not sure how to cache across separate function calls. Is your cache designed to stay alive across different function calls? Or, the cache only works for the lifetime of one function?

That is with "sheetCache" I do not want to open a new spreadsheet every time and create a new worksheet range.
Once I initiate sheetCache, what arguments can I use to call it a second time without reopening the spreadsheet again?

I have the code below to cache my "Project" sheet in the "Project" spreadsheet:
var ws=SpreadsheetApp.openById(SPREADSHEET_ID_PROJECT).getSheetByName("Project");
var wr=ws.getDataRange(wr);
 var projcache = mcpher.sheetCache(wr);

Can I access projcache from different functions with:
var projcache = mcpher.sheetCache(wr);
and not having to repeat:
var ws=SpreadsheetApp.openById(SPREADSHEET_ID_PROJECT).getSheetByName("Project");
var wr=ws.getDataRange(wr);

I want to avoid having to reopen the spreadsheet everytime I reaccess the cache from a different function.
When I try
var wr = mcpher.wholeSheet("Project");
Error: Cannot call method "getSheetByName" of null.

Thanks much,

Jay

[Bruce McPherson]

Hi

This kind of worksheet caching is to cause messing around with worksheet  content to be able to happen in the script context rathe than having to go back and forwards to the spreadsheet.

Var cache = sheetCache(a range, a sheet name, a sheet) will create a cache or use an existing one if it already exists, So you don't need to care about opening a sheet in any given function, only about committing any changes at appropriate times.

A couple of points .. You really don't need to use ranges any more.. The cache has properties that tell you the dimensions of the sheet. In fact once you start using the cache you should stay away from any direct sheet access, since it defeats the purpose of having the cache .. Which is about avoiding server requests to the sheet .. But more importantly it may be incorrect if you have been updating the cache contents and have not yet committed them.

It looks like you want this to persist across different workbooks. I'll need to take a look at this when I have time and will update in the context of your question. Its been a while since i wrote all that. But I think the scope is within a particular workbook.. Hence the name sheetCache.

There are other ways to cache across different project scopes such as google caching, or maybe scriotdb.. but in all cases .. As with this sheetCache which does it automatically, you have to cater for the cache not yet being populated and doing it if not.

Can you describe the components of all this .. Is it a standalone script that opens and processes various workbooks then commits the results somewhere?

Bruce

Sent from my iPad

--
 
 

[bruce]

Jay

here's an example - im opening up another workbook if its not already known, and copying stuff over, transposing the data.

var pBook;

function test(){

  var projectData = projects("freegeoip");

  // do something with the data .. transpose it for example

  var cache = mcpher.sheetCache("Sheet1").clear();

  projectData.forEach(

    function (v,r,c) {

      cache.setValue (v,c,r);

    }

  );

  cache.close();

}

function projects(sh) {

  pBook = pBook || openProjectBook();

  var s = pBook.getSheetByName(sh);

  return  mcpher.sheetCache(s);

}

function openProjectBook() {

  return SpreadsheetApp.openById ("0At2ExLh4POiZdFAzUElyTGNQLW90aEFKeHhJZDR1WWc");

}

 Now even though the pBook variable is global it only holds it value for the lifetime of the test script here, meaning that it would open it again next time round. On the one hand you dont care .. writing it as above it is invisible to you whether  the book was opened or taken from cache. On the other hand, if you have a performance issue, you might care (doubt if you would if its just small number of parameters by the way). For global variable to persist across function calls (you need to use script/user properties or scriptDB. For example, my rest library .. which is essentially a set of parameters .. is kept in a scriptDB, not a sheet. http://ramblings.mcpher.com/Home/excelquirks/scriptdb. However in this case you would need to stringify your parameters to be able to store them.

[Jay Santry]

Greetings Bruce,

 Thank you so much for the response..I hope my questions help you also clarify your endeavours - your feedback is of great help to me in speeding up slow data access code. I really appreciate it!

 My context: platform is Google Sites with Google Apps Script as you know. I have two main spreadsheets: Project and Task. I can repose Project and Task as sheets residing the same spreadsheet.

Let’s say there is the initiating function to create the cache:

function initCache(){
 var wsproj=SpreadsheetApp.openById(SP_ID_PROJECT).getSheetByName("Project");
 var projcache = mcpher.sheetCache(wsproj);
}

Next, I want to read the cache back without having to reopen the spreadsheet:

function readCache(){
 var projcache = mcpher.sheetCache(wsproj);
}

So, I guess my problem boils down to how I can persist my Project sheet - wsproj?
Otherwise, I will have to reopen the spreadsheet to get the project sheet again.

I tried to persist wsproj as a global variable, but only global constants work in Google Apps Script.

Thanks again for your patience Bruce..otherwise your cache is amazing!

sincerely,

Jay Santry

--
 
 

--
Jay Santry
571-201-5357

[Jay Santry]

Hello Again Bruce,

  I have some more thoughts to share with you on caching across script invocations. 

Persisting the sheet object is not possible in Google Apps script(as you know the persistence mechanism only stores strings). So, it would definitely add value to uncouple sheetCache from spreadsheets by adding another way to reference the cache once it has been created. The new reference mechanism would not have a dependency on sheets. 

Many advanced programs will require multiple script invocations as users interact by clicking buttons, editing text...so adding this additional flexibility sans spreadsheets may be quite a bonus!

Food for thought, I hope you do not mind my mentioning.

sincerely, 

Jay

--
Jay Santry
571-201-5357

[Bruce Mcpherson]

Jay

here's how to use google cache to persist data for a while .. if you run x, then run it again immediately, you'll see it runs at 10 times the speed. So the cache persists across function calls.

// we need to get data for a specific sheet/workbook combination

//could be in google cache or nowhere

function t() {

  Logger.log( getPersist ("0At2ExLh4POiZdFAzUElyTGNQLW90aEFKeHhJZDR1WWc","freegeoip"));

}

function getPersist ( wb, ws) {

  var googCache = CacheService.getPublicCache();

  var sheetString = googCache.get(wb + "!" + ws ) ;

  

  if (sheetString) 

    // we know it

    return JSON.parse (sheetString); 

  else {

    // we know nothing, open the workbook & cache

    sw = SpreadsheetApp.openById(wb).getSheetByName(ws).getDataRange().getValues();

    googCache.put(wb + "!" + ws, JSON.stringify(sw), 60);

    return sw;

  }

}

function x () {

   mcpher.useTimer('a').start('first');

   getPersist ("0At2ExLh4POiZdFAzUElyTGNQLW90aEFKeHhJZDR1WWc","freegeoip");

   mcpher.useTimer('a').stop();

   Logger.log(mcpher.useTimer().report());

}

--
 
 

[Jay Santry]

- Thank you Bruce, this is some fascinating code, I will be looking at shortly! 

regards Jay

[Bruce Mcpherson]

HI Jay

i just posted some more detail on this code in the bliog http://excelramblings.blogspot.com/2012/11/using-google-cache-to-persist-data.html

--
 
 

[Jay Santry]

- thanks for the update Bruce, I tried out the Google cache, is smoking-fast! regards Jay

[Bruce Mcpherson]

Im assuming your data doesnt change much, so you could use CacheService.getPublicCache() instead and up the timeout time.. that way multiple visitors might get it from cache directly

bruce

--