Moving Google Drive Docs/Sheets/Forms between workspaces/organizations

Recently, I needed to move a complicated Google Form and an associated Google Sheet that stored the responses to another account. Within an organization, this is pretty easy to do, but moving between workspaces doesn’t appear possible.

There is a workaround by using a Google Shared Drive in the target account and adding the source account to the Google Shared Drive. The source account can then move the files to the Shared Drive and doing so transfers the ownership of the files to the Shared Drive. At that point, the target account can go into the shared drive and move the files to their drive.

Jumping through all of these hoops preserves existing links to the form and avoids the need to relink the responses sheet which can sometimes be problematic.

Onboarding new users with GAM with an assist from CloudHQ

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.

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” 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.

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:

="gam create user " & (left(I2,find("@",I2)-1)) & " firstname """ & A2 & """ lastname """ & B2 & """ password random changepassword on notify " & E2

Here is a key to the fields I’m using:

I2A field with the new school email address we are creating
(left(I2,find("@",I2)-1))Calculates into the part of their email address before the @ symbol
A2A field with the first name of the new hire
B2A field with their last name of the new hire
E2The personal email address of the new hire

To create an account for me:

gam create user ssimon firstname "Shandor" lastname "Simon" password random changepassword on notify shandor@mypersonalemaildomain.com

Once the account is created there are a few other useful GAM commands:

Put the new user in an OU

gam update user ssimon org "/Faculty and Staff"

Add the user to a group

gam update group staff@myschooldomain.org add member user ssimon

GAM, Google Sheets & Calendars

While web interfaces make things easier, it is often much faster to do things on the command line, especially if you are trying to make changes in bulk. 

My school recently added Google Calendars for all our sports teams. One trick to make this easier is to use Google Sheets (or Excel) to convert spreadsheet data into commands I can paste into the terminal window of my Mac. 

I use GAM-ADV-XTD3 (a more advanced version of GAM) that uses the Google Admin APIs to mange my Google Workspace environment from the command line.

In GAM-ADV-XTD3 you can create a calendar in a user account like so:

gam user athletics-calendars create calendar summary "Sailing - Varsity"

In the example athletics-calendars is the username of account I wish to create the account in and Sailing - Varsity is the calendar name.

If I have a Google sheet with a list of sports I need to create a calendars for, I can simply write a formula (like the one below) to generate the commands to create all those calendars.

=" gam user athletics-calendars create calendar summary """ & A1 &""""

This combines the text of the command with the contents of the cell A1 (which is where the sports team’s name is). To quote a quote is a little tricky but the formula above works. I then fill down for all the sports teams and now I have the commands to create all of the calendars. I can highlight all of the cells with these commands (B1:B17 in the example below) and paste them into terminal to run them.

After a few seconds I now have 17 calendars created!

Some other handy e GAM-ADV-XTD3 calendar related commands:

Give a user access/permission to a calendar:

gam calendar xyzzyasfda@group.calendar.google.com add acls writer user@exampledomain.com

xyzzyasfda@group.calendar.google.com is the Calendar ID that you want to give access to user@exampledomain.com. While writer allows someone to edit a calendar, you need to assign owner in order for them to change sharing settings.

Make a calendar public:

gam calendar xyzzyasfda@group.calendar.google.com add acls reader default

If you want to make a calendar public (so that anyone on the can access it) you use default as the username. Using reader provides read-only access.

List all of the calendar in a users account:

gam user sample_user print calendars showhidden todrive

This lists all of the calendars in sample_user's account in a new Google Sheet (todrive). This can be handy to find the Calendar ID of a specific calendar.