I´m geting Cannot read property "0" from undefined. Error on line 16. ( while (colunaDias[emptyCell][0] <= dias) )

It should be a very simple function for google SpreadSheets. I can´t see what I´m doing wrong...

The bizarre thing is that if I don´t use the variable "dias" and use a integer instead. The while function works....

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var leadsSheet = ss.getSheetByName("Leads Todas as Categorias, menos outros - Days (5days)");
  var targetSheet = ss.getSheetByName("feticaria");

  var cellLeads = leadsSheet.getRange(1,1).getValue();
  //var cellTarget = targetSheet.getRange(1,1).setValue(valor);

  var colunaDias = leadsSheet.getRange('B:B').getValues();
  var sourceMedium = leadsSheet.getRange('A:A').getValues();
  var emptyCell = 16;
  var dias = 1;

  while (colunaDias[emptyCell][0] != ""){
    while (colunaDias[emptyCell][0] <= dias){
        dias++;
        emptyCell++;
    }


    emptyCell++;

  }

  Logger.log(emptyCell);


}

I´m geting Cannot read property "0" from undefined. Error on line 16. ( while (colunaDias[emptyCell][0] <= dias) )

It should be a very simple function for google SpreadSheets. I can´t see what I´m doing wrong...

The bizarre thing is that if I don´t use the variable "dias" and use a integer instead. The while function works....

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var leadsSheet = ss.getSheetByName("Leads Todas as Categorias, menos outros - Days (5days)");
  var targetSheet = ss.getSheetByName("feticaria");

  var cellLeads = leadsSheet.getRange(1,1).getValue();
  //var cellTarget = targetSheet.getRange(1,1).setValue(valor);

  var colunaDias = leadsSheet.getRange('B:B').getValues();
  var sourceMedium = leadsSheet.getRange('A:A').getValues();
  var emptyCell = 16;
  var dias = 1;

  while (colunaDias[emptyCell][0] != ""){
    while (colunaDias[emptyCell][0] <= dias){
        dias++;
        emptyCell++;
    }


    emptyCell++;

  }

  Logger.log(emptyCell);


}

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Jul 31, 2014 at 14:01 user3347814 asked Jul 31, 2014 at 13:06 user3347814user3347814 1,14399 gold badges3434 silver badges5858 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 2

I think the only thing that could cause that error is if emptyCell is bigger than the colunaDias array, i.e. if your sheet is smaller than 16 rows (if the value you show here is correct).

Add this line right before the first while :

Logger.log('emptyCell = '+emptyCell+'  and colunaDias.length = '+colunaDias.length);

I tested a copy of your script and it runs without error except if I define emptyCell > 1000 on a 1000 rows Sheet.

I'm guessing that colunaDias[emptyCell] is undefined. It passes the first while condition, because "undefined" is not equal to "". If colunaDias[emptyCell] is undefined, then either there is something wrong with this line:

var colunaDias = leadsSheet.getRange('B:B').getValues();

or

colunaDias[emptyCell][0]

Where "emptyCell" is 16 is the problem. getValues() returns an object of rectangular grid of values. I would test to see if there is anything in the rectangular grid, by checking [0][0].

Logger.log('is there any data? ' + colunaDias[0][0])

If there is no data, then the something failed on line:

var colunaDias = leadsSheet.getRange('B:B').getValues();

If that line is working, then something higher up is wrong.

You should be checking the return type of getSheetByName for null.

// The code below will log the index of a sheet named "YourSheetName"
var leadsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YourSheetName");
if (leadsSheet != null) {
  Logger.log(leadsSheet.getIndex());
}

I know this post in from a few years ago, but this was the closest to a problem I am having. I just want to post my answer in case someone else ends up here one day.

For some reason the check variable "dias" is being passed as a string. That's why replacing it with a number allows the script to run.

dias = parseInt(dias);    //use this before the variable needs to be read

I cant say why it is passing a string after being ++ but this will fix it