Consent

This project creates JSON/JSONP files in the GVL schema based on SOM relevant partners maintained in a Google Spreadsheet.

Overview

• Active Spreadsheet: https://docs.google.com/spreadsheets/d/1_rK7mgudKGM6H4iP_VjcKieJR7J9ZagHd65vgH8xjYU
• Apps Script Project: https://script.google.com/d/1F7J_e8OSFYlE7Qy8hWlZy5QhlOLxgHIyIq7mozzTwWoPr0HC3QWtIpti/edit

Features

• Loads latest Global Vendor Lists (GVL)
• Custom (non-IAB) vendor support
• Checkboxes for vendor eligibility in distributions
• Contact/address information management
• One-click JSON file publishing via spreadsheet menu

Files & URLs

Most Common:

• https://ad.71i.de/vendorlist3/som-vendor-list-web.json
• https://ad.71i.de/vendorlist3/som-vendor-list-web.jsonp
• https://ad.71i.de/vendorlist3/CHANGELOG-web.md

All distributions available:

• Web: som-vendor-list-web.{json,jsonp}
• InApp: som-vendor-list-inapp.{json,jsonp}
• Display/Video variants: som-vendor-list-{web,inapp}-{display,video}.{json,jsonp}
• IAB-only variants: som-iabvendor-list-*.{json,jsonp}
• Changelogs: CHANGELOG-{web,inapp,webdisplay,webvideo,etc}.md

Workflow

Spreadsheet Operations

1. Add/Remove vendors using checkmarks in the spreadsheet
2. Update GVL data via "Request latest GVL" menu button
3. Publish JSON files via "Publish JSON files" menu button

Automated Updates

• Weekly Schedule: Thursdays at 16:30 UTC via GitHub Actions
• Target: Master branch → Active spreadsheet
• Actions: Updates vendor data and publishes files

Manual Updates

Via GitHub Actions:

1. Go to Actions → "📊 Update Vendor Lists" → "Run workflow"
2. Choose action: updateTable, updateFiles, or both
3. Select branch: master (production)
4. Always test first: Set test mode to true

Via Spreadsheet Menu:

• Use "Vendorlist Actions" menu for immediate updates

Development

Local Testing

npm test                    # Run all tests
npm run test:updateTable    # Test spreadsheet update (read-only)
npm run test:updateFiles    # Test file generation (local only)
npm run test:updateChangelog # Test changelog generation

Apps Script Development

Apps Script provides the spreadsheet menu integration and CI/CD triggers:

npm run apps-script:pull   # Pull changes from Apps Script
npm run apps-script:push   # Push local changes to Apps Script  
npm run apps-script:open   # Open Apps Script in browser

Files:

• src/app-scripts/Code.gs - Main logic with menu functions and CI abstraction
• src/app-scripts/lang.gs - JavaScript polyfills for older features
• src/app-scripts/spreadsheet-functions.gs - Custom spreadsheet functions
• src/app-scripts/appsscript.json - Apps Script project configuration

CI Provider Configuration:

// In Code.gs - change this to switch CI systems
const CI_PROVIDER = 'github'; // Options: 'travis', 'github', 'gitlab'

Production Commands

npm run updateTable         # Update spreadsheet with latest GVL
npm run updateFiles         # Generate and deploy JSON files
npm run updateChangelog     # Generate changelogs

Setup Requirements

GitHub Organization Secrets

• GOOGLE_SERVICE_ACCOUNT - Google service account JSON (gopass: seven-one/google-cloud/google-cloud-service-account)
• ADTECHMACHINE_GITHUB_TOKEN - GitHub API token (gopass: seven-one/somquery/adtechmachine-github-token-ci)
• AWS_SECRET_ACCESS_KEY_PRD - AWS secret for S3/CDN (gopass: seven-one/aws/secret-access-key)

GitHub Organization Variables

• AWS_ACCESS_KEY_ID_PRD - AWS access key for S3/CDN (gopass: seven-one/aws/access-key-id)

Apps Script Token Setup (Admin Only - One-time)

Apps Script securely stores CI/CD tokens via Properties Service. Admin setup required once:

1. Get tokens from gopass:

   # GitHub token for CI workflows
   gopass show seven-one/somquery/adtechmachine-github-token-ci
      
   # Travis CI token (legacy)
   gopass show seven-one/somquery/adtechmachine-travisci-token

2. Configure Script Properties:

   • Go to Apps Script Project → Project Settings → Script Properties
   • Add property: SEVENONEMEDIA_ADTECHMACHINE_GITHUB_TOKEN_CI with GitHub token value
   • Add property: SEVENONEMEDIA_ADTECHMACHINE_TRAVISCI_TOKEN with Travis token value

Security Benefits:

• No hardcoded secrets - tokens stored in Google's secure PropertiesService
• Only Apps Script admins can view/edit Script Properties
• All spreadsheet users benefit without any setup
• Easy token rotation without code changes

Consumers

• https://www.seven.one/datenerhebung-anderer-dienstleister
• Joyn (TBD)

Architecture

• Spreadsheet: Data source with vendor management UI
• Apps Script: Spreadsheet menu integration + CI/CD triggers
• GitHub Actions: Automated processing and deployment
• AWS S3 + CDN: File hosting and distribution

Data Flow Diagram

graph TB
    %% Central Hub - Spreadsheet
    Spreadsheet[📊 Google Spreadsheet - SOM Vendor Management - 🎯 CENTRAL HUB]
    
    %% Actors/Triggers
    User[👤 User - Spreadsheet Editor]
    Cron[⏰ Cron Job - Thursdays 16:30 UTC]
    Developer[👨‍💻 Developer]
    
    %% External Data Sources
    GVL[🌐 IAB Global Vendor List - vendor-list.consensu.org]
    
    %% Processing Components
    AppScript[⚙️ Apps Script - Menu Integration & CI/CD Triggers]
    GitHubActions[🔄 GitHub Actions - Automation Runner]
    
    %% Output & Distribution
    LocalFiles[📁 Local Repository - JSON/JSONP Files]
    S3[☁️ AWS S3 - File Storage]
    CDN[🚀 CDN - ad.71i.de/vendorlist3/]
    Consumers[🌍 Consumers - seven.one, Joyn, etc.]
    
    %% User Actions → Spreadsheet (Central Hub)
    User -->|Edit vendor data| Spreadsheet
    User -->|Menu: Request latest GVL| AppScript
    User -->|Menu: Publish JSON files| AppScript
    
    %% Apps Script ↔ Spreadsheet (Central Hub) & GVL Check
    AppScript -.->|Read data for processing| Spreadsheet
    AppScript -->|Check latest GVL version| GVL
    AppScript -.->|Compare versions & show status| Spreadsheet
    AppScript -->|Trigger CI/CD workflows| GitHubActions
    
    %% Automated Triggers → Processing
    Cron -->|Weekly automation| GitHubActions
    Developer -->|Manual workflows & testing| GitHubActions
    
    %% Data Processing Flow (all centered around Spreadsheet)
    GitHubActions -->|Fetch latest vendor data| GVL
    GitHubActions -.->|Read current vendor list| Spreadsheet
    GitHubActions -->|Update with new GVL data| Spreadsheet
    GitHubActions -.->|Read updated data| Spreadsheet
    GitHubActions -->|Generate JSON files| LocalFiles
    GitHubActions -->|Deploy files| S3
    GitHubActions -->|Purge cache| CDN
    
    %% Distribution Chain
    CDN -->|Serve vendor lists| Consumers
    
    %% Developer Direct Access
    Developer -->|Apps Script development| AppScript
    Developer -->|Local testing| LocalFiles
    
    %% Styling
    classDef central fill:#fff3e0,stroke:#f57c00,stroke-width:4px,font-weight:bold
    classDef actor fill:#e1f5fe,stroke:#01579b,stroke-width:2px
    classDef external fill:#f3e5f5,stroke:#4a148c,stroke-width:2px
    classDef process fill:#e8f5e8,stroke:#1b5e20,stroke-width:2px
    classDef output fill:#fce4ec,stroke:#880e4f,stroke-width:2px
    
    class Spreadsheet central
    class User,Cron,Developer actor
    class GVL external
    class AppScript,GitHubActions process
    class LocalFiles,S3,CDN,Consumers output

• npm run test:updateChangelog

ancient whispers about google worksheet libraries

First, you need credentials to access the worksheet.

Create a service account in the Google Developer Console. For that, you might need to create a project first.

In the Create service account window, type a name for the service account, and select Furnish a new private key. Then click Save. Download the credentials and save them to config/credentials.json.

Finally, enable the Google Sheets API for the project here.

CI/CD - GitHub Actions

This project has been migrated from Travis CI to GitHub Actions. The CI/CD pipeline provides both automated and manual workflows for managing vendor lists.

Available Workflows

🚀 Continuous Integration (ci.yml)

• Purpose: Automatically builds and deploys when code is pushed to main branches
• Triggers: Push to master branch
• Manual Run: Available with test mode option (prevents actual deployment)
• Use Case: Automatic deployment of code changes

🧪 Run Tests (test.yml)

• Purpose: Validates code quality and runs comprehensive test suite
• Triggers: Pull requests or manual execution
• Manual Run: Yes - use this to test changes before merging
• Use Case: Quality assurance and troubleshooting

📊 Update Vendor Lists (manual-update.yml)

• Purpose: Manual workflow to update Google Sheets and/or generate vendor list files
• Triggers: Manual execution only
• Options:
  • updateTable - Update Google Sheets data
  • updateFiles - Generate vendor list files
  • both - Do both operations
• Use Case: On-demand vendor list updates outside of weekly schedule

⏰ Weekly Auto-Update (Master) (cron-master.yml)

• Purpose: Automated weekly vendor list updates for master branch
• Triggers: Scheduled - Thursdays at 16:30 UTC
• Manual Run: Not available (automatic only)
• Use Case: Regular maintenance to keep vendor lists current

Quick Start Guide

To run tests: Use "🧪 Run Tests" workflow
To update vendor lists manually: Use "📊 Update Vendor Lists" workflow
For troubleshooting deployment: Use "🚀 Continuous Integration" in test mode

Required GitHub Secrets

The following organization-level secrets must be configured in GitHub:

• GOOGLE_SERVICE_ACCOUNT - Google Service Account JSON credentials for spreadsheet access (gopass: seven-one/google-cloud/adtec-consent-service-account)
• ADTECHMACHINE_GITHUB_TOKEN - GitHub API token for automated commits and pushes (gopass: seven-one/somquery/adtechmachine-github-token-ci)
• AWS_SECRET_ACCESS_KEY_PRD - AWS secret access key for S3 and CDN operations (gopass: seven-one/aws/secret-access-key)

Required GitHub Variables

The following organization-level variables must be configured in GitHub:

• AWS_ACCESS_KEY_ID_PRD - AWS access key ID for S3 and CDN operations (gopass: seven-one/aws/access-key-id)

Environment Variables

The workflows automatically set Travis-compatible environment variables for the deployment script:

• TRAVIS_EVENT_TYPE - Set to 'push' for CI builds, 'cron' for scheduled builds
• TRAVIS_BRANCH - Current branch name
• TRAVIS_PULL_REQUEST - Set to 'false' (PRs are disabled)
• TRAVIS_COMMIT_RANGE - Commit range for the current build

Migration Notes

• The project successfully migrated from Travis CI to GitHub Actions
• All functionality has been preserved and validated
• Cron schedules match the original Travis CI configuration
• Secret management moved from Travis CI environment variables to GitHub organization secrets
• AWS credentials and Google service account access remain unchanged