How We Saved 8 Hours Weekly for a Marketing Agency with Automated Asset Management

How We Saved 8 Hours Weekly for a Marketing Agency with Automated Asset Management

Marketing agencies waste hours hunting for files. We built a Google Workspace automation that organizes 10,000+ assets across 20+ clients, tracks versions, and automates approvals—cutting weekly search time by 8 hours.

The Real Cost of Chaos

A mid-sized agency’s team lost 6-8 hours weekly searching for correct file versions. Outdated assets caused 1-2 client errors per week. Approvals dragged on for 2-3 days via email chains. New hires spent 3+ hours deciphering disorganized folders. Concurrent edits led to overwrites. During peak campaigns, they hit Drive’s version limits—losing audit trails.

The Solution: Auto-File, Track, Approve

We built “The Automated Asset Engine” using Google Apps Script and Drive API v3. A Google Form captures uploads. The script auto-creates folder paths (Clients > [Client] > [Project] > [Asset Type]), logs versions in Sheets, and emails clients for approval. Reply “APPROVED,” and the file auto-shares with the team. Runs serverless in Google Workspace—no infrastructure needed.

How It Works

Each step includes retry logic and quota checks to stay within Google’s limits.

Implementation Guide

Setup Requirements

OAuth Scopes (add in Apps Script):

• https://www.googleapis.com/auth/drive
• https://www.googleapis.com/auth/spreadsheets
• https://www.googleapis.com/auth/gmail.send
• https://www.googleapis.com/auth/script.external_request

Enable Advanced Drive Service: In Apps Script, go to Services > Add a service > Google Drive API (v3). Required for Drive.Files methods.

Permissions: Admin authorization needed. Executing account must have Editor access to target Drive folder and linked Sheet.

Configuration: Store root folder ID and client email in Script Properties:

PropertiesService.getScriptProperties()
  .setProperty('ROOT_FOLDER_ID', 'your-drive-folder-id')
  .setProperty('CLIENT_EMAIL', 'client-approval@example.com');

Core Script

Processes form submissions, builds folder hierarchy, logs versions, and triggers approvals—using batch operations and exponential backoff to avoid quota errors.

// Pseudo code for the application 
FUNCTION onFormSubmit(event)
  START Timer
  TRY
    GET Responses [Client, Project, AssetFile]
    IF Missing Data THEN THROW Error
    
    GET Root Folder ID
    
    // Structure: Root > Client > Project
    GET OR CREATE Client Folder
    GET OR CREATE Project Folder
    
    // File Handling
    EXTRACT FileID from URL
    MOVE File to Project Folder
    
    // Logging
    GET 'VersionLog' Sheet
    APPEND Row [Date, Client, Project, FileID, 'Pending Approval', User]
    
    // Approval
    SEND Email to Client
      SUBJECT: "Approval Required: {Project}"
      BODY: "View Asset: {Link}. Reply APPROVED or REVISIONS."
      
    IF Time > Limit THEN WARN "Near Timeout"
    
  CATCH Error
    LOG Error
    NOTIFY Admin
    THROW Error
END FUNCTION
 
FUNCTION getOrCreateFolder(parentID, name)
  ATTEMPT 3 Times (Exponential Backoff):
    SEARCH for Folder by Name in Parent
    IF Found THEN RETURN ID
    
    CREATE Folder
    RETURN New ID
  
  IF Failed THEN THROW Error
END FUNCTION
 
FUNCTION moveFileToFolder(fileID, targetID)
  GET File Parents
  REMOVE Old Parents
  ADD Target Parent
END FUNCTION

Step-by-Step Setup

1. Create intake form: Google Form with fields: Client Name (dropdown), Project Title (text), Asset Type (dropdown), Description (paragraph), File Upload (required). Enable email collection.

2. Link to Sheets: In Form Responses, link to a new spreadsheet named "Asset Library Log." Add a second sheet "VersionLog" with headers: Timestamp, Client, Project, File ID, Status, Uploader.

3. Open Apps Script: In Sheet, go to Extensions > Apps Script. Replace default code with the script above. Save as "Asset Library Engine."

4. Enable Drive API: In Apps Script, click Services (+), add "Drive API" v3. Accept OAuth scopes.

5. Set triggers: Add onFormSubmit trigger: event source "From form," type "On form submit." Add time-driven trigger for processEmailResponses every 15 minutes.

6. Add approval scanner: Use this function to detect replies and update status:

// Pseudo code for the application 
FUNCTION processEmailResponses
  GET Gmail Threads (Subject: "Approval Required", Body: "APPROVED" or "REVISIONS")
  GET 'VersionLog' Sheet Data
  
  FOR EACH Thread:
    EXTRACT Project Name, Status (Approved/Revisions)
    FIND Matching Row in Sheet
    IF Found AND Status is Pending:
      UPDATE Status in Sheet
      
  LOG "Processed Responses"
END FUNCTION

7. Test: Submit a dummy form entry. Confirm file appears in correct folder, VersionLog updates, and approval email sends. Check Apps Script logs for errors.

Common Fixes

• Blank version history: Caused by direct Drive overwrites. Always use the Form or script. Re-upload if corrupted Can't Load Version History.
• Script timeout: Break large jobs into chunks. Use time-based triggers to resume. 6-minute limit applies per run Google Apps Script limits.
• Missed approvals: Standardize subject lines. Use a dedicated Gmail label. Consumer accounts have 20,000 daily URL fetch calls; Workspace plans allow 100,000 Quota details.

Error Handler (Optional)

// Pseudo code for the application 
FUNCTION withErrorHandling(functionToRun)
  START Timer
  TRY
    EXECUTE functionToRun
    LOG "Success"
  CATCH Error
    LOG "Failed: {Error}"
    THROW Error
END FUNCTION

Deploy Yours in Days—Not Weeks

Need this for your team? Book a 30-minute call. We’ll customize the folder structure, approval flow, and notifications to match your workflow—and deploy it by Friday. Every project includes our 30-Day Code Warranty: if it breaks, we fix it free. Most agencies recover the cost in under two weeks from time saved.