AI Consultation Assistant in Google Sheets

Build an AI assistant in Google Sheets to draft patient responses in seconds.

Introduction

30 Second Summary

Doctors at Puskesmas handle dozens of telemedicine consultations daily through Mobile JKN. Typing out each response from scratch takes minutes per patient, eating into time that could be spent on actual care.

In this project, you will build an AI-powered consultation response assistant inside Google Sheets that generates professional medical response drafts in Bahasa Indonesia. Paste a patient's complaint, click one button, and get a draft response in seconds.

What You'll Build

A Google Sheets-based AI assistant that connects to Google Gemini to generate telemedicine consultation response drafts for Puskesmas general practitioners.

By the end of this project, you'll have:

A Google Sheet with a custom "Generate Response" button that triggers AI-powered draft responses in Bahasa Indonesia.
A system prompt tailored for a Puskesmas general practitioner (dokter umum) with preventive, promotive, and curative focus.
An automatic consultation log with timestamps and status tracking for every patient interaction.
Secret Mission: automatic complaint categorization that classifies each consultation by medical category (Kardiovaskular, Respirasi, Gastrointestinal, and more).

Are there any prerequisites?

You only need a Google account (Gmail). No coding experience is required. This project uses Google Apps Script and the free tier of the Gemini API, so there are no costs involved.

Get Your Free Gemini API Key

Your AI consultation assistant needs a brain. That brain is Google Gemini, a powerful AI model that can understand medical questions and generate professional responses in Bahasa Indonesia.

To connect your Google Sheet to Gemini, you need an API key. In this step, you will create your free key and confirm it works.

In this step, get ready to:

Sign in to Google AI Studio with your Google account.
Create a free Gemini API key.
Verify the key works by testing it in AI Studio's chat.

Sign in to Google AI Studio

Google AI Studio is a free web tool where you can create API keys and test AI models directly in your browser.

Open your browser and go to aistudio.google.com.
Click Sign in to Google AI Studio.
Sign in with your Google account (the same one you use for Gmail).
If prompted, accept the Terms of Service to continue.

What is Google AI Studio?

Google AI Studio is a free platform for experimenting with Google's AI models. You will use it today to create an API key. You can also chat directly with AI models here to test ideas.

Create your free API key

An API key is a unique code that identifies your project when it calls the Gemini AI. Google's free tier gives you up to 500 requests per day, more than enough for daily consultations.

In Google AI Studio, click Get API key in the left sidebar.
Click Create API key.
If asked to select a project, choose the default project that Google AI Studio created for you.
Your new API key appears on screen. Copy it immediately.
Paste the key into a safe, private place such as a note in Apple Notes or a text file on your Desktop. You will need it in the next step.

Keep your API key private

Your API key is like a password. Never share it publicly, post it on social media, or include it in files you share with others. Anyone with your key can make requests on your behalf.

Create API key button is greyed out?

This can happen if your Google account belongs to an organization that restricts API key creation. Try creating a new project that is not associated with any organization from the same API Keys page in AI Studio.

Help me fix the Create API key button being unavailable

Test that your API key works

Before building anything, confirm that your account has access to Gemini models. You can do this right inside AI Studio's built-in chat.

In the left sidebar of Google AI Studio, click the + button to open a new prompt.
Type a simple message like Halo, apa kabar? into the chat input box.
Press Enter or click the send button.
Wait a few seconds for the AI to respond with a message.

If you see a response from the AI, your setup is working. This confirms your account has access to Gemini models and your key will work when connected to Google Sheets.

Why test in the chat instead of with code?

The AI Studio chat uses the same Gemini models your API key will access. If the chat responds successfully, your account is active and your key will work when you connect it to Google Sheets.

Not getting a response?

Make sure you accepted the Terms of Service when you first signed in. If your account is brand new, it may take a minute for access to activate. Try refreshing the page and sending the message again.

Help me troubleshoot why AI Studio chat is not responding

Your API key is ready to go. Next up, you will create the Google Sheet that will become your daily consultation assistant.

Create Your Consultation Sheet

You have a working Gemini API key ready to go. Now you need a place for the AI assistant to live.

In this step, you will create a Google Sheet that serves as both your consultation interface and your automatic patient log. You will also write the first piece of Google Apps Script code that powers the custom menu button.

In this step, get ready to:

Create a Google Sheet with the consultation columns.
Add a custom AI Assistant menu using Google Apps Script.
Write a function that reads the patient complaint from the active row.

Create your consultation spreadsheet

Your AI assistant needs a structured home. A Google Sheet with dedicated columns keeps your patient interactions organized and lets the script know exactly where to read complaints and write responses.

Open a new browser tab and go to sheets.new to create a blank spreadsheet.
Click Untitled spreadsheet at the top left and rename it to AI Konsultasi Puskesmas.
Click cell A1 and type Timestamp.
Click cell B1 and type Keluhan Pasien.
Click cell C1 and type Respons AI.
Click cell D1 and type Status.

You should see four column headers across row 1 of your sheet.

What are these columns for?

Each column has a specific job in your consultation workflow.

Timestamp records exactly when the AI generated a response.
Keluhan Pasien is where you paste the patient's complaint from Mobile JKN.
Respons AI is where the AI draft will appear after you click the button.
Status tracks whether the response is still a Draft or has been Sent.

Open Apps Script and store your API key

Google Apps Script is a coding environment built into Google Sheets. It lets you add custom buttons and connect to external services like the Gemini API.

In your Google Sheet, click Extensions in the top menu bar.
Click Apps Script. A new tab opens with the Apps Script editor.
You will see a file called Code.gs with a blank myFunction inside. Select all the existing code and delete it.

Before writing any code, you need to store your Gemini API key securely in Script Properties so it never appears directly in your code.

At the left side of the Apps Script editor, click Project Settings (the gear icon).
Scroll down to the Script Properties section.
Click Add script property.
In the Property field, type GEMINI_API_KEY.
In the Value field, paste the API key you saved in Step 1.
Click Save script properties.

Why store the key in Script Properties?

Script Properties is a secure key-value store built into Apps Script. Your API key stays hidden from anyone who might view the code, and you can update it later without editing the script itself.

At the left side of the editor, click Editor (the < > icon) to return to your code.

Now add the configuration constants that tell your script which AI model to use and where to find the API key. Paste this code into the empty Code.gs file:

const GEMINI_API_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
const GEMINI_MODEL = 'gemini-2.5-flash';
const API_URL = `https://generativelanguage.googleapis.com/v1beta/models/${GEMINI_MODEL}:generateContent`;

const SYSTEM_PROMPT = ``;

What does this code do?

GEMINI_API_KEY retrieves your API key from the secure Script Properties you just set up.
GEMINI_MODEL specifies gemini-2.5-flash, the free-tier model you will use.
API_URL builds the full endpoint URL for the Gemini generateContent API.
SYSTEM_PROMPT is empty for now. You will fill it with medical instructions in the next step.

Next, add the function that creates your custom menu. Paste this code directly below the constants:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('\u{1F916} AI Assistant')
    .addItem('Generate Response', 'generateResponse')
    .addToUi();
}

What does onOpen do?

The onOpen() function runs automatically every time you open the spreadsheet. It creates a custom menu called AI Assistant in the menu bar with a single item: Generate Response. Clicking that item will eventually trigger the AI to respond.

Save the script by pressing Cmd+S (macOS) or Ctrl+S (Windows).
Switch back to your Google Sheet tab in the browser.
Refresh the page (Cmd+R on macOS or Ctrl+R on Windows).
After a few seconds, you should see a new menu item AI Assistant appear in the menu bar to the right of Help.

Don't see the AI Assistant menu?

Make sure you saved Code.gs before refreshing the sheet.
Wait 5-10 seconds after refresh. Custom menus can take a moment to load.
Check that your onOpen function name is spelled exactly as shown. Capitalization matters.

Help me troubleshoot my custom menu not appearing in Google Sheets.

Write the function that reads the patient complaint

The custom menu needs a function to call when clicked. This function will read whichever row you have selected, grab the complaint text from column B, and validate that the input is usable.

Switch back to the Apps Script editor tab.
Below the onOpen function, paste the following code:

function generateResponse() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = sheet.getActiveCell().getRow();

  // Prevent running on the header row
  if (row <= 1) {
    SpreadsheetApp.getUi().alert('Pilih baris yang berisi keluhan pasien (bukan header).');
    return;
  }

  // Read the patient complaint from column B
  const complaint = sheet.getRange(row, 2).getValue();

  // Check that the complaint cell is not empty
  if (!complaint) {
    SpreadsheetApp.getUi().alert('Kolom Keluhan Pasien kosong. Silakan isi terlebih dahulu.');
    return;
  }
}

What does this code do?

sheet.getActiveCell().getRow() finds which row your cursor is currently on.
The row <= 1 check prevents the script from accidentally running on the header row.
sheet.getRange(row, 2).getValue() reads the text from column B (Keluhan Pasien) on the selected row.
The empty-check ensures the script stops early with a helpful message if you forgot to paste a complaint.

Save the script (Cmd+S or Ctrl+S).
Switch to your Google Sheet tab.
Click on cell B2 (leave it empty for now).
Click the AI Assistant menu, then click Generate Response.

Google will ask you to authorize the script the first time you run it. This is normal for any Apps Script that interacts with your spreadsheet.

Click Continue in the authorization dialog.
Select your Google account.
If you see a "Google hasn't verified this app" warning, click Advanced, then click Go to AI Konsultasi Puskesmas (unsafe).
Click Allow to grant permissions.

You should see an alert box saying: "Kolom Keluhan Pasien kosong. Silakan isi terlebih dahulu." This confirms your script is reading the active row and validating the input correctly.

Seeing a different error?

If the alert says "Pilih baris yang berisi keluhan pasien (bukan header)", your cursor is on row 1. Click any cell in row 2 or below before running.
If you see a red error about permissions, make sure you completed the authorization flow and clicked Allow.

Help me fix an authorization or script error in Google Apps Script.

✔️ Awesome, I've got everything!

Great. Double check you saved Code.gs and that your custom menu is visible in the sheet.

ⓧ I'd like to double check the full code

Here is the complete Code.gs you should have at this point:

const GEMINI_API_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
const GEMINI_MODEL = 'gemini-2.5-flash';
const API_URL = `https://generativelanguage.googleapis.com/v1beta/models/${GEMINI_MODEL}:generateContent`;

const SYSTEM_PROMPT = ``;

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('\u{1F916} AI Assistant')
    .addItem('Generate Response', 'generateResponse')
    .addToUi();
}

function generateResponse() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = sheet.getActiveCell().getRow();

  if (row <= 1) {
    SpreadsheetApp.getUi().alert('Pilih baris yang berisi keluhan pasien (bukan header).');
    return;
  }

  const complaint = sheet.getRange(row, 2).getValue();

  if (!complaint) {
    SpreadsheetApp.getUi().alert('Kolom Keluhan Pasien kosong. Silakan isi terlebih dahulu.');
    return;
  }
}

Your consultation sheet is set up and your script can read patient complaints. Next up, you will connect the Gemini API and craft a medical system prompt so the AI responds like a Puskesmas dokter umum.

Connect Gemini AI with Your Medical System Prompt

Your Google Sheet can read patient complaints from column B. Now you need to connect it to Gemini AI so it can actually generate response drafts.

In this step, you will store your API key securely, write a medical system prompt in Bahasa Indonesia, and wire everything together so one button click calls the Gemini API and fills in the AI response.

In this step, get ready to:

Connect your script to the Gemini API using your stored API key.
Write a system prompt tailored for a Puskesmas general practitioner.
Test the full flow end-to-end with a sample patient complaint.

Store your API key as a Script Property

Your code already reads the API key from Script Properties. Now you need to store your actual key there so the script can find it at runtime.

In the Apps Script editor, click Project Settings in the left sidebar (the gear icon).
Scroll down to the Script Properties section.
Click Add script property.
In the Property field, enter GEMINI_API_KEY.
In the Value field, paste the Gemini API key you saved earlier.
Click Save script properties.

Why store the key in Script Properties?

Script Properties act like environment variables for your project. Your API key stays hidden from the code editor and is never accidentally shared when you copy code.

The line PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY') in your code retrieves the key at runtime without exposing it.

Don't see the Script Properties section?

Make sure you clicked the gear icon on the left sidebar, not the Editor icon (the angle brackets). Scroll all the way down in Project Settings to find Script Properties.

Help me find Script Properties in the Apps Script editor

Add the Gemini API connection code

With the key stored, you can now add the code that sends the patient complaint to Gemini and writes the AI response back to the sheet.

Switch back to the Editor view by clicking the angle brackets icon in the left sidebar.
In Code.gs, find the closing brace of the if (!complaint) check. Add the following code directly below it, before the final } that closes the generateResponse function:

  const payload = {
    system_instruction: {
      parts: [{ text: SYSTEM_PROMPT }]
    },
    contents: [
      {
        parts: [{ text: complaint }]
      }
    ],
    generationConfig: {
      temperature: 1.0,
      maxOutputTokens: 1024
    }
  };

  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'x-goog-api-key': GEMINI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(API_URL, options);
    const data = JSON.parse(response.getContentText());
    const aiResponse = data.candidates[0].content.parts[0].text;

    // Write timestamp, AI response, and status to the sheet
    sheet.getRange(row, 1).setValue(new Date());
    sheet.getRange(row, 3).setValue(aiResponse);
    sheet.getRange(row, 4).setValue('Draft');

    SpreadsheetApp.getUi().alert('✅ Respons AI berhasil dibuat! Silakan review di kolom C.');
  } catch (error) {
    SpreadsheetApp.getUi().alert('❌ Error: ' + error.message);
  }

What does this code do?

payload packages the system prompt and patient complaint into the format the Gemini API expects.
system_instruction tells Gemini how to behave (your medical persona). contents is the actual patient complaint.
options configures the HTTP request. The x-goog-api-key header authenticates your request.
UrlFetchApp.fetch() sends the request to the Gemini API and returns the response.
On success, the code writes the timestamp to column A, the AI response to column C, and Draft to column D.

Write the system prompt for Puskesmas consultations

The system prompt is the most important part. It defines how AI responds to every patient complaint.

In Code.gs, find the line that says const SYSTEM_PROMPT = ``; near the top of the file.
Replace that entire line with the following code:

const SYSTEM_PROMPT = `Kamu adalah asisten dokter umum yang bekerja di Puskesmas (Pusat Kesehatan Masyarakat) di Indonesia.

Peranmu:
- Membantu dokter menyusun draft jawaban konsultasi telemedicine BPJS melalui Mobile JKN
- Fokus pada pelayanan preventif, promotif, dan kuratif sesuai kompetensi dokter umum Puskesmas

Aturan menjawab:
- Gunakan Bahasa Indonesia yang hangat, empatik, dan mudah dipahami masyarakat umum
- Hindari istilah medis yang terlalu teknis, gunakan bahasa awam
- Jawaban harus SINGKAT (maksimal 3-4 paragraf pendek)
- Sertakan saran praktis yang bisa dilakukan pasien di rumah
- Jika gejala menunjukkan kondisi darurat (red flag), SELALU sarankan untuk segera ke IGD/RS
- Jika keluhan di luar kompetensi dokter umum, sarankan rujukan ke spesialis
- Akhiri dengan kalimat yang ramah dan tawaran untuk konsultasi lanjutan jika diperlukan
- JANGAN memberikan diagnosis pasti, gunakan frasa seperti "kemungkinan" atau "bisa jadi"
- JANGAN meresepkan obat spesifik dengan dosis, cukup sarankan umum (misalnya "obat pereda nyeri yang dijual bebas")`;

What does this system prompt do?

This prompt shapes the AI into a Puskesmas dokter umum persona. It enforces warm Bahasa Indonesia, avoids technical jargon, and keeps responses short for Mobile JKN.

The safety rules are critical. The AI will never give a definitive diagnosis, never prescribe specific medications with doses, and will always flag emergencies with a referral to IGD or a specialist.

Save the project by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Seeing a syntax error after saving?

Make sure the backtick (`) at the start of the prompt matches the closing backtick followed by a semicolon (`;). Template literals use backticks, not regular quotes.

Also check that the old empty const SYSTEM_PROMPT = ``; line is fully replaced. You should not have two SYSTEM_PROMPT declarations.

Help me fix a syntax error in my Apps Script code

✔️ Awesome, I've got everything!

Great. Double check you have saved Code.gs with no errors showing in the editor.

ⓧ I'd like to double check the full code

Here is the complete Code.gs file at this point. Compare yours against it:

const GEMINI_API_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
const GEMINI_MODEL = 'gemini-2.5-flash';
const API_URL = `https://generativelanguage.googleapis.com/v1beta/models/${GEMINI_MODEL}:generateContent`;

const SYSTEM_PROMPT = `Kamu adalah asisten dokter umum yang bekerja di Puskesmas (Pusat Kesehatan Masyarakat) di Indonesia.

Peranmu:
- Membantu dokter menyusun draft jawaban konsultasi telemedicine BPJS melalui Mobile JKN
- Fokus pada pelayanan preventif, promotif, dan kuratif sesuai kompetensi dokter umum Puskesmas

Aturan menjawab:
- Gunakan Bahasa Indonesia yang hangat, empatik, dan mudah dipahami masyarakat umum
- Hindari istilah medis yang terlalu teknis, gunakan bahasa awam
- Jawaban harus SINGKAT (maksimal 3-4 paragraf pendek)
- Sertakan saran praktis yang bisa dilakukan pasien di rumah
- Jika gejala menunjukkan kondisi darurat (red flag), SELALU sarankan untuk segera ke IGD/RS
- Jika keluhan di luar kompetensi dokter umum, sarankan rujukan ke spesialis
- Akhiri dengan kalimat yang ramah dan tawaran untuk konsultasi lanjutan jika diperlukan
- JANGAN memberikan diagnosis pasti, gunakan frasa seperti "kemungkinan" atau "bisa jadi"
- JANGAN meresepkan obat spesifik dengan dosis, cukup sarankan umum (misalnya "obat pereda nyeri yang dijual bebas")`;

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🤖 AI Assistant')
    .addItem('Generate Response', 'generateResponse')
    .addToUi();
}

function generateResponse() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = sheet.getActiveCell().getRow();

  if (row <= 1) {
    SpreadsheetApp.getUi().alert('Pilih baris yang berisi keluhan pasien (bukan header).');
    return;
  }

  const complaint = sheet.getRange(row, 2).getValue();

  if (!complaint) {
    SpreadsheetApp.getUi().alert('Kolom Keluhan Pasien kosong. Silakan isi terlebih dahulu.');
    return;
  }

  const payload = {
    system_instruction: {
      parts: [{ text: SYSTEM_PROMPT }]
    },
    contents: [
      {
        parts: [{ text: complaint }]
      }
    ],
    generationConfig: {
      temperature: 1.0,
      maxOutputTokens: 1024
    }
  };

  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'x-goog-api-key': GEMINI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(API_URL, options);
    const data = JSON.parse(response.getContentText());
    const aiResponse = data.candidates[0].content.parts[0].text;

    sheet.getRange(row, 1).setValue(new Date());
    sheet.getRange(row, 3).setValue(aiResponse);
    sheet.getRange(row, 4).setValue('Draft');

    SpreadsheetApp.getUi().alert('✅ Respons AI berhasil dibuat! Silakan review di kolom C.');
  } catch (error) {
    SpreadsheetApp.getUi().alert('❌ Error: ' + error.message);
  }
}

Test the full flow with a sample complaint

Everything is wired up. Time to test with a real patient complaint and see AI generate a response.

Switch back to your Google Sheet tab in the browser.
Reload the page so the custom menu refreshes.
Click on cell B2 and type a sample complaint:

Dok, sudah 3 hari saya batuk-batuk dan tenggorokan terasa gatal. Tidak ada demam tapi agak lemas.

Make sure your cursor is still on row 2 (click any cell in row 2).
Click the 🤖 AI Assistant menu at the top of the sheet.
Click Generate Response.

What happens next?

Google will ask you to authorize the script the first time you run it. Click Continue, choose your Google account, then click Allow to grant the script permission to access your sheet and make external requests.

After authorization, run Generate Response again from the menu. Within 5-10 seconds you should see an alert confirming the response was generated.

Check column A (Timestamp). You should see today's date and time.
Check column C (Respons AI). You should see a warm, empathetic response in Bahasa Indonesia advising the patient about their cough.
Check column D (Status). You should see Draft.

Seeing an error alert instead of a response?

If the error mentions API key, go back to Project Settings and confirm GEMINI_API_KEY is spelled exactly right with no extra spaces in the value.
If the error mentions UrlFetchApp, make sure you authorized the script when prompted. Try running it again from the menu.
If nothing happens when you click the menu, reload the sheet and try once more. The onOpen function must run first to register the menu.

Help me debug my Gemini API connection in Apps Script

Your AI assistant is generating response drafts. Next up, you will polish the workflow with automatic timestamps and status tracking for daily use.

Polish the Workflow for Daily Use

Your AI assistant is generating responses. Now it's time to make sure the full workflow is production-ready for your daily consultations.

A polished workflow means every consultation is automatically logged with a timestamp, every response is tracked with a status, and you can move from complaint to reply in under 60 seconds.

In this step, get ready to:

Verify automatic timestamp logging works.
Practice the Status column workflow (Draft to Sent).
Run through the complete daily workflow end-to-end.

Verify automatic timestamp logging

Every consultation needs a timestamp so you can track when each patient interaction happened. Your code already handles this automatically.

Switch back to the Apps Script editor tab in your browser.
In your generateResponse() function, find the try block. Look at the three lines after const aiResponse:

    // Write the current date and time to column A
    sheet.getRange(row, 1).setValue(new Date());
    // Write the AI-generated response to column C
    sheet.getRange(row, 3).setValue(aiResponse);
    // Mark this row's status as Draft in column D
    sheet.getRange(row, 4).setValue('Draft');

What do these three lines do?

These three lines fire every time the AI successfully generates a response.

sheet.getRange(row, 1).setValue(new Date()) writes the current date and time into column A. This is your automatic consultation log.
sheet.getRange(row, 3).setValue(aiResponse) places the AI draft into column C (Respons AI).
sheet.getRange(row, 4).setValue('Draft') marks the status as "Draft" so you know which responses still need your review.

Return to your Google Sheet tab.
Click on cell B2 and type a sample complaint:

Dok, sudah 3 hari saya batuk berdahak dan agak sesak napas. Tidak demam tapi badan terasa lemas.

Make sure your cursor is still on row 2 (click any cell in row 2).
Click the AI Assistant menu at the top, then click Generate Response.
After the success alert appears, check column A (Timestamp). You should see today's date and time automatically filled in.
Check column D (Status). You should see the word Draft automatically filled in.

Timestamp or Status not appearing?

If column A shows a date but column D is empty, check that your try block has all three setValue lines after const aiResponse.
Make sure you saved the script (Ctrl+S or Cmd+S) in the Apps Script editor before running.

Help me debug why my timestamp or status isn't appearing in my Google Sheet.

Use the Status column for tracking

The Status column helps you tell apart responses you've already sent to patients from ones still waiting for your review.

Look at cell D2 in your sheet. It should say Draft from the test you just ran.
Read the AI response in column C (Respons AI). This is the draft you would review before sending to a patient.
Double-click cell D2 to edit it.
Replace Draft with Sent and press Enter.

Why is Status manual instead of automatic?

The AI generates a draft, but you are the doctor. You need to review every response before sending it to the patient. Manually changing the status to Sent confirms that you have reviewed and approved the response.

This also gives you a clear audit trail. At a glance, you can see which consultations are done and which still need attention.

Test the complete daily workflow

Now run through the full workflow exactly as you would during a real consultation shift on Mobile JKN.

Click on cell B3 to start a new row.
Paste this sample complaint (simulating what a patient sends through Mobile JKN):

Selamat pagi dok, anak saya umur 4 tahun demam tinggi sejak semalam. Suhu 38.5 derajat. Sudah dikasih kompres tapi belum turun. Apa yang harus saya lakukan?

Make sure your cursor is on row 3 (click any cell in row 3).
Click AI Assistant > Generate Response.
Wait for the success alert and click OK.
Read the AI response in cell C3. Verify it gives warm, practical advice in Bahasa Indonesia about managing a child's fever.
Check that column A3 has a timestamp and column D3 says Draft.
Select the text in cell C3 and copy it (Cmd+C on macOS or Ctrl+C on Windows). In real life, you would paste this into your Mobile JKN reply.
Double-click cell D3 and change Draft to Sent, then press Enter.

Your sheet should now have two rows of consultation data. Row 2 and row 3 each have a timestamp, complaint, AI response, and a status.

Your daily workflow at a glance

Here is the complete flow you will use every time a patient contacts you through Mobile JKN:

Copy the patient's complaint from Mobile JKN.
Paste it into column B on the next empty row.
Click AI Assistant > Generate Response.
Review the draft in column C. Edit if needed.
Copy the response back into Mobile JKN.
Change Status from Draft to Sent.

AI response doesn't sound right?

If the response is in English instead of Bahasa Indonesia, check that your SYSTEM_PROMPT variable still contains the full Indonesian instructions. It may have been accidentally deleted.
If the response is too long or too technical, you can adjust the system prompt later to be more specific. The prompt is your main lever for controlling tone.

Help me improve my AI medical response quality.

✔️ Awesome, I've got everything!

Great. Double check you've saved your script file in the Apps Script editor.

ⓧ I'd like to double check the full code

Here is the complete Code.gs file. Compare it against yours in the Apps Script editor.

const GEMINI_API_KEY = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
const GEMINI_MODEL = 'gemini-2.5-flash';
const API_URL = `https://generativelanguage.googleapis.com/v1beta/models/${GEMINI_MODEL}:generateContent`;

const SYSTEM_PROMPT = `Kamu adalah asisten dokter umum yang bekerja di Puskesmas (Pusat Kesehatan Masyarakat) di Indonesia.

Peranmu:
- Membantu dokter menyusun draft jawaban konsultasi telemedicine BPJS melalui Mobile JKN
- Fokus pada pelayanan preventif, promotif, dan kuratif sesuai kompetensi dokter umum Puskesmas

Aturan menjawab:
- Gunakan Bahasa Indonesia yang hangat, empatik, dan mudah dipahami masyarakat umum
- Hindari istilah medis yang terlalu teknis, gunakan bahasa awam
- Jawaban harus SINGKAT (maksimal 3-4 paragraf pendek)
- Sertakan saran praktis yang bisa dilakukan pasien di rumah
- Jika gejala menunjukkan kondisi darurat (red flag), SELALU sarankan untuk segera ke IGD/RS
- Jika keluhan di luar kompetensi dokter umum, sarankan rujukan ke spesialis
- Akhiri dengan kalimat yang ramah dan tawaran untuk konsultasi lanjutan jika diperlukan
- JANGAN memberikan diagnosis pasti, gunakan frasa seperti "kemungkinan" atau "bisa jadi"
- JANGAN meresepkan obat spesifik dengan dosis, cukup sarankan umum (misalnya "obat pereda nyeri yang dijual bebas")`;

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('\u{1F916} AI Assistant')
    .addItem('Generate Response', 'generateResponse')
    .addToUi();
}

function generateResponse() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const row = sheet.getActiveCell().getRow();

  if (row <= 1) {
    SpreadsheetApp.getUi().alert('Pilih baris yang berisi keluhan pasien (bukan header).');
    return;
  }

  const complaint = sheet.getRange(row, 2).getValue();

  if (!complaint) {
    SpreadsheetApp.getUi().alert('Kolom Keluhan Pasien kosong. Silakan isi terlebih dahulu.');
    return;
  }

  const payload = {
    system_instruction: {
      parts: [{ text: SYSTEM_PROMPT }]
    },
    contents: [
      {
        parts: [{ text: complaint }]
      }
    ],
    generationConfig: {
      temperature: 1.0,
      maxOutputTokens: 1024
    }
  };

  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'x-goog-api-key': GEMINI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(API_URL, options);
    const data = JSON.parse(response.getContentText());
    const aiResponse = data.candidates[0].content.parts[0].text;

    sheet.getRange(row, 1).setValue(new Date());
    sheet.getRange(row, 3).setValue(aiResponse);
    sheet.getRange(row, 4).setValue('Draft');

    SpreadsheetApp.getUi().alert('\u2705 Respons AI berhasil dibuat! Silakan review di kolom C.');
  } catch (error) {
    SpreadsheetApp.getUi().alert('\u274C Error: ' + error.message);
  }
}

Secret mission

Auto-Categorize Patient Complaints

Your AI assistant already generates response drafts. But what if it could also automatically classify each complaint into a medical category like Kardiovaskular, Respirasi, or Gastrointestinal? You would build an automatic consultation log that is easy to search and analyze.

Clean Up Your Resources

This project runs entirely on Google Sheets and the Gemini API free tier, so there are no ongoing costs. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

Google Sheet (your consultation assistant spreadsheet).
Google Apps Script project (bound to the sheet, contains Code.gs).
Gemini API key (stored in Script Properties and managed in Google AI Studio).

Keep everything running

No action needed. Choose this if you plan to use the assistant for daily consultations.

Your Google Sheet stays in Google Drive, ready to use anytime.
The Gemini API free tier allows up to 500 requests per day, so normal daily use costs nothing.
Your API key remains active in Script Properties for the next time you click 🤖 AI Assistant.

Pause - I'll come back to this later

Remove access to your API key so nobody else can use it, but keep all your files and code intact for later.

Open your Apps Script project by going to Extensions > Apps Script in your Google Sheet.
Click the gear icon (Project Settings) in the left sidebar.
Scroll down to Script Properties and delete the GEMINI_API_KEY property.
Your sheet and code remain untouched. When you come back, just re-add the GEMINI_API_KEY script property with your key value.

Delete - I don't want to use this again

Remove all project resources completely.

Delete your Gemini API key: go to the Google AI Studio API Keys page at aistudio.google.com/apikey. Find your key in the list and click the trash icon next to it to delete it.
Delete the Google Sheet (this also removes the bound Apps Script project): open Google Drive, right-click the consultation sheet, and select Move to trash.
Empty Google Drive trash if you want the files permanently gone: click Trash in the left sidebar of Google Drive, then click Empty trash.

That's a Wrap!

Selamat! You just built a working AI consultation assistant that lives entirely inside Google Sheets. What used to take 3 minutes of typing per patient response now takes under 60 seconds.

You've learned how to:

Built a Google Apps Script automation connected to the Gemini API free tier, proving you can integrate AI into everyday tools with zero budget.
Created a custom system prompt tailored for a Puskesmas general practitioner, with appropriate medical tone, red-flag warnings, and referral guidance in Bahasa Indonesia.
Automated the full BPJS telemedicine response workflow with automatic timestamps and a consultation log that records every patient interaction for future reference.
Secret Mission: Added automatic complaint categorization that classifies each patient complaint into medical categories for better record-keeping.

Ready to quiz yourself?