Programmatic Google Docs share

[Gabriel Clifton]

Here is the latest one I am pondering over. We have "guest" WIFI SSID that requires 802.1x authentication, username and password. The password for this SSID changes daily and I have a Google Doc that has that day's password and it is in a Shared drive that only members of a Google Group can access the Doc. Now, this SSID is for special visitors like guest speakers to our school district, sports coaches when they are here for a game, and our local radio station so that they can broadcast the game live.

Here is my thinking on what I am hoping to do with your help. I would like to add on to my Powershell script that changes the password and uploads it to our Doc. I would like for us to get the email addresses of any guest coming the next day and enter them into a Google Sheet, say all in column A. Then use GAM to read those email addresses and add them to be a read only share for that Doc. I would also like for the script to utilize GAM to delete all shared email addresses that are not part of our district shared group at the end of the day. I'm in Texas so Friday night football is king so on Thursday we gather all of the visiting coaches email addresses and enter into the Sheet. That Sheet gets shared with them and I would like to ensure they get the notification that the file has been shared with them. Saturday, all rights have been stripped from them.

The password changes daily early in the morning. Run Friday morning removing all share permissions from Thursday, read email addresses entered in the Sheet on Thursday, share the Sheet with them, email them the link to the Sheet, and clear all email addresses in the Sheet ready for the next day. Any help on planning this out from A - Z would be greatly appreciated.

[Ross Scroggs]

Gabriel,

Here's a start, you'll have to translate to PowerShell and customize the settings

I'm assuming a Google sheet with a sheet name Email with a column header email on column A.

export Owner="us...@domain.com"

export FileID="12345abcde"

export SheetName="Email"

export EmailHeader="email"

export VisitorACLsCSV="./VisitorACLs.csv"

export DeleteACLsLog="./DeleteACLS.txt"

export AddACLsLog="./AddACLS.txt"

export Message="Here is the link today's visitor information."

export ClearEmailSheetLog="./ClearEmailSheet.txt"

# Get the current ACLs

gam redirect csv $VisitorACLsCSV user $Owner print drivefileacls $FileID oneitemperrow

# Delete all ACLs with role reader

gam config csv_input_row_filter "permission.role:regex:reader" redirect stdout $DeleteACLsLog multiprocess redirect stderr stdout csv $VisitorACLsCSV gam user $Owner delete drivefileacl $FileID "~permission.emailAddress"

# Add reader ACLs for email addresses in sheet

gam redirect stdout $AddACLsLog multiprocess redirect stderr stdout csv gsheet $Owner $FileID $SheetName gam user $Owner add drivefileacl $FileID user "~$EmailHeader" role reader sendemail emailmessage "$Message" expiration +1d

# Clear email address column

gam redirect stdout $ClearEmailSheetLog redirect stderr stdout user $Owner clear sheetrange $FileID range "$SheetName!A2:A999"

Ross

--

ross.s...@gmail.com

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/fece4d5b-3def-4aba-9ade-2e628424f74fn%40googlegroups.com.

[Ross Scroggs]

Gabriel,

I reread your reqiest and now see that the data file and email file are different.

 

Here's an update.

Ross

export Owner="tests...@rdschool.org"

export DataFileID="1234abd"

export EmailFileID="abcd1234"

export SheetName="Email"

export EmailHeader="email"

export VisitorACLsCSV="./VisitorACLs.csv"

export DeleteACLsLog="./DeleteACLS.txt"

export AddACLsLog="./AddACLS.txt"

export Message="Here is the link today's visitor information."

export ClearEmailSheetLog="./ClearEmailSheet.txt"

$gam redirect csv $VisitorACLsCSV user $Owner print drivefileacls $DataFileID oneitemperrow

$gam config csv_input_row_filter "permission.role:regex:reader" redirect stdout $DeleteACLsLog multiprocess redirect stderr stdout csv $VisitorACLsCSV gam user $Owner delete drivefileacl $DataFileID "~permission.emailAddress"

$gam redirect stdout $AddACLsLog multiprocess redirect stderr stdout csv gsheet $Owner $EmailFileID $SheetName gam user $Owner add drivefileacl $DataFileID user "~$EmailHeader" role reader sendemail emailmessage "$Message" expiration +1d

$gam redirect stdout $ClearEmailSheetLog redirect stderr stdout user $Owner clear sheetrange $EmailFileID range "$SheetName!A2:A999"

--

ross.s...@gmail.com

[Gabriel Clifton]

Interesting take on it. Let me see if I understand the process.

Line 1 of actual GAM code, get list of all ACL of the Google Doc and write to VisitorACLs.csv

Line 2, delete all ACLs on the Google Doc?

Line 3, grab email addresses in column A of Google Sheet, write them to addACLS.txt, add each email address to read only permission of Doc and email them.

Line 4, erase all email addresses in Google Sheet.

$gam config csv_input_row_filter "permission.role:regex:reader" redirect stdout $DeleteACLsLog multiprocess redirect stderr stdout csv $ AddACLsLog gam user $Owner delete drivefileacl $DataFileID "~permission.emailAddress"

$gam redirect stdout $AddACLsLog multiprocess redirect stderr stdout csv gsheet $Owner $EmailFileID $SheetName gam user $Owner add drivefileacl $DataFileID user "~$EmailHeader" role reader sendemail emailmessage "$Message" expiration +1d

$gam redirect stdout $ClearEmailSheetLog redirect stderr stdout user $Owner clear sheetrange $EmailFileID range "$SheetName!A2:A999"

Why not take the email addresses in the addACLS.txt from prior day since it is not being deleted, yet, and remove ACLs in the Doc in the first place?

Delete or clear the AddACLS.txt from the prior day and then carry on.

When it gets to line 3, I receive this error in the AccACLS.txt "ERROR: User: tests...@rdschool.org , Spreadsheet: 1234abd, Sheet: Email, Data Not Retrieved: A failure in the SSL library occurred (_ssl.c:1129)"

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/6B7908B1-6303-4F86-8AD7-EB2DFF026629%40gmail.com.

--

Gabriel Clifton | Network Administrator

Fort Stockton ISD | Technology Center
gabriel...@fsisd.net | http://www.fsisd.net
Office (432) 336-4055 ext 2

Fax (432) 336-4050
1204 W. Second St., Fort Stockton, TX 79735

CONFIDENTIALITY NOTICE: The contents of this email message and any attachments are intended solely for the addressee(s) and may contain confidential and/or privileged information and may be legally protected from disclosure. If you are not the intended recipient of this message or their agent, or if this message has been addressed to you in error, please immediately alert the sender by reply email and then delete this message and any attachments. If you are not the intended recipient, you are hereby notified that any use, dissemination, copying, or storage of this message or its attachments is strictly prohibited.

"You must always be willing to work without applause."
— Ernest Hemingway

"You just have to find that thing that's special about you that distinguishes you from all the others, and through true talent, hard work, and passion, anything can happen."
— Dr. Dre

[Gabriel Clifton]

Ross, I goofed! All of that hard work we did on this is gone. I either accidently did not save that Powreshell script or I forgot where I put it. I got put onto other projects and when I came to work on it today, I could not find the script so I tried to rebuild it from the last post here but it is not working. I think I will blame my meds on this one because I am a little zombied out right now.

[Ross Scroggs]

Gabriel,

Give me a call.

Ross

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/84f076fa-1d76-40a0-a09d-3de4bc787a35n%40googlegroups.com.

--

Ross Scroggs

ross.s...@gmail.com

[Gabriel Clifton]

Thank you again sir for the help.

Here is what we created just in case someone in the future needs something similar.

$Owner="tests...@rdschool.org"

$DataFileID="1234abd"

$EmailFileID="abcd1234"

$SheetName="Email"

$EmailHeader="Email"

$VisitorACLsCSV="./VisitorACLs.csv"

$DeleteACLsLog="./DeleteACLS.txt"

$AddACLsLog="./AddACLS.txt"

$Message="Here is the link today's visitor information."

$ClearEmailSheetLog="./ClearEmailSheet.txt"

$EmailDownload="./EmailDownload.csv"

gam config csv_output_row_drop_filter "permission.domain:regex:rdschool.org"  redirect csv $VisitorACLsCSV user $Owner print drivefileacls $DataFileID oneitemperrow fields role,domain,emailaddress,id

gam config csv_input_row_filter "permission.role:regex:reader" redirect stdout $DeleteACLsLog multiprocess redirect stderr stdout csv $VisitorACLsCSV gam user $Owner delete drivefileacl $DataFileID "~permission.emailAddress"

gam user $Owner get drivefile id $EmailFileID format csv targetfolder . targetname $EmailDownload overwrite

gam redirect stdout $AddACLsLog multiprocess redirect stderr stdout csv $EmailDownload gam user $Owner add drivefileacl $DataFileID user "~$EmailHeader" role reader sendemail emailmessage "$Message"

gam redirect stdout $ClearEmailSheetLog redirect stderr stdout user $Owner clear sheetrange $EmailFileID range "$SheetName!A2:A999"

This grabs all permissions from the Google Doc and writes all that are a not a member of our domain to a csv vile

Then it removes permissions of those in the csv from the Google Doc

Now we grab all email addresses from the Sheet and write them to a CSV. These files are in a Shared drive which made reading directly have a fit or something.

Next we add all of the email addresses as Reader permission on the Doc and then email each user a link to the file.

Finally we clear out all of the email addresses in the Sheet so that we can start over tomorrow.

Well Ross, wouldn't you know it. As I was going to move the Powershell script to its final resting place, I FOUND THE ORIGINAL!

I guess that is the way it works.

In case you're wondering why I said I blame my meds, that can be true. I take high doses of pain meds and muscle relaxers so that I can function normally at work. When I am hot, like it's hot today, my brain gets a little foggy.