General Considerations

[mxpx1412]

Some things to consider as you work on this

who is the originator of the invitation? - is it important to see responses / replies etc?
is the originator a "person" - i.e) are they able to make manual changes as needed?
if the originator is not a person - what will this mean for usage? if it is, what will that mean for their inbox?
is there a way to add a column with "email sent" or other verifications so duplication does not happen (i.e. if "email sent" then skip if run more than once)
any way to automatically track and record responses?
do you need to have a row per person or is it possible to create a row per calendar invite (so it remains human readable?)
possible to add date format handling in excel (people, dates, and excel don't always mesh too well)?

More questions

is the manual process (the current process) well understood, are there improvements that could be made that would help the automation?
    can you draw a picture?
    of what currently happens
    of what ideally happens
how many people will need edit access to the excel sheet?
how is the data managed as it goes into the excel sheet? What does the user need to be aware of
    capitalization? list separators? spelling?
if it doesn't work, how will the user know? can they follow a manual process if everything explodes?
how will changes be tracked in the excel sheet / process?
is there anything that shouldn't be edited (locked cells?)
is there anything that acts as a group that should be dynamic or can change?
is there an "undo"? i.e) what needs warnings/confirmations?
what happens when you leave - how can this be maintained/managed?
is there a difference in running the first time vs updates? how is this managed?
does this require a certain version of Excel? (Year, OS)?

[mxpx1412]

who is the originator of the invitation? - is it important to see responses / replies etc?

• The originator will be the LS Email address "[email protected]", replies are not handled nor relevant as of yet. Manual changes are possible and (for finalization) are encouraged for anyone with access to the shared LS Calendar from said Email address. Similar restriction applies to who can use this workbook to automatically draft invites (random people can't pull this workbook and start drafting in our calendar box). This address can be changed later in the code if needed. After the invite is drafted, it is functionally the same as any other manually produced invite in the LS calendar.

[mxpx1412]

is there a way to add a column with "email sent" or other verifications so duplication does not happen (i.e. if "email sent" then skip if run more than once) any way to automatically track and record responses?

• The "CALENDAR INVITE" column in exam controls the behaviour. Updating previous invites are possible, but not enabled in the recommended button which runs the code. Overall, if the Calendar Invite cell for an exam contains "sent" or "saved" or "drafted" (found via regex) then it'll be skipped (or updated if settings are customized to do so). Exams in the past are skipped also (except in debug mode for testing purposes)

[mxpx1412]

do you need to have a row per person or is it possible to create a row per calendar invite (so it remains human readable?)
possible to add date format handling in excel (people, dates, and excel don't always mesh too well)?

• Each course will have a row rather than per person. For courses with the same support session (sometimes two distinct courses are supported by the same crew at the same timeframe) then the "TIER 2" cells for those courses should be merged to indicate only one invite is needed (same mechanism as before automation). All tier 2s go in one tier 2 cell under "TIER 2", all tier 2s go in one tier 2 cell under "TIER 1", people are found from the mail list by searching for their names, so full names should be included in these cells, separated by line breaks or commas. Instructor info are pulled for the invite bodies but not added to the invite by the script.

[mxpx1412]

how many people will need edit access to the excel sheet?

• Those who right now edit the exam google sheet regularly (mainly us Ops) need access. Anyone entrusted with making calendar invites can have access. They will need the "[email protected]" outlook calendar shared with them