Apps Script: Company Copy

Oct 25, 2021

Since 2013, I have operated in offices where the primary tools for email, word processing, and spreadsheets has been Google Workspace (formerly Google Suite). The ability to quickly share documents, folders, and collaborate was a game changer when compared to the Sharepoint solutions of the past. In addition, folders and files could be shared with others who have Google accounts even if those accounts are not associated to a companies domain. Features like this were invaluable when collaborating with other vendors as it reduced the number of files in email and enabled everyone to work from a single source of truth. Over time though, each firm may start adding files to a folder and you’d end up with a situation like the following:

TypeFolder/ File NameOwner
folderProject DeliverablesJane (XYZ Co)
descriptionOnboardingMargot (XYZ Co)
descriptionStatement of WorkTina (XYZ Co)
table_chartTeam Contact InfoTodd (ABC Inc)
descriptionTeam NotesZeke (ABC Inc)

At first blush, this may seem all well and good. All files appear to be in a central location and are well named. However, the two firms, XYZ Co and ABC Inc, own different portions of the folder and neither are in control of all files. What’s more, ABC Inc may have only been on for a portion of the project and the primary vendor, XYZ Co, needs to retain all files. Transferring ownership of files in Drive is very straightforward but only for owners who reside on the same domain. In this scenario though, the files owned by ABC Inc cannot be transferred to XYZ Co due to limitations put in place by Google.

In order to keep everything together, there are a few options:

  1. Manually download all files or just those owned by ABC Inc to your local machine. Reupload files to the correct directory. Pick through all sub folders, check ownership of each, hope you can keep things organized.
  2. Reach out to your Google Workspace admins, ask them to run Takeout on all files, send you the outputs, and reupload.
  3. Generate copies of files owned by ABC Inc to ensure XYZ Co retains all information. Likewise, the operation could be performed in reverse so as to provide ABC Inc a full set too.

Manually doing anything with files is fraught with challenges, tedium, and potential for missteps. Takeout is a great tool but more than likely, your Google Admins are busy elsewhere. Here is how to address #3…

Create new Apps Script Project. Go to https://script.google.com/home/start and click New Project. Copy contents of Gist to project. Alternatively, if this link is blocked or you are not able to create Projects directly, create a new Google Sheet (in Chrome navigate to sheet.new) and add Gist contents into Script Editor window.

Add values to the COMPANY_DOMAIN and BASE_FOLDER_ID variables. The BASE_FOLDER_ID can be taken from the URL of the Drive Folder. For example a URL of https://drive.google.com/drive/folders/1dFWGjkgZQQKv9WZM84ScORDXMceR8kqM has an ID value of "1dFWGjkgZQQKv9WZM84ScORDXMceR8kqM".

Run with start()

A spreadsheet will be created and titled “fileOwnerConversion” which contains a list of each converted file along with the link to the copy. All new files will be owned by the running user.

Old IDOld Owner EmailNew IDNew Owner Email
gZQQKv…zeke@abcinc.comeIO32b…jane@xyzco.com
wEXKI8…todd@abcinc.comxTH031…jane@xyzco.com

In the Base Folder, files owned by the other company will be prefixed with “DEL - ”. For my purposes, having “DEL” as a prefix made sense. An alternate approach would be to use the other vendor name as the prefix.

TypeFolder/ File NameOwner
folderProject DeliverablesJane (XYZ Co)
descriptionDEL - Team Contact InfoTodd (ABC Inc)
descriptionDEL - Team NotesZeke (ABC Inc)
descriptionOnboardingMargot (XYZ Co)
descriptionStatement of WorkTina (XYZ Co)
table_chartTeam Contact InfoJane (XYZ Co)
descriptionTeam NotesJane (XYZ Co)

The full source code is below and available as a Github Gist.

Tagged