contacts frequently change roles within NASA and in relation to SNWG projects and need updated information. Group access contact lists need to remain as up to date as possible.
Deliverable
[ ] changing contact info in Google contacts updates all applicable sheets
[ ] Google Contacts tags sorts contacts into appropriate sheet
[ ] button successfully pushes updates to a Google contacts file
Implementation Considerations
Google Contacts: Name, email, Labels; Labels should equate project or thematic area
Script to Update Sheet from Contact Card
Method 1
trigger on Contact update
Contact List Sheet: Tab for each Label
adding contact to a project
check label; find tab with that label
check rows in the name column tab for name
if name not found, add name, email, job title, department
if name found, check name, email, job title, department; add missing elements or change differing elements
repeat for each label on the Contact Card
remove contact from a project
check tab, starting with the first one that is not a label
check rows in the name column tab for name
if name not found, move to the next tab, skipping tabs that have labels on the contact card
if name found, check the notes column. If a note with "removed {Date}" exists, move to the next tab
if name found, check the notes column. If a note with "removed {Date}" does NOT exist, strike-through the name, email, job title, department cells; add a note "removed {Date}" in specified column
thoughts:
should one person be the designated Contact Card person? Multiple inputs may degraded the quality/accuracy of the list.
If each person pushes the list to their inbox though, then all of the cards will be accurate.
Script to Update Sheet from Contact Card
Method 2
trigger on Contact update
Contact List Sheet: COLUMN for each Label
check rows in the name column tab for name
if name not found, add name, email, job title, department, and check the column matching the Contact label
if name found, check name, email, job title, department, and labels; add missing elements or change differing elements
repeat for each label on the Contact Card; if a change has been made, access Sheet 2 "Change Log"
add date of change in column 1, name of changed person in column 2, cell changed in column 3, description of change {text updated to {{text}}" in column 4
Script to update Contact Card from the Sheet
manual trigger by button in the Sheet;
button asks for email input (limitation: must be gmail; user must authorize script)
script searches Google Contacts for the for the first name on the list.
compares name, email address, labels
change fields as needed
Thoughts:
If Method 1, will need to track updated names so script isn't continuously reupdating names that appear on multiple sheets
should add a custom field to complete what has been changed or is existing "history" log enough?
is the push button even necessary? If the sheet is formatted properly, they can save as csv and import manually.
Description
contacts frequently change roles within NASA and in relation to SNWG projects and need updated information. Group access contact lists need to remain as up to date as possible.
Deliverable
Implementation Considerations
Google Contacts: Name, email, Labels; Labels should equate project or thematic area
Script to Update Sheet from Contact Card
Method 1
remove contact from a project
check tab, starting with the first one that is not a label
check rows in the name column tab for name
if name not found, move to the next tab, skipping tabs that have labels on the contact card
if name found, check the notes column. If a note with "removed {Date}" exists, move to the next tab
if name found, check the notes column. If a note with "removed {Date}" does NOT exist, strike-through the name, email, job title, department cells; add a note "removed {Date}" in specified column
thoughts:
should one person be the designated Contact Card person? Multiple inputs may degraded the quality/accuracy of the list.
If each person pushes the list to their inbox though, then all of the cards will be accurate.
Script to Update Sheet from Contact Card
Method 2
Script to update Contact Card from the Sheet
Thoughts:
Related Files
Implementation Contact Lists