Overcome slice() error on blank cells.

[Octávio Teixeira]

Hello to all,

The following Javascript code is failing with: "Uncaught TypeError: Cannot read properties of null (reading 'slice')"

function setDataForSearch(){
dados = [[1.0, Last one, 1aDIRECTO Maison, 1.0, , Wed Feb 03 00:00:00 GMT+00:00 2021], [57.0, veber, 1aDIRECTO Maison, 1001.55, , Fri Jul 21 00:00:00 GMT+01:00 2023], [58.0, agora sim, 1aDIRECTO Maison, 11234.21, , Fri Jul 21 00:00:00 GMT+01:00 2023]];
         dados = dataReturned.slice();
 }

I suspect that the problem is due to blank cells from the Google sheet where "dados" is coming from. If this is true, is there any way of overcoming this problem?

Many thanks for any kind help

Octávio

[💼]

You’re not showing us where you get ‘dataReturned’ from…

> On Jul 8, 2023, at 11:30 AM, Octávio Teixeira <o.teix...@gmail.com> wrote:
>
> dataReturned

[Keith Andersen]

to define "dados" you need to use;

let dados = 

or

var dados =

And array.slice() needs a beginning and end

array.slice(1, 4)

See: https://www.w3schools.com/jsref/jsref_slice_array.asp

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/70A413D4-4379-458C-9A24-4D4E6626C6AD%40gmail.com.

[Octávio Teixeira]

Sorry, you're right. Just forget that variable. The code is: "dados.slice();"

[Octávio Teixeira]

Tried that, did not work...

[Web Dev]

because you're executing .slice() method on the nested array [[...]]. You need to target the inner array: dados[0].slice(...)

[Octávio Teixeira]

Thanks for helping but, can you please explain with more detail? I'm very new to this...

Sender notified by Mailtrack

08/07/23, 22:46:18

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/lWLrB6rAaY4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/15aa856b-c85b-42d6-9bc7-a83c2ebb7ca8n%40googlegroups.com.

[Keith Andersen]

If you can share your spreadsheet I can make the necessary corrections. 

However, in this forum, it would take entirely too much space to explain to you the proper JavaScript methods which your code lacks. In addition I have no idea exactly what you are trying to 'slice' out of the array.

You did not declare the variable (let dados=)

Again in this type correspondence it would be difficult to explain array structure as well as the array slice method.

There are many video tutorials on YouTube that cover these subjects in detail. 

Again if you can share your sheet I'd be more than happy to help. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOZudc4OirGzrRSHUY53_dYNT9S1oxMk%3DMQ%3DzO7vr1DiNtBjOg%40mail.gmail.com.

[Octávio Teixeira]

Many thanks for spend your time on this issue, here's the spreadsheet link, Data resides on sheet "Registos":

https://docs.google.com/spreadsheets/d/1tst4H-U9qYSghajdVsTjvCZImMKhNPydQLpZZD84KH0/edit?usp=sharing

Sender notified by Mailtrack

09/07/23, 08:09:44

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4yhjOTjkzhZaO0Md05iqnGryTTshpgV%3DiPpmJVf5AniZQ%40mail.gmail.com.

[Keith Andersen]

Did you delete the script?

I find no script attached to this sheet.

Also can you explain what you are trying to do with the script?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOZudc468k3bekfL%2BMqAYgxVeu5qtbZNjLgzxmBb9y4Ze8VPeg%40mail.gmail.com.

[Octávio Teixeira]

Hello and thank you again for your patience.

It is  a standalone script. Here is the data retrieving code:

var url = "https://docs.google.com/spreadsheets/d/1tst4H-U9qYSghajdVsTjvCZImMKhNPydQLpZZD84KH0/edit#gid=0";
function getDataForSearch(){
  const ss = SpreadsheetApp.openByUrl(url);
  const ws = ss.getSheetByName("Registos");
 return ws.getRange(2,1,ws.getLastRow()-1,26).getValues();    //If we change 26 to 5 there are no errors and the app works fine...
}

Here is the function that calls the above one:

function setDataForSearch(){
          google.script.run.withSuccessHandler(function(dataReturned){
          dados = dataReturned.slice();   //the error refers this line
           }).getDataForSearch();
        }

This function also fails:

function procurar(){
  var searchInput = document.getElementById("searchInput").value.toString().toLowerCase().trim();
  var searchWords = searchInput.split(/\s+/);
  var searchColumns = [1,2,3];
  var resultadosProcura = searchInput === "" ? [] : dados.filter(function(r){     //It fails in this line too

   return searchWords.every(function(word){
     return searchColumns.some(function(colIndex){
        return r[colIndex].toString().toLowerCase().indexOf(word) !== -1;
      })
    })
   
    });
    var searchResultsBox = document.getElementById("searchResults");
    var templateBox = document.getElementById("rowTemplate");
    var template = templateBox.content;
    searchResultsBox.innerHTML="";
    resultadosProcura.forEach(function(r){
      var tr = template.cloneNode(true);
      var registoIdCol = tr.querySelector(".regId");
      var descricaoCol = tr.querySelector(".descricao");
      var rubricaCol = tr.querySelector(".rubrica");
      var valorCol = tr.querySelector(".valor");
      var deleteButton = tr.querySelector(".delete-button");
      registoIdCol.textContent = r[0];
      deleteButton.dataset.IDRegisto = r[0];
      descricaoCol.textContent = r[1];
      rubricaCol.textContent = r[2];
      valorCol.textContent = r[3].toLocaleString('pt-PT', {minimumFractionDigits: 2});  
      searchResultsBox.appendChild(tr);
    });
}

Cheers,

Octávio

Sender notified by Mailtrack

09/07/23, 23:28:36

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4wYZmLS4ywk-RMjRyoa3VTVQLn1bDBNrbdCRpvAC7vN%2BQ%40mail.gmail.com.

[Keith Andersen]

function getDataForSearch(){
  const ss = SpreadsheetApp.openByUrl(url);
  const ws = ss.getSheetByName("Registos");
 return ws.getRange(2,1,ws.getLastRow()-1,26).getValues();
 }  

***The above works when I duplicate and run. I have no idea why its not working for you.

function setDataForSearch(){
          google.script.run.withSuccessHandler(function(dataReturned){
          dados = dataReturned.slice();   //the error refers this line
           }).getDataForSearch();
        }

***Also,where is the array - dataReturned?  What part of dataReturned are you applying slice()
Please look here (https://www.w3schools.com/jsref/jsref_slice_array.asp) to see how to apply slice() for your needs

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAOZudc4hXPWXFdmwOyyWsggNvihU6p5%3DvdY_u99e-Ycy%3Dr93HA%40mail.gmail.com.

[Octávio Teixeira]

Hello,

Function getDataForSearch also works for me with no problem. I just mentioned that because when I use up to columns 5 there are no errors with slice() (and filter()). If I pick more than 6 columns (26 are all the worksheet columns) then the errors come up.

dataReturned is the data retrieved by the getDataForSearch function. I forgot to copy the global variable declaration and the full code for this function is:

var dados;

function setDataForSearch(){
          google.script.run.withSuccessHandler(function(dataReturned){
          dados = dataReturned.slice();

           }).getDataForSearch();
        }

Thank You,

Octávio

Sender notified by Mailtrack

10/07/23, 07:21:10

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4xzQOU6k7N3ma55sXF4c9m-vcMEJ%2BRYEqjukataAOqw8w%40mail.gmail.com.

[Octávio Teixeira]

Also I am not giving  a start or end to slice() because I need all the data returned and slice() is used only to hold a copy of that data.

Sender notified by Mailtrack

10/07/23, 07:27:53

Em seg., 10 de jul. de 2023 às 01:05, Keith Andersen <keith.a...@gmail.com> escreveu:

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAJ8MD4xzQOU6k7N3ma55sXF4c9m-vcMEJ%2BRYEqjukataAOqw8w%40mail.gmail.com.

[Octávio Teixeira]

Finally I found the cause of the problem (at least I believe so). There is no problem with the code.

The slice() , and of course filter(), methods fail due the unfinished query to the worksheet. Whenever "net state" goes from "BUSY" to "IDLE" everything runs smoothly.

BY THE WAY, as I am very new to this things, if someone has an idea to solve the problem I will be very grateful.

Many thanks to everybody for the patiente,

Octávio

A sábado, 8 de julho de 2023 à(s) 19:53:26 UTC+1, Keith Andersen escreveu:

[Octávio Teixeira]

Thank You but that did not help. I tried:

let dados = [];

var dados=[];

with this change, even with net state = 'IDLE', slice() runs into error...

Sender notified by Mailtrack

12/07/23, 20:18:14

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/lWLrB6rAaY4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/e04ad545-018b-460f-8531-c4b82ead5248n%40googlegroups.com.