jeudi 2 avril 2020

Google Apps Scripts If function not skipping rows with null value

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();
}

Aucun commentaire:

Enregistrer un commentaire