Skip to main content

baked-google-sheets-sg-sync-firebase

For coordinators sending shots and receiving notes

This is a python flask script hosted on Firebase that accepts HTTPS POST requests from Shotgrid's Action Menu Items. It links with gspread to create and edit google sheets.

Repo: https://github.com/BakedStudios/baked-google-sheets-sg-sync-firebase

Firebase: https://console.firebase.google.com/u/0/project/crafty-willow-453321-f1/overview

Template Sheet is here: https://docs.google.com/spreadsheets/d/12_bVH4YUCNVAD5sxwwOwWZftSG6kx2M18YXzodcdyuc/

Baked Tools App is here: https://workspace.google.com/u/0/marketplace/app/bakedtools/360591990321?flow_type=42

Environment Build​

Create virtual env and install dependencies: cd functions python3.10 -m venv venv . venv/bin/activate python3.10 -m pip install -r requirements.txt

Essential Commands​

Deploy to firebase: firebase deploy --only functions

Check logs: firebase functions:log

Further Documentation

Spreadsheet Tools: Google Apps Script to manage client/version matching and client notes workflows in Google Sheets.

Spreadsheet Tools (Google Apps Script)

  • Custom Menu: Adds Baked Tools menu with items:
    • Match Client Names to Internal (maps incoming client codes to internal versions).
    • Prepare Notes (updates body/link formulas based on workflow state).
    • Send Notes to Flow PTR (emails or dispatches prepared notes).
    • Reset Workflow (resets internal script properties and clears formatting).
  • Workflow State:
    • Managed via PropertiesService keys: workflow_state & notes_sent_state.
    • Guards each step to enforce correct sequence before proceeding.
  • Formula Template: Defined in sheets_cell_formulae.txt to standardize note formatting.

Prerequisites​

  • Node.js & npm (for Firebase CLI)
  • Python 3.12+
  • Firebase CLI (npm install -g firebase-tools)
  • Google Workspace Account with edit permissions on the target spreadsheet
  • ShotGrid credentials (API Key & Script Name)

Installation​

A. Clone the repository​

B. Set up Firebase Functions​

cd functions
git checkout main
python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

C. Deploy Spreadsheet Tools​

  1. Open the target Google Sheet.
  2. Go to Extensions > Apps Script.
  3. Copy contents of google_appscript/ into the script editor.
  4. Save and reload the spreadsheet.

This can then be deployed using the google Marketplace SDK and is currently here: https://workspace.google.com/u/0/marketplace/app/bakedtools/360591990321?flow_type=42

Configuration​

  • functions/config.json: 
    {
      "SHOTGRID_API_KEY": "<your_api_key>",
      "SHOTGRID_SCRIPT_NAME": "<your_script>",
      "SECRET_TOKEN": "<your_secret>",
      "SHOTGRID_URL": "https://your.shotgrid.url"
    }

Deployment​

Firebase Functions (Gen-2)​

firebase login
firebase init functions  # if first time
firebase deploy --only functions

Google Apps Script​

  • Save and authorize triggers on first run.
  • The onOpen function registers the Baked Tools menu.

Usage​

  1. ShotGrid AMI: Configure your ShotGrid integration to POST to the deployed endpoints.
  2. Spreadsheet Tools: Open the sheet, use Baked Tools menu in sequence:
    1. Match Client Names to Internal
    2. Prepare Notes
    3. Send Notes to Flow PTR
    4. Reset Workflow (when workflow completes or to restart)

Project Structure​

repo-root/
├── functions/                # Firebase Cloud Functions code
│   ├── main.py
│   ├── config.json
│   ├── status_mapping.yaml
│   ├── requirements.txt
│   └── .firebaserc
└── google_appscript/         # Google Sheets automation scripts
    ├── util.js
    ├── menu.js
    ├── version_sort.js
    ├── note_prep.js
    ├── sendNotes.js
    └── sheets_cell_formulae.txt