[GAS] Integración de Google Calendar y ChatGPT para la gestión automatizada de la nutrición

📅 Creado: 2025/07/19, Actualizado: 2025/07/19 ⏱️ Tiempo estimado de lectura ~3 mins (509 palabras)

Cómo combinar Google Apps Script, el registro de eventos de Google Calendar y resultados estructurados de ChatGPT para realizar un seguimiento de los macronutrientes de la dieta y visualizar métricas de salud en Looker Studio sin costo de infraestructura.

Índice

Background: Why Automate Nutritional Tracking with Google Calendar and ChatGPT?
Initial Setup & Application Workflow
Daily Logging Workflow & Looker Studio Visualization
Resumen

Background: Why Automate Nutritional Tracking with Google Calendar and ChatGPT?

Following medical advice recommending purine intake restrictions, maintaining accurate daily dietary logs became essential.
While numerous nutrition apps exist on iOS and Android, few easily monitor specific micronutrients such as purine levels or customizable dietary components.
Given ChatGPT’s strong semantic comprehension of food ingredients, querying ChatGPT directly for nutritional breakdowns provided an ideal solution.
By instructing ChatGPT to output structured YAML or JSON, programmatic pipelines can easily parse responses and write records directly into Google Sheets or visualize them in Looker Studio dashboards.

To keep operational infrastructure entirely free, Google Apps Script (GAS) was chosen to schedule batch executions and bridge Google Calendar events directly with the OpenAI API.

Initial Setup & Application Workflow

Step 1: Copy the Template Google Sheet

Access the application template spreadsheet here:
gcal-ai-event-manager - Google Sheets Template
In the spreadsheet menu, select File → Make a copy to replicate it into your personal Google Drive.

Open the copied spreadsheet and click Extensions → Apps Script .

Step 2: Configure Script Constants ( `const.gs` )

In the left file tree, open const.gs and update SPREADSHEET_ID with the ID from your newly copied Google Sheet URL ( https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit ).

const.gs manages the core parameters:

// Target Google Spreadsheet ID
const SPREADSHEET_ID = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

// Log levels
const LOG_LEVELS = { DEBUG: 1, INFO: 2, WARN: 3, ERROR: 4 };
let LOG_LEVEL = LOG_LEVELS.DEBUG;

// Target sheet names
const SHEET_NAME_EVENT_LIST = '予定一覧';
const SHEET_NAME_RULE = 'ルール';

// Target LLM Model
const OPENAI_MODEL = 'gpt-3.5-turbo';
const AI_RESPONSE_HEADER = 'AI による回答';

// Date range queries (0 = today, negative = past days, positive = future days)
const DAYS_FROM_TODAY_START = 0;
const DAYS_RANGE = -7;

// Dry-run mode flag
const DRY_RUN = false;

Next, save your OpenAI API key in Apps Script Project Settings:
Select Project Settings (gear icon) in the left panel.
Click Add script property .
Enter Property Name: OPEN_AI_API_KEY and value: your OpenAI API secret key.
Click Save script properties .

Step 3: Define Matching Rules

Return to the Google Sheet and open the ルール (Rules) tab:

Rule Parameters Explained :
Rule Name : Unique identifier for the rule.
Title Pattern : Regular expression matched against Google Calendar event titles (e.g., (🍝|🍽️) ).
Description Pattern : Regex matched against event description fields.
Query AI ( TRUE / FALSE ) : If TRUE , sends query to ChatGPT. If FALSE , copies event metadata without LLM calls.
AI Prompt Template : Prompt instruction sent to ChatGPT. Variables ${title} and ${description} are dynamically injected.

Step 4: Test Run & Schedule Automation Trigger

From the custom top navigation menu, click Custom Menu → Execute onSchedule .
(Ensure you have at least one matching meal event on your Google Calendar within the query window).

On first invocation, click through Google’s OAuth consent screens (Advanced → Proceed to script).

Upon execution, parsed calorie, purine, protein, and fat statistics populate the 予定一覧 sheet.

Finally, install the recurring timer trigger by selecting Custom Menu → Execute installTrigger (runs every 4 hours automatically).

Daily Logging Workflow & Looker Studio Visualization

During daily life, simply register meal events directly into your standard Google Calendar. Using emoji identifiers (e.g., 🍝 Lunch ) triggers the script matcher.

Detailed dishes and rough portion estimates go into the description. Once processed, the script writes YAML-formatted nutritional estimates back into the description below --- .

Because the Google Sheet serves as a structured relational dataset, connecting it to Google Looker Studio produces real-time analytical dashboards tracking daily intake, macronutrient proportions, and health milestones.

Resumen

By combining Google Apps Script’s zero-cost cron scheduling with Google Calendar and LLM API structured prompts, personalized healthcare tracking can be automated without recurring SaaS subscription fees.
While LLM estimations provide valuable trend insights, precise medical dietary regimens should always be corroborated by professional clinical nutritionists and medical advice.