How to optimize queries to Spreadsheet via Google WebApp

user_14202889 0
Viewed 53

I have a WebApp which sends vía google.script.run.withFailureHanlder(data).withSuccesHandler(data).someFunction(data) some attributes to filter a retrieve data from a spreadsheet in Drive. For example

Host side

google.script.run.withSuccesHandler(e => {
  console.log(e);
}).someFunction(data);

Google Apps Server Side:

function someFunction(data){
 let book = SpreadsheetApp.openByUrl(url).getSheetByName(data.name);
 let info = book.getDataRange().getDisplayValues().filter(x => {
   return new Date(data.start) > new Date(x[0]);
 });
 return result;
}

These spreadsheets are consulted by several people at the same time. At the beginning it was going well but now the data has grown a lot and the queries have become slow due to the amount of data.

Is there any way to optimize these queries without affecting the result of the other people when querying at the same time?

google-apps-script google-sheets web-applications javascript
1 Answers

If the data in your spreadsheets does not change frequently, you can try caching the data on the client-side.

You can use IndexedDB as a cache to store the results of common queries in your clients' browsers, assuming their browsers support it. You'll also need to setup some code to periodically check the sheet for changes to update the cache/database on the client-side when necessary.

Dexie is a good IndexedDB wrapper, so I'd recommend using that library to get started.

user_65862550
Related