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?
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.