/** Report helper written by ChatGPT; aggregation is the tested Gemini function. */ function createGeminiEventReport() { const sourceId = 'PASTE_YOUR_SPREADSHEET_ID'; if(sourceId === 'PASTE_YOUR_SPREADSHEET_ID') throw new Error('Set your source spreadsheet ID first'); const lock=LockService.getScriptLock(); if(!lock.tryLock(10000)) throw new Error('Another report run is active'); try { const p=PropertiesService.getScriptProperties(); const existing=p.getProperty('geminiGuideReportUrl'); if(existing) { console.log(JSON.stringify({status:p.getProperty('geminiGuideReportComplete')==='true'?'EXISTING_REPORT_NO_DUPLICATE':'INCOMPLETE_REPORT_REVIEW_REQUIRED',url:existing})); return; } if(p.getProperty('geminiGuideAttemptStarted')) throw new Error('Previous creation outcome uncertain. Inspect Docs before retrying.'); const data=Sheets.Spreadsheets.Values.get(sourceId,"'Event data'!A1:D4",{valueRenderOption:'UNFORMATTED_VALUE'}).values; if(!data || JSON.stringify(data[0])!==JSON.stringify(['Session','Registrations','Attendances','Revenue (CAD)'])) throw new Error('Unexpected source headers'); const totals=geminiSummarizeEventRows(data.slice(1)); const expected=Sheets.Spreadsheets.Values.get(sourceId,"'Event data'!G2:G5",{valueRenderOption:'UNFORMATTED_VALUE'}).values.flat(); const actual=[totals.registrations,totals.attendances,totals.revenueCAD,totals.attendanceRate]; if(expected.length!==4 || actual.some((n,i)=>typeof expected[i]!=='number'||Math.abs(n-expected[i])>1e-10)) throw new Error('Sheet formulas disagree'); const lines=[ '가상 행사 집계 보고서', '가상 데이터로 Gemini 생성 집계 함수와 Apps Script 보고서 저장을 검증했어요.', '등록 합계: '+totals.registrations+'건', '참석 합계: '+totals.attendances+'건', '매출 합계: CAD '+totals.revenueCAD.toFixed(2), '참석률: '+(totals.attendanceRate===null?'계산 대상 없음':(totals.attendanceRate*100).toFixed(2)+'%'), '등록과 참석은 세션별 합계이며, 중복을 제거한 사람 수가 아니에요.', '',...data.map(r=>r.join(' / ')), '', '원본 시트: https://docs.google.com/spreadsheets/d/'+sourceId+'/edit', '검증: 시트 G2:G5 수식 결과와 일치. Gemini 집계 함수는 별도 15개 입력 테스트를 통과했어요.' ]; const text=lines.join('\n')+'\n'; p.setProperty('geminiGuideAttemptStarted',new Date().toISOString()); const doc=Docs.Documents.create({title:'BumbleV Gemini Apps Script — Fictional Event Report'}); const url='https://docs.google.com/document/d/'+doc.documentId+'/edit'; p.setProperty('geminiGuideReportUrl',url); Docs.Documents.batchUpdate({requests:[{insertText:{location:{index:1},text}},{updateParagraphStyle:{range:{startIndex:1,endIndex:1+lines[0].length},paragraphStyle:{namedStyleType:'TITLE'},fields:'namedStyleType'}}]},doc.documentId); const saved=Docs.Documents.get(doc.documentId); const savedText=(saved.body.content||[]).map(e=>(e.paragraph?.elements||[]).map(t=>t.textRun?.content||'').join('')).join(''); if(!savedText.includes(text)) throw new Error('Saved document text mismatch'); p.setProperty('geminiGuideReportComplete','true'); console.log(JSON.stringify({status:'REPORT_CREATED_VERIFIED',...totals,url})); } finally {lock.releaseLock();} }