Hi,
I will be sharing this file with my coworkers to track the time we spent on each task. I found some problems with the script below.
1) If I want to delete a specific row - and try to continue adding data and using the macro to track the time spent on a task, the time is inserted on the row below the deleted row - and not actually on the actual "new" row.
2) Every time that I click on the Clear Button (btn_clear) it clears ALL the data. - I added a similar function "btn_clearRow()" to delete the current row - but something is missing and I can't get it to work.
FYI I have headers, and the data (task name, task description - start time, end time, etc...) the data - specifically the start time/end time - will be recorded on Column F&G - Row 8.
How should I update the script to fix the 2 problems mentioned above?
Could a function be also added so that it requires the person to select a task (column D) before using the automated time tracker?
Thank you!
SCRIPT:
I will be sharing this file with my coworkers to track the time we spent on each task. I found some problems with the script below.
1) If I want to delete a specific row - and try to continue adding data and using the macro to track the time spent on a task, the time is inserted on the row below the deleted row - and not actually on the actual "new" row.
2) Every time that I click on the Clear Button (btn_clear) it clears ALL the data. - I added a similar function "btn_clearRow()" to delete the current row - but something is missing and I can't get it to work.
FYI I have headers, and the data (task name, task description - start time, end time, etc...) the data - specifically the start time/end time - will be recorded on Column F&G - Row 8.
How should I update the script to fix the 2 problems mentioned above?
Could a function be also added so that it requires the person to select a task (column D) before using the automated time tracker?
Thank you!
SCRIPT:
Rich (BB code):
function btn_clear() {
var spreadsheet = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();
var userChoice = ui.alert('STOP! Are you sure you want to clear all DATA?', ui.ButtonSet.OK_CANCEL);
if (userChoice == ui.Button.OK) {
spreadsheet.getRange('AA1').setValue(8);
spreadsheet.getRange('F8:H' + spreadsheet.getDataRange().getNumRows() +1 ).clearContent();
}
};
function btn_clearROW() {
var spreadsheet = SpreadsheetApp.getActive();
var ui = SpreadsheetApp.getUi();
var userChoice = ui.alert('STOP! DELETE CURRENT ROW??', ui.ButtonSet.OK_CANCEL);
if (userChoice == ui.Button.OK) {
spreadsheet.getRange('CurrentRow').setValue(0);
spreadsheet.getRange('CurrentRow' + spreadsheet.getDataRange().getNumRows() +1 ).clearContent();
}
};
//THIS BUTTON IS PRESSED WHEN WE START THE PROCESS
function btn_start() {
var spreadsheet = SpreadsheetApp.getActive();
if (spreadsheet.getRange('AA1').getValue() == '' ) {
SpreadsheetApp.getUi().alert('Click on Clear Button First');
}
else {
var CurrentRow = spreadsheet.getRange('AA1').getValue();
spreadsheet.getRange('F' + CurrentRow).setValue(new Date());
spreadsheet.getRange('F' + CurrentRow).setNumberFormat('h:mm:ss');
}
};
//THIS BUTTON IS PRESSED WHEN WE END THE PROCESS
function btn_stop() {
var spreadsheet = SpreadsheetApp.getActive();
var CurrentRow = spreadsheet.getRange('AA1').getValue();
if (CurrentRow == '' ) {
SpreadsheetApp.getUi().alert('Click on Clear Button First');
}
else if ( spreadsheet.getRange('F' + CurrentRow).getValue() == '' ){
SpreadsheetApp.getUi().alert('Click on Start Button first');
}
else{
if ( spreadsheet.getRange('F' + CurrentRow).getValue() != '' ) {
spreadsheet.getRange('G' + CurrentRow).setValue(new Date());
spreadsheet.getRange('G' + CurrentRow).setNumberFormat('h:mm:ss');
spreadsheet.getRange('H' + CurrentRow).setValue('=text(G' + CurrentRow + '-F' + CurrentRow + ', "hh:mm:ss")');
spreadsheet.getRange('AA1').setValue(CurrentRow + 1 ) ;
}
}
};
//THIS BUTTON IS USED TO RECORD ENDTIME AND START TIME IN CONTIUATION
function btn_split() {
//first we stop the process
var spreadsheet = SpreadsheetApp.getActive();
var CurrentRow = spreadsheet.getRange('AA1').getValue();
if (CurrentRow == '' ) {
SpreadsheetApp.getUi().alert('Click on Clear Button First');
}
else if ( spreadsheet.getRange('F' + CurrentRow).getValue() == '' ){
SpreadsheetApp.getUi().alert('Click on Start Button first');
}
else{
spreadsheet.getRange('G' + CurrentRow).setValue(new Date());
spreadsheet.getRange('G' + CurrentRow).setNumberFormat('h:mm:ss');
spreadsheet.getRange('H' + CurrentRow).setValue('=text(C' + CurrentRow + '-B' + CurrentRow + ', "hh:mm:ss")');
CurrentRow ++;
spreadsheet.getRange('AA1').setValue(CurrentRow) ;
//Start Time
spreadsheet.getRange('F' + CurrentRow).setValue(new Date());
spreadsheet.getRange('F' + CurrentRow).setNumberFormat('h:mm:ss');
}
};
function UntitledMacro() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('F8').activate();
};