Check whether Google Sheets cells contain values using Google Apps Script

Question

In google app scripts, I am pulling in data (all strings) from a google sheet and then trying to split up the array into separate columns excluding the first row (header row). The data has an arbitrary amount of rows and columns. "projects" are the headers and "tasks" is the data underneath each header.

  // load in task names
  function LoadTasks() {
    var sheet = ss.getSheetByName("Task List");
    sheet.activate();

    var allTasks = sheet.getDataRange().getValues();

    return allTasks;
  }


  function Analysis(project, tasks) {
    var sheet = ss.getSheetByName(project);
    sheet.activate;

    var taskLength = tasks.length;
    var sheetLength = (taskLength*4) + 10;

    Logger.log("The Project is " + project + " with task length of " +       taskLength + " and task data of: " + tasks);
  }

  taskData = LoadTasks();
  numProjects = taskData[0].length;
    // load all project names into single array
  for (i = 0; i < numProjects; i++) {
    projectNames[i] = taskData[0][i];
  }

  for (i = 0; i < numProjects; i++) {
    project = projectNames[i];
    j = 1;
    while (taskData[j][i] != null) {
      tasks[j-1] = taskData[j][i];
      j++;
    }
    Analysis(project, tasks)
    tasks = [];
  }

In the very last while loop, how can I check to see if the array value I'm looking at holds a value (does not contain a null, undefined, or blank). My current method gives me the error: Cannot read property "0.0" from undefined. (line 91, file "Code")

***Line 91: while (taskData[j][i] != null) {

Unknown Unknown · Accepted Answer · 2016-06-03T10:40:36

The method getValues() returns a double array of values which can be numbers, strings, or Date objects. There are no "null" or "undefined" among the values. Blank cells are represented as empty strings "" and you can detect them by comparison to "".

That said, one should control the index bounds explicitly:

while (j < taskData.length && taskData[j][i] !== "") {
  tasks[j-1] = taskData[j][i];
  j++;
}

Check whether Google Sheets cells contain values using Google Apps Script

1 Answers