CODEWITHSUNDEEP
javascriptgoogle-apps-script
DragonHunter
DragonHunter

Reputation: 21

How properly use array here?

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

Upvotes: 0

Views: 81

Answers (2)

Cooper
Cooper

Reputation: 64120

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]]

Upvotes: 1

DragonHunter
DragonHunter

Reputation: 21

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

Upvotes: 0

Related Questions