How properly use array here?

Question

i have a problem with my code, i cant use my array properly, for some reason its blank, here is my code

    function myFunction() {
  const ss = SpreadsheetApp.openByUrl ('sheet url');
  var sheet = ss.getSheetByName("new project")
  var v1 = sheet.getLastRow()
  Logger.log(v1);
  var v2 = sheet.getLastColumn()
  Logger.log(v2);
  var data = sheet.getRange(2, 1, sheet.getLastRow() -1, sheet.getLastColumn()).getValues();
  Logger.log(data);
  
  

    for (var i = 1; i <= data.length; ++i) 
      {
        var data1 = sheet.getRange(i,1,1,13).getValues()
        Logger.log(i);
        Logger.log(data1); 
        var cell = data[i][9]; //this array is a problem
        Logger.log(cell);
        if (cell ==="") {
          Logger.log('blank cell');
        }
        
      }  
      }

so this is why its a problem : example

this how my sheet looks like : sheet

screen of my error: error

as you can see its just empty, idk if its even null, please help

Can you show what your data looks like? How many columns, rows and any blank cells in there? Can you paste the whole Logger report please? And what's the expected output? — Dmitry Kostyuk
– Dmitry Kostyuk, Commented Jul 19, 2021 at 18:41
@DmitryKostyuk i added how my sheet looks like, and data is basicaly reading from sheet without first row — DragonHunter
– DragonHunter, Commented Jul 19, 2021 at 18:56
@Cooper i will be honnest, it will take me so much time to understand your code, its not rly comment about you but about my stupidity :) — DragonHunter
– DragonHunter, Commented Jul 19, 2021 at 19:12
I gather that you have never used Array Methods or Arrow Function Notation — Cooper
– Cooper, Commented Jul 19, 2021 at 19:13

halfer · Accepted Answer · 2022-03-19 23:59:46Z

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1")
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  vs.forEach((r,i) => {
    //the length keeps you from thinking zeroes are blank
    if(!r[8] && !r[8].toString().length) {
      Logger.log('blank cell');
    }
  });
}

Try this:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet1")
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  for(let i = 0;i < vs.length;i++) {
    if(!vs[i][8] && !vs[i][8].toString().length) {
      Logger.log('blank cell')
    }
  }
  Logger.log(JSON.stringify(vs));
}

Sheet1:

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
10	11	13	2	15	8	7	3	4	7
13	19	8	16	6	17	11	8		1
18	1	14	11	0	11	19	1	15	2
4	12	12	6	15	9	10	17	10	11
10	8	2	17	9	16	14	5		15
7	14	15	13	12	18	18	18	19	19
15	14	13	5	10	7	19	9	12	9
19	13	8	7	4	18	13	10		11
13	14	15	6	17	2	8	17	0	12
2	3	5	1	1	12	10	12	16	3
13	13	7	6	19	8	10	11	17	5
6	6	11	1	2	4	4	16	18	13
4	15	4	7	7	8	16	16	9	18
11	3	3	14	7	15	7	6	0	12
19	10	1	13	14	1	9	13	8	3
4	10	18	5	16	19	14	11	0	13
11	10	7	14	19	3	4	4	11	11
0	2	4	1	18	3	0	12	4	16
0	11	0	4	11	7	0	4	13	0
7	9	0	3	3	11	0	6	10	3

vs:

[[10,11,13,2,15,8,7,3,4,7],
[13,19,8,16,6,17,11,8,"",1],
[18,1,14,11,0,11,19,1,15,2],
[4,12,12,6,15,9,10,17,10,11],
[10,8,2,17,9,16,14,5,"",15],
[7,14,15,13,12,18,18,18,19,19],
[15,14,13,5,10,7,19,9,12,9],
[19,13,8,7,4,18,13,10,"",11],
[13,14,15,6,17,2,8,17,0,12],
[2,3,5,1,1,12,10,12,16,3],
[13,13,7,6,19,8,10,11,17,5],
[6,6,11,1,2,4,4,16,18,13],
[4,15,4,7,7,8,16,16,9,18],
[11,3,3,14,7,15,7,6,0,12],
[19,10,1,13,14,1,9,13,8,3],
[4,10,18,5,16,19,14,11,0,13],
[11,10,7,14,19,3,4,4,11,11],
[0,2,4,1,18,3,0,12,4,16],
[0,11,0,4,11,7,0,4,13,0],
[7,9,0,3,3,11,0,6,10,3]]

used your if, but i still have error: "TypeError: Cannot read property '8' of undefined"

DragonHunter · Accepted Answer · 2021-07-21 08:35:04Z

so, reason why i had a problem, i used 'i' as a number of a column, while my column started with 2, however fist array is a 0, and i had 3 columns, so basicaly this is what i did:

    function myFunction() {
  const ss = SpreadsheetApp.openByUrl ('my url');
  var sheet = ss.getSheetByName("new project")
  var v1 = sheet.getLastRow()
  Logger.log(v1);
  var v2 = sheet.getLastColumn()
  Logger.log(v2);
  var data = sheet.getRange(2, 1, sheet.getLastRow()-1 , sheet.getLastColumn()).getValues();
  Logger.log(data);
  for (var i = 2; i <= data.length+1; ++i) 
  {
    var data1 = sheet.getRange(i,1,1,13).getValues()
    Logger.log(i);
    Logger.log(data1); 
    Logger.log(data[i-2][8])
    var cell= data [i-2][9]
      if ( cell ==="") {
          Logger.log('blank cell');
    
  }  
  }
}

execution

Collectives™ on Stack Overflow

How properly use array here?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
10	11	13	2	15	8	7	3	4	7
13	19	8	16	6	17	11	8		1
18	1	14	11	0	11	19	1	15	2
4	12	12	6	15	9	10	17	10	11
10	8	2	17	9	16	14	5		15
7	14	15	13	12	18	18	18	19	19
15	14	13	5	10	7	19	9	12	9
19	13	8	7	4	18	13	10		11
13	14	15	6	17	2	8	17	0	12
2	3	5	1	1	12	10	12	16	3
13	13	7	6	19	8	10	11	17	5
6	6	11	1	2	4	4	16	18	13
4	15	4	7	7	8	16	16	9	18
11	3	3	14	7	15	7	6	0	12
19	10	1	13	14	1	9	13	8	3
4	10	18	5	16	19	14	11	0	13
11	10	7	14	19	3	4	4	11	11
0	2	4	1	18	3	0	12	4	16
0	11	0	4	11	7	0	4	13	0
7	9	0	3	3	11	0	6	10	3

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
10	11	13	2	15	8	7	3	4	7
13	19	8	16	6	17	11	8		1
18	1	14	11	0	11	19	1	15	2
4	12	12	6	15	9	10	17	10	11
10	8	2	17	9	16	14	5		15
7	14	15	13	12	18	18	18	19	19
15	14	13	5	10	7	19	9	12	9
19	13	8	7	4	18	13	10		11
13	14	15	6	17	2	8	17	0	12
2	3	5	1	1	12	10	12	16	3
13	13	7	6	19	8	10	11	17	5
6	6	11	1	2	4	4	16	18	13
4	15	4	7	7	8	16	16	9	18
11	3	3	14	7	15	7	6	0	12
19	10	1	13	14	1	9	13	8	3
4	10	18	5	16	19	14	11	0	13
11	10	7	14	19	3	4	4	11	11
0	2	4	1	18	3	0	12	4	16
0	11	0	4	11	7	0	4	13	0
7	9	0	3	3	11	0	6	10	3

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related

COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9	COL10
10	11	13	2	15	8	7	3	4	7
13	19	8	16	6	17	11	8		1
18	1	14	11	0	11	19	1	15	2
4	12	12	6	15	9	10	17	10	11
10	8	2	17	9	16	14	5		15
7	14	15	13	12	18	18	18	19	19
15	14	13	5	10	7	19	9	12	9
19	13	8	7	4	18	13	10		11
13	14	15	6	17	2	8	17	0	12
2	3	5	1	1	12	10	12	16	3
13	13	7	6	19	8	10	11	17	5
6	6	11	1	2	4	4	16	18	13
4	15	4	7	7	8	16	16	9	18
11	3	3	14	7	15	7	6	0	12
19	10	1	13	14	1	9	13	8	3
4	10	18	5	16	19	14	11	0	13
11	10	7	14	19	3	4	4	11	11
0	2	4	1	18	3	0	12	4	16
0	11	0	4	11	7	0	4	13	0
7	9	0	3	3	11	0	6	10	3