/* Korean reading sync v2 / reader v75. Bind to the designated spreadsheet, then run setup. */ const SHEET_ID='1meHM9tiic10LJ7oJC3-Y2jAcCLUCA0hSVkQVCIixuJA'; function setup(){ const p=PropertiesService.getScriptProperties();if(!p.getProperty('SYNC_KEY'))p.setProperty('SYNC_KEY',Utilities.getUuid()+Utilities.getUuid()); const ss=SpreadsheetApp.openById(SHEET_ID); ensureSheet_(ss,'同期イベント',['イベントID','書籍ID','種類','項目ID','結果','改訂番号','内容JSON1','内容JSON2','内容JSON3','受信日時']); SpreadsheetApp.getUi().alert('準備できました。「プロジェクトの設定」→「スクリプト プロパティ」のSYNC_KEYをMacとiPhoneの同期キー欄に設定してください。'); } function ensureSheet_(ss,name,headers){let s=ss.getSheetByName(name);if(!s){s=ss.insertSheet(name);s.getRange(1,1,1,headers.length).setValues([headers]);s.setFrozenRows(1);}return s;} function doGet(){return ContentService.createTextOutput('KStudy sync v2 + reading progress v1: use authenticated POST.');} function doPost(e){ let out;try{const body=e.postData.contents;if(body.length>1000000)throw Error('送信量が大きすぎます。');out=handle_(JSON.parse(body));}catch(err){out={schema:2,ok:false,error:String(err.message||err)};} return ContentService.createTextOutput(JSON.stringify(out)).setMimeType(ContentService.MimeType.JSON); } function text_(s,max){return typeof s==='string'&&s.length<=max;} function validEvent_(e,bookId){ if(!e||e.bookId!==bookId||!text_(e.eventId,120)||!e.eventId||!text_(e.id,260)||!e.id||!['ocr','vocabulary','review'].includes(e.type)||!Number.isInteger(e.baseRevision)||e.baseRevision<0||!e.payload||typeof e.payload!=='object'||JSON.stringify(e.payload).length>45000)throw Error('同期データが不正です。'); const p=e.payload; if(e.type==='ocr'&&(!text_(p.before,20000)||!text_(p.after,20000)||!p.after.trim()||!text_(p.pageId,120)||!text_(p.utteranceId,120)||e.id!==p.pageId+':'+p.utteranceId))throw Error('訂正文が不正です。'); if(e.type==='vocabulary'&&(!text_(p.term,200)||!text_(p.meaning,300)||p.image!==undefined))throw Error('語句が不正です。'); if(e.type==='review'&&(!text_(p.pageId,120)||!text_(p.utteranceId,120)))throw Error('復習データが不正です。'); } function safeCell_(v){if(v===null||v===undefined)return '';if(typeof v==='string'&&/^[=+@-]/.test(v))return "'"+v;return v;} function upsert_(ss,name,headers,keys,values){ const s=ensureSheet_(ss,name,headers),rows=s.getDataRange().getValues(); let index=rows.findIndex((r,i)=>i>0&&keys.every(k=>String(r[k])===String(values[k])));if(index<0)index=Math.max(1,rows.length); s.getRange(index+1,1,1,values.length).setValues([values.map(safeCell_)]); } function project_(ss,r){ const p=r.payload,now=r.at,b=r.bookId; if(r.type==='ocr')upsert_(ss,'OCR訂正',['書籍ID','ページID','セリフID','元のOCR','訂正後の原文','改訂番号','元データ版','翻訳再確認','更新日時','更新端末','最終イベントID'],[0,1,2],[b,p.pageId,p.utteranceId,r.original||p.before,p.after,r.revision,'','要再確認',now,r.deviceId,r.eventId]); if(r.type==='vocabulary')upsert_(ss,'単語帳',['語句ID','書籍ID','ページID','セリフID','文中の形','基本形','品詞','元の意味','文中の意味','活用・分解','例文','メモ','習得状態','更新日時','改訂番号','削除フラグ'],[0,1],[r.id,b,p.pageId,p.utteranceId,p.term,p.lemma||'',p.pos||'',p.base_meaning||'',p.meaning,p.morphology||'',p.example,p.note,p.learnedAt?'習得済み':'学習中',now,r.revision,!!p.deleted]); if(r.type==='review')upsert_(ss,'復習',['復習ID','書籍ID','ページID','セリフID','原文','訳','学習状態','更新日時','改訂番号','削除フラグ'],[0,1],[r.id,b,p.pageId,p.utteranceId,p.ko,p.ja,'未確認',now,r.revision,!!p.deleted]); } function history_(ss,e,result){ if(e.type!=='ocr')return;const p=e.payload; upsert_(ss,'訂正履歴',['イベントID','書籍ID','ページID','セリフID','変更前','変更後','基準改訂番号','採用改訂番号','受信日時','端末ID','結果','Mac反映日時'],[0],[e.eventId,e.bookId,p.pageId,p.utteranceId,p.before,p.after,e.baseRevision,result.revision||'',result.at,e.deviceId,result.status,'']); } function decide_(e,current){ if(current&&(current.revision!==e.baseRevision||(e.type==='ocr'&¤t.payload.after!==e.payload.before)))return {eventId:e.eventId,status:'conflict',current}; const record={...e,revision:e.baseRevision+1,original:current?.original||e.payload.before,at:new Date().toISOString()}; return {eventId:e.eventId,status:'accepted',revision:record.revision,record}; } function handle_(input){ const key=PropertiesService.getScriptProperties().getProperty('SYNC_KEY');if(!key||input.token!==key)throw Error('同期キーが一致しません。'); if(input.operation==='deviceInventory')return inventory_(input); if(input.schema!==2||!text_(input.bookId,120)||!input.bookId||!Array.isArray(input.events)||input.events.length>50)throw Error('同期形式が不正です。'); input.events.forEach(e=>validEvent_(e,input.bookId));if(input.progress)validateProgress_(input.progress); const lock=LockService.getScriptLock();lock.waitLock(20000); try{ const ss=SpreadsheetApp.openById(SHEET_ID),log=ensureSheet_(ss,'同期イベント',['イベントID','書籍ID','種類','項目ID','結果','改訂番号','内容JSON1','内容JSON2','内容JSON3','受信日時']); const rows=log.getDataRange().getValues().slice(1),seen=new Map(),latest=new Map(); for(const row of rows){if(row[1]!==input.bookId)continue;const x=JSON.parse(String(row[6])+String(row[7]||'')+String(row[8]||''));seen.set(row[0],x);if(x.result.status==='accepted')latest.set(x.event.type+':'+x.event.id,x.result.record);} const acks=[],blocked=new Set(); for(const e of input.events){let saved=seen.get(e.eventId); if(saved&&(saved.event.type!==e.type||saved.event.id!==e.id||JSON.stringify(saved.event.payload)!==JSON.stringify(e.payload)))throw Error('同じイベントIDで内容が異なります。'); if(!saved){const current=latest.get(e.type+':'+e.id),result=blocked.has(e.type+':'+e.id)?{eventId:e.eventId,status:'conflict',current}:decide_(e,current);saved={event:e,result};const json=JSON.stringify(saved);if(json.length>120000)throw Error('1件の訂正履歴が大きすぎます。');log.appendRow([e.eventId,e.bookId,e.type,e.id,result.status,result.revision||'',json.slice(0,40000),json.slice(40000,80000),json.slice(80000),new Date().toISOString()].map(safeCell_));SpreadsheetApp.flush();seen.set(e.eventId,saved);if(result.status==='accepted')latest.set(e.type+':'+e.id,result.record);} history_(ss,e,{...saved.result,at:saved.result.record?.at||new Date().toISOString()}); if(saved.result.status==='conflict')blocked.add(e.type+':'+e.id); acks.push({eventId:e.eventId,status:saved.result.status,revision:saved.result.revision,current:saved.result.current}); } // Rebuild views from the durable event log. A retry repairs interrupted projections. for(const r of latest.values())project_(ss,r); const bookHeaders=['書籍ID','シリーズ','巻','書名','言語','ページ数','元データ版','書籍フォルダID','登録日時','更新日時'],bookSheet=ensureSheet_(ss,'書籍一覧',bookHeaders),previous=bookSheet.getDataRange().getValues().find((r,i)=>i>0&&r[0]===input.bookId),bookRow=previous?previous.slice(0,10):[input.bookId,'','',String(input.bookTitle||input.bookId).slice(0,500),'ko','','','',new Date().toISOString(),''];bookRow[9]=new Date().toISOString();upsert_(ss,'書籍一覧',bookHeaders,[0],bookRow); const progress=readingProgress_(ss,input); SpreadsheetApp.flush();return {schema:2,ok:true,bookId:input.bookId,acks,records:Array.from(latest.values()),capabilities:['reading-progress-v1'],progress}; }finally{lock.releaseLock();} } // Full device snapshots; a missing book means absent only on this device. const DEVICE_REPORT_FOLDER='1ZOjyF1BheiZTXn6Bs_TmEoPCGM-8QXLD'; function inventory_(input){ if(input.schema!==2||!/^[-a-zA-Z0-9]{10,120}$/.test(input.deviceId||'')||!text_(input.deviceName,80)||!input.deviceName.trim()||!Number.isSafeInteger(input.revision)||input.revision<1||!Array.isArray(input.books)||input.books.length>1000)throw Error('端末情報が不正です。'); const ids=new Set(); for(const b of input.books){if(!b||!text_(b.id,120)||!b.id||!text_(b.bookId,120)||!b.bookId||!text_(b.title,500)||!text_(b.fileName,500)||!Number.isInteger(b.pages)||b.pages<0||b.pages>100000||!text_(b.importedAt,40)||!Number.isFinite(Date.parse(b.importedAt))||ids.has(b.id))throw Error('端末の書籍一覧が不正です。');ids.add(b.id);} const lock=LockService.getScriptLock();lock.waitLock(20000); try{ const folder=DriveApp.getFolderById(DEVICE_REPORT_FOLDER),name='device-'+input.deviceId+'.device-inventory.json',files=folder.getFilesByName(name),file=files.hasNext()?files.next():null; if(files.hasNext())throw Error('端末情報ファイルが重複しています。'); const old=file?JSON.parse(file.getBlob().getDataAsString('UTF-8')):null; if(old&&old.revision>input.revision)throw Error('端末の送信番号が古いため更新しませんでした。'); if(old&&old.revision===input.revision&&(JSON.stringify(old.books)!==JSON.stringify(input.books)||old.deviceName!==input.deviceName))throw Error('同じ送信番号の内容が異なります。'); const data=old&&old.revision===input.revision?old:{schema:'kstudy.device-inventory/1',deviceId:input.deviceId,deviceName:input.deviceName,revision:input.revision,receivedAt:new Date().toISOString(),books:input.books}; const json=JSON.stringify(data); if(!old||old.revision!==input.revision){if(file)file.setContent(json);else folder.createFile(name,json,'application/json');} const ss=SpreadsheetApp.openById(SHEET_ID); upsert_(ss,'読書端末',['端末ID','端末名','取り込み冊数','最終受信日時','送信番号'],[0],[data.deviceId,data.deviceName,data.books.length,data.receivedAt,data.revision]); const headers=['端末ID','端末名','書籍ID','保存ID','書名','ファイル名','ページ数','取り込み日時','状態','最終受信日時'],sheet=ensureSheet_(ss,'端末別の本棚',headers); for(const r of sheet.getDataRange().getValues().slice(1)){if(r[0]===data.deviceId&&!ids.has(String(r[3]))){r[1]=data.deviceName;r[8]='端末から削除済み';r[9]=data.receivedAt;upsert_(ss,'端末別の本棚',headers,[0,3],r.slice(0,10));}} for(const b of data.books)upsert_(ss,'端末別の本棚',headers,[0,3],[data.deviceId,data.deviceName,b.bookId,b.id,b.title,b.fileName,b.pages,b.importedAt,'取り込み済み',data.receivedAt]); SpreadsheetApp.flush();return {schema:2,ok:true,operation:'deviceInventory',deviceId:data.deviceId,revision:data.revision,receivedAt:data.receivedAt}; }finally{lock.releaseLock();} } function setupDeviceInventory(){ DriveApp.getFolderById(DEVICE_REPORT_FOLDER).getName(); const ss=SpreadsheetApp.openById(SHEET_ID); ensureSheet_(ss,'読書端末',['端末ID','端末名','取り込み冊数','最終受信日時','送信番号']); ensureSheet_(ss,'端末別の本棚',['端末ID','端末名','書籍ID','保存ID','書名','ファイル名','ページ数','取り込み日時','状態','最終受信日時']); SpreadsheetApp.getUi().alert('端末連携を準備しました。既存のWebアプリを新しいバージョンで再デプロイしてください。同期URLとSYNC_KEYはそのまま使えます。'); } // One monotonically numbered position per book/device. Never replace another // device's position. Learning records continue to use revision conflicts above. function validateProgress_(p){ if(!p||!/^[-a-zA-Z0-9]{10,120}$/.test(p.deviceId||'')||!Number.isSafeInteger(p.sequence)||p.sequence<1||!text_(p.pageId,120)||!p.pageId||!Number.isInteger(p.pageNumber)||p.pageNumber<1||p.pageNumber>100000||!text_(p.furthestPageId,120)||!['page','panel','speech'].includes(p.mode)||!Number.isInteger(p.panel)||p.panel<0||p.panel>10000||!text_(p.utteranceId,120)||!text_(p.updatedAt,40)||!Number.isFinite(Date.parse(p.updatedAt)))throw Error('読書位置が不正です。'); return {deviceId:p.deviceId,sequence:p.sequence,pageId:p.pageId,pageNumber:p.pageNumber,furthestPageId:p.furthestPageId,mode:p.mode,panel:p.panel,utteranceId:p.utteranceId,updatedAt:p.updatedAt}; } function readingProgress_(ss,input){ const headers=['書籍ID','端末ID','送信番号','読書位置JSON','受信日時'],sheet=ensureSheet_(ss,'読書位置',headers); let rows=sheet.getDataRange().getValues().slice(1); if(input.progress){ const p=validateProgress_(input.progress),old=rows.find(r=>r[0]===input.bookId&&r[1]===p.deviceId); if(!old||p.sequence>Number(old[2])){ upsert_(ss,'読書位置',headers,[0,1],[input.bookId,p.deviceId,p.sequence,JSON.stringify(p),new Date().toISOString()]); rows=sheet.getDataRange().getValues().slice(1); }else if(p.sequence===Number(old[2])&&JSON.stringify(p)!==String(old[3]))throw Error('同じ送信番号の読書位置が異なります。'); } return rows.filter(r=>r[0]===input.bookId).map(r=>validateProgress_(JSON.parse(String(r[3])))); }