0

I'm building some functionality in the onEdit(e) function of my google sheet.
However, I noticed that the 'reaction time' of my script became slower as more and more sheets were added in the spreadsheet. It reached the point where the user-experience became very poor.

The line of code that is causing the increasing delay is the standard function

getSheetByName(name)

Google does provide a standard getSheetByID() function, so I made one myself, however it had the same performance as the getSheetByName(name) function.

For that reason, I wanted to put this Sheet object in cache, in order to retrieve it faster during my onEdit(e) function:

function GetTemplateSheet(){

let cache = CacheService.getScriptCache();
let templateSheet = JSON.parse(cache.get("templateSheet"));

if (!templateSheet){

  let spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  templateSheet = spreadSheet.getSheetByName('myTemplate');

  cache.put("templateSheet", JSON.stringify(templateSheet));
}

return templateSheet;
}

However this does not seem to work. I'm not getting back the object that I put in. I just cannot figure out how to overcome this.

Tom Bonte
  • 1
  • 1
  • Cache can only store string values. – Karan Jan 16 '22 at 16:31
  • 2
    You should consider reorganizing the structure of spreadsheet, where you don't have to have many sheets, but exactly two sheets - one for raw data entry and other for presentation. A sheet for every month or day has been and still is a very bad idea. – TheMaster Jan 16 '22 at 16:36
  • Yes, cache can only store strings. that's why its being stringified and I'm limiting the qty of sheets to the minimum. I used the code proposed in this [answer](https://stackoverflow.com/questions/27527669/improve-script-performance-by-caching-spreadsheet-values) but I do not get it working. – Tom Bonte Jan 16 '22 at 22:37
  • What are you going to do with the sheet afterwards? – TheMaster Jan 17 '22 at 08:41
  • It contains some template forms (in specific ranges) that I want to copy into my active sheet. The copying from a template was more convenient that coding all the formatting, text, etc.... – Tom Bonte Jan 18 '22 at 15:46
  • Well you can't store the sheet, but you can store, formats and values as strings. – TheMaster Jan 18 '22 at 19:08

0 Answers0