# v9: Google Sheets tL h p t  Vertex AI| X p D tL\ XX 0D l. t 줸 X pt0| Google Sheets <\ X,  p t ȸ\ 0T ܤ\D D1i. ## 1: Google Sheets t|t pt `src/googleSheetClient.ts` |D UX Freee p tL| h X 0D i. < D\ | hD li: ```typescript // src/googleSheetClient.ts // ܸ t Ux  1 const ensureSheetExists = async (sheetName: string) => { try { const sheets = await getGoogleSheetClient(); const response = await sheets.spreadsheets.get({ spreadsheetId: GOOGLE_SHEET_ID, }); const sheetExists = response.data.sheets?.some( sheet => sheet.properties?.title === sheetName ); if (!sheetExists) { await sheets.spreadsheets.batchUpdate({ spreadsheetId: GOOGLE_SHEET_ID, requestBody: { requests: [{ addSheet: { properties: { title: sheetName, }, }, }], }, }); console.log(`ܸ "${sheetName}"| 1.`); } } catch (error: any) { console.error('ܸ Ux/1 (:', error.message); throw error; } }; // T t Ux const checkHeaderExists = async (sheetName: string): Promise => { try { const sheets = await getGoogleSheetClient(); const response = await sheets.spreadsheets.values.get({ spreadsheetId: GOOGLE_SHEET_ID, range: `${sheetName}!A1:I1`, }); return !!(response.data.values && response.data.values.length > 0); } catch (error) { return false; } }; // 0t p ID 8$0 ( l) const getExistingDealIds = async (sheetName: string): Promise> => { try { const sheets = await getGoogleSheetClient(); const response = await sheets.spreadsheets.values.get({ spreadsheetId: GOOGLE_SHEET_ID, range: `${sheetName}!A:A`, }); const ids = new Set(); if (response.data.values) { // (T) x response.data.values.slice(1).forEach(row => { if (row[0]) { ids.add(row[0]); } }); } return ids; } catch (error) { return new Set(); } }; ``` ## 2: p h l tL h p D Google Sheets X Tx h| li: ```typescript // src/googleSheetClient.ts export const appendFreeeDeals = async ( deals: any[], categories?: Map, sheetName: string = 'Freeep' ) => { try { const sheets = await getGoogleSheetClient(); // ܸ t Ux  1 await ensureSheetExists(sheetName); // T Ux const hasHeader = await checkHeaderExists(sheetName); if (!hasHeader) { // T (tL h) const headers = [ ['pID', '', '', 'a', '', ' ', '$', 'tL', ']|'] ]; await sheets.spreadsheets.values.append({ spreadsheetId: GOOGLE_SHEET_ID, range: `${sheetName}!A1`, valueInputOption: 'USER_ENTERED', requestBody: { values: headers, }, }); console.log('T| .'); } // 0t p ID 8$0 ( l) const existingIds = await getExistingDealIds(sheetName); // \ p D0 const newDeals = deals.filter(deal => !existingIds.has(deal.id.toString())); if (newDeals.length === 0) { console.log('` \ p Ƶ.'); return { added: 0, skipped: deals.length }; } // p D pt0\ X (tL h) const rows = newDeals.map(deal => [ deal.id.toString(), deal.issue_date || '', deal.type === 'income' ? '' : '', deal.amount || 0, deal.status === 'settled' ? 'D' : deal.status, deal.partner_id || '', deal.ref_number || '', categories ? (categories.get(deal.id) || '0') : 'X', new Date().toISOString(), ]); // pt0 const response = await sheets.spreadsheets.values.append({ spreadsheetId: GOOGLE_SHEET_ID, range: `${sheetName}!A:I`, valueInputOption: 'USER_ENTERED', requestBody: { values: rows, }, }); console.log(` ${newDeals.length}X p| . (${deals.length - newDeals.length}  )`); return { added: newDeals.length, skipped: deals.length - newDeals.length }; } catch (error: any) { console.error('L Google Sheets pt0| X p (:', error.message); throw error; } }; ``` ## 3: i l 1 0D iX `src/index.ts`| D1i: ```typescript // src/index.ts import { getDeals } from './freeeClient'; import { appendFreeeDeals } from './googleSheetClient'; import { classifyTransactionsBatch } from './vertexAiClient'; const main = async () => { console.log('= Freee Vertex AI Google Sheets 0T ܑ...\n'); const companyId = parseInt(process.env.FREEE_BUSINESS_ID || '', 10); if (!companyId) { throw new Error('X  FREEE_BUSINESS_ID ,t $ JX.'); } // p` 0 $ (: t ) const today = new Date(); const firstDayOfMonth = new Date(today.getFullYear(), today.getMonth(), 1); const startDate = firstDayOfMonth.toISOString().split('T')[0]; const endDate = today.toISOString().split('T')[0]; console.log(`= p 0: ${startDate} ~ ${endDate}\n`); try { // 1. Freee p 8$0 console.log('1 Freee API p p ...'); const deals = await getDeals(companyId, startDate, endDate); console.log(`  ${deals.length}X p D 8T.\n`); if (deals.length === 0) { console.log('` p Ƶ.'); return; } // 2. Vertex AI\ tL X console.log('2 Vertex AI\ tL X ...'); const categories = await classifyTransactionsBatch(deals); console.log(`  ${categories.size}X p tL X D\n`); // 3. Google Sheets console.log('3 Google Sheets p ...'); const result = await appendFreeeDeals(deals, categories); console.log('\n 0T D!'); console.log(` -  p: ${result.added}`); console.log(` -  : ${result.skipped}`); console.log(` - AI X tL: ${categories.size}\n`); console.log('= Google Sheet Ux:'); console.log(' https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID/edit\n'); } catch (error: any) { console.error('\nL $X :', error.message); throw error; } }; main().catch(error => { console.error('  $X :', error); process.exit(1); }); ``` ## 4:  Ux t i l| X  \8| Li: ```bash npx ts-node src/index.ts ```  %: ``` = Freee Vertex AI Google Sheets 0T ܑ... = p 0: 2025-10-31 ~ 2025-11-27 1 Freee API p p ...  11X p D 8T. 2 Vertex AI\ tL X ... Vertex AI\ 11X p X ... ĉ: 5/11 ĉ: 10/11 ĉ: 11/11  11X p tL X D 3 Google Sheets p ...  11X p| . (0  )  0T D! -  p: 11 -  : 0 - AI X tL: 11 ``` ## Google Sheet Ux Google Sheets| t L @ <\ pt0  D : | pID | | | a | |  | $ | tL | ]| | |--------|------|------|------|------|--------|------|----------|----------| | 12345 | 2025-11-22 | | 2550 | D | 123 | AWS |  | 2025-11-27T... | | 12346 | 2025-11-20 | | 16680 | D | 456 | ݬ | D | 2025-11-27T... | ## 0 1. **  p**: p ID| 0<\ t  p 2. ** ܸ 1**: \ ܸ