Skip to main content

Google Apps Script Configuration Guide

Table of Contents​

  1. What is Google Apps Script?
  2. Apps Script Usage in the Project
  3. Audit Log Apps Script Configuration
  4. Image Insertion Apps Script Configuration
  5. Apps Script Execution API Configuration
  6. Service Account Permission Configuration
  7. Testing and Verification

What is Google Apps Script?​

Google Apps Script is a JavaScript-based scripting platform provided by Google that can automate operations in Google Workspace applications (such as Google Sheets, Google Slides, Google Docs). In this project, Apps Script is used for:

  1. Audit Logging: Record user operations to Google Sheet
  2. Image Insertion: Insert generated images into Google Slides

Why Use Apps Script?

  1. Google Workspace Integration: Can directly operate Google Sheets and Google Slides without complex API calls
  2. Simplified Development: Uses familiar JavaScript language, simple to develop
  3. Execution Identity: Can execute as service account, ensuring permission control
  4. Cost-Effective: Google Apps Script is free for personal and enterprise users

Apps Script Usage in the Project​

Audit Log Apps Script​

Function: Record user operation logs to Google Sheet

Usage:

  • User login/logout records
  • Feature usage records (Market Opportunity Analysis, Audience Signal Analysis, Persona Slide)
  • Result data records
  • Export operation records
  • User feedback records

File Location: docs/apps-script-code/AuditLogCode.gs

Image Insertion Apps Script​

Function: Insert generated images into Google Slides

Usage:

  • Insert analysis result charts into Google Slides template
  • Replace placeholder text with actual images
  • Insert images in blank slides

File Location: docs/apps-script-code/SlidePictureCode.gs

Note: These two Apps Script projects are completely independent and need to be created and configured separately.

Audit Log Apps Script Configuration​

Step 1: Create Google Sheet​

  1. Create New Google Sheet

    • Visit Google Sheets
    • Create a new blank spreadsheet
    • Name it InsightHub Audit Log (or your chosen name)

  2. Record Sheet ID

    • Extract Sheet ID from URL
    • URL format: https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit
    • For example: If URL is https://docs.google.com/spreadsheets/d/1a2b3c4d5e6f7g8h9i0j/edit, then Sheet ID is 1a2b3c4d5e6f7g8h9i0j

Step 2: Create Apps Script Project​

  1. Open Apps Script Editor

  2. Create New Project

    • Click "New Project"
    • Project name: InsightHub Audit Log

  3. Copy Code

    • Open the docs/apps-script-code/AuditLogCode.gs file in the project
    • Copy all content
    • Paste into Apps Script editor's Code.gs file

Step 3: Configure Script Properties​

  1. Open Project Settings

    • Click "Project Settings" (gear icon) on the left

  2. Add Script Properties

    • In the "Script Properties" section, click "Add Script Property"
    • Property: SPREADSHEET_ID
    • Value: Sheet ID recorded in Step 1
    • Click "Save Script Property"

Step 4: Record Script ID​

  1. Get Script ID

    • In project settings page, find "Script ID"
    • Or get from Apps Script editor URL
    • URL format: https://script.google.com/home/projects/{SCRIPT_ID}/edit

  2. Configure Environment Variables

    • (Deprecated) Previous versions used APPS_SCRIPT_AUDIT_ID for Apps Script–based logging. The current release writes directly to Google Sheets via AUDIT_LOG_SPREADSHEET_ID, so this step can be skipped unless you are maintaining legacy deployments.
    • Development environment: Add to .env file
    • Production environment: Add to Cloud Build trigger substitution variables

Step 5: Configure Sheet Permissions​

  1. Share Sheet to Service Account
    • In Google Sheet, click "Share" button in the top right
    • Add service account email (format: service-account-name@project-id.iam.gserviceaccount.com)
    • Set permission to "Editor"
    • Click "Send"

Important: Service account must have Editor permission on the Sheet to write logs.

Image Insertion Apps Script Configuration​

Step 1: Create Apps Script Project​

  1. Open Apps Script Editor

  2. Project Name

    • Project name: InsightHub Slide Picture

  3. Copy Code

    • Open the docs/apps-script-code/SlidePictureCode.gs file in the project
    • Copy all content
    • Paste into Apps Script editor's Code.gs file

Step 2: Record Script ID​

  1. Get Script ID

    • In project settings page, find "Script ID"
    • Or get from Apps Script editor URL

  2. Configure Environment Variables

    • Configure Script ID to environment variable APPS_SCRIPT_SLIDES_ID
    • Development environment: Add to .env file
    • Production environment: Add to Cloud Build trigger substitution variables

Step 3: Configure Execution Identity​

  1. Open Project Settings

    • Click "Project Settings" (gear icon) on the left

  2. Execution Identity

    • In the "Execution Identity" section, select "Me"
    • This means Apps Script will execute as the caller's identity (usually service account)

Note: If using service account to call, Apps Script will execute as the service account, so the service account needs to have access permission to Google Slides.

Apps Script Execution API Configuration​

What is Execution API?​

Apps Script Execution API allows external applications (such as our backend server) to call functions in Apps Script through HTTP requests without deploying Apps Script as a Web App.

Enable Execution API​

  1. Open Google Cloud Console

  2. Enable Apps Script API

    • Navigate to "APIs & Services" > "Library"
    • Search for "Apps Script API"
    • Click "Enable"

Configure Service Account Permissions​

Service account needs to be able to call Apps Script Execution API:

  1. Grant Service Account Permission
    • In Apps Script project, click "Share" button in the top right
    • Add service account email
    • Set permission to "Viewer" (sufficient)
    • Click "Send"

Note:

  • Service account only needs "Viewer" permission to call Execution API
  • But when executing Apps Script, it will execute as the service account, so the service account needs to have actual operation permissions (such as Editor permission on Sheet, Editor permission on Slides)

Service Account Permission Configuration​

Audit Log Service Account Permissions​

Service account needs the following permissions:

  1. Google Sheet Permission

    • In audit log Sheet, add service account as "Editor"
    • Service account email format: service-account-name@project-id.iam.gserviceaccount.com

  2. Apps Script Permission

    • In audit log Apps Script project, add service account as "Viewer"
    • This allows service account to call Execution API

Image Insertion Service Account Permissions​

Service account needs the following permissions:

  1. Google Slides Permission

    • In Google Slides that need image insertion, add service account as "Editor"
    • Or use Google Slides template, add service account as "Editor"

  2. Apps Script Permission

    • In image insertion Apps Script project, add service account as "Viewer"
    • This allows service account to call Execution API

Permission Configuration Checklist​

  • Service account created
  • Service account key downloaded
  • Audit log Sheet shared to service account (Editor permission)
  • Audit log Apps Script shared to service account (Viewer permission)
  • Image insertion Apps Script shared to service account (Viewer permission)
  • Google Slides template shared to service account (Editor permission, if needed)

Testing and Verification​

Test Audit Log Apps Script​

Method 1: Test in Apps Script Editor​

  1. Select Test Function

    • In Apps Script editor, select a test function (e.g., testLogUserLogin)
    • Click "Run" button

  2. Authorize Permissions

    • First run requires authorization
    • Click "Authorize Access"
    • Select your Google account
    • Click "Allow"

  3. View Results

    • Check if logs have been written to Google Sheet
    • View Apps Script execution logs, confirm no errors

Method 2: Test Through Backend API​

  1. Start Backend Server

    cd server
    npm run dev

  2. Call Test Endpoint (if exists)

    • Or directly call audit log API endpoint
    • Check if logs have been written to Sheet

Test Image Insertion Apps Script​

Method 1: Test in Apps Script Editor​

  1. Prepare Test Data

    • Create a test Google Slides
    • Add placeholder text in slides (e.g., {{图片}})
    • Prepare a Base64-encoded test image

  2. Run Test Function

    • In Apps Script editor, select testInsertImage function
    • Modify function parameters (Presentation ID, placeholder, image data)
    • Click "Run"

  3. View Results

    • Check if placeholder in Google Slides has been replaced with image
    • View Apps Script execution logs, confirm no errors

Method 2: Test Through Backend API​

  1. Call Image Insertion API
    • Call image insertion function through backend API
    • Check if image has been inserted in Google Slides

Verify Configuration​

Checklist​

  • Apps Script project created
  • Code correctly copied
  • Script ID recorded and configured to environment variables
  • Apps Script API enabled
  • Service account permissions configured
  • Test function runs successfully
  • Logs/images correctly written/inserted

Frequently Asked Questions​

Q1: Apps Script execution fails with insufficient permissions?​

Error Message: PERMISSION_DENIED or You do not have permission to call this function

Possible Causes:

  1. Service account doesn't have access permission to Apps Script project
  2. Apps Script API not enabled
  3. Service account doesn't have actual operation permissions (such as Editor permission on Sheet)

Solution:

  1. In Apps Script project, add service account as "Viewer"
  2. Enable Apps Script API
  3. In Sheet/Slides, add service account as "Editor"

Q2: Audit log cannot write to Sheet?​

Error Message: Exception: You do not have permission to perform that action

Possible Causes:

  1. Service account doesn't have Editor permission on Sheet
  2. Script ID configuration error
  3. Sheet ID configuration error

Solution:

  1. In Sheet, add service account as "Editor"
  2. (Legacy) Check if the deprecated APPS_SCRIPT_AUDIT_ID variable is still referenced anywhere in your deployment scripts.
  3. Check if script property SPREADSHEET_ID in Apps Script project is correct

Q3: How to get Script ID?​

Method 1: Get from Project Settings

  1. In Apps Script editor, click "Project Settings" (gear icon)
  2. In "General" section, find "Script ID"

Method 2: Get from URL

  • Apps Script editor URL format: https://script.google.com/home/projects/{SCRIPT_ID}/edit
  • Extract SCRIPT_ID part from URL

Q4: Apps Script execution timeout?​

Error Message: Execution timeout

Possible Causes:

  1. Apps Script execution time exceeds 6 minutes (free version limit)
  2. Data volume too large
  3. Network issues

Solution:

  1. Optimize Apps Script code to reduce execution time
  2. Process data in batches
  3. Check network connection

Q5: How to view Apps Script execution logs?​

Method 1: View in Editor

  1. In Apps Script editor, click "Execute" (▶️) button
  2. View "Execution Log" panel

Method 2: View in Google Cloud Console

  1. Open Google Cloud Console
  2. Navigate to "Logs" > "Logs Explorer"
  3. Filter Apps Script related logs

Summary​

This section detailed Google Apps Script configuration steps, including:

  1. Apps Script Overview: What is Apps Script, why use it
  2. Audit Log Configuration: How to create and configure audit log Apps Script
  3. Image Insertion Configuration: How to create and configure image insertion Apps Script
  4. Execution API Configuration: How to enable and configure Execution API
  5. Service Account Permissions: How to configure service account permissions
  6. Testing and Verification: How to test and verify configuration

Correctly configuring Apps Script is key to normal system function operation. Please complete the configuration step by step.

Related Documentation: