Google Apps Script Setup

Overview

SGD-MCS backend runs on Google Apps Script (GAS), a serverless JavaScript platform that provides direct integration with Google Workspace services including Sheets and Drive.

The backend uses Apps Script V8 runtime for modern JavaScript support including ES6+ features.

Prerequisites

Google Account

A Google Workspace or Gmail account with access to Google Drive and Sheets

Node.js & clasp

Optional: Install clasp CLI for command-line deployment

npm install -g @google/clasp

Source Code

Clone the repository and navigate to the Backend directory

Google Sheets Database

A properly configured Google Sheets database (see Database Structure)

Apps Script Manifest

The appsscript.json file configures the Apps Script project:

{
    "timeZone": "America/Bogota",
    "dependencies": {},
    "exceptionLogging": "STACKDRIVER",
    "runtimeVersion": "V8",
    "oauthScopes": [
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/script.external_request"
    ],
    "webapp": {
        "executeAs": "USER_DEPLOYING",
        "access": "ANYONE_ANONYMOUS"
    }
}

Location: Backend/appsscript.json

Configuration Options

timeZone

string

default:"America/Bogota"

Sets the default timezone for date/time operations. Uses IANA timezone identifiers.

runtimeVersion

string

default:"V8"

JavaScript runtime version. V8 enables modern ES6+ syntax, arrow functions, and more.

exceptionLogging

string

default:"STACKDRIVER"

Error logging destination. STACKDRIVER logs to Google Cloud Logging for better debugging.

oauthScopes

array

required

OAuth 2.0 scopes required by the application:

spreadsheets - Read/write Google Sheets data
drive - Create and manage Drive folders/files
script.external_request - Make external HTTP requests (if needed)

webapp.executeAs

string

default:"USER_DEPLOYING"

Execution context. USER_DEPLOYING runs code with deployer’s permissions.

webapp.access

string

default:"ANYONE_ANONYMOUS"

Access control. ANYONE_ANONYMOUS allows public access without authentication.

For production, consider changing to ANYONE (requires login) or DOMAIN (organization only) for better security.

Deployment Methods

Method 1: Using clasp (Recommended)

Install clasp

npm install -g @google/clasp

clasp login

This opens a browser window for Google authentication.

Create New Project

Navigate to the Backend directory and create a new Apps Script project:

cd source/Backend
clasp create --title "SGD-MCS Backend" --type webapp

This generates a .clasp.json file with your script ID.

Configure Script ID

Edit .clasp.json and verify the script ID:

{
  "scriptId": "YOUR_SCRIPT_ID_HERE",
  "rootDir": "."
}

Push Code

Deploy all files to Google Apps Script:

clasp push

Use clasp push --watch to enable auto-deployment on file changes.

Deploy Web App

Create a deployment:

clasp deploy --description "Initial deployment"

Method 2: Manual Upload

Create New Project

Go to script.google.com
Click “New Project”
Rename to “SGD-MCS Backend”

Configure Manifest

Click the gear icon (Project Settings)
Check “Show appsscript.json manifest file”
Edit appsscript.json with the configuration above

Create Files

Create each file from the Backend/ directory structure:

config.js
core/Main.js
core/EntityManager.js
core/controller.js
services/DataService.js
services/DriveManager.js
services/DriveFileManager.js
services/SearchService.js
utils/Utils.js
utils/DataUtils.js

Copy Code

Copy the code from each source file into the corresponding Apps Script file.

Save and Deploy

Save all files (Ctrl+S)
Click “Deploy” → “New deployment”
Select type: “Web app”
Set access as needed
Click “Deploy”

Manual deployment is tedious for updates. Use clasp for production workflows.

File Structure

The backend follows a modular architecture:

Backend/
├── appsscript.json          # Project manifest
├── core/
│   ├── config.js            # Global configuration
│   ├── Main.js              # Entry points (doGet, API proxies)
│   ├── EntityManager.js     # CRUD operations
│   └── controller.js        # Dashboard statistics
├── services/
│   ├── DataService.js       # Data retrieval services
│   ├── DriveManager.js      # Drive folder management
│   ├── DriveFileManager.js  # File operations
│   └── SearchService.js     # Universal search
└── utils/
    ├── Utils.js             # ID generation, normalization, audit logging
    └── DataUtils.js         # Sheet data conversion utilities

Key Functions

Entry Point: doGet()

Serves the web application:

function doGet() {
    return HtmlService.createHtmlOutputFromFile('web/index')
        .setTitle('SGD-MCS v3.0')
        .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
        .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

Location: Backend/core/Main.js:6-11

API Proxies

Apps Script requires global function declarations for client-side calls via google.script.run. The Main.js file provides these proxies:

// Dashboard
function getStats() { return getDashboardStats(); }

// Entity Lists
function getStudents() { return listStudents(); }
function getTeachers() { return listTeachers(); }
// ... etc

Location: Backend/core/Main.js:22-42

OAuth Scopes Explained

spreadsheets - Full Sheets Access

Scope: https://www.googleapis.com/auth/spreadsheetsRequired for:

Reading student, faculty, thesis, and event data
Writing new records and updates
Auto-incrementing ID counters in Config sheet
Audit logging to Historial_Documentos

drive - Drive File Management

Scope: https://www.googleapis.com/auth/driveRequired for:

Creating entity folders (Students, Thesis, etc.)
Uploading documents and certificates
Organizing files into structured directories
Moving files to trash on deletion

script.external_request - HTTP Requests

Scope: https://www.googleapis.com/auth/script.external_requestRequired for:

Making external API calls (if needed)
Webhook integrations
Future extensibility

Testing Deployment

Test Configuration

In the Apps Script editor, run getAppConfig():

Logger.log(getAppConfig());

Verify it returns:

{
  "appVersion": "3.0.0",
  "environment": "Production",
  "sheets": {...},
  "colors": {...}
}

Test Database Connection

Run getDB() and verify it connects:

var ss = getDB();
Logger.log(ss.getName());

Test CRUD Operations

Test creating a student record:

var result = createItem('estudiante', {
  Nombre1: 'Test',
  Apellido1: 'User',
  Email: '[email protected]'
});
Logger.log(result);

Test Web App

Deploy as web app and visit the deployment URL to ensure the frontend loads.

Updating the Script

Using clasp

# Pull latest code from Apps Script
clasp pull

# Make changes locally
# ...

# Push updates
clasp push

# Create new version
clasp deploy --description "Updated entity manager"

Manual Update

Open the script at script.google.com
Edit the relevant files
Save changes (Ctrl+S)
Create new deployment or update existing one

Always test changes in a development environment before deploying to production.

Permissions and Security

Service Account

The script runs with the permissions of the deploying user. Ensure this account has:

Owner or Editor access to the Google Sheets database
Access to the Drive folder specified in ROOT_FOLDER_ID
Appropriate organizational permissions

Public Access Warning

The default configuration allows anonymous public access. For production:

Change webapp.access to "DOMAIN" (organization only)
Or change to "ANYONE" (requires Google login)
Implement additional authentication in doGet() if needed

Monitoring and Logs

View Execution Logs

Open your Apps Script project
Click “Executions” in the left sidebar
View recent runs, errors, and execution times

Cloud Logging

With exceptionLogging: "STACKDRIVER", errors are logged to Google Cloud:

Go to console.cloud.google.com
Select your Apps Script project
Navigate to “Logging” → “Logs Explorer”
Filter by severity and timestamp

Troubleshooting

Script Authorization Required

Error: “Authorization required to perform that action”Solution: Run any function manually in the editor to trigger OAuth consent flow.

Deployment Failed

Error: “Unable to deploy”Solution: Check that all file syntax is valid. Run “Debug” → “Check for errors” in the editor.

Scope Mismatch

Error: “Access not granted”Solution: Verify all required scopes are in appsscript.json. Re-authorize the script.

Functions Not Found

Error: “Function not found” when calling from frontendSolution: Ensure functions are declared in global scope (Main.js proxies).

Next Steps

Configuration

Configure database connection and settings

Database Structure

Understand the Google Sheets schema

Permissions

Set up user roles and access control

Get Started

Core Features

User Guides

Administration

Google Apps Script Setup

Overview

Prerequisites

Google Account

Node.js & clasp

Source Code

Google Sheets Database

Apps Script Manifest

Configuration Options

Deployment Methods

Method 1: Using clasp (Recommended)

Method 2: Manual Upload

File Structure

Key Functions

Entry Point: doGet()

API Proxies

OAuth Scopes Explained

Testing Deployment

Updating the Script

Using clasp

Manual Update

Permissions and Security

Service Account

Public Access Warning

Monitoring and Logs

View Execution Logs

Cloud Logging

Troubleshooting

Next Steps

Configuration

Database Structure

Permissions

Build docs developers (and LLMs) love

Get Started

Core Features

User Guides

Administration

​Overview

​Prerequisites

Google Account

Node.js & clasp

Source Code

Google Sheets Database

​Apps Script Manifest

​Configuration Options

​Deployment Methods

​Method 1: Using clasp (Recommended)

​Method 2: Manual Upload

​File Structure

​Key Functions

​Entry Point: doGet()

​API Proxies

​OAuth Scopes Explained

​Testing Deployment

​Updating the Script

​Using clasp

​Manual Update

​Permissions and Security

Service Account

Public Access Warning

​Monitoring and Logs

​View Execution Logs

​Cloud Logging

​Troubleshooting

​Next Steps

Configuration

Database Structure

Permissions

Build docs developers (and LLMs) love

Overview

Prerequisites

Apps Script Manifest

Configuration Options

Deployment Methods

Method 1: Using clasp (Recommended)

Method 2: Manual Upload

File Structure

Key Functions

Entry Point: doGet()

API Proxies

OAuth Scopes Explained

Testing Deployment

Updating the Script

Using clasp

Manual Update

Permissions and Security

Monitoring and Logs

View Execution Logs

Cloud Logging

Troubleshooting

Next Steps