Starter and Leaver notification from GSheet

# The purpose of this Python script is to pull the forecast of starters and leavers from form responses and send a summary forecast for the starters/leavers in the next 2 weeks
# Include scopes for GMail API
# Any data you need to customise yourself has been placeholdered with <replace>

import gspread
import datetime
import ezgmail
gc = gspread.service_account(filename='service_account.json')

# Function for getting spreadsheet data and returning filtered lists of leavers and starters

def makelists():
    try:
        print("Make call to form response spreadsheets to pull starter/leaver data")
        
        ssOnboarding = gc.open_by_url('<replace>').sheet1
        ssOffboarding = gc.open_by_url('<replace>').sheet1
        
        print("Pull starter/leaver information as list of dictionaries")
        
        listOfStarters = ssOnboarding.get_all_records()
        listOfLeavers = ssOffboarding.get_all_records()
      
        print("Define date boundaries")
        today = datetime.datetime.today()
        twoWeeks = datetime.timedelta(days=14)
        endOfForecast = today + twoWeeks
        
        print("Find and return the starters with start dates that fall on this week or next week")
        forecastStarters = []
        i = 0
        while i < len(listOfStarters):
            startDateString = listOfStarters[i]['What is your new report\'s start date?']
            startDate = datetime.datetime.strptime(startDateString,"%d/%m/%Y")
            if startDate>=today and startDate<=endOfForecast:
                forecastStarters.append(listOfStarters[i])
            i+=1
        print("Find and return the leavers whose official leave date is this week or next week")
        forecastLeavers = []
        i = 0
        while i < len(listOfLeavers):
            leaveDateString = listOfLeavers[i]["When is the leaver\'s official leave date?"]
            leaveDate = datetime.datetime.strptime(leaveDateString,"%d/%m/%Y")
            if leaveDate>=today and leaveDate<=endOfForecast:
                forecastLeavers.append(listOfLeavers[i])
            i+=1
        print("Return the lists of starters and leavers as lists of dictionaries")
        
        return forecastStarters, forecastLeavers
    except:
        listOfStarters = "No starters came up"
        listOfLeavers = "No leavers came up"
        return listOfStarters, listOfLeavers

def emailBody():
    print("Convert list of dictionaries into lists of string format")
    listOfStartersDict, listOfLeaversDict = makelists()
    i = 0
   
    listOfStartersStrings = []
    while i < len(listOfStartersDict):
        starterString= "".join(listOfStartersDict[i]["What is your new report's name?"])+", "
        listOfStartersStrings.append(starterString)
        i+=1
    
    
    i = 0
    
    listOfLeaversStrings=[]
    while i < len(listOfLeaversDict):
        leaverString= "".join(listOfLeaversDict[i]["What is the name of the leaver?"])+", "
        listOfLeaversStrings.append(leaverString)
        i+=1
    
    
    stringOfStarters= "\n".join(listOfStartersStrings)
    startersText ="Upcoming starters: \n"+stringOfStarters

    if len(startersText)==0:
        startersText="No upcoming leavers"

    stringOfLeavers = "\n".join(listOfLeaversStrings)
    leaversText="Upcoming leavers: \n"+stringOfLeavers

    if len(leaversText)==0:
        leaversText="No upcoming leavers"

    emailBodyText=startersText+"\n \n"+leaversText
    return emailBodyText


# Send the results of the previous two functions as a summary in an email
def main():
    message = emailBody()
    EMAIL_TO = '<replace>'
    EMAIL_SUBJECT = 'People Ops Forecast: This week and next week'
    EMAIL_CONTENT = message
    ezgmail.send(EMAIL_TO, EMAIL_SUBJECT, EMAIL_CONTENT)
    
if __name__ == "__main__":
    main()