filters and more in Google Sheets

Thought of trying out Google Sheets as a database replacement for our schedule page. Tried some different Google Apps Script code snippets for testing.

 TESTED:

1. Using the javascript filter function - This takes around 25 seconds for our data set, so is not very useful for production use, with code similar to
   

   var searchValue = '2022-12-12';

   var streamdailydata = SpreadsheetApp

   .getActiveSpreadsheet().getSheetByName('streamdaily')

   .getDataRange()

   .getValues();

   var f = streamdailydata.filter(function(e) {return e[3].includes(searchValue) } );

   // this takes around 25 seconds

   if (f.length > 0) {

   return f;

   }

   // else

   return SpreadsheetApp

   .getActiveSpreadsheet().getSheetByName('streamdaily')

   .getRange(1,1)

   .getValues();	}

2. Using the Google Sheets filter class - Documentation is a bit sparse on how to use the data obtained after filtering, but here is an example to help, https://yamm.com/blog/create-filters-in-google-sheets-with-google-apps-script/
   Still, just adding the filter seemed to take around 30 seconds for our data. So, again not practical for production, too slow. Code snippet like:
   
   
   function getDataWFilter() {

   
   

   var searchValue = '2022-12-12';

   var sheet1 = SpreadsheetApp

   .getActiveSpreadsheet().getSheetByName('streamdaily');

   var range = sheet1.getRange("D2:D");

   const filter = range.createFilter();

   const Filter_Criteria2 = SpreadsheetApp.newFilterCriteria().whenTextContains(["2022-12-12"]);

   const columntofilter = 4;

   const add_filter2 = filter.setColumnFilterCriteria(columntofilter,Filter_Criteria2);

   
   

   Logger.log(add_filter2.getRange().getA1Notation());

   // This took 32 seconds.
   
   
3. Directly querying a MySQL database from Google Apps script - This is probably the fastest method, but then we would need a database to connect to. Code snippet similar to the code below for testing with a similar number of rows returned, took 6 seconds. This is still significantly slower - 3x? - than our current hosted php/mysql solution, but perhaps can be optimized with better code avoiding loops.
   
   
   function getDatafromMysql() {

   // Database Credentials

   var dbAddress = 'ip.address.of.server';

   var dbUser = 'the_username_on_db';

   var dbPassword = 'the_db_password';

   var dbName = 'the_name_of_db';

   
   

   // connect to SQL database

   var db = Jdbc.getConnection('jdbc:mysql://' + dbAddress + ':3306/' + dbName, dbUser, dbPassword);

   var stmt = db.createStatement();

   var TableData = stmt.executeQuery("select vu.id, vu.username, vu.firstname, vu.lastname from the_table_name vu where vu.idnumber = 'OurSelectionCriteria'");

   //https://stackoverflow.com/questions/24547406/resultset-into-2d-array

   //https://stackoverflow.com/questions/52867326/how-to-add-values-to-two-dimensional-array-with-for-loops-using-google-apps-scri

   var metadata = TableData.getMetaData();

   var numberOfColumns = metadata.getColumnCount();

   var i=0;

   var resultasarray = [];

   while (TableData.next()) {

   var rowvals = [];

   
   

   for (var j = 0; j < numberOfColumns; j++) {

   rowvals.push(TableData.getString(j+1));

   //Logger.log(TableData.getString(j+1))

   }

   resultasarray.push(rowvals)

   i++;

   }

   // this takes around 6 seconds due to the loop.

   Logger.log(resultasarray);

   return resultasarray;

   }
   
   
4. SQL in Google Sheets - https://learnsql.com/blog/sql-in-google-sheets-query/ 
   This is something which I did not know about. We can query sheets using the visualization API - 
   https://developers.google.com/chart/interactive/docs/querylanguage
   There was another feature of using nosql as mentioned here,
   https://stackoverflow.com/questions/22330542/can-i-use-google-visualization-api-to-query-a-spreadsheet-in-apps-script
   But that was part of ScriptDB which is now SunSet - https://developers.google.com/apps-script/guides/support/sunset#copy_a_spreadsheet_into_the_database
   Using gviz to query Sheets inside a Google Apps Script needs the GSheet to be shared as "anyone with the link" can view, as mentioned by Makhrov at the last answer here. More details of gviz are at this blog post. The following code snippet ran in less than 6 seconds, using the code from Makhrov above. So this looks promising, will explore further later.
   
   
   function getSheetsQueryResult_(fileId, sheetName, rangeA1, sqlText)

   {

   
   

     var file = SpreadsheetApp.openById(fileId);

     var sheetId = file.getSheetByName(sheetName).getSheetId();

   
   

     var request = 'https://docs.google.com/spreadsheets/d/' + fileId + '/gviz/tq?gid=' + sheetId + '&range=' + rangeA1 + '&tq=' + encodeURIComponent(sqlText);

     var result = UrlFetchApp.fetch(request).getContentText(); 

     Logger.log(result)    

     // get json object

     var from = result.indexOf("{");

     var to   = result.lastIndexOf("}")+1;  

     var jsonText = result.slice(from, to);  

     var parsedText = JSON.parse(jsonText);      

   
   

     // get types

     var types = [];

     var addType_ = function(col) { types.push(col.type); }

     var cols = parsedText.table.cols;

     // above line gave error TypeError: Cannot read properties of undefined (reading 'cols')

     // that was due to "Query error" when a wrong number of columns was entered

     cols.forEach(addType_);    

   
   

     // loop rows

     var rows = parsedText.table.rows;  

     var result = [];  

     var rowQuery = [];

     var eltQuery = {};

     var row = [];

     var nRows = rows[0].c.length;

     var type = '';

     for (var i = 0, l = rows.length; i < l; i++)

     {

       rowQuery = rows[i].c;

       row = [];

       // loop values   

       for (var k = 0; k < nRows; k++)

       {

         eltQuery = rowQuery[k];

         type = types[k];

         if (type === 'number') { row.push(parseInt(eltQuery.v)); }

         if (type === 'boolean' || type === 'string') { row.push(eltQuery.v); }

         else { row.push(eltQuery.f); }      

       }    

       result.push(row);

     }

   
   

     return result;

   
   

   }

   
   

   function test_getSheetsQueryResult()

   {

     var fileId = '1RN6THEFILEID-w22EdE';

     var sheetName = 'streamdaily';

     var rangeA1 = 'A1:E328700';

     var sqlText = "select A, C, D where D contains '2022-12-12'";

     

     var res = getSheetsQueryResult_(fileId, sheetName, rangeA1, sqlText);

     Logger.log(res);      

   
   

   }