Apps Script: Company Copy
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:
Type | Folder/ File Name | Owner |
---|---|---|
Project Deliverables | Jane (XYZ Co) | |
Onboarding | Margot (XYZ Co) | |
Statement of Work | Tina (XYZ Co) | |
Team Contact Info | Todd (ABC Inc) | |
Team Notes | Zeke (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:
- 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.
- Reach out to your Google Workspace admins, ask them to run Takeout on all files, send you the outputs, and reupload.
- 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 ID | Old Owner Email | New ID | New Owner Email |
---|---|---|---|
gZQQKv… | zeke@abcinc.com | eIO32b… | jane@xyzco.com |
wEXKI8… | todd@abcinc.com | xTH031… | 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.
Type | Folder/ File Name | Owner |
---|---|---|
Project Deliverables | Jane (XYZ Co) | |
DEL - Team Contact Info | Todd (ABC Inc) | |
DEL - Team Notes | Zeke (ABC Inc) | |
Onboarding | Margot (XYZ Co) | |
Statement of Work | Tina (XYZ Co) | |
Team Contact Info | Jane (XYZ Co) | |
Team Notes | Jane (XYZ Co) |
The full source code is below and available as a Github Gist.