lots of javascript recipes

Since I'm new to javascript and am trying to write some javascript code, lots of googling and some mis-steps. But useful, since loading json data and displaying can be done much, much faster with javascript rather than, for example, google apps script. Summarizing some "how-to"s below.

• The addressing style using $ is usually used with jQuery. For example, 
  <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
  $(function () {
     // this runs on page load
     ourdropdownupdate();
     loadourdata();
  });
  
  
• Javascript generally loads and executes from top to bottom linearly, except for async calls. So, within functions, for example, one would execute linearly. 
  
  
• Global variables - variables declared outside function braces {} will be global variables. (But in google apps script, functions called from templated html will start execution from the top, as a separate thread, so the global variables initialized earlier would not be initialized for that execution.)
  
  
• Returning Multiple Values from a Function - we need to either return an array or an object which has multiple properties.
  
  
• Clear all rows in an html table except the first row - 
  

  var mytable = document.getElementById("mytableid");
  mytable.getElementsByTagName("tbody")[0].innerHTML = mytable.rows[0].innerHTML;

• Create options in a dropdown list (select element in a form) -
  https://stackoverflow.com/questions/78932/how-do-i-programmatically-set-the-value-of-a-select-box-element-using-javascript
  https://www.w3schools.com/jsref/met_select_add.asp
  https://stackoverflow.com/questions/4618763/removing-all-option-of-dropdown-box-in-javascript
  var selecDay = document.getElementById("dateselector");
  selecDay.options.length = 0; // clear the select element of all options
  var option = document.createElement("option");
  var optiondate = new Date(optiondatemillisec);
  option.value = optiondate.toISOString().substring(0, 10);
  option.text = optiondate.toLocaleString('en-us', { day: 'numeric', month: 'short', year: 'numeric' })
  selecDay.add(option);
  
  
• The function toISOString() (as used above) will generally return a string with zero offset - so, if we want the local time in the string, we need to get the offset and subtract it - not add it. 
  https://stackoverflow.com/questions/10830357/javascript-toisostring-ignores-timezone-offset
  javascript - Get month name from Date - Stack Overflow 
  var tzoffsetmilli = (new Date()).getTimezoneOffset() * 60000; //offset in milliseconds
  var localtimestring = (new Date(gmtDateTime.getTime() - tzoffsetmilli)).toISOString();
  
  
• How to format numbers by prepending 0 to single-digit numbers? - Stack Overflow
  var formattedNumber = ("0" + myNumber).slice(-2);
  
  
• How to display the abbreviated name of the user's timezone (not GMT offset) in JavaScript? - Stack Overflow
  The method of getTimezoneShort() mentioned in the page above does not display strings like IST for Indian Standard Time, instead it shows GMT +5:30 etc. One could use some jerry-rigged code taking the first letter as mentioned here, but that is not guaranteed to work across many timezones. I just displayed "local time" instead, and displayed the full timezone string elsewhere, as obtained with
  var timeZoneStr = Intl.DateTimeFormat().resolvedOptions().timeZone;
  
  
• How do I check if file exists in jQuery or pure JavaScript? - the similar syntax as this one can be used as a get() replacement, like below, replacing HEAD with GET.
  $.ajax({
                      url: pathname1,
                      type: 'GET',
       success: function (data) {
        schdata = JSON.parse(data);
       },
         error: function () {
          // try something else                            
       }
  });
• If we do a setInterval(), we may need to do a clearInterval() if we want to stop it - otherwise, if the element checked inside the setInterval is not found, there would be an exception and all javascript processing will stop. For example, this time counter -
   
  
  <div id='typingdiv'>
  <h3 id='seccounter'> </h3>
  <script>
  // Set the date we're counting down to
  var sec = 1;
  
  
  // Update the count down every 1 second
  var x = setInterval(function() {
  try {
  document.getElementById("seccounter").innerHTML = sec + "s ";
  sec++;
  }
  catch (err) {
  // error because the setInterval cannot find the element
  // after the ajax completes and overwrites the div.
  clearInterval(x);
  }
  }, 1000);
  </script>
  
  
• A useful way to debug is to add console.log(err) in catch (err) blocks - or console.log some other string at someplace else - and to watch the console on the html page with a right-click, Inspect, and the Console tab.
  
  
• If we have an iframe and want to pass parameters to it (or, in general, we want to pass parameters to javascript on a page), we need to use GET requests and not POST requests - https://stackoverflow.com/questions/831030/how-to-get-get-request-parameters-in-javascript - 
  

  var queryString = location.search
  let params = new URLSearchParams(queryString)
  // example of retrieving 'id' parameter
  let id = parseInt(params.get("id"))
  console.log(id)

exporting to csv download from php

Following https://mehulgohil.com/blog/how-to-export-data-to-csv-using-php/

In our case, the required code was something like this:

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=filemastercsv_export.csv');
require("SessionCheck.php");
require("Constants.php");

$output = fopen( 'php://output', 'w' );
ob_end_clean();
$query= "select * from our_table_master f  where f.fileId > 50900 order by f.fileId";

$dbcon= mysql_connect($DBHOST,$DBUSER,$DBPASSWORD);

if(!$dbcon)
{
echo "Unable to connect to database !";
return;
}

mysql_select_db($DBNAME);

$totalrows= 0;

$result= mysql_query($query);

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

while ($row = mysql_fetch_row($result)) {

    fputcsv($output, $row);

}

mysql_free_result($result);

mysql_close($dbcon);

exit;

uploading files to AWS S3 bucket from Google Apps Script

One possible implementation -

https://engetc.com/projects/amazon-s3-api-binding-for-google-apps-script/

https://github.com/eschultink/S3-for-Google-Apps-Script

pushing vars to templates

A link dump on populating a Google Apps html template, followed by the javascript solution (without Google Apps) which was finally used.

https://developers.google.com/apps-script/guides/html/templates#code.gs_5 

explanation of problem

Passing data to html service - Desktop Liberation (google.com)

 

apparently safe way to do it

google apps script - How to pass a parameter to html? - Stack Overflow

 

Apps Script parameters with script.google.run - Stack Overflow 

not clear from documentation that <? ?> exposes top level variables.

 

correct syntax uses json.stringify

 

Javascript solution instead of Google Apps web app - 
https://github.com/hn-88/program/blob/main/index.html

 

twitter widget not loading in Edge browser

One of our pages which had an embedded twitter timeline widget had the issue that the twitter widget was not loading on Microsoft Edge browser, but would load on Firefox / Chromium. Looking at the console (right-click on the page, Inspect --> Console tab), found that Edge was reporting "Tracking prevention blocked a script resource from loading", along with a Bing search link.

Tracking Prevention blocked a Script resource from loading https - Search (bing.com)

Tracking prevention in Microsoft Edge - Microsoft Edge Development | Microsoft Learn

 

So, we can add exceptions at

edge://settings/privacy/trackingPreventionExceptions - 

adding our domain name solved the issue.

typewriter effect in js vs css

One disadvantage of using the typewriter effect in javascript instead of a spinner gif is that we can't replace the text with something else when finished -
$(#"divname").hide() does not seem to work.

What does seem to work is
document.getElementById('typingdiv').innerHTML = '<br><br>'

So we can use that with either a gif created with a "typing gif creator" like msgif.net or with a typewriter effect in css as the initial innerHTML.

downloading from google cloud shell

Logging in to Google Cloud Shell, noted the "help" options, and a useful function -if we have some file on our home directory in google cloud shell and want to download it, we need not struggle with setting up ssh keys and so on - an easy way is to use

dl filename 

in cloud console - this means download to your client machine (browser). To download a file into cloud shell, we can use standard 

wget url

etc.

way to get "logger-like" continuously updating Google Apps Script Web app output

Since we need to use client-side Ajax to update html pages of the Google Apps Script Web app - https://developers.google.com/apps-script/guides/html/best-practices - one way to get a terminal-like or console log-like experience in the web app would be like this - 

 In the code.gs, 

function doGet() {

return HtmlService

.createTemplateFromFile('Page')

.evaluate()

.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

}

 In the Page.html, a <script> section at the beginning, which has our ajax code, and the <body> section containing the suitable formatted blocks with the relevant ids as called by the ajax functions. 

<script

src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.js">

</script>

<script>

$(function() {

google.script.run
.withSuccessHandler(displaylog1)
.withFailureHandler(displaylog1err)

.getdata1();

});

function displaylog1err(data) {

var logger = document.getElementById('log');

logger.innerHTML += "<p>There was an error 1!</p>";

}

function displaylog1(data) {

var logger = document.getElementById('log');

logger.innerHTML += "<p>Done 1!</p>";

google.script.run.withSuccessHandler(displaylog2)

.getdata2();
// we have to call this inside displaylog1
// if we want getdata2() to execute
// only after getdata1() completes.

}

function displaylog2(data) {

var logger = document.getElementById('log');

logger.innerHTML += "<p>Done 2!</p>";

}

</script>

</head>

<body>

<pre id="log0">Logging the output here: </pre>

<pre id="log"> </pre>

</body>

</html>

 

The method mentioned at https://stackoverflow.com/questions/20256760/javascript-console-log-to-html for redirecting console.log did not work for me, maybe because console.log logs to the console of the client making the call, and in our case, the relevant functions are server-side. Or maybe there are other differences, like the (function () { }) call syntax seems to be different from the one below:

$(function() {

var old = console.log;

var logger = document.getElementById('log');

console.log = function () {

for (var i = 0; i < arguments.length; i++) {

if (typeof arguments[i] == 'object') {

logger.innerHTML += (JSON && JSON.stringify ? JSON.stringify(arguments[i], undefined, 2) : arguments[i]) + '<br />';

} else {

logger.innerHTML += arguments[i] + '<br />';

}

}

}

navigating the new Google Analytics 4 reports

Navigating the new analytics.google.com - takes a bit of getting used to.

• A tour shows us the location of the main navigation elements.
  
  
• Typing "tour" in the search box brings up some tours.
  
  
• We can add the tracking code to subdomains also, no problems with tracking. So I deleted the separate property I had created for a subdomain from Admin --> Property Settings (of the relevant property) --> Move to Trash Can button (on the top right) as per
  https://support.google.com/analytics/answer/1042032?hl=en
   
• We can access the tracking code from Admin --> Property Settings --> Data Streams --> Click on the relevant domain or stream --> View tag instructions (at the bottom of the page).
  
  
• We can find stats for a particular page from Reports --> Engagement --> Pages and screens.
  
  
• We're able to see stats of pages 
  
  (a) based on the page title and not based on the page domain - subdomain.ourdomain.org returns no results
  
  
  (b) based on the path excluding the domain name - /ourfoldername or index.php or index_new.php does show up.
  
  
  
  
  
  But for these, we have to change the drop-down accordingly, as in the screenshots below -
  
  For search based on page title - 
  
  
  
  
  
  
  
  
  
  For search based on page path -
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  And it also seems to be a bit buggy - Searching for program gives only 2 results, while searching for  /program gives many results, and searching for program/ gives the 4 results we want.
  
  
  
  
  
  
  
  
  
  

javascript date comparison

Some interesting things about client-side javascript date comparison - 

• if you want to compare with ==, you would need to prefix the variable with a + but > and < would work fine.
  
  
• you can't just write
  if ( Date(datestring) < Date("2022-12-08 23:59:59" ) ) - you need to create new Date(string) explicitly, like
  var d1 = new Date("2022-12-07 23:59:59" );
  var d2 = new Date("2022-12-08 23:59:59" );
  var testdate = new Date(testdatestr);
  if ( testdate > d1 && testdate < d2 )

link dump of possible google apps and javascript solutions

(The following is a link dump of various possibilities searched out for implementing this, for which plain javascript without Google Apps script was finally used.)

Link dump:

We can automatically choose the user's timezone, and also have a timezone setting from any of these,

Joda-Time - Java date and time API - Time Zones (sourceforge.net)

according to

javascript - Google Apps Script formatDate using user's time zone instead of GMT - Stack Overflow

 

google apps script - Getting current user timezone - Stack Overflow 

 

Linking Google Sheets: Reference Another Spreadsheet | Coupler.io Blog

FILTER Function Google Sheets How-To Guide | Coupler.io Blog 

 

"Correct way" - tested working

https://stackoverflow.com/questions/1091372/getting-the-clients-time-zone-and-offset-in-javascript

console.log(Intl.DateTimeFormat().resolvedOptions().timeZone)

Documentation
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/DateTimeFormat/resolvedOptions

"Do we have some stats on the number of daily user visits to the schedule page? If so, that would be useful in planning the type of page - could even make it a static page with data delivered via ajax using client-side javascript. There are some limits to the number of Google Apps Script executions per day. For example, I'm using a URL Fetch function, that is limited to 100,000 calls per day. " 

Checking out client side javascript rendering, found that is faster than Google Apps Script, faster than our php/mysql also :)

Client-side example:

https://hn-88.github.io/Ajax-example-with-json/AjaxExample.html

for which the code is at 

https://github.com/hn-88/Ajax-example-with-json/blob/master/AjaxExample.html

(takes the data from a text file saved in the same server)

Google Apps script example:

(link removed)

(takes the data from a Google Sheet)

Google Apps test code has now been put up at 

https://github.com/hn-88/Test-schedule-page/

comments in the code show the various issues with the different approaches.

 

idea for updating website once a day

This post talks about manually downloading and uploading, using Google Apps Script only to generate the webpage once and save it to Drive.
https://stackoverflow.com/questions/70071534/i-want-to-use-google-app-script-with-html-service-to-create-a-static-html-file

I suppose the file saved to Drive can be uploaded to some other site using some sort of scripting running either on Google Apps Script or on our local server. 

Edit: Possible uploading to S3 bucket using Google Apps Script code similar to this. 

Edit Mar 2023: Implemented a Github Pages upload version using Github's REST API in the PHP translation of our Google Apps Script mentioned in a later post. 

https://github.com/hn-88/mktxt/blob/main/maketxt.php#L271

Google photos - no bulk rotate option

I had several albums of photos imported into a google photos account, which needed to be rotated left. There seems to be no bulk rotate tool. So I wrote a SikuliX script - though it takes half a second per photo, at least it'll do the job unattended:

maxi=30 #the number of images in the album

i=0

sleeptime1=0.2

sleeptime2=0.2

sleep(1.0)

while(i<maxi):  

  type("R")            #rotate

  sleep(sleeptime1)

  type(Key.RIGHT)        #go to next   

  sleep(sleeptime2)

  i=i+1

interesting comparison website

Comparing github pages versus cloudflare pages, and many others too, with interesting timing info:

https://bejamas.io/compare/cloudflare-pages-vs-github-pages/

for Income Tax Return (ITR) filing - reference

 https://www.etmoney.com/learn/income-tax/how-to-file-income-tax-return-itr-for-mutual-funds-on-new-tax-portal/

- a good reference for what details are needed, and where what needs to be filled up, for those with Capital gains from Mutual Funds. 

Edit: June 17 -

Apparently it's not a good idea to file before June 15 - https://www.livemint.com/money/personal-finance/why-you-should-avoid-filing-tax-returns-before-15-june-11686674063937.html

And while there is the option of uploading a csv file, in my case, the manual entry of the data needed for each Mutual Fund transaction was the easier way, since some of the data was not directly 1:1 matching in the format the IT department wanted vs the format provided by Kfintech / CAMS in their profit/loss statement. For example, the ASIN information was part of the "fund name" field, we need to copy-paste it out (or write code to check for it and take it out, which would make the process more complex). 

 And also found that Groww (and other such online mutual fund facilitators) provides only profit/loss from the funds purchased through Groww - Kfintech + CAMS statements are needed for the full picture. 

Also, for ease of opening the protected Excel sheets with Google Sheets, the method I used was to download them, open them locally using LibreOffice, then save as .ods or something like that without password protection, and then upload to Google sheets.

correct way to append with jquery

jquery append() actually appends an element. So, we need to make the append statement an entire row, in case we're appending to a table.

Populate html table on jQuery success event - Stack Overflow

table.append("<tr><td>"+elem.username+"</td><td>"+elem.name+"</td>   <td>"+elem.lastname+"</td></tr>");

and not something like

table.append("<tr><td>");

table.append(elem.username);

table.append("</td><td>");

etc.   

correct way to use jQuery append

Doing a table fill with AJAX, I was trying to use $('#maintable').append() multiple times in a loop, assuming that the append function is just appending whatever text I supply to it - but it turns out jQuery append actually appends a child element to the parent element. The above syntax is for jQuery and not plain javascript. 

I was ending up with content like

<tr></tr>
<tr></tr>
<tr></tr>
<td>Our data</td> ... and so on.

 So, I must append an entire row with a single append statement. In javascript, the function call can span multiple lines, no problem. Most probably the correct way would be to use maintable.tbody.append, though the snippet below works.  

function showThings(things) {

var maintable = $('#maintable');

maintable.empty();

for (var i = 0; i < things.length; i++) {

maintable.append('<tr><td>Row '+ i + '</td><td>' + things[i][0] + '</td><td>' +

things[i][1] + '</td><td>' +

things[i][2] + '</td><td>' +

things[i][3] + '</td><td>' +

things[i][4] + '</td><td>' +

things[i].length + '</td></tr>');

}

}

updating URL in descriptions

Adapted from an email from PB:

Executed the following SQL in the local postgresql database:

UPDATE file_information SET description = REPLACE(description,'http://old.domain/', 'https://new.domain/sss/') where description like '%old.domain%'

1539 row(s) affected.


UPDATE file_information SET description = REPLACE(description,'http://www.old.domain/', 'https://new.domain/sss/') where description like '%old.domain%'
354 row(s) affected.


Executed the following SQL on the web server:

UPDATE `our_file_master` SET `description` = REPLACE(description,'http://old.domain/', 'https://new.domain/sss/') where description like '%old.domain%'
(1022 rows)
and

UPDATE `our_file_master` SET `description` = REPLACE(description,'http://www.old.domain/', 'https://new.domain/sss/') where description like '%old.domain%'
(345 rows)

exporting mysql table to csv

I wanted to export quite a large subset of a table which was using up 1.8 GB - first tried this method,
https://phoenixnap.com/kb/mysql-export-table-to-csv

 SELECT ourfield1, ourfield2, ourfield3 FROM ourTable
INTO OUTFILE '/path/to/ExportFile.csv'
FIELDS ENCLOSED BY '"'
TERMINATED BY ';'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

This failed, --secure-file-priv is set to null(?) on our server and I didn't want to change that.

database - How should I resolve --secure-file-priv in MySQL? - Stack Overflow

Next plan was to export by redirecting a here document in a bash script. One hiccup was that the here doc should end with the end characters without any indent, and there should be no other characters after it on its line, otherwise the script fails to find the characters.

https://stackoverflow.com/questions/18660798/here-document-gives-unexpected-end-of-file-error 

Next issue was that the exported data was large, and probably not indexed, so
mysql: Out of memory (Needed 698144116 bytes)

For this, I would probably need to limit the number of result rows (by timestamp, in this case),

How do I get the current Unix time in milliseconds in Bash? - Server Fault

https://www.vanstechelman.eu/linux/time-conversion-using-bash

How to increment a variable in bash? - Ask Ubuntu

Then, the easiest way to code seemed to be to put the entire statement in one line, avoiding issues with SQL/bash multi-line statements:

$MYSQL --batch -u $MyUSER -h $MyHOST -p$MyPASS $DBS -e "select l.id, l.timecreated, l.userid, l.ip, f.filename from prefix_logstore_standard_log l left join prefix_files f on l.contextid = f.contextid where l.timecreated > $StartDate  and l.timecreated < $EndDate and l.action = 'viewed' and l.target='course_module' and f.filename like '%.pp%' order by l.timecreated;" > $FILE

This would actually give a tab separated file and not comma separated, but this is also imported without issues by Google Sheets.

 

easier way to handle RDP failure on Azure burstable VMs

Since leaving the Remote Desktop Protocol (RDP) port 3389 open is not recommended, I had tried to move one of the teams working on a couple of remote Windows servers on Azure B-series VMs to use ssh tunnels instead of directly RDP into the server. They could not get the tunnels to work with the reg file I sent them - probably because the paths were different. A workaround would be to just stop the remote desktop service when not in use, and start the service only when they need to use it. So, created a stoprdp.bat file, containing

net stop UmRdpService

net stop termservice

A similar restartrdp.bat file has

net stop UmRdpService

net stop termservice

net start termservice

net start UmRdpService

This is another easier way of doing the same thing described in my earlier post at https://hnsws.blogspot.com/2021/05/restarting-remote-desktop-via-ssh-or.html

port 2000 shown as open by netcat

Checking for open ports on one of our servers using netcat,
nc -zv our.server.name 2000
Connection to our.server.name 2000 port [tcp/cisco-sccp] succeeded!

Googling, found that this could be due to our ISP trying to block VOIP ports.

https://security.stackexchange.com/questions/182422/connection-to-ports-2000-and-5060-successful-despite-filtering

https://www.google.com/search?q=why+is+port+2000+open+on+my+azure+server

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);      

   
   

   }