Google Sheets Time Tracker: Update #1

2019-09-28 07:47发布

站内问答 / 前端开发
1866 1 4

I am trying to create a time sheet based on the video here. However, in the video, he sets it up so that it is built for two users, whereas mine is only going to be for one user.

When the user presses the button (really a drawing) to clock in, I want the time to be recorded in cell B2 and the status (in) to be recorded in E2. Just the same, when the user presses the button (another drawing) to clock out, I want the time to be recorded in C2 and the status (Out) to still be recorded in E2. Column D will tell the duration of time passed, which will be used to determine weekly pay.

Here's the script where the "Clock In" button is assigned the "In" Function and the "Clock Out" button is assigned the "Out" Function: 

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

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

function getNextRowIn() {
  return SpreadsheetApp.getActiveSpreadsheet().getLastRow() - 6;
}

function getNextRowOut() {
  return SpreadsheetApp.getActiveSpreadsheet().getLastRow() - 6;
}

function addRecordIn(b) {
  var row = getNextRowIn ();
   setValue('B' + row, b);
}

function addRecordOut(c) {
  var row = getNextRowOut ();
   setValue('C' + row, c);
}

function In() {
  setValue('E2', 'In');
  addRecordIn(new Date());
}

function Out() {
  setValue('E2', 'Out');
  addRecordOut(new Date());
}

What do I need to do to make this code work out so that each time a user presses "Clock In" it is recorded sequentially in column B, starting at B2. Same for Clock Out in Column C, starting at C2?

Here is a picture of the sheet I'm working with: View it on Imgur.

1条回答
萌系小妹纸
2楼-- · 2019-09-28 08:11

Here's the way I would do it.

timerecord.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {

      });
      function punchIn(){
        google.script.run.punchIn();
      }
      function punchOut(){
        google.script.run.punchOut();
      }

    console.log('My Code');
    </script>
  </head>
  <body>
   <input type="button" value="Punch In" onClick="punchIn()" />
   <input type="button" value="Punch Out" onClick="punchOut()" />
  </body>
</html>

code.gs:

function onOpen(){
  SpreadsheetApp.getUi().createMenu('My Tools')
     .addItem('Time Clock', 'showTimeTrackerSidebar')
     .addToUi();
}

function punchIn() {
  SpreadsheetApp.getActive().getSheetByName('Time Record').appendRow([Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM dd yyyy HH:mm:ss")])
}

function punchOut(){
  var ss=SpreadsheetApp.getActive()
  var sh=ss.getSheetByName('Time Record');
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  var lr=vA[vA.length-1];
  var outdt=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MMM dd yyyy HH:mm:ss");
  vA[vA.length-1][1]=outdt;
  vA[vA.length-1][2]=calcTimeDifference(vA[vA.length-1][0],outdt);
  rg.setValues(vA);
}

function showTimeTrackerSidebar(){
  var ui=HtmlService.createHtmlOutputFromFile('timerecord');
  SpreadsheetApp.getUi().showSidebar(ui);
}

function calcTimeDifference(Start,End)
{
  if(Start && End)
  {
    var hour=1000*60*60;
    var t1=new Date(Start).valueOf();
    var t2=new Date(End).valueOf();
    var d=t2-t1;
    var hours=d/hour;
    return hours
  }
  else
  {
    return 'Invalid Inputs';
  }
}

This is what the screen looks like:

enter image description here

This view shows the menu

enter image description here

Instead of building psuedo buttons with images I just used the sidebar and used the standard <input type="button" value="Punch Out" onClick="punchOut();" /> This is a lot easier to extend to other features. You'll need to take a look at the google.script.run feature here. You'll also need to run the onOpen once to get the menu updated with the siderbar function.

This will append a new datetime with punchIn and punchOut with add a Datetime and calculate difference on the last row.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)