Trying to create auto-calculation for hours of in and out script for google sheet

Question

I created a script for my team in google sheets where they can click a button to clock in and another button to clock out.

However, I've been trying to create a script to auto-calculate the amount of hours they worked in that time frame and I'm not able to build it.

I inserted a drawing of a few blocks that says Bianca, In and Out. I assigned a script to each block for the script below.

This is the portion that is not working.

function onPunchOut (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var finish = sheet.getRange("E"+lastRow).getValue();
  var hoursWorked = sheet.getRange("F"+lastRow);
}

if (finish === "Out") {
  hoursWorked.setFormulaR1C1('=R[0]C[-2]-R[-1]C[-2]');
  hoursWorked.setNumberFormat("H:mm");
}

Current code that works for clocking in and out:

function setValue(cellName, value) {
 SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).setValue(value);
}

function getValue(cellName){
  return SpreadsheetApp.getActiveSpreadsheet().getRange(cellName).getValue();
}

function getNextRow() {
  return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1;
}

function setBianca() {
 setValue('L1' , 'Bianca');
}

function addRecord(a, b, c){
  var row = getNextRow();
  setValue('A' + row, a);
  setValue('D' + row, b);
  setValue('E' + row, c);
}

function punchInBianca(){
  addRecord(getValue('L1'), new Date(), 'In');
}

function punchOutBianca(){
  addRecord(getValue('L1'), new Date(), 'Out');
}

Expected results:

Name           Date/Time         In/Out   **Hours worked**
Bianca          4/25/2019 7:59:34   In     **3.15**
Bianca          4/25/2019 11:15:20  Out    **0.29**
Bianca          4/25/2019 11:44:44  In     **4.55**

Actual results: get script errors trying to get hours worked. Says cannot find variable "finish"

score 0 · Answer 1 · answered Apr 27 '19 at 03:26

Your problem is that (as you know) finish is not defined. This is because the variable is local inside of the function onPunchOut(), and your if statement can not directly gain access to the variable. Variables have two different kinds of scope; you can learn more about them here. To solve your problem, move the if statement inside of the closing bracket of onPunchOut().

score 0 · Answer 2 · answered Apr 29 '19 at 16:03

That worked. Thanks!

function onPunchOut (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var finish = sheet.getRange("E"+lastRow).getValue();
  var hoursWorked = sheet.getRange("F"+lastRow);
  if (finish === "Out") {
  hoursWorked.setFormulaR1C1('=R[0]C[-2]-R[-1]C[-2]');
  hoursWorked.setNumberFormat("H:mm");
  }
}

Trying to create auto-calculation for hours of in and out script for google sheet

2 Answers2