Build Memory-based AI Doctor using OpenAI Responses API in Google Sheets

Medical Chatbot in Google Sheets: A Beginner's Guide

Who is this guide for?
This guide is for beginners who are new to Google Apps Script and might not have much experience with coding or APIs like OpenAI's. We'll go step-by-step.

What does this script do?
At its core, this script turns your Google Sheet into an interface for chatting with an AI assistant configured as a medical consultant. It can:

1. Chat: Allow a user (patient) to converse with the AI in the AI Chat sheet.
2. Remember: Save important information from the chat into a Patient History sheet using AI function calls.
3. Learn: Update the AI's knowledge base (an OpenAI Vector Store) with information from the Patient History and Checkup History sheets.

HERE'S THE FEATURE VIDEO OF THE AI DOCTOR TOOL

---

Table of Contents

• 1. Getting Started: Adding the Script
• 2. Overall Architecture: How it Works
• 3. Feature 1: The AI Chat
• 4. Feature 2: Updating the AI's Full Knowledge
• 5. Feature 3: Updating Only Patient History in AI Knowledge
• 6. Supporting Functions & Configuration
• 7. Conclusion

---

1. Getting Started: Adding the Script

Before we dive into the details, you need to add this code to your Google Sheet.

Instructions:

1. Open the Google Sheet you intend to use for this chatbot.
2. Go to the menu: Extensions > Apps Script.
3. This will open a new browser tab with the script editor.
4. Delete any default code you see in the editor (like function myFunction() { ... }).
5. Copy the entire code block you provided in the prompt.
6. Paste it into the Apps Script editor.
7. Click the floppy disk icon (Save project) near the top. Give your script project a name (e.g., "Medical Chatbot").
8. Important: You'll need to replace "vs_..." at the very top with your actual OpenAI Vector Store ID. You also need to ensure your OpenAI API Key is correctly placed in cell B2 of your Settings sheet.

You'll also need sheets named exactly: AI Chat, Checkup History, Patient History, and Settings. The script relies on these specific names.

---

2. Overall Architecture: How it Works

Think of this system like a team working together:

• Google Sheets: This is your main dashboard and data storage.
  • AI Chat: Where you type messages and see the AI's replies.
  • Patient History: A log of important facts saved by the AI.
  • Checkup History: A log of past medical checkups, etc.
  • Settings: Holds configuration like API key and model choice.
• Google Apps Script (The Code): The manager or "brain" inside your Sheet. Watches for changes, talks to OpenAI, updates the sheet.
• OpenAI API: The AI service in the cloud.
  • Models (like GPT-4): Generate text responses.
  • Vector Store: Special OpenAI database holding your uploaded history files (.json) for the AI to search.
  • Function Calling: Lets the AI ask Apps Script to run functions (like saving history).
• Constants and Configuration: Fixed values (sheet names, cell refs) defined at the script's start for easier updates.

---

3. Feature 1: The AI Chat

This is the primary function – talking to the AI doctor.

Code Snippet: onEdit Trigger (Chat part)

// Function to handle the onEdit event
function onEdit(e) {
  const aichatSheet = e.source.getSheetByName(SHEET.AI_CHAT);
  const range = e.range; // The cell that was just edited

  // ... (other checks for checkboxes)

  // Check if the edit happened in the 'AI Chat' sheet
  if (e.source.getSheetName() == SHEET.AI_CHAT) {

    // Check if the edit was in the User Message column (Column A) and not the header row (Row 1)
    if (range.getColumn() == AI_CHAT_REF.USER_MESSAGE && range.getRow() > 1) {
      // Check if the cell is not empty
      if (!range.getValue() == "") {
        // If all conditions met, start the chat process
        aiChat(e)
      }
    }
  }
  // ... (checkbox logic)
}

Explanation: How Chat Starts

• Google Sheets automatically runs onEdit(e) every time you edit any cell.
• The code checks if the edit was in the AI Chat sheet, in the user message column (A), below row 1, and not empty.
• If all true, it calls aiChat(e).

Code Snippet: aiChat(e) Function

function aiChat(e) {
  // ... (Get sheet, range, user input, API key, model)

  let messages = []; // Initialize conversation history

  // ... (Add system message)

  // ... (Loop through previous chat rows and add to messages)

  const url = "https://api.openai.com/v1/chat/completions"; // Adjusted for chat completions API
  let tools = [ /* ... tool definitions for addPatientHistory, updatePatientHistory ... */ ];
  let payload = {
    model: /* ... model from settings ... */,
    messages: messages,
    tools: tools,
    tool_choice: "auto", // Let OpenAI decide when to call functions
    // Potentially add 'stream': false if not handling streaming
  };
  const headers = {
    "Authorization": "Bearer " + getApiKey(),
    "Content-Type": "application/json",
    "OpenAI-Beta": "assistants=v1" // Keep if using assistant-like features/tools
  };
  let options = {
    method: "post",
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true // Important to handle errors gracefully
  };

  // ---- CHAT - STEP 1: Send message history to OpenAI ----
  let response = UrlFetchApp.fetch(url, options);
  let responseCode = response.getResponseCode();
  let jsonResponse = JSON.parse(response.getContentText());

  // Error Handling (Basic Example)
  if (responseCode !== 200) {
     console.error("OpenAI API Error:", jsonResponse);
     displayResponse("Error communicating with AI. Check Logs.", aichatSheet, range.getRow());
     return; // Stop processing
  }

  // ---- CHAT - STEP 2: Check for function calls ----
  const message = jsonResponse.choices[0].message;
  const hasToolCalls = message.tool_calls && message.tool_calls.length > 0;

  if (hasToolCalls) {
    // ---- CHAT - STEP 3A: Tool call requested ----
    console.log("TOOL CALL DETECTED");
    messages.push(message); // Add AI's message asking to use a tool

    for (const toolCall of message.tool_calls) {
      // ---- CHAT - STEP 4: Execute the function ----
      const functionToCall = toolCall.function;
      const name = functionToCall.name;
      const args = JSON.parse(functionToCall.arguments);
      let functionResult = callFunction(name, args); // Your function to route calls

      // ---- CHAT - STEP 5: Prepare for second API call ----
      messages.push({
        tool_call_id: toolCall.id,
        role: "tool",
        name: name,
        content: JSON.stringify(functionResult) // Result needs to be stringified
      });
    }

    // Prepare payload2 and options2 for the second call
    const payload2 = {
       model: /* ... model from settings ... */,
       messages: messages
       // No 'tools' or 'tool_choice' needed here usually
    };
    const options2 = {
       method: "post",
       headers: headers,
       payload: JSON.stringify(payload2),
       muteHttpExceptions: true
    };

    // ---- CHAT - STEP 6: Make second API call ----
    let response2 = UrlFetchApp.fetch(url, options2);
    let responseCode2 = response2.getResponseCode();
    let jsonResponse2 = JSON.parse(response2.getContentText());

    if (responseCode2 !== 200) {
       console.error("OpenAI API Error (2nd call):", jsonResponse2);
       displayResponse("Error after function call. Check Logs.", aichatSheet, range.getRow());
       return;
    }

    // ---- CHAT - STEP 7: Display final response ----
    let assistantReply = jsonResponse2.choices[0].message.content;
    displayResponse(assistantReply, aichatSheet, range.getRow());

  } else {
    // ---- CHAT - STEP 3B: No tool call ----
    console.log("NO TOOL CALL");
    let assistantReply = message.content;
    displayResponse(assistantReply, aichatSheet, range.getRow());
  }
}

Explanation: The Main Chat Logic

1. Gathering Information: Gets input, API key, model. Builds messages list with system prompt and past chat history from the sheet.
2. Talking to OpenAI (First API Call): Defines API endpoint (/v1/chat/completions), tools (functions AI can use), payload. Sends request via UrlFetchApp.fetch. Uses muteHttpExceptions: true to catch errors.
3. Handling Function Calls (Tool Calls): Checks OpenAI response (message.tool_calls). If it requests a function (e.g., addPatientHistory), it calls the corresponding Apps Script function via callFunction.
4. Talking to OpenAI Again (If needed): If a function was called, adds the function's request and its result to the messages list. Sends this updated list back to OpenAI in a second API call to get the final text response.
5. Displaying the Response: Extracts the text reply (from 1st or 2nd API call) and writes it to the sheet using displayResponse.
6. Error Handling: Basic checks for non-200 responses from OpenAI are included.

Code Snippet: systemMessage Variable

let systemMessage = `You are a medical consultant...
# TALKING STYLE
... (Define how it should talk) ...
# RECORD KEEPING OF PATIENT HISTORY
When a new, distinct piece of patient information (like an allergy, condition, medication, symptom history, lifestyle factor) is confirmed or mentioned, you MUST call the 'addPatientHistory' function with the relevant 'aspect' (e.g., 'Allergies', 'Conditions', 'Medications') and the 'patienthistory' detail.
If the user provides an update to existing information already likely recorded, call the 'updatePatientHistory' function. You will need to figure out the 'patienthistory_id' (usually the row number in the Patient History sheet where the item is) and provide the updated 'aspect' and 'patienthistory' details. Search the available files (patienthistory.json) to help find the ID if needed.
# FILES
- You have access to retrieval using a file named 'patienthistory.json' containing patient history records. Each record has an 'id', 'aspect', and 'detail'.
- You have access to retrieval using a file named 'checkuphistory.json' containing past checkup details.
# WARNING
- Before replying, ALWAYS check the files using retrieval if the user asks about past information or to avoid asking redundant questions.
- Be concise but thorough. Prioritize patient safety. Do NOT provide diagnoses. Advise seeking professional medical help for serious concerns.
# Function calling
## Add Patient History
Use this function to add NEW patient history records.
Parameters:
- 'aspect': (string) The category (e.g., 'Allergies', 'Conditions', 'Medications', 'Symptoms', 'Lifestyle'). REQUIRED.
- 'patienthistory': (string) The specific detail being recorded. REQUIRED.
ALWAYS CALL 'addPatientHistory' function when new distinct information is available.
## Update Patient History
Use this function to update EXISTING patient history records.
Parameters:
- 'patienthistory_id': (number or string) The unique ID (often the row number) of the record to update. REQUIRED. Search patienthistory.json file to get the patienthistory_id before calling.
- 'aspect': (string) The category being updated (e.g., 'Medications'). REQUIRED.
- 'patienthistory': (string) The NEW or updated detail. REQUIRED.
Always call 'updatePatientHistory' function when modifying an existing record.
`;

Explanation: Instructing the AI

• This long text is the critical instruction set (system prompt) for the AI.
• Defines its Role, Personality/Style, and Process.
• Tells it about available Files in its Vector Store knowledge (how it can access 'patienthistory.json' and 'checkuphistory.json' via retrieval).
• Provides clear Function Calling Rules: *when* and *how* to ask Apps Script to run functions like addPatientHistory or updatePatientHistory, including parameter details.
• Includes Warnings and guidelines on responsible interaction.

Code Snippet: displayResponse, getApiKey

function displayResponse(response, sheet, row) {
  // Display the response in the Doctor Reply column for the corresponding user message row
  sheet.getRange(row, AI_CHAT_REF.DOCTOR_REPLY).setValue(response);
  SpreadsheetApp.flush(); // Ensure the sheet updates immediately
}

function getApiKey() {
  // Retrieve API key from the Settings sheet
  const settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET.SETTINGS);
  if (!settingsSheet) {
    SpreadsheetApp.getUi().alert("Error: 'Settings' sheet not found!");
    throw new Error("'Settings' sheet not found!");
  }
  const key = settingsSheet.getRange(SETTINGS_REF.OPENAI_KEY).getValue();
  if (!key) {
     SpreadsheetApp.getUi().alert("Error: OpenAI API Key not found in Settings sheet cell " + SETTINGS_REF.OPENAI_KEY + "!");
     throw new Error("OpenAI API Key not found");
  }
  return key;
}

Explanation: Helper Functions

• displayResponse: Puts the final AI reply text into the correct cell in the AI Chat sheet (Column B). Includes SpreadsheetApp.flush() to help ensure the update is visible quickly.
• getApiKey: Reads the OpenAI API key securely from the specified cell (B2) in the Settings sheet. Includes basic error checking.

---

4. Feature 2: Updating the AI's Full Knowledge

This updates the AI's Vector Store with the latest info from both the Checkup History and Patient History sheets.

Code Snippet: Triggers (onEdit Checkbox & onOpen Menu)

// --- Part of the onEdit function ---
function onEdit(e) {
  const ss = e.source;
  const sheet = e.range.getSheet();
  const range = e.range;

  // --- Checkbox logic for FULL KNOWLEDGE update ---
  if (sheet.getName() === SHEET.AI_CHAT && range.getA1Notation() === AI_CHAT_REF.UPDATE_KNOWLEDGE) {
    if (range.isChecked()) {
      console.log("Full Knowledge Update checkbox triggered.");
      uploadKnowledgeToOpenAI(); // Call the function to upload both files
      range.uncheck(); // Uncheck the box after running
      SpreadsheetApp.getActiveSpreadsheet().toast("Full knowledge update process initiated.");
    }
  }

  // --- Checkbox logic for PATIENT HISTORY ONLY update ---
  if (sheet.getName() === SHEET.AI_CHAT && range.getA1Notation() === AI_CHAT_REF.UPDATE_PATIENT_HISTORY) {
     if (range.isChecked()) {
       console.log("Patient History Only Update checkbox triggered.");
       uploadPatientHistoryToOpenAI(); // Call the function to upload only patient history
       range.uncheck(); // Uncheck the box after running
       SpreadsheetApp.getActiveSpreadsheet().toast("Patient history update process initiated.");
     }
  }

  // --- Chat trigger logic (as shown before) ---
  if (sheet.getName() == SHEET.AI_CHAT) {
    if (range.getColumn() == AI_CHAT_REF.USER_MESSAGE && range.getRow() > 1) {
      if (range.getValue() !== "") {
        aiChat(e);
      }
    }
  }
}

// --- Runs when the spreadsheet is opened ---
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('UPDATE DOCTOR KNOWLEDGE')
    .addItem('Update FULL Knowledge (Both Histories)', 'uploadKnowledgeToOpenAI_menu') // Calls wrapper
    .addItem('Update PATIENT HISTORY Only', 'uploadPatientHistoryToOpenAI_menu') // Calls wrapper
    .addToUi();
}

// --- Wrapper functions for menu items (recommended practice) ---
function uploadKnowledgeToOpenAI_menu() {
  console.log("Full Knowledge Update triggered from menu.");
  uploadKnowledgeToOpenAI();
  SpreadsheetApp.getActiveSpreadsheet().toast("Full knowledge update process initiated via menu.");
}

function uploadPatientHistoryToOpenAI_menu() {
  console.log("Patient History Only Update triggered from menu.");
  uploadPatientHistoryToOpenAI();
  SpreadsheetApp.getActiveSpreadsheet().toast("Patient history update process initiated via menu.");
}

Explanation: How Knowledge Update Starts

There are two ways to trigger the Full Knowledge Update:

1. Checkbox: In the AI Chat sheet, check the box in cell G1 (defined by AI_CHAT_REF.UPDATE_KNOWLEDGE). The onEdit function detects this, calls uploadKnowledgeToOpenAI(), and then unchecks the box.
2. Menu Item: When the spreadsheet opens, the onOpen function creates a custom menu named "UPDATE DOCTOR KNOWLEDGE". Clicking the "Update FULL Knowledge (Both Histories)" option runs the uploadKnowledgeToOpenAI_menu wrapper, which then calls uploadKnowledgeToOpenAI().

Code Snippet: uploadKnowledgeToOpenAI() Function

// Uploads BOTH Checkup History and Patient History to OpenAI Files and updates Vector Store
function uploadKnowledgeToOpenAI() {
  try {
    const apiKey = getApiKey();
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const settingsSheet = ss.getSheetByName(SHEET.SETTINGS);

    if (!VECTOR_STORE_ID || VECTOR_STORE_ID === "vs_...") {
       SpreadsheetApp.getUi().alert("Error: VECTOR_STORE_ID is not set correctly at the top of the script.");
       return;
    }

    // --- Helper Function to Convert Sheet Data ---
    function sheetDataToJson(sheetData) {
       if (!sheetData || sheetData.length < 2) return []; // Handle empty or header-only sheet
       const headers = sheetData[0].map(header => header.toString().trim());
       const jsonData = [];
       for (let i = 1; i < sheetData.length; i++) {
           // Skip empty rows (check if first cell is empty)
           if (!sheetData[i][0] || sheetData[i][0].toString().trim() === "") {
               continue;
           }
           let obj = {};
           for (let j = 0; j < headers.length; j++) {
               // Ensure header exists and data exists for the column
               if (headers[j] && sheetData[i][j] !== undefined) {
                   obj[headers[j]] = sheetData[i][j];
               } else if (headers[j]) {
                   obj[headers[j]] = ""; // Assign empty string if cell is blank
               }
           }
           jsonData.push(obj);
       }
       return jsonData;
    }

    // --- Prepare & Upload Checkup History File ---
    console.log("Processing Checkup History...");
    const checkupSheet = ss.getSheetByName(SHEET.CHECKUP_HISTORY);
    if (!checkupSheet) throw new Error(`Sheet not found: ${SHEET.CHECKUP_HISTORY}`);
    const checkupData = checkupSheet.getDataRange().getValues();
    const checkupJsonArray = sheetDataToJson(checkupData);
    const checkupJsonContent = JSON.stringify(checkupJsonArray, null, 2); // Pretty print JSON
    const checkupBlob = Utilities.newBlob(checkupJsonContent, 'application/json', 'checkuphistory.json');
    const checkupFileId = uploadFileToOpenAI(apiKey, checkupBlob);
    if (!checkupFileId) throw new Error("Failed to upload Checkup History file.");
    settingsSheet.getRange(SETTINGS_REF.CHECKUP_FILE_ID).setValue(checkupFileId);
    console.log(`Checkup History uploaded. File ID: ${checkupFileId}`);

    // --- Prepare & Upload Patient History File ---
    console.log("Processing Patient History...");
    const patientSheet = ss.getSheetByName(SHEET.PATIENT_HISTORY);
    if (!patientSheet) throw new Error(`Sheet not found: ${SHEET.PATIENT_HISTORY}`);
    const patientData = patientSheet.getDataRange().getValues();
    // Add row number as 'id' if header is 'id' or similar
    const patientHeaders = patientData.length > 0 ? patientData[0] : [];
    const idColumnIndex = patientHeaders.findIndex(h => h.toString().toLowerCase() === 'id');
    if (idColumnIndex !== -1) {
      for (let i = 1; i < patientData.length; i++) {
         // Assign row number + 1 (since sheets are 1-indexed and data array is 0-indexed after header)
         patientData[i][idColumnIndex] = i + 1;
      }
    }
    const patientJsonArray = sheetDataToJson(patientData);
    const patientJsonContent = JSON.stringify(patientJsonArray, null, 2);
    const patientBlob = Utilities.newBlob(patientJsonContent, 'application/json', 'patienthistory.json');
    const patientFileId = uploadFileToOpenAI(apiKey, patientBlob);
    if (!patientFileId) throw new Error("Failed to upload Patient History file.");
    settingsSheet.getRange(SETTINGS_REF.PATIENT_FILE_ID).setValue(patientFileId);
    console.log(`Patient History uploaded. File ID: ${patientFileId}`);

    // --- Update the Vector Store (replace existing files) ---
    console.log(`Updating Vector Store (${VECTOR_STORE_ID}) with new files...`);
    const success = updateVectorStoreFiles(apiKey, VECTOR_STORE_ID, [checkupFileId, patientFileId]); // Pass array of file IDs

    if (success) {
      console.log("Vector Store updated successfully.");
      SpreadsheetApp.getActiveSpreadsheet().toast("Knowledge update successful: Both history files uploaded and Vector Store updated.");
    } else {
      throw new Error("Failed to update Vector Store with new files.");
    }

  } catch (error) {
    console.error("Error in uploadKnowledgeToOpenAI:", error);
    Logger.log("Error in uploadKnowledgeToOpenAI: " + error.message + "\n" + error.stack);
    SpreadsheetApp.getUi().alert("Error during knowledge update: " + error.message + ". Check Logs for details.");
  }
}


// --- Helper Function to Upload a Single File ---
function uploadFileToOpenAI(apiKey, blob) {
  const url = "https://api.openai.com/v1/files";
  const payload = {
    purpose: 'assistants', // Use 'assistants' for Vector Stores
    file: blob
  };
  const options = {
    method: "post",
    headers: { "Authorization": "Bearer " + apiKey },
    payload: payload,
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(url, options);
  const responseCode = response.getResponseCode();
  const jsonResponse = JSON.parse(response.getContentText());

  if (responseCode === 200 && jsonResponse.id) {
    return jsonResponse.id;
  } else {
    console.error("OpenAI File Upload Error:", responseCode, jsonResponse);
    Logger.log("OpenAI File Upload Error: " + responseCode + " - " + response.getContentText());
    return null;
  }
}


// --- Helper Function to Update Vector Store ---
// Replaces ALL files in the vector store with the provided list
function updateVectorStoreFiles(apiKey, vectorStoreId, fileIds) {
   const url = `https://api.openai.com/v1/vector_stores/${vectorStoreId}`;
   const payload = JSON.stringify({
       file_ids: fileIds // Pass the array of new file IDs
   });
   const options = {
       method: "post", // Use POST to update/overwrite
       headers: {
           "Authorization": "Bearer " + apiKey,
           "Content-Type": "application/json",
           "OpenAI-Beta": "assistants=v2" // Use v2 for vector stores generally
       },
       payload: payload,
       muteHttpExceptions: true
   };

   const response = UrlFetchApp.fetch(url, options);
   const responseCode = response.getResponseCode();
   const responseBody = response.getContentText();

   if (responseCode === 200) {
       console.log(`Vector Store ${vectorStoreId} updated successfully with files: ${fileIds.join(', ')}`);
       return true;
   } else {
       console.error(`Failed to update Vector Store ${vectorStoreId}. Status: ${responseCode}`, responseBody);
       Logger.log(`Failed to update Vector Store ${vectorStoreId}. Status: ${responseCode} Body: ${responseBody}`);
       return false;
   }
}

Explanation: Uploading All History

1. Error Handling & Setup: Uses a try...catch block for robustness. Gets the API key and checks if the VECTOR_STORE_ID is set.
2. Convert Sheet Data to JSON: Defines and uses a nested helper function sheetDataToJson. This reads all data from a sheet, uses the first row as headers (keys), and converts subsequent rows into an array of JSON objects. It handles empty rows and missing cell values gracefully. For 'Patient History', it automatically adds the row number as the 'id' if an 'id' column exists.
3. Upload Files to OpenAI:
   • Reads data from Checkup History sheet, converts it to JSON string, creates a data Blob.
   • Calls uploadFileToOpenAI helper function, which sends the Blob to OpenAI's /v1/files endpoint with purpose 'assistants'.
   • If successful, OpenAI returns a unique File ID (e.g., file-xxxxxxxx). This ID is stored in the Settings sheet for reference.
   • Repeats the process for the Patient History sheet.
4. Update Vector Store: Calls the updateVectorStoreFiles helper function. This function makes a POST request to the specific Vector Store endpoint (/v1/vector_stores/{VECTOR_STORE_ID}), providing the array containing the new File IDs for both history files. This action tells OpenAI to associate these *specific* files with the Vector Store, effectively replacing any previously associated files. Uses the "assistants=v2" beta header.
5. Feedback: Uses console.log for detailed logs (viewable in Apps Script editor) and SpreadsheetApp.getActiveSpreadsheet().toast() for brief user feedback messages in the sheet. Shows error alerts using SpreadsheetApp.getUi().alert().

---

5. Feature 3: Updating Only Patient History in AI Knowledge

This feature is useful when only the Patient History sheet has changed (e.g., after function calls), and you want to update the AI's knowledge without re-uploading the (potentially large) Checkup History.

Code Snippet: Triggers (onEdit Checkbox & onOpen Menu)

// --- Part of the onEdit function (also shown in Section 4) ---
function onEdit(e) {
  // ... (other onEdit logic) ...

  // --- Checkbox logic for PATIENT HISTORY ONLY update ---
  if (sheet.getName() === SHEET.AI_CHAT && range.getA1Notation() === AI_CHAT_REF.UPDATE_PATIENT_HISTORY) {
     if (range.isChecked()) {
       console.log("Patient History Only Update checkbox triggered.");
       uploadPatientHistoryToOpenAI(); // Call the function to upload only patient history
       range.uncheck(); // Uncheck the box after running
       SpreadsheetApp.getActiveSpreadsheet().toast("Patient history update process initiated.");
     }
  }

  // ... (other onEdit logic) ...
}

// --- Part of the onOpen function (also shown in Section 4) ---
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('UPDATE DOCTOR KNOWLEDGE')
    .addItem('Update FULL Knowledge (Both Histories)', 'uploadKnowledgeToOpenAI_menu')
    .addItem('Update PATIENT HISTORY Only', 'uploadPatientHistoryToOpenAI_menu') // This one
    .addToUi();
}

// --- Wrapper function for menu item (also shown in Section 4) ---
function uploadPatientHistoryToOpenAI_menu() {
  console.log("Patient History Only Update triggered from menu.");
  uploadPatientHistoryToOpenAI();
  SpreadsheetApp.getActiveSpreadsheet().toast("Patient history update process initiated via menu.");
}

Explanation: How Patient History Update Starts

Similar to the full update, there are two ways to trigger the Patient History Only Update:

1. Checkbox: In the AI Chat sheet, check the box in cell H1 (defined by AI_CHAT_REF.UPDATE_PATIENT_HISTORY). The onEdit function calls uploadPatientHistoryToOpenAI() and unchecks the box.
2. Menu Item: Use the "UPDATE DOCTOR KNOWLEDGE" menu and click "Update PATIENT HISTORY Only". This runs the uploadPatientHistoryToOpenAI_menu wrapper, which calls uploadPatientHistoryToOpenAI().

Code Snippet: uploadPatientHistoryToOpenAI() Function

// Uploads ONLY Patient History to OpenAI Files and updates Vector Store
function uploadPatientHistoryToOpenAI() {
  try {
    const apiKey = getApiKey();
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const settingsSheet = ss.getSheetByName(SHEET.SETTINGS);

    if (!VECTOR_STORE_ID || VECTOR_STORE_ID === "vs_...") {
       SpreadsheetApp.getUi().alert("Error: VECTOR_STORE_ID is not set correctly at the top of the script.");
       return;
    }

    // Retrieve the *existing* Checkup History File ID from Settings
    const checkupFileId = settingsSheet.getRange(SETTINGS_REF.CHECKUP_FILE_ID).getValue();
    if (!checkupFileId) {
      SpreadsheetApp.getUi().alert("Warning: Checkup History File ID not found in Settings. Running Full Knowledge update is recommended first.");
      // Optionally, you could call uploadKnowledgeToOpenAI() here or just return
      // For now, we'll proceed but the vector store might be incomplete.
      console.warn("Checkup History File ID missing. Proceeding with Patient History only.");
    }

    // --- Helper Function to Convert Sheet Data (Same as in uploadKnowledgeToOpenAI) ---
    function sheetDataToJson(sheetData) {
       // ...(same implementation as before)...
       if (!sheetData || sheetData.length < 2) return [];
       const headers = sheetData[0].map(header => header.toString().trim());
       const jsonData = [];
       for (let i = 1; i < sheetData.length; i++) {
           if (!sheetData[i][0] || sheetData[i][0].toString().trim() === "") continue;
           let obj = {};
           for (let j = 0; j < headers.length; j++) {
               if (headers[j]) {
                   obj[headers[j]] = sheetData[i][j] !== undefined ? sheetData[i][j] : "";
               }
           }
           jsonData.push(obj);
       }
       return jsonData;
    }

    // --- Prepare & Upload Patient History File ---
    console.log("Processing Patient History for update...");
    const patientSheet = ss.getSheetByName(SHEET.PATIENT_HISTORY);
    if (!patientSheet) throw new Error(`Sheet not found: ${SHEET.PATIENT_HISTORY}`);
    const patientData = patientSheet.getDataRange().getValues();
    // Add row number as 'id'
    const patientHeaders = patientData.length > 0 ? patientData[0] : [];
    const idColumnIndex = patientHeaders.findIndex(h => h.toString().toLowerCase() === 'id');
     if (idColumnIndex !== -1) {
      for (let i = 1; i < patientData.length; i++) {
         patientData[i][idColumnIndex] = i + 1;
      }
    }
    const patientJsonArray = sheetDataToJson(patientData);
    const patientJsonContent = JSON.stringify(patientJsonArray, null, 2);
    const patientBlob = Utilities.newBlob(patientJsonContent, 'application/json', 'patienthistory.json');
    const newPatientFileId = uploadFileToOpenAI(apiKey, patientBlob); // Use the helper function
    if (!newPatientFileId) throw new Error("Failed to upload updated Patient History file.");
    settingsSheet.getRange(SETTINGS_REF.PATIENT_FILE_ID).setValue(newPatientFileId); // Update stored ID
    console.log(`Patient History re-uploaded. New File ID: ${newPatientFileId}`);

    // --- Update the Vector Store ---
    // We need BOTH the *existing* checkup ID and the *new* patient ID
    let filesToUpdate = [];
    if (checkupFileId) {
        filesToUpdate.push(checkupFileId);
    }
    filesToUpdate.push(newPatientFileId);

    console.log(`Updating Vector Store (${VECTOR_STORE_ID}) with Checkup File (${checkupFileId || 'None'}) and new Patient File (${newPatientFileId})...`);
    const success = updateVectorStoreFiles(apiKey, VECTOR_STORE_ID, filesToUpdate); // Use the helper function

    if (success) {
      console.log("Vector Store updated successfully with new Patient History.");
      SpreadsheetApp.getActiveSpreadsheet().toast("Knowledge update successful: Patient History uploaded and Vector Store updated.");
    } else {
      throw new Error("Failed to update Vector Store with new Patient History file.");
    }

  } catch (error) {
    console.error("Error in uploadPatientHistoryToOpenAI:", error);
    Logger.log("Error in uploadPatientHistoryToOpenAI: " + error.message + "\n" + error.stack);
    SpreadsheetApp.getUi().alert("Error during patient history update: " + error.message + ". Check Logs for details.");
  }
}

// --- Re-use helper functions ---
// sheetDataToJson (defined inline above for clarity, could be global)
// uploadFileToOpenAI (defined in Section 4)
// updateVectorStoreFiles (defined in Section 4)

Explanation: Uploading Only Patient History

1. Setup & Get Existing File ID: Gets API key, checks VECTOR_STORE_ID. Crucially, it reads the currently stored File ID for the Checkup History from the Settings sheet. This is needed because updating a Vector Store replaces *all* its files.
2. Convert Patient History to JSON: Uses the same sheetDataToJson logic to process the Patient History sheet, adding row numbers as IDs.
3. Upload New Patient History File: Calls uploadFileToOpenAI to upload the updated patient history JSON. Gets back a new File ID for this version of the patient history. Updates the ID stored in the Settings sheet.
4. Update Vector Store: Creates an array containing the existing Checkup History File ID (if found) and the new Patient History File ID. Calls updateVectorStoreFiles with this array. This ensures the Vector Store remains linked to the unchanged checkup history while getting the latest patient history.
5. Feedback & Error Handling: Similar logging, toasts, and alerts as the full update function. Includes a warning if the checkup history ID is missing.

---

6. Supporting Functions & Configuration

These are other essential pieces of the script that enable the core features.

Code Snippet: callFunction

// Routes function calls from the AI to the correct Apps Script function
function callFunction(functionName, args) {
  console.log(`Attempting to call function: ${functionName} with args: ${JSON.stringify(args)}`);
  try {
    switch (functionName) {
      case 'addPatientHistory':
        // Validate arguments (basic example)
        if (!args.aspect || !args.patienthistory) {
           throw new Error("Missing required arguments 'aspect' or 'patienthistory' for addPatientHistory.");
        }
        return addPatientHistory(args.aspect, args.patienthistory);

      case 'updatePatientHistory':
        // Validate arguments (basic example)
         if (!args.patienthistory_id || !args.aspect || !args.patienthistory) {
           throw new Error("Missing required arguments 'patienthistory_id', 'aspect', or 'patienthistory' for updatePatientHistory.");
         }
        // Convert ID to number if possible, handle potential errors
        const id = parseInt(args.patienthistory_id);
        if (isNaN(id)) {
            throw new Error(`Invalid patienthistory_id: ${args.patienthistory_id}. It should be a number (row number).`);
        }
        return updatePatientHistory(id, args.aspect, args.patienthistory);

      // Add cases for other functions if needed

      default:
        console.log(`Function ${functionName} not found.`);
        return { status: "error", message: `Function ${functionName} not implemented.` };
    }
  } catch (error) {
      console.error(`Error calling function ${functionName}:`, error);
      Logger.log(`Error in callFunction (${functionName}): ${error.message}\n${error.stack}`);
      // Return an error object that the AI might understand
      return { status: "error", message: `Error executing function ${functionName}: ${error.message}` };
  }
}

Explanation: callFunction

• Router: Acts as a central dispatcher. When the aiChat function receives a tool call request from OpenAI, it passes the function name and arguments to callFunction.
• Switch Statement: Uses a switch statement to determine which actual Apps Script function (addPatientHistory, updatePatientHistory) to execute based on the functionName provided by the AI.
• Argument Handling: Extracts arguments (args) sent by the AI and passes them to the target function. Includes basic validation to check if required arguments are present and attempts to convert the ID to a number for updatePatientHistory.
• Return Value: Returns the result from the called function (e.g., a success message or status object) back to aiChat. This result is then sent back to OpenAI in the second API call.
• Error Handling: Includes a try...catch block and a default case to handle situations where the AI tries to call an unknown function or provides invalid arguments. Returns an error object to inform the AI.

Code Snippet: addPatientHistory & updatePatientHistory

// Adds a new row to the Patient History sheet
function addPatientHistory(aspect, patienthistory) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const historySheet = ss.getSheetByName(SHEET.PATIENT_HISTORY);
    if (!historySheet) throw new Error(`Sheet not found: ${SHEET.PATIENT_HISTORY}`);

    const newRowData = [new Date(), aspect, patienthistory]; // Example: Timestamp, Aspect, Detail
    // Find the first empty row to append data correctly
    const lastRow = historySheet.getLastRow();
    const firstEmptyRow = lastRow + 1;

    // Assuming headers are ID, Timestamp, Aspect, Detail - Adjust indices as needed!
    // We let the upload function handle the ID (row number) later.
    // Here, we write Timestamp (col 2), Aspect (col 3), Detail (col 4)
    historySheet.getRange(firstEmptyRow, 2, 1, newRowData.length).setValues([newRowData]);
    console.log(`Added to Patient History: Aspect=${aspect}, Detail=${patienthistory} at row ${firstEmptyRow}`);

    // Optionally trigger patient history knowledge update immediately
    // uploadPatientHistoryToOpenAI(); // Uncomment if desired, but can be slow/costly

    return { status: "success", message: `Patient history added: ${aspect} - ${patienthistory}` };
  } catch (error) {
    console.error("Error in addPatientHistory:", error);
    Logger.log("Error in addPatientHistory: " + error.message + "\n" + error.stack);
    return { status: "error", message: `Failed to add patient history: ${error.message}` };
  }
}

// Updates an existing row in the Patient History sheet
function updatePatientHistory(patienthistory_id, aspect, patienthistory) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const historySheet = ss.getSheetByName(SHEET.PATIENT_HISTORY);
     if (!historySheet) throw new Error(`Sheet not found: ${SHEET.PATIENT_HISTORY}`);

    // patienthistory_id corresponds to the row number provided by the AI
    const rowToUpdate = parseInt(patienthistory_id); // Already parsed in callFunction, but good practice
    if (isNaN(rowToUpdate) || rowToUpdate <= 1) { // Row must be > 1 (data rows)
        throw new Error(`Invalid row number: ${patienthistory_id}. Must be a number greater than 1.`);
    }

    // Check if row exists within sheet bounds
    if (rowToUpdate > historySheet.getMaxRows()) {
         throw new Error(`Row ${rowToUpdate} does not exist in the sheet.`);
    }

    // Assuming headers are ID, Timestamp, Aspect, Detail
    // Update Timestamp (col 2), Aspect (col 3), Detail (col 4)
    historySheet.getRange(rowToUpdate, 2).setValue(new Date()); // Update timestamp
    historySheet.getRange(rowToUpdate, 3).setValue(aspect);
    historySheet.getRange(rowToUpdate, 4).setValue(patienthistory);
    console.log(`Updated Patient History at row ${rowToUpdate}: Aspect=${aspect}, Detail=${patienthistory}`);

    // Optionally trigger patient history knowledge update immediately
    // uploadPatientHistoryToOpenAI(); // Uncomment if desired

    return { status: "success", message: `Patient history updated for ID ${patienthistory_id}` };
  } catch (error) {
    console.error("Error in updatePatientHistory:", error);
    Logger.log("Error in updatePatientHistory: " + error.message + "\n" + error.stack);
    return { status: "error", message: `Failed to update patient history for ID ${patienthistory_id}: ${error.message}` };
  }
}

Explanation: Modifying Sheet Data

• addPatientHistory:
  • Takes the `aspect` and `patienthistory` detail provided by the AI.
  • Gets the Patient History sheet.
  • Constructs the data for the new row (e.g., adding a current timestamp).
  • Finds the next empty row and writes the new data using getRange().setValues(). The column indices (e.g., starting at column 2) depend on your sheet structure (assuming Column A might be 'ID').
  • Logs the action and returns a success status object.
  • (Optionally, could trigger uploadPatientHistoryToOpenAI immediately, but this is often better done manually/periodically via checkbox/menu).
• updatePatientHistory:
  • Takes the `patienthistory_id` (row number), `aspect`, and `patienthistory` detail.
  • Gets the Patient History sheet.
  • Validates the row number (`patienthistory_id`) to ensure it's a valid row within the sheet.
  • Uses getRange(row, column).setValue() to update the specific cells (Timestamp, Aspect, Detail) in the identified row.
  • Logs the action and returns a success status object.
  • (Optionally could trigger knowledge update).
• Error Handling: Both functions include try...catch blocks and return status objects to inform the AI (via callFunction) whether the operation succeeded or failed.

Code Snippet: Constants

// --- CONFIGURATION CONSTANTS ---

// !!! IMPORTANT: Replace with your actual OpenAI Vector Store ID !!!
const VECTOR_STORE_ID = "vs_xxxxxxxxxxxxxxxxxxxxxxxx"; // e.g., vs_abc123xyz

// Sheet Names
const SHEET = {
  AI_CHAT: "AI Chat",
  CHECKUP_HISTORY: "Checkup History",
  PATIENT_HISTORY: "Patient History",
  SETTINGS: "Settings"
};

// Cell References in AI Chat Sheet
const AI_CHAT_REF = {
  USER_MESSAGE: 1,          // Column A
  DOCTOR_REPLY: 2,          // Column B
  UPDATE_KNOWLEDGE: "G1",   // Checkbox for full update
  UPDATE_PATIENT_HISTORY: "H1" // Checkbox for patient history only update
  // Add other references if needed
};

// Cell References in Settings Sheet
const SETTINGS_REF = {
  OPENAI_KEY: "B2",          // Cell containing the OpenAI API Key
  MODEL_SELECTION: "B3",     // Cell containing the desired model (e.g., gpt-4-turbo)
  CHECKUP_FILE_ID: "B4",     // Cell to store the uploaded Checkup History File ID
  PATIENT_FILE_ID: "B5"      // Cell to store the uploaded Patient History File ID
  // Add other settings references if needed
};

// Column References in Patient History Sheet (Example - Adjust to your sheet!)
const PATIENT_HISTORY_COLS = {
    ID: 1,          // Column A (Often managed automatically/by row number)
    TIMESTAMP: 2,   // Column B
    ASPECT: 3,      // Column C
    DETAIL: 4       // Column D
}

// (Add constants for Checkup History columns if needed)

// --- END CONFIGURATION CONSTANTS ---

Explanation: Constants

• Centralized Configuration: These objects (VECTOR_STORE_ID, SHEET, AI_CHAT_REF, SETTINGS_REF, PATIENT_HISTORY_COLS) define fixed values used throughout the script (like sheet names, specific cell addresses, and column numbers).
• Maintainability: Using constants makes the code significantly cleaner and easier to update. If you decide to rename a sheet (e.g., "AI Chat" to "Chat Interface") or move the API key cell, you only need to change the value in *one* place (the constant definition) instead of searching and replacing it everywhere in the code.
• Readability: Code like sheet.getRange(row, AI_CHAT_REF.DOCTOR_REPLY) is much easier to understand than sheet.getRange(row, 2).
• VECTOR_STORE_ID: This is particularly important. You **must** replace the placeholder with your actual ID obtained from OpenAI after creating a Vector Store.

---

7. Conclusion

This script provides a powerful example of integrating Google Sheets with OpenAI's API to create a functional medical chatbot interface with memory and knowledge update capabilities.

• It leverages Google Sheets as a user-friendly front-end and data store.
• Google Apps Script acts as the crucial intermediary, handling user input triggers (onEdit), orchestrating API calls to OpenAI, executing local functions requested by the AI, and updating the sheet.
• OpenAI provides the conversational AI (via models like GPT-4), the ability to understand and request actions (Function Calling / Tool Use), and persistent knowledge storage through file uploads and Vector Stores.
• The detailed systemMessage is vital for guiding the AI's behavior, defining its persona, and instructing it on how and when to use tools and retrieved knowledge.
• The Knowledge update features (upload... functions) demonstrate how to prepare sheet data (as JSON), upload it as Files to OpenAI, and link those files to a Vector Store, keeping the AI's reference material current.

Key Takeaways for Beginners:

1. The onEdit(e) simple trigger is powerful for initiating actions based on specific cell changes (like typing a message or checking a box).
2. The aiChat function manages the multi-step conversation flow: send message -> potentially receive tool call -> execute tool -> send tool result -> receive final response.
3. Functions called by the AI (like addPatientHistory) modify the Google Sheet directly, acting on the AI's instructions.
4. Updating AI knowledge involves converting Sheet data to JSON, using UrlFetchApp to interact with the OpenAI Files API (/v1/files) and Vector Store API (/v1/vector_stores).
5. Correct configuration (API Key in Settings, your specific VECTOR_STORE_ID, matching Sheet Names, and accurate Constants) is absolutely essential for the script to function. Double-check these!
6. Use console.log and Logger.log liberally during development to understand the flow and troubleshoot errors in the Apps Script execution logs.

Remember that this is a starting point. You can expand on this by adding more complex functions, improving error handling, refining the AI's instructions, or integrating other Google Workspace services.