GSA-TTS / tts-tech-operations

TTS Technology Operations
https://handbook.tts.gsa.gov/tech-operations/
Other
6 stars 0 forks source link

Onboarding Email Automations #1729

Open nateprice18f opened 4 months ago

nateprice18f commented 4 months ago
### Tasks
- [x] Create/update formula to pull data from primary sheet
- [x] Create formula evaluate which email template to use
- [x] Weekly meeting with Ethan and onboarding team.
- [ ] Find out how people work.... Terllo,Github,other
### Tasks
- [x] Create formula for 7 days
- [x] Create formula for 21 days hiring manager
- [x] Create formula for 14 days -
- [x] Create formula for 21 days - on boarding
- [x] Update formula for sending email to onboarding team for missing sup
### Tasks
- [ ] Demo email notification functions
- [ ] Update formulas
- [ ] address ongoing user feedback
nateprice18f commented 2 months ago

Check IF hiring manger first then check IF onboarding buddy is there.

nateprice18f commented 2 months ago

={FILTER({Onboarding_Tracker!A:A,Onboarding_Tracker!B:B,Onboarding_Tracker!AK:AK,Onboarding_Tracker!F:F,Onboarding_Tracker!C:C,Onboarding_Tracker!AM:AM,Onboarding_Tracker!AX:AX,Onboarding_Tracker!BA:BA,Onboarding_Tracker!BC:BC,Onboarding_Tracker!BD:BD,Onboarding_Tracker!AE:AE,Onboarding_Tracker!AR:AR,Onboarding_Tracker!BW:BW},Onboarding_Tracker!F:F - TODAY() <= 21,Onboarding_Tracker!F:F > TODAY(),Onboarding_Tracker!BV:BV = "",Onboarding_Tracker!C:C = "External hire",Onboarding_Tracker!D:D = "Onboarding");

FILTER({Onboarding_Tracker!A:A,LEFT(Onboarding_Tracker!B:B,5),Onboarding_Tracker!AK:AK,Onboarding_Tracker!F:F,Onboarding_Tracker!C:C,Onboarding_Tracker!AM:AM,Onboarding_Tracker!AX:AX,Onboarding_Tracker!BA:BA,Onboarding_Tracker!BC:BC,Onboarding_Tracker!BD:BD,Onboarding_Tracker!AE:AE,Onboarding_Tracker!AR:AR,Onboarding_Tracker!BW:BW},Onboarding_Tracker!F:F - TODAY() <= 21,Onboarding_Tracker!F:F > TODAY(),Onboarding_Tracker!BV:BV = "",Onboarding_Tracker!C:C = "External hire",Onboarding_Tracker!D:D = "Onboarding")}

nateprice18f commented 2 months ago

=If(OR(K2 = "", L2 = ""), 'Email templates'!$A$16, "") =If(OR(I2 = "", J2 = ""), 'Email templates'!$A$15, "") =QUERY(SPLIT(QUERY(SPLIT(K2,",",TRUE,TRUE),"Select Col2")," ",TRUE,TRUE),"Select Col1")