Skip to content

Instantly share code, notes, and snippets.

@Xhynk
Last active February 6, 2023 00:18
Show Gist options
  • Save Xhynk/ce44cc528c6e0fec92f33c5f215c3f68 to your computer and use it in GitHub Desktop.
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/
/*-----------------------------------*\
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