-1

I have issues with a cell in google apps script where if a cell = "0:00" it needs to equal some text like "Unexcused Absence". when I run the code it gives me a blank cell.

function Boh() {

  var sheet = SpreadsheetApp;

  var ss = sheet.getActiveSpreadsheet().getActiveSheet();

  var lastRow = ss.getLastRow();

  var spreadsheet = SpreadsheetApp.getActive();

  var b = 0;

  var wCell = 'Work';

  var x = new Date('3/16/2013 0:00:00');


  for(var i=2;i<=lastRow;i++){ 
  ss.getRange(i,10).activate();

  if(x.setHours(0,0,0,0)  == ss.getRange(i, 17).getValue() && 'Work' == ss.getRange(i, 5).getValues()){

     ss.getRange(i, 10).setValue('Unexcused Absence');
   } 

 }
Rubén
  • 34,714
  • 9
  • 70
  • 166
Yuhe
  • 1
  • 1
  • How are you running this script? Is it supposed to be an onEdit(e) type trigger or run from menu or button? – TheWizEd Nov 20 '18 at 17:42
  • Hi, I'm running this on Macros on GoogleSheets by using the menu. On cell "Q" is all time-frames and cell "E" shows the type of day Work/Off and Cell "J" is where I want to print the answer if true. – Yuhe Nov 20 '18 at 17:50
  • Found the answer: Needed to use `**.getDisplayValues()**` returns the value as you see in the screen, therefore always a string. And now it works, thanks for the help anyway. – Yuhe Nov 22 '18 at 17:09

1 Answers1

0

I don't use recorded macros so not sure how that works. This will add a menu item to the main menu and you can run the script from there. Try this.

function onOpen() {
  var thisMenu = SpreadsheetApp.getUi().createMenu('Boh');
  thisMenu.addItem('Boh', 'Boh').addToUi();
}

function Boh() {
  try {
    var spread = SpreadsheetApp.getActiveSpreadsheet();
    // Be sure you are on the right sheet when you run this
    var sheet = spread.getActiveSheet();
    // Get everything
    var values = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
    // results will be put in column J later
    var results = [];
    // Skip row 1
    for(var i=1; i<values.length; i++ ) {
      //  Assuming ss.getRange(i,17).getValue() is a duration, column 17 or Q becomes values[i][16]
      if( ( Utilities.formatDate(values[i][16],Session.getScriptTimeZone(),"HH:mm:ss") === "00:00:00" ) &&
          ( values[i][4] === "Work" ) ) {
        results.push(["Unexcused Absence"]);
      }
      else {
        // Notice I push an array to make results a 2D array of n rows 1 column
        // values[i][9] is column 10 or J
        results.push([""]);
      }
    }
    // Note we skipped line 1
    sheet.getRange(2,10,results.length,1).setValues(results);
  }
  catch(err) {
    Logger.log(err);
  }
}
TheWizEd
  • 7,517
  • 2
  • 11
  • 19