Category Archives: AI

Apps Script + clasp로 MCP 서버를 만들어 Spark에 연결하는 초보자 튜토리얼 (Moonlight Ops 제작기)

이 문서는 Moonlight English(TPT 스토어)의 운영 시트 Moonlight_English_Ops를 Gemini Spark에서 대화로 조회할 수 있게 만든 과정을 그대로 정리한 것입니다. 코딩 경험이 거의 없어도 순서대로 따라 하면 자기 시트로 같은 것을 만들 수 있도록 썼습니다. 실제로 만들면서 막혔던 부분과 해결 방법도 모두 넣었습니다.

1. 완성하면 무엇이 달라지나

전에는

• 시트를 열고, 탭을 찾고, 필터를 걸고, 원하는 행을 눈으로 찾았습니다.
• 812개 상품이 있는 카탈로그에서 “Easel이 아직 안 붙은 상품”을 찾으려면 직접 필터를 만들어야 했습니다.

이제는

• Gemini Spark에 한국어로 물어보면 Spark가 시트를 직접 읽고 답합니다.
  • “이번 주 우선순위 알려줘”
  • “Easel 아직 안 붙은 상품 목록 뽑아줘”
  • “시리즈별 상품 수 세어줘”
• Spark는 24시간 돌아가는 에이전트라서, “매주 월요일 아침에 파이프라인 상태를 요약해서 메일로 보내줘” 같은 예약 작업에도 이 시트를 쓸 수 있습니다.
• 시트 내용을 복사해서 붙여넣을 필요가 없습니다. 항상 지금 시트에 있는 최신 값을 읽습니다.

한 줄 요약: 내 구글 시트가 Spark가 쓸 수 있는 “도구”가 됐습니다.

2. 먼저 알아둘 개념 4가지

어려운 말이 몇 개 나오는데, 아래 비유만 기억하면 충분합니다.

3. 전체 구조 한눈에 보기

 나 (한국어로 질문)
   │
   ▼
 Gemini Spark ──── "@Moonlight Ops 이번 주 우선순위 알려줘"
   │  (MCP 규격으로 요청: tools/call ops_overview)
   ▼
 Apps Script 웹앱  https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec?accessKey=YOUR_ACCESS_KEY
   │  1) 비밀키가 맞는지 확인
   │  2) 시트를 "읽기 전용"으로 읽음
   ▼
 구글 시트 Moonlight_English_Ops (00_KPI, 01_Catalog, 05_Pipeline ...)

Spark가 쓸 수 있는 도구는 4개입니다.

4. 준비물 체크리스트

• ☐ 개인 구글 계정 (회사/학교 계정은 Spark 맞춤 앱이 안 됩니다)
• ☐ Gemini Spark 사용 권한 (Google AI Pro 또는 Ultra 구독)
• ☐ Gemini 설정에서 Keep Activity(활동 기록 유지) 켜기 (꺼져 있으면 맞춤 앱 연결이 안 됩니다)
• ☐ 연결할 구글 시트 1개
• ☐ Windows PC (이 튜토리얼은 PowerShell 기준입니다. Mac도 명령어만 조금 다르고 흐름은 같습니다)

5. 1단계: 컴퓨터 준비 (Node.js, clasp)

5-1. Node.js 설치

1. https://nodejs.org 에서 LTS 버전을 받아 설치합니다. (다음, 다음, 설치만 누르면 됩니다)
2. PowerShell을 새로 열고 확인합니다.

node -v
npm -v

버전 숫자가 나오면 성공입니다.

5-2. clasp 설치

npm i -g @google/clasp
clasp --version

5-3. Apps Script API 켜기 (중요, 자주 빠뜨림)

1. 브라우저에서 https://script.google.com/home/usersettings 에 들어갑니다.
2. Google Apps Script API를 On으로 바꿉니다.

이걸 안 켜면 clasp가 프로젝트를 만들거나 올리지 못합니다.

5-4. clasp 로그인

clasp login

브라우저가 열리면 시트를 가진 구글 계정으로 로그인하고 허용을 누릅니다. “Logged in” 같은 메시지가 나오면 끝입니다.

6. 2단계: Apps Script 프로젝트 만들기

작업할 폴더를 만들고, 그 안에서 프로젝트를 만듭니다.

mkdir $HOME\moonlight-ops-mcp
cd $HOME\moonlight-ops-mcp
clasp create --type standalone --title "Moonlight Ops MCP"

성공하면 이렇게 나옵니다.

Created new script: https://script.google.com/d/YOUR_SCRIPT_ID/edit
└─ appsscript.json

폴더 안에 .clasp.json(어느 프로젝트와 연결됐는지 적힌 파일)과 appsscript.json(설정 파일)이 생깁니다.

7. 3단계: 코드와 설정 파일 넣기

폴더에 파일이 2개 있어야 합니다.

7-1. Code.gs

부록의 전체 코드를 Code.gs라는 이름으로 저장합니다. 메모장이나 VS Code에 붙여넣고 폴더에 저장하면 됩니다.

꼭 바꿔야 하는 곳은 딱 1줄입니다.

const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';

시트 주소가 https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit라면, /d/와 /edit 사이의 YOUR_SPREADSHEET_ID 부분이 ID입니다.

7-2. appsscript.json

주의: 이 파일은 반드시 “BOM 없는 UTF-8″로 저장해야 합니다. Windows PowerShell 5.1의 Set-Content -Encoding utf8은 파일 맨 앞에 BOM이라는 보이지 않는 3바이트를 붙이는데, clasp가 이걸 읽지 못하고 Invalid manifest file 오류를 냅니다. (저희도 여기서 한 번 막혔습니다.)

아래 명령을 폴더 안에서 그대로 실행하면 안전하게 저장됩니다.

$m = @'
{
  "timeZone": "Asia/Seoul",
  "dependencies": {
    "enabledAdvancedServices": [
      { "userSymbol": "Sheets", "version": "v4", "serviceId": "sheets" }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.readonly"
  ]
}
'@
[IO.File]::WriteAllText("$PWD\appsscript.json", $m, (New-Object Text.UTF8Encoding $false))

각 줄의 의미:

8. 4단계: 올리기(push)와 배포(deploy)

clasp push -f

이렇게 나오면 성공입니다.

Pushed 2 files.
└─ appsscript.json
└─ Code.gs

Pushed 2 files가 안 나오고 오류가 나면 절대 다음 단계로 넘어가지 마세요. (15장 참고)

이어서 배포합니다.

clasp deploy --description "v1"

Deployed YOUR_DEPLOYMENT_ID @2

YOUR_DEPLOYMENT_ID 자리에 나오는 긴 문자열(AKfycb…로 시작)이 배포 ID입니다. 메모해 두세요. 웹앱 주소는 이렇게 됩니다.

https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec

9. 5단계: 접근 키 저장하기

웹앱은 누구나 주소로 접근할 수 있게 열려 있으므로, 비밀 키를 아는 요청만 받도록 합니다.

9-1. 랜덤 키 만들기

PowerShell에서 실행하면 안전한 랜덤 문자열이 나옵니다.

$b = New-Object byte[] 24
[Security.Cryptography.RandomNumberGenerator]::Create().GetBytes($b)
[Convert]::ToBase64String($b).Replace('+','-').Replace('/','_').TrimEnd('=')

나온 문자열을 복사해 둡니다. 비밀번호처럼 다루세요. 채팅, 스크린샷, 공개 저장소에 올리면 안 됩니다.

9-2. Script Properties에 저장

1. https://script.google.com 에서 프로젝트를 엽니다.
2. 왼쪽 톱니바퀴 **Project Settings(프로젝트 설정)**를 누릅니다.
3. 맨 아래 Script Properties 에서 Add script property를 누릅니다.
4. Property: ACCESS_KEY, Value: 방금 만든 키
5. Save script properties를 누릅니다.

코드는 이 값을 읽어서, 요청 주소의 ?accessKey= 값과 같을 때만 응답합니다. 키를 코드에 직접 쓰지 않아서, 나중에 코드를 공유해도 키가 새지 않습니다.

10. 6단계: 권한 승인하기

웹앱이 “내 권한으로” 시트를 읽으려면, 처음 한 번 내가 직접 허락해야 합니다. 이걸 안 하면 웹앱 주소가 Authorization needed 페이지만 돌려줍니다.

1. 프로젝트 편집기(Editor)에서 Code.gs를 엽니다.
2. 위쪽 함수 선택 칸에서 **testLocal**을 고릅니다.
3. **Run(실행)**을 누릅니다.
4. “Authorization required” 창이 뜨면 Review permissions를 누릅니다.
5. 계정을 고르면 “Google hasn’t verified this app” 경고가 나옵니다.
   • 놀라지 마세요. 개발자가 나 자신이라서 나오는 정상 경고입니다.
   • Advanced(고급) → Go to Moonlight Ops MCP (unsafe) 를 누릅니다.
6. 요청 권한이 “See all your Google Sheets spreadsheets”(시트 보기) 하나뿐인지 확인하고 **Continue(계속)**를 누릅니다.
7. 아래 Execution log에 도구 목록과 시트 요약이 찍히면 성공입니다.

11. 7단계: 제대로 동작하는지 확인하기

Spark에 붙이기 전에 직접 호출해 보면 문제를 빨리 찾을 수 있습니다. PowerShell에서 실행합니다. (YOUR_DEPLOYMENT_ID, YOUR_ACCESS_KEY를 바꾸세요)

$url = "https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec?accessKey=YOUR_ACCESS_KEY"
$body = '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'
Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $body | ConvertTo-Json -Depth 6

ops_overview, ops_read_tab, ops_search, ops_count_by 4개가 보이면 성공입니다.

시트를 실제로 읽는지도 확인합니다.

$body = '{"jsonrpc":"2.0","id":2,"method":"tools/call","params":{"name":"ops_overview","arguments":{}}}'
Invoke-RestMethod -Uri $url -Method Post -ContentType "application/json" -Body $body | ConvertTo-Json -Depth 6

틀린 키로도 한 번 해 보세요. Unauthorized 오류가 나와야 정상입니다.

저희가 실제로 확인한 결과:

• initialize, tools/list 정상 응답
• ops_overview가 실제 시트 값 반환 (01_Catalog 812행, 03_Listing_QA 774행, 02_Series 73행 등)
• 틀린 키는 Unauthorized: missing or wrong accessKey로 거부

12. 8단계: Gemini Spark에 연결하기

1. https://gemini.google.com/spark/apps 로 갑니다. (또는 Gemini 왼쪽 메뉴 Spark → Connected Apps)
2. Custom apps 섹션에서 Add a custom app을 누릅니다.
3. MCP Server URL 칸에 키까지 포함한 주소를 넣습니다.

   https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec?accessKey=YOUR_ACCESS_KEY

4. Next → 안내문을 읽고 “I understand and accept…” 체크 → Connect
5. “Save your custom app” 창이 뜨고 Available actions에 도구 4개가 보이면 성공입니다.
6. 앱 이름을 확인(예: Moonlight Ops)하고 Connect를 누릅니다.

이제 Custom apps 목록에 Moonlight Ops가 켜진 상태로 보입니다.

13. 9단계: 실제로 써보기

Spark 대화창에서 @Moonlight Ops를 붙여 물어보면 됩니다. 처음 도구를 쓸 때 Spark가 “이 앱을 사용해도 될까요?”라고 물으면 허용합니다.

현황 파악

• @Moonlight Ops 시트 전체 현황이랑 이번 주 우선순위 알려줘

찾기

• @Moonlight Ops 01_Catalog에서 easel_status가 비어있는 상품 목록 보여줘
• @Moonlight Ops Junie 들어간 행 전부 찾아서 탭별로 정리해줘
• @Moonlight Ops 03_Listing_QA에서 listing_score 낮은 상품 10개

집계

• @Moonlight Ops 01_Catalog를 series별로 개수 세줘
• @Moonlight Ops 05_Pipeline stage별 개수

다른 앱과 섞어 쓰기 (Spark의 진짜 힘)

• @Moonlight Ops 파이프라인에서 live 된 것들 뽑아서 구글 문서로 주간 리포트 만들어줘
• 매주 월요일 오전 9시에 @Moonlight Ops 로 00_KPI 요약해서 내 Gmail로 보내줘 (Spark 예약 작업)

14. 이걸로 무엇을 할 수 있게 되었나

14-1. 시트를 “대화로” 조회

필터나 수식을 몰라도 됩니다. “Easel 안 붙은 Digital 상품”처럼 말로 조건을 주면 Spark가 알맞은 도구와 조건을 골라 시트를 읽습니다.

14-2. 항상 최신 데이터

시트를 복사해서 AI에 붙여넣는 방식은 금방 옛날 자료가 됩니다. 이 방식은 물어보는 순간의 시트를 읽습니다. 시트를 고치면 바로 답이 바뀝니다.

14-3. 예약 작업과 자동화

Spark는 컴퓨터를 꺼도 클라우드에서 돌아갑니다. “매주”, “매일 아침” 같은 일정으로 시트 요약, 리포트 작성, 메일 발송을 맡길 수 있습니다.

14-4. 여러 앱을 한 번에 연결

Spark에 연결된 다른 앱과 조합할 수 있습니다.

14-5. 안전한 구조

• 읽기 전용이라 AI가 실수해도 시트가 망가지지 않습니다.
• 비밀 키가 없으면 아무도 못 씁니다.
• Google 서버에서 무료로 돌아가서 따로 서버 비용이 없습니다.

14-6. 같은 틀로 계속 늘릴 수 있음

이번에 만든 코드는 “도구 목록(TOOLS) + 도구 실행 함수” 구조라서, 도구를 하나씩 더 붙이기 쉽습니다. 다른 시트(예: 공고 스캔 결과, 레포 대시보드)도 같은 방법으로 Spark 전용 앱으로 만들 수 있습니다. (17장 참고)

15. 실제로 겪은 문제와 해결법

직접 만들면서 부딪힌 문제 3가지입니다. 초보자가 가장 많이 막히는 곳이기도 합니다.

문제 1. clasp push에서 Invalid manifest file "appsscript.json"

• 증상: push가 실패했는데, 모르고 deploy까지 진행했습니다. deploy 자체는 “Deployed …”라고 성공처럼 나옵니다.
• 원인: PowerShell 5.1의 Set-Content -Encoding utf8이 파일 앞에 BOM을 붙였고, clasp가 이를 잘못된 JSON으로 봤습니다.
• 해결: 7-2의 [IO.File]::WriteAllText(..., UTF8Encoding $false) 방식으로 BOM 없이 저장한 뒤 다시 push했습니다.
• 교훈: push 결과에 Pushed 2 files가 나오는지 반드시 확인하세요.

문제 2. 웹앱 주소가 404를 반환

• 증상: 배포 ID로 주소를 열면 404가 나왔습니다. 배포 관리 화면을 보니 배포가 “Library(라이브러리)”로만 만들어져 있었습니다.
• 원인: 문제 1 때문에 코드와 웹앱 설정이 서버에 올라가지 않은 빈 프로젝트를 배포한 것이었습니다. 웹앱 설정(webapp)이 없는 프로젝트를 배포하면 웹앱 주소가 생기지 않습니다.
• 해결: push를 제대로 한 뒤 clasp deploy -i YOUR_DEPLOYMENT_ID로 같은 배포를 업데이트했습니다. 주소는 그대로 두고 웹앱으로 바뀌었습니다.
• 확인법: 편집기 왼쪽 파일 목록에 Code.gs가 있는지, appsscript.json 안에 webapp이 있는지 보세요.

문제 3. Specified permissions are not sufficient to call SpreadsheetApp.openById

• 증상: 도구 목록은 잘 나오는데, 시트를 읽는 도구만 오류가 났습니다.
• 원인: Apps Script의 기본 시트 도구인 SpreadsheetApp.openById는 시트 쓰기 권한 전체(spreadsheets)를 요구합니다. 저희는 안전을 위해 읽기 전용(spreadsheets.readonly)만 줬기 때문에 막혔습니다.
• 선택지:
  1. 권한을 전체(spreadsheets)로 넓힌다: 쉽지만, 인터넷에 열린 웹앱이 시트 수정 권한까지 갖게 됩니다.
  2. Sheets API 고급 서비스로 읽는다 (선택한 방법): 읽기 전용 권한으로도 동작합니다.
• 해결: 코드를 Sheets.Spreadsheets.get, Sheets.Spreadsheets.Values.batchGet으로 바꾸고, 설정 파일에 enabledAdvancedServices로 Sheets를 추가했습니다.

그 밖에 알아두면 좋은 것

16. 보안에 대해 꼭 알아둘 것

• 접근 키 = 비밀번호입니다. 주소 전체(...?accessKey=...)를 공개된 곳에 붙이지 마세요.
• 키가 새면: Script Properties에서 ACCESS_KEY를 새 값으로 바꾸고, Spark에서 앱을 지운 뒤 새 주소로 다시 연결하면 됩니다. 옛 키는 즉시 쓸모없어집니다.
• 읽기 전용 권한만 줬기 때문에, 키가 새도 시트를 고치거나 지울 수는 없습니다. 다만 시트 내용은 읽힐 수 있으니 키 관리가 중요합니다.
• Google은 Spark 맞춤 앱으로 연결한 외부 서버를 검토하거나 보호하지 않는다고 안내합니다. 이 경우 서버가 내 소유이고 코드도 직접 확인할 수 있어서 그 걱정이 가장 적은 형태입니다.
• 나중에 “쓰기” 도구(행 추가, 상태 변경 등)를 만들고 싶다면, 권한이 넓어지는 만큼 별도 프로젝트로 분리하고 키도 따로 두는 것을 권합니다.

17. 나중에 고치고 늘리는 법

코드 수정 → 반영

cd $HOME\moonlight-ops-mcp
# Code.gs 수정 후
clasp push -f
clasp deploy -i YOUR_DEPLOYMENT_ID -d "v1.2 설명"

-i를 쓰면 주소가 그대로라서 Spark는 다시 연결할 필요가 없습니다.

도구 하나 추가하는 방법 (예: 파이프라인 마감 임박 목록)

1. TOOLS 배열에 도구 설명을 추가합니다. (이름, 설명, 입력값 형식)
2. callTool_ 함수의 switch에 case '새도구이름': data = 새함수_(args); break;를 추가합니다.
3. 새함수_를 만듭니다. table_('05_Pipeline')로 행을 받아 원하는 조건으로 거르면 됩니다.
4. push, deploy 후 Spark의 앱 상세에서 도구가 늘었는지 확인합니다.

다른 시트로 새 앱 만들기

이 폴더를 통째로 복사해 SPREADSHEET_ID와 도구 설명만 바꾸고, 2단계부터 다시 하면 됩니다. 앱마다 키는 새로 만드세요.

18. 자주 묻는 질문

Q. 돈이 드나요? Apps Script는 무료이고 Google 계정의 일일 사용량 한도 안에서 동작합니다. 개인 업무용 조회로는 넉넉합니다. Spark 자체는 AI Pro/Ultra 구독이 필요합니다.

Q. clasp 없이 브라우저만으로도 되나요? 됩니다. script.google.com에서 새 프로젝트를 만들고, 편집기에 코드를 붙여넣고, Project Settings에서 “Show appsscript.json”을 켜서 설정을 붙여넣은 뒤, Deploy → New deployment → Web app(Execute as: Me, Who has access: Anyone)으로 배포하면 됩니다. clasp는 이 과정을 명령어로 빠르게, 실수 없이 반복하게 해 줍니다.

Q. Claude나 Cursor에서도 쓸 수 있나요? MCP 표준을 따르므로 같은 주소를 원격 MCP 서버로 등록하는 도구라면 대부분 쓸 수 있습니다. 다만 클라이언트마다 연결 방식이 조금씩 달라서 직접 확인이 필요합니다.

Q. 시트가 아주 크면 느리지 않나요? 한 번 호출할 때 시트 전체를 한 번에 읽고(batchGet), 결과는 기본 50행(최대 200행)으로 잘라 보냅니다. 셀 하나가 500자를 넘으면 잘라서 보냅니다. 수천 행 규모까지는 문제없이 쓸 수 있습니다.

Q. 컴퓨터를 꺼도 되나요? 됩니다. 서버는 Google에서, Spark는 클라우드에서 돌아갑니다. 내 컴퓨터는 코드를 고칠 때만 필요합니다.

19. 부록: 전체 코드

Code.gs

/**
 * Moonlight Ops MCP server (read-only) for Gemini Spark custom apps.
 *
 * Deploy: Deploy > New deployment > Web app
 *   Execute as: Me   |   Who has access: Anyone
 * Script property ACCESS_KEY must be set (Project Settings > Script properties).
 * Spark URL: https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec?accessKey=YOUR_ACCESS_KEY
 */
const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID'; // <- 내 시트 ID로 바꾸기
const SERVER_INFO = { name: 'moonlight-ops', version: '1.0.0' };
const SUPPORTED_PROTOCOLS = ['2025-06-18', '2025-03-26', '2024-11-05'];
const DEFAULT_LIMIT = 50;
const MAX_LIMIT = 200;
const MAX_CELL_CHARS = 500;

const FILTERS_SCHEMA = {
  type: 'object',
  description: 'Exact, case-insensitive column matches, e.g. {"status":"active","easel_status":""}. Empty string means the cell is blank.',
  additionalProperties: { type: 'string' }
};
const COLUMNS_SCHEMA = {
  type: 'array',
  items: { type: 'string' },
  description: 'Optional. Only return these columns.'
};

const TOOLS = [
  {
    name: 'ops_overview',
    description: 'Overview of the Moonlight English (Teachers Pay Teachers store) ops spreadsheet Moonlight_English_Ops: every tab with data row count and column headers, plus the 00_KPI snapshot and this-week priorities. Call this first.',
    inputSchema: { type: 'object', properties: {} },
    annotations: { title: 'Ops overview', readOnlyHint: true }
  },
  {
    name: 'ops_read_tab',
    description: 'Read rows from one tab as objects keyed by header. Tabs: 00_KPI, 01_Catalog (master SKU list), 02_Series, 03_Listing_QA, 04_Content_QA, 05_Pipeline, 06_Easel, 07_Marketing, 08_Hygiene. Partial names like "catalog" work.',
    inputSchema: {
      type: 'object',
      properties: {
        tab: { type: 'string', description: 'Tab name or partial name, e.g. "01_Catalog" or "pipeline".' },
        offset: { type: 'integer', minimum: 0, description: 'Data rows to skip. Default 0.' },
        limit: { type: 'integer', minimum: 1, maximum: MAX_LIMIT, description: 'Max rows to return. Default 50.' },
        columns: COLUMNS_SCHEMA
      },
      required: ['tab']
    },
    annotations: { title: 'Read tab', readOnlyHint: true }
  },
  {
    name: 'ops_search',
    description: 'Find rows by free text and/or exact column filters, in one tab or across all tabs. Examples: query "Junie"; tab "01_Catalog" with filters {"easel_status":""}; tab "05_Pipeline" with filters {"stage":"live"}.',
    inputSchema: {
      type: 'object',
      properties: {
        query: { type: 'string', description: 'Case-insensitive substring matched against every cell.' },
        tab: { type: 'string', description: 'Optional tab name or partial name. Omit to search all tabs.' },
        filters: FILTERS_SCHEMA,
        limit: { type: 'integer', minimum: 1, maximum: MAX_LIMIT, description: 'Max rows to return. Default 50.' },
        columns: COLUMNS_SCHEMA
      }
    },
    annotations: { title: 'Search rows', readOnlyHint: true }
  },
  {
    name: 'ops_count_by',
    description: 'Count rows in a tab grouped by one column. Examples: 01_Catalog by status, line, series, easel_status, harness_status, edition; 05_Pipeline by stage. Optional filters.',
    inputSchema: {
      type: 'object',
      properties: {
        tab: { type: 'string', description: 'Tab name or partial name. Default 01_Catalog.' },
        column: { type: 'string', description: 'Column header to group by.' },
        filters: FILTERS_SCHEMA
      },
      required: ['column']
    },
    annotations: { title: 'Count by column', readOnlyHint: true }
  }
];

// ---------- HTTP entry points ----------

function doGet(e) {
  if (!isAuthorized_(e)) return json_({ error: 'unauthorized' });
  return json_({ ok: true, server: SERVER_INFO, transport: 'POST JSON-RPC 2.0 (MCP streamable HTTP, JSON responses)' });
}

function doPost(e) {
  if (!isAuthorized_(e)) return json_(rpcError_(null, -32001, 'Unauthorized: missing or wrong accessKey'));
  let msg;
  try {
    msg = JSON.parse((e.postData && e.postData.contents) || '');
  } catch (err) {
    return json_(rpcError_(null, -32700, 'Parse error'));
  }
  if (Array.isArray(msg)) {
    const out = msg.map(handle_).filter(function (r) { return r; });
    return out.length ? json_(out) : empty_();
  }
  const res = handle_(msg);
  return res ? json_(res) : empty_();
}

// ---------- JSON-RPC / MCP ----------

function handle_(msg) {
  if (!msg || typeof msg !== 'object') return rpcError_(null, -32600, 'Invalid Request');
  const id = msg.id;
  const isNotification = id === undefined || id === null;
  const method = msg.method;
  if (!method) return null; // a client response; nothing to do
  try {
    switch (method) {
      case 'initialize': {
        const requested = msg.params && msg.params.protocolVersion;
        const version = SUPPORTED_PROTOCOLS.indexOf(requested) >= 0 ? requested : SUPPORTED_PROTOCOLS[0];
        return rpcResult_(id, {
          protocolVersion: version,
          capabilities: { tools: { listChanged: false } },
          serverInfo: SERVER_INFO,
          instructions: 'Read-only access to Moonlight_English_Ops, the ops sheet for the Moonlight English TPT store. Start with ops_overview, then use ops_search / ops_read_tab / ops_count_by.'
        });
      }
      case 'ping':
        return rpcResult_(id, {});
      case 'tools/list':
        return rpcResult_(id, { tools: TOOLS });
      case 'tools/call':
        return rpcResult_(id, callTool_(msg.params || {}));
      case 'resources/list':
        return rpcResult_(id, { resources: [] });
      case 'resources/templates/list':
        return rpcResult_(id, { resourceTemplates: [] });
      case 'prompts/list':
        return rpcResult_(id, { prompts: [] });
      default:
        if (isNotification) return null; // notifications/initialized etc.
        return rpcError_(id, -32601, 'Method not found: ' + method);
    }
  } catch (err) {
    return isNotification ? null : rpcError_(id, -32603, String((err && err.message) || err));
  }
}

function callTool_(params) {
  const name = params.name;
  const args = params.arguments || {};
  let data;
  try {
    switch (name) {
      case 'ops_overview': data = overview_(); break;
      case 'ops_read_tab': data = readTab_(args); break;
      case 'ops_search': data = search_(args); break;
      case 'ops_count_by': data = countBy_(args); break;
      default:
        return { content: [{ type: 'text', text: 'Unknown tool: ' + name }], isError: true };
    }
  } catch (err) {
    return { content: [{ type: 'text', text: 'Error: ' + ((err && err.message) || err) }], isError: true };
  }
  return { content: [{ type: 'text', text: JSON.stringify(data) }] };
}

// ---------- Tools ----------

function overview_() {
  const meta = meta_();
  const all = allValues_();
  const tabs = sheetNames_().map(function (name) {
    const values = all[name] || [];
    const header = (values[0] || []).filter(String);
    const dataRows = values.slice(1).filter(function (r) { return r.some(String); }).length;
    return { name: name, data_rows: dataRows, header: header };
  });
  const kpiName = sheetNames_().filter(function (n) { return /kpi/i.test(n); })[0];
  const kpi = kpiName ? rawRows_(kpiName, 60) : [];
  return { spreadsheet: meta.properties.title, url: meta.spreadsheetUrl, tabs: tabs, kpi_rows: kpi };
}

function readTab_(a) {
  const sheet = resolveSheet_(a.tab);
  if (/kpi/i.test(sheet)) {
    return { tab: sheet, note: 'KPI tab has no header row; raw rows returned.', rows: rawRows_(sheet, clampLimit_(a.limit)) };
  }
  const t = table_(sheet);
  const offset = Math.max(0, parseInt(a.offset, 10) || 0);
  const limit = clampLimit_(a.limit);
  const rows = t.rows.slice(offset, offset + limit).map(function (r) { return pick_(r, a.columns); });
  return { tab: sheet, header: t.header, total_rows: t.rows.length, offset: offset, returned: rows.length, rows: rows };
}

function search_(a) {
  const q = a.query ? String(a.query).toLowerCase().trim() : '';
  const filters = a.filters || {};
  if (!q && !Object.keys(filters).length) throw new Error('Give a query and/or filters.');
  const sheets = a.tab ? [resolveSheet_(a.tab)] : sheetNames_().filter(function (n) { return !/kpi/i.test(n); });
  const limit = clampLimit_(a.limit);
  const out = [];
  let total = 0;
  sheets.forEach(function (sheet) {
    const t = table_(sheet);
    t.rows.forEach(function (r) {
      if (!matches_(r, q, filters)) return;
      total++;
      if (out.length < limit) out.push(Object.assign({ _tab: sheet }, pick_(r, a.columns)));
    });
  });
  return { query: a.query || null, filters: filters, total_matches: total, returned: out.length, rows: out };
}

function countBy_(a) {
  const sheet = resolveSheet_(a.tab || '01_Catalog');
  const t = table_(sheet);
  const want = String(a.column || '').toLowerCase().trim();
  const col = t.header.filter(function (h) { return h.toLowerCase() === want; })[0];
  if (!col) throw new Error('Unknown column "' + a.column + '". Columns: ' + t.header.join(', '));
  const counts = {};
  t.rows.forEach(function (r) {
    if (!matches_(r, '', a.filters || {})) return;
    const v = r[col] === undefined ? '(blank)' : String(r[col]);
    counts[v] = (counts[v] || 0) + 1;
  });
  const groups = Object.keys(counts)
    .map(function (k) { return { value: k, count: counts[k] }; })
    .sort(function (x, y) { return y.count - x.count; });
  const total = groups.reduce(function (s, g) { return s + g.count; }, 0);
  return { tab: sheet, column: col, total: total, groups: groups };
}

// ---------- Sheet helpers ----------

// Uses the Sheets advanced service (read-only scope). Sheets are addressed by tab name.
let META_, ALL_;
function meta_() {
  if (!META_) {
    META_ = Sheets.Spreadsheets.get(SPREADSHEET_ID, { fields: 'properties.title,spreadsheetUrl,sheets.properties.title' });
  }
  return META_;
}

function sheetNames_() {
  return (meta_().sheets || []).map(function (s) { return s.properties.title; });
}

function a1_(name) {
  return "'" + String(name).replace(/'/g, "''") + "'";
}

function allValues_() {
  if (!ALL_) {
    const names = sheetNames_();
    const res = Sheets.Spreadsheets.Values.batchGet(SPREADSHEET_ID, { ranges: names.map(a1_), valueRenderOption: 'FORMATTED_VALUE' });
    ALL_ = {};
    (res.valueRanges || []).forEach(function (vr, i) { ALL_[names[i]] = vr.values || []; });
  }
  return ALL_;
}

function values_(name) {
  const rows = allValues_()[name] || [];
  let width = 0;
  rows.forEach(function (r) { width = Math.max(width, r.length); });
  return rows.map(function (r) {
    const out = [];
    for (let i = 0; i < width; i++) out.push(r[i] === undefined || r[i] === null ? '' : String(r[i]));
    return out;
  });
}

function resolveSheet_(tab) {
  if (!tab) throw new Error('tab is required.');
  const names = sheetNames_();
  const q = String(tab).toLowerCase().trim();
  const hit =
    names.filter(function (n) { return n.toLowerCase() === q; })[0] ||
    names.filter(function (n) { return n.toLowerCase().indexOf(q) >= 0; })[0];
  if (!hit) throw new Error('No tab matches "' + tab + '". Tabs: ' + names.join(', '));
  return hit;
}

function table_(sheet) {
  const values = values_(sheet);
  if (!values.length) return { header: [], rows: [] };
  const header = values[0].map(function (h, i) { return String(h).trim() || 'col_' + (i + 1); });
  const rows = [];
  for (let r = 1; r < values.length; r++) {
    const row = values[r];
    if (row.every(function (v) { return v === ''; })) continue;
    const obj = { _row: r + 1 };
    header.forEach(function (h, i) {
      if (row[i] !== '') obj[h] = clip_(row[i]);
    });
    rows.push(obj);
  }
  return { header: header, rows: rows };
}

function rawRows_(sheet, limit) {
  return values_(sheet)
    .filter(function (r) { return r.some(String); })
    .slice(0, limit)
    .map(function (r) {
      let end = r.length;
      while (end > 0 && r[end - 1] === '') end--;
      return r.slice(0, end).map(clip_);
    });
}

function matches_(row, q, filters) {
  const keys = Object.keys(row);
  for (const k in filters) {
    const want = String(filters[k]).toLowerCase().trim();
    const key = keys.filter(function (h) { return h.toLowerCase() === String(k).toLowerCase().trim(); })[0];
    const have = key ? String(row[key]).toLowerCase().trim() : '';
    if (have !== want) return false;
  }
  if (q) {
    return keys.some(function (k) { return k !== '_row' && String(row[k]).toLowerCase().indexOf(q) >= 0; });
  }
  return true;
}

function pick_(row, columns) {
  if (!columns || !columns.length) return row;
  const out = { _row: row._row };
  const wanted = columns.map(function (c) { return String(c).toLowerCase().trim(); });
  Object.keys(row).forEach(function (k) {
    if (wanted.indexOf(k.toLowerCase()) >= 0) out[k] = row[k];
  });
  return out;
}

function clip_(v) {
  const s = String(v);
  return s.length > MAX_CELL_CHARS ? s.slice(0, MAX_CELL_CHARS) + '…' : s;
}

function clampLimit_(n) {
  const v = parseInt(n, 10);
  if (!v || v < 1) return DEFAULT_LIMIT;
  return Math.min(v, MAX_LIMIT);
}

// ---------- Auth / output ----------

function isAuthorized_(e) {
  const key = PropertiesService.getScriptProperties().getProperty('ACCESS_KEY');
  if (!key) return false;
  const given = e && e.parameter && e.parameter.accessKey;
  return given === key;
}

function rpcResult_(id, result) {
  return { jsonrpc: '2.0', id: id, result: result };
}

function rpcError_(id, code, message) {
  return { jsonrpc: '2.0', id: id === undefined ? null : id, error: { code: code, message: message } };
}

function json_(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

function empty_() {
  return ContentService.createTextOutput('').setMimeType(ContentService.MimeType.JSON);
}

// ---------- Manual test (run from the editor) ----------

function testLocal() {
  console.log(JSON.stringify(handle_({ jsonrpc: '2.0', id: 1, method: 'tools/list' })).slice(0, 500));
  console.log(JSON.stringify(callTool_({ name: 'ops_overview', arguments: {} })).slice(0, 1500));
  console.log(JSON.stringify(callTool_({ name: 'ops_count_by', arguments: { column: 'status' } })));
}

appsscript.json

{
  "timeZone": "Asia/Seoul",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  },
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.readonly"
  ]
}

작성: 2026-09-28. Moonlight Ops v1.1 (읽기 전용 도구 4개) 기준.