Google表格时间跟踪器:更新#1 [英] Google Sheets Time Tracker: Update #1
问题描述
我正在尝试根据视频此处创建时间表.但是，在视频中，他对其进行了设置，以便为两个用户创建，而我的仅用于一个用户.
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.
当用户按下按钮(确实是图形)进行计时时，我希望将时间记录在单元格B2中，并将状态(输入)记录在E2中.同样，当用户按下按钮(另一幅图)以进行计时时，我希望将时间记录在C2中，而将状态(Out)仍然记录在E2中. D列将说明经过的时间长度，该时间长度将用于确定每周工资.
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.
在以下脚本中，为"Clock In"按钮分配了"In"功能，为"Clock Out"按钮分配了"Out"功能:
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());
}
我需要做些什么才能使此代码生效，以便每次用户按下"Clock In"时，它都顺序记录在B列中，从B2开始.从C2开始的C列中的Clock Out一样吗?
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?
这是我正在处理的工作表的图片: 在Imgur上查看.
Here is a picture of the sheet I'm working with: View it on Imgur.
推荐答案
这就是我要做的方式.
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';
}
}
这是屏幕上的样子:
此视图显示菜单
我没有使用图像构建伪按钮，而是使用了侧边栏并使用了标准的
<input type="button" value="Punch Out" onClick="punchOut();" />.这很容易扩展到其他功能.您需要在此处.您还需要运行一次onOpen，以使用补充工具栏功能更新菜单.
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.
这将在新日期时间后加上punchIn和punchOut，并添加一个日期时间，并计算最后一行的差值.
This will append a new datetime with punchIn and punchOut with add a Datetime and calculate difference on the last row.
这篇关于Google表格时间跟踪器:更新#1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
