- Google AppSheet
- Google Apps Script
- Google Drive API
- Google Cloud Secret Manager
- Google Workspace (Domain-Wide Delegation & Admin SDK)
Before you begin, ensure you have the following:
- A Google Cloud project with billing enabled.
- A Google Workspace account with Super Administrator privileges.
- The Drive API and Secret Manager API enabled in your GCP project.
- A dedicated "bot" user (e.g., sd-automator@domain.com) with a Google Workspace license that includes Shared Drives.
- An AppSheet license.
Goal: Create a licensed "bot" user and give it permission to create Shared Drives using the correct, granular service policy.
- In the Admin Console (
admin.google.com
), navigate to Directory > Users > Add a new user. - Create the user (e.g.,
poc-sd-automator@domain.com
). - Assign a License: Ensure this user has a Google Workspace license that includes Shared Drives (e.g., Enterprise Plus).
- Assign to OU: Place this user in the intended Target OU (e.g., OU1).
- In the Admin Console, go to Apps > Google Workspace > Drive and Docs.
- Click on "Sharing settings".
- Find the "Shared Drive creation" card.
- Apply the Policy:
- Method A (OU-based): Select the Target OU and check the box to "Allow users in [Target OU] to create new shared drives".
- Method B (Group-based): For a more granular approach, apply this "Allow" policy to a specific Configuration Group that contains the bot user, as this will take precedence over the OU.
- Click Save.
💡: If you did Method B, you can either reuse that group for auditing purpuses or using this Monitoring Group for the Shared Drive creation policy
- Go to Directory > Groups > Create group.
- Name it
(POC) Automator Monitoring
. - Add
poc-sd-automator@domain.com
as a member (this group is for auditing purposes only).
Goal: Create the Service Account (SA) for DwD, securely store its key in Secret Manager, and grant the developer access to read that secret.
- In the GCP Console (
console.cloud.google.com
), go to IAM & Admin > Service Accounts. - Click + Create Service Account, name it
(poc) appsheet-drive-creator
, and click Create and Continue, then Done.
- Click on the new SA, go to the Keys tab.
- Click Add Key > Create new key > JSON.
- A JSON file will download. Open this file in a text editor.
- In the GCP Console, go to Security > Secret Manager.
- Click + Create Secret. Name it
poc-dwd-key
. - In the Secret value box, paste the entire contents of the JSON file.
- Click Create Secret.
- CRITICAL STEP: Permanently delete the JSON file you downloaded from your computer.
- Go back to IAM & Admin > Service Accounts and click the
(poc) appsheet-drive-creator
SA. - Go to the Details tab and copy the Unique ID (Client ID).
- In the Workspace Admin Console, go to Security > API controls > Manage Domain-Wide Delegation.
- Click Add new.
- Paste the Client ID.
- Add the one scope:
https://www.googleapis.com/auth/drive
- Click Authorize.
- In the GCP Console > IAM & Admin > IAM.
- Find the developer's user account in the "Principal" list (the user who will own the AppSheet app).
- Click the pencil icon ✏️ to edit its roles.
- Click + ADD ANOTHER ROLE and select Secret Manager Secret Accessor.
- Click Save.
Goal: Link the script to GCP and write the code to fetch the secret (as the developer) and then create the drive (as the bot).
- At
script.google.com
, create a New project. - Name it
(POC) Secure Drive Creator Script
. - Click Project Settings ⚙️.
- Scroll down and click Change project.
- Paste your GCP Project Number.
- Click Set project.
- In the GCP Console, go to APIs & Services > OAuth consent screen.
- Select Internal and click Create.
- App name:
(POC) Shared Drive Automator
- User support email: The developer's email.
- Developer contact information: The developer's email.
- Click Save and Continue (skip Scopes), then Back to Dashboard.
- In the Apps Script editor, click Libraries + and add the OAuth2 Library:
1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
- Go to Project Settings ⚙️ > Script Properties.
- Property 1:
- Property:
SECRET_RESOURCE_NAME
- Value:
projects/[YOUR-PROJECT-ID]/secrets/poc-dwd-key/versions/1
- Property:
- Property 2:
- Property:
IMPERSONATED_USER
- Value:
poc-sd-automator@domain.com
- Property:
- Go to Project Settings ⚙️.
- Check the box 'Show "appsscript.json" manifest file in editor'.
- Go back to the Editor 📄 and click the
appsscript.json
file. - Paste in the required manifest JSON. This file must include the
oauthScopes
forhttps://www.googleapis.com/auth/drive
,https://www.googleapis.com/auth/cloud-platform
, andhttps://www.googleapis.com/auth/script.external_request
.
- Go to the Editor 📄 and click
Code.gs
. - Delete everything in the code editor.
- Paste in the complete, final
Code.gs
(the 3-function script, which usesUrlFetchApp
, returns aString
).
- In the editor toolbar, select the
createSharedDrive
function and click Run. - A "Authorization required" pop-up will appear.
- Click Review permissions, select the developer's admin account, and Allow all 3 scopes.
- The script will log "ERROR: Drive name was empty...". This is expected.
- Go to
appsheet.com
> Create > Database > New database. - Name it:
(POC) Shared Drive Requests DB
. - Rename the default table to
Requests
. - Configure the columns:
Timestamp
(Type:DateTime
, Initial value:NOW()
)RequesterEmail
(Type:Email
, Initial value:USEREMAIL()
)RequestedDriveName
(Type:Text
, Required:Yes
)Status
(Type:Text
, Initial value:"Pending"
)DriveID
(Type:Text
)
- Create a new app from the
(POC) Shared Drive Requests DB
.
- Go to the Data tab 💾, select the
Requests
table, and Regenerate schema (🔄). - Verify
Status
isText
.
- Go to the Automation tab 🤖 > Create > New bot.
- Note: Use simple names for your steps (e.g.,
CallScript
), as special characters can cause errors. - Event: Adds only on the
Requests
table. - Process:
- Step 1 (Name:
CallScript
):- Select Call a script > (POC) Secure Drive Creator Script.
- Function Name:
createSharedDrive
. - Arguments:
[RequestedDriveName]
. - Enable the "Return Value" toggle.
- "Return value type":
String
.
- Step 2 (Name:
UpdateRow
):- Select Data: Set row values.
- Table:
Requests
. - Status formula:
IF(STARTSWITH([CallScript].[Output], "ERROR:"), "Failed", "Created")
- DriveID formula:
IF(STARTSWITH([CallScript].[Output], "ERROR:"), "", [CallScript].[Output])
- Step 1 (Name:
- Click Save.
- Run the App: Open the AppSheet app and submit a new request.
- Check the App: Sync the app (🔄). The
Status
for the new row should change from "Pending" to "Created". TheDriveID
column will populate. - Verify Admin Console:
- Proof of Location: Go to Admin Console > Apps > Drive and Docs > Manage Shared Drives. The new drive will be in the Target OU.
- Proof of Creator: The "Creator" will be the bot,
poc-sd-automator@...
. - Proof of Monitoring: Go to Reporting > Audit and investigation and filter by
Group: (POC) Automator Monitoring)
to see the creation log.