Last active
February 6, 2023 00:18
-
-
Save Xhynk/ce44cc528c6e0fec92f33c5f215c3f68 to your computer and use it in GitHub Desktop.
A simple Google Apps Script to submit a Google Sheets powered timesheet: https://docs.google.com/spreadsheets/d/1D-YWjt_Zl8WrAuicmUKZsMzSpEOwv1vYL06dBdObyb4/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*-----------------------------------*\ | |
Written with 💖 by @Xhynk | |
Donate: | |
https://xhynk.com/#donate | |
\*-----------------------------------*/ | |
// Example Google Sheets Timesheet: https://docs.google.com/spreadsheets/d/1D-YWjt_Zl8WrAuicmUKZsMzSpEOwv1vYL06dBdObyb4/ | |
function submitTimesheet(e){ | |
var RECIPIENT_EMAIL_ADDRESS = 'SOMEBODYS_EMAIL_GOES_HERE'; // CHANGE ME - Where should this timesheet get sent? | |
var error = false, | |
SS = SpreadsheetApp.getActiveSheet(), | |
ui = SpreadsheetApp.getUi(), | |
date = SS.getRange("B5"), | |
name = SS.getRange("C2").getValue().split(' '), | |
hours = SS.getRange("Q5:R11").getValues(), | |
start = date.getValue(), | |
end = SS.getRange("B11").getValue(), | |
_start = Utilities.formatDate(new Date(start), 'GMT', 'MMM. dd'), | |
_end = Utilities.formatDate(new Date(end), 'GMT', 'dd, YYYY'); | |
// Make sure email has been changed! | |
if( RECIPIENT_EMAIL_ADDRESS == 'SOMEBODYS_EMAIL_GOES_HERE' ){ | |
ui.alert( 'Please change the Email Address (line 2 of the submitTimesheet() function)' ); | |
return; | |
} | |
// Rough validation of the email they changed, lol | |
if( !validateEmail(RECIPIENT_EMAIL_ADDRESS) ){ | |
ui.alert( 'Invalid Email Address' ); | |
return; | |
} | |
// Make sure all hours are actually set | |
if( !isComplete(hours) ){ | |
ui.alert( 'Your timesheet appears to be unfinished. Please make sure all start times have a corresponding end time!' ); | |
return; | |
} | |
var response = ui.alert('Submit Timesheet', 'Hi '+ name[0] +',\nAre you sure you want to submit the timesheet for ' + _start +'-'+ _end +'?', ui.ButtonSet.YES_NO); | |
if( response != ui.Button.YES ){ | |
ui.alert( 'Submission has been halted. Please finish your timesheet and click the Submit Timesheet button again when you\'re ready.' ); | |
return; | |
} | |
// Send PDF via Email | |
var result = sendSpreadsheetToPdf(0, SS.getName(), RECIPIENT_EMAIL_ADDRESS, name[0] + '\'s Timesheet: '+ _start +'-'+ _end, "Please find the attached timesheet:"); | |
if( result == true ){ | |
// Clear Content | |
SS.getRange("E5:P11").clearContent(); // Wipe Current Times | |
SS.getRange("S5:S11").clearContent(); // Clear Notes | |
SS.getRange("U5:U11").clearContent(); // Clear Holiday | |
// Update the Starting Date | |
var d = new Date(start); | |
date.setValue( new Date(d.setDate(d.getDate()+7)) ); | |
/** | |
* Modify Forecast calendar | |
* | |
* Move next week to current, postproximate to next and | |
* clear postproximate | |
*/ | |
// IN OFFICE | |
SS.getRange("D5").setValue( SS.getRange("E15").getValue() ); // Mon | |
SS.getRange("D6").setValue( SS.getRange("G15").getValue() ); // Tue | |
SS.getRange("D7").setValue( SS.getRange("I15").getValue() ); // Wed | |
SS.getRange("D8").setValue( SS.getRange("K15").getValue() ); // Thu | |
SS.getRange("D9").setValue( SS.getRange("M15").getValue() ); // Fri | |
SS.getRange("D10").setValue( SS.getRange("O15").getValue() ); // Sat | |
SS.getRange("D11").setValue( SS.getRange("Q15").getValue() ); // Sun | |
// PTO | |
SS.getRange("T5").setValue( SS.getRange("F15").getValue() ); // Mon | |
SS.getRange("T6").setValue( SS.getRange("H15").getValue() ); // Tue | |
SS.getRange("T7").setValue( SS.getRange("J15").getValue() ); // Wed | |
SS.getRange("T8").setValue( SS.getRange("L15").getValue() ); // Thu | |
SS.getRange("T9").setValue( SS.getRange("N15").getValue() ); // Fri | |
// POSTPROXIMATE WEEK TO NEXT WEEK | |
SS.getRange("E15:Q15").setValues( SS.getRange("E16:Q16").getValues() ); | |
// CLEAR POSTPROXIMATE WEEK | |
SS.getRange("E16:Q16").clearContent(); | |
ui.alert( 'Thanks '+ name[0] +'! You have successfully submitted your timesheet for '+ _start +'-'+ _end +'!' ); | |
} else { | |
ui.alert( 'An error occurred when trying to send your timesheet.' ); | |
} | |
} | |
function sendSpreadsheetToPdf(sheetNumber, pdfName, email, subject, htmlbody){ | |
var SS = SpreadsheetApp.getActiveSpreadsheet(), | |
ui = SpreadsheetApp.getUi(), | |
sheetId = SS.getSheetId(), | |
url_base = SS.getUrl().replace(/edit$/,''); | |
var url_ext = 'export?exportFormat=pdf&format=pdf' //export as pdf | |
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) | |
+ '&size=A4' // paper size | |
+ '&portrait=false' // orientation, false for landscape | |
+ '&fitw=true' // fit to width, false for actual size | |
+ '&gridlines=false' // hide gridlines | |
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page | |
+ '&sheetnames=true'; // show sheet names | |
+ '&printtitle=false'; // hide print title | |
+ '&pagenumbers=true'; // Hide page numbers | |
var options = { | |
headers: { | |
'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(), | |
} | |
} | |
var response = UrlFetchApp.fetch( url_base + url_ext, options ); | |
var blob = response.getBlob().setName(pdfName + '.pdf'); | |
if( email && validateEmail(email) ){ | |
var mailOptions = { | |
htmlBody: htmlbody, | |
attachments: blob | |
} | |
MailApp.sendEmail( | |
email, | |
subject, | |
"html content only", | |
mailOptions | |
); | |
return true; | |
} else { | |
return false; | |
} | |
} | |
// "Total Hours" Formula: =if(isodd(countblank(E5,G5,I5,K5,M5,O5)),"…⏳…",sum(G5-E5,K5-I5,O5-M5,U5*K22)) | |
function isComplete( checks ){ | |
var re = /\d*:\d*/; | |
for( var i = 0; i < checks.length; i++ ){ | |
if( checks[i].indexOf('…⏳…') > -1 || !re.test(checks[i]) ){ | |
return false; | |
} | |
} | |
return true; | |
} | |
// string@string.string - No *true* front end validation for this | |
function validateEmail( email ){ | |
var re = /\S+@\S+\.\S+/; | |
return re.test(email); | |
} | |
function isEven( value ){ | |
if( isNaN(value) ) | |
return false; | |
return ( value % 2 == 0 ) ? true : false; | |
} | |
function isOdd( value ){ | |
if( isNaN(value) ) | |
return false; | |
return ( value % 2 != 0 ) ? true : false; | |
} | |
function onEdit(e){ | |
// Prevent "PTO and In-Office" from being selected together. | |
var SS = SpreadsheetApp.getActiveSheet(), | |
forecast = SS.getRange('forecast'), | |
range = e.range, | |
row = range.getRow(), | |
col = range.getColumn(), | |
value = range.getValue(); | |
// Are we in the Forecast range? | |
if( col < forecast.getColumn() || col > forecast.getLastColumn() || row < forecast.getRow() || row > forecast.getLastRow() ) | |
return; | |
// What's the comparison? Odd cells compare to Next, Even to Previous | |
var compareTo = ( isEven(col) ) ? col - 1 : col + 1, | |
compareCell = SS.getRange(row, compareTo, 1, 1); | |
// If this value is TRUE, set it's comparison cell to FALSE, | |
if( value == true ) | |
compareCell.setValue( false ); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment