When someone is hired at my school, our HR system sends tech support an automated message with some basic information (first name, last name, start date, personal email address, job title, supervisor, etc.). Unfortunately, this isn’t formatted the best and I used to copy\/paste the information from this email into an onboarding Google Sheet I used to mange the process.<\/p>\n\n\n\n
I decided to try to improve on this process. I wanted the email to automatically go into my Google Sheet. While I’d love it if our payroll system could pass along the data I need in JSON format via a webhook\/API call, alas it can’t do that. I figured that I could write some App Script to parse the emails and grab the data that way. However, before coding, I always like to check if someone has already solved the problem. CloudHQ has an add-on called “Export Email to Sheets<\/a>” which does exactly what its name suggests. For any email with a given Gmail label (I match on sender and subject) it parses the email and adds the results to a Google Sheet.<\/p>\n\n\n\n As I covered in a previous blog post, I like writing GAM-ADV-XTD3 commands a Google Sheet formulas. I can create an account and send the password to the users personal email with the following Google Sheet formula:<\/p>\n\n\n\n Here is a key to the fields I’m using:<\/p>\n\n\n\n To create an account for me:<\/p>\n\n\n\n Once the account is created there are a few other useful GAM commands:<\/p>\n\n\n\n <\/p>\n","protected":false},"excerpt":{"rendered":" When someone is hired at my school, our HR system sends tech support an automated message with some basic information (first name, last name, start date, personal email address, job title, supervisor, etc.). Unfortunately, this isn’t formatted the best and I used to copy\/paste the information from this email into an onboarding Google Sheet I […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,21],"tags":[],"class_list":["post-197","post","type-post","status-publish","format-standard","hentry","category-gam","category-google-workspace"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/duff.io\/index.php?rest_route=\/wp\/v2\/posts\/197","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/duff.io\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/duff.io\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/duff.io\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/duff.io\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=197"}],"version-history":[{"count":2,"href":"https:\/\/duff.io\/index.php?rest_route=\/wp\/v2\/posts\/197\/revisions"}],"predecessor-version":[{"id":199,"href":"https:\/\/duff.io\/index.php?rest_route=\/wp\/v2\/posts\/197\/revisions\/199"}],"wp:attachment":[{"href":"https:\/\/duff.io\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/duff.io\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/duff.io\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}=\"gam create user \" & (left(I2<\/mark><\/strong>,find(\"@\",I2<\/mark><\/strong><\/strong>)-1)) & \" firstname \"\"\" & A2<\/mark><\/strong> & \"\"\" lastname \"\"\" & B2<\/mark><\/strong> & \"\"\" password random changepassword on notify \" & E2<\/mark><\/strong><\/pre>\n\n\n\n
I2<\/code><\/mark><\/strong><\/td>A field with the new school email address we are creating<\/td><\/tr> (left(I2<\/mark><\/strong>,find(\"@\",I2<\/mark><\/strong><\/strong>)-1))<\/code><\/td>Calculates into the part of their email address before the @ symbol<\/td><\/tr> A2<\/code><\/mark><\/strong><\/td>A field with the first name of the new hire<\/td><\/tr> B2<\/code><\/mark><\/strong><\/td>A field with their last name of the new hire<\/td><\/tr> E2<\/code><\/mark><\/strong><\/td>The personal email address of the new hire<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n gam create user ssimon<\/mark><\/strong> firstname \"Shandor<\/mark><\/strong>\" lastname \"Simon<\/mark><\/strong>\" password random changepassword on notify shandor@mypersonalemaildomain.com<\/mark><\/strong><\/pre>\n\n\n\n
Put the new user<\/mark> in an OU<\/mark><\/h4>\n\n\n\n
gam update user ssimon<\/mark><\/strong> org \"\/Faculty and Staff<\/mark><\/strong>\"<\/pre>\n\n\n\n
Add the user<\/mark> to a group<\/mark><\/h4>\n\n\n\n
gam update group staff@myschooldomain.org<\/mark> add member user ssimon<\/mark><\/strong><\/pre>\n\n\n\n