Google Apps Scripts If function not skipping rows with null value

Question

I am writing a function that will search a spreadsheet for days an employee has worked hours, create an array with the values for job number, job name, employee name, hours, and date, and then put them onto another spreadsheet. It also only shows hours that were within the past 8 days so that time can be worked out for the week when we have to report hours (I'm scheduling it to run the day time is due). I also have been filtering out days with blank hours, which limits the amount of data that gets copied over.

I am encountering an issue where on one employee the function to skip the blank hours does not work. For other employees I have been able to use "" to indicate an empty cell. I have also tried to use (null) as a value, but that only ignores 6 of 7 days (It still logs days with no hours that are adjacent to cells that calculate hours in the week).

What I can't figure out is why this doesn't work on just one sheet out of the whole Google Sheets document. I have simplified my spreadsheet to reduce personal information, and to make it easier to parse the script, but in my original document I track 6 employees with similar code, and only one is showing this issue.

https://docs.google.com/spreadsheets/d/1ve0EPVQJ2vmWG1NYHMncw1ZljP3yXd28dMeNC38Jiy4/edit?usp=sharing

Is a link to the spreadsheet. Code is below.

function shoptime(){
  var ss = SpreadsheetApp.getActive().getId();
  var stephensheet = Sheets.Spreadsheets.Values.get(ss, 'Stephen!A2:G');
  var tiffanysheet = Sheets.Spreadsheets.Values.get(ss, 'Tiffany!A2:G');
  var scripts = SpreadsheetApp.getActive().getSheetByName("Scripts");
  var currentDate = new Date();
  var pastweek = new Date();
  pastweek.setDate(currentDate.getDate() -8);

var array=[];

  for (var a = 0; a < stephensheet.values.length; a++){
  var jobdate = stephensheet.values[a][1];
  var intime = stephensheet.values[a][2];
  var outtime = stephensheet.values[a][3];
  var dailyhours = stephensheet.values[a][4];

  if (new Date(jobdate) > pastweek){
      if (dailyhours != (null)){
      array.push(["NA","Office","Stephen",dailyhours,jobdate]);
    }
  }
 }
  for (var a = 0; a < tiffanysheet.values.length; a++){
  var jobdate = tiffanysheet.values[a][1];
  var intime = tiffanysheet.values[a][2];
  var outtime = tiffanysheet.values[a][3];
  var dailyhours = tiffanysheet.values[a][4];

  if (new Date(jobdate) > pastweek){
      if (dailyhours != ("")){
      array.push(["NA","Office","Tiffany",dailyhours,jobdate]);
    }
  }
 }
  if(array[0]){
    scripts.getRange(scripts.getLastRow()+1,1,array.length,5).setValues(array);  
    }
  SpreadsheetApp.flush();
}

@StephenSchonewolf Have you inspected what the values of dailyhours are in the script? Either by running through the debugger or logging the values and their types? Also, I know the script you've posted is abridged, but is there a particular reason for using the advanced Sheets service and not the standard? — Diego
Ok, I see the issue now. Is there any particular reason that you need to use the advanced API rather than the build in one SpreadsheetApp? When changing your code to use the built-in service it works as expected. — James D
@Diego I ran the logs for 'daily hours'. It gives the values as null...except for the 7th day in the cycle that is next to the cell that adds the past 7 days for a weekly total. I'm using advanced Sheets purely out of habit, since that's where I found the first example of using a row iterator about a year back. I've been unsuccessful trying to do it other ways. In the rest of the script I also am pulling data from a number of google sheets and creating an array that gives me the job#, hours, etc. for other employees. — Stephen Schonewolf

James D James D · Accepted Answer · 2020-04-02T14:02:24

I'm not sure of the exact issue but checking for cells that have a value rather than cells that are not null should work for you.

function shoptime(){
      var ss = SpreadsheetApp.getActive().getId();
      var stephensheet = Sheets.Spreadsheets.Values.get(ss, 'Stephen!A2:G');
      var tiffanysheet = Sheets.Spreadsheets.Values.get(ss, 'Tiffany!A2:G');
      var scripts = SpreadsheetApp.getActive().getSheetByName("Scripts");
      var currentDate = new Date();
      var pastweek = new Date();
      pastweek.setDate(currentDate.getDate() -8);

    var array=[];

      for (var a = 0; a < stephensheet.values.length; a++){
      var jobdate = stephensheet.values[a][1];
      var intime = stephensheet.values[a][2];
      var outtime = stephensheet.values[a][3];
      var dailyhours = stephensheet.values[a][4];

      if (new Date(jobdate) > pastweek){
          if (dailyhours){
          array.push(["NA","Office","Stephen",dailyhours,jobdate]);
        }
      }
     }
      for (var a = 0; a < tiffanysheet.values.length; a++){
      var jobdate = tiffanysheet.values[a][1];
      var intime = tiffanysheet.values[a][2];
      var outtime = tiffanysheet.values[a][3];
      var dailyhours = tiffanysheet.values[a][4];

      if (new Date(jobdate) > pastweek){
          if (dailyhours){
          array.push(["NA","Office","Tiffany",dailyhours,jobdate]);
        }
      }
     }
      if(array[0]){
        scripts.getRange(scripts.getLastRow()+1,1,array.length,5).setValues(array);  
        }
      SpreadsheetApp.flush();
    }

And to modify the code to use the built-in service with is much more straight forward in this case;

function shoptime(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var stephensheet =  ss.getSheetByName('Stephen').getDataRange().getValues();
      var tiffanysheet = ss.getSheetByName('Tiffany').getDataRange().getValues();
      var scripts = ss.getSheetByName("Scripts");
      var currentDate = new Date();
      var pastweek = new Date();
      pastweek.setDate(currentDate.getDate() -8);

        var array=[];

          for (var a = 0; a < stephensheet.length; a++){
          var jobdate = stephensheet[a][1];
          var intime = stephensheet[a][2];
          var outtime = stephensheet[a][3];
          var dailyhours = stephensheet[a][4];

          if (new Date(jobdate) > pastweek){
              if (dailyhours != ""){
              array.push(["NA","Office","Stephen",dailyhours,jobdate]);
            }
          }
         }
          for (var a = 0; a < tiffanysheet.length; a++){
          var jobdate = tiffanysheet[a][1];
          var intime = tiffanysheet[a][2];
          var outtime = tiffanysheet[a][3];
          var dailyhours = tiffanysheet[a][4];

          if (new Date(jobdate) > pastweek){
              if (dailyhours != ""){
              array.push(["NA","Office","Tiffany",dailyhours,jobdate]);
            }
          }
         }
         if(array[0]){
           scripts.getRange(scripts.getLastRow()+1,1,array.length,5).setValues(array);  
           }
      SpreadsheetApp.flush();
    }

Finally, if you are scraping data from 6 sheets in the exact same format then a another loop and an array of the sheet names will save on repetition of code

        function shoptime() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheets = ['Stephen', 'Tiffany'] // add the aditional sheet names here
        var scripts = SpreadsheetApp.getActive().getSheetByName("Scripts");
        var currentDate = new Date();
        var pastweek = new Date();
        pastweek.setDate(currentDate.getDate() - 8);

        var array = [];

        for (var i = 0; i < sheets.length; i++) {
            var sheet = ss.getSheetByName(sheets[i]);
            var data = sheet.getDataRange().getValues();

            for (var a = 0; a < data.length; a++) {
                var jobdate = data[a][1];
                var intime = data[a][2];
                var outtime = data[a][3];
                var dailyhours = data[a][4];

                if (new Date(jobdate) > pastweek) {
                    if (dailyhours) {
                        array.push(["NA", "Office", sheets[i], dailyhours, jobdate]);
                    }
                }
            }
        }

        if (array[0]) {
            scripts.getRange(scripts.getLastRow() + 1, 1, array.length, 5).setValues(array);
        }
        SpreadsheetApp.flush();
    }

Google Apps Scripts If function not skipping rows with null value

1 Answers