aaronrene / knowtation public
google-sheets.mjs
117 lines 4.5 KB
Raw
sha256:65ccb454656ea5acdea0a10e559b78bcde1eb6ff753ecc2911bc99d1c3d7cadd feat(calendar): enforce agent context tiers in retrieval AP… Human minor ⚠ breaking 1 day ago
1 /**
2 * Google Sheets API — read a range, same tabular note model as generic-csv (header row + data rows).
3 * Requires: service account with spreadsheet shared (Viewer) or `GOOGLE_SERVICE_ACCOUNT_JSON`
4 * (JSON string) or `GOOGLE_APPLICATION_CREDENTIALS` (path to a .json key file).
5 */
6
7 import fs from 'fs';
8 import { GoogleAuth } from 'google-auth-library';
9 import { importStringMatrixToNotes } from './tabular-import.mjs';
10
11 const SCOPE = 'https://www.googleapis.com/auth/spreadsheets.readonly';
12
13 /**
14 * @returns {Record<string, unknown>}
15 */
16 function loadServiceAccount() {
17 if (process.env.GOOGLE_SERVICE_ACCOUNT_JSON && String(process.env.GOOGLE_SERVICE_ACCOUNT_JSON).trim().length) {
18 return JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_JSON);
19 }
20 const p = process.env.GOOGLE_APPLICATION_CREDENTIALS
21 ? String(process.env.GOOGLE_APPLICATION_CREDENTIALS).trim()
22 : '';
23 if (p && fs.existsSync(p) && fs.statSync(p).isFile()) {
24 return JSON.parse(fs.readFileSync(p, 'utf8'));
25 }
26 throw new Error(
27 'google-sheets import: set GOOGLE_SERVICE_ACCOUNT_JSON to the service account JSON string, or GOOGLE_APPLICATION_CREDENTIALS to a path to the key file. Share the spreadsheet with that service account (Viewer) or use a sheet owned by the same GCP project.',
28 );
29 }
30
31 /**
32 * @param {string} spreadsheetId
33 * @param {string} accessToken
34 * @param {string} [explicitRange]
35 * @returns {Promise<(string|number)[][]>}
36 */
37 async function fetchValuesMatrix(spreadsheetId, accessToken, explicitRange) {
38 const id = encodeURIComponent(spreadsheetId);
39 let rangeParam = (explicitRange && String(explicitRange).trim()) || '';
40 if (!rangeParam) {
41 const metaUrl = `https://sheets.googleapis.com/v4/spreadsheets/${id}?fields=sheets.properties(title,sheetId)`;
42 const mRes = await fetch(metaUrl, { headers: { Authorization: `Bearer ${accessToken}` } });
43 if (!mRes.ok) {
44 const t = await mRes.text();
45 throw new Error(`google-sheets: could not read spreadsheet metadata (${mRes.status}): ${t.slice(0, 200)}`);
46 }
47 const mJson = await mRes.json();
48 const titles = (mJson.sheets || [])
49 .map((s) => s && s.properties && s.properties.title)
50 .filter((x) => x != null && String(x).length > 0);
51 if (!titles.length) {
52 return [];
53 }
54 const esc = String(titles[0]).replace(/'/g, "''");
55 rangeParam = `'${esc}'!A1:ZZ10000`;
56 }
57
58 const vUrl = `https://sheets.googleapis.com/v4/spreadsheets/${id}/values/${encodeURIComponent(rangeParam)}`;
59 const vRes = await fetch(vUrl, { headers: { Authorization: `Bearer ${accessToken}` } });
60 if (!vRes.ok) {
61 const t = await vRes.text();
62 throw new Error(`google-sheets: could not read range (${vRes.status}): ${t.slice(0, 200)}`);
63 }
64 const vJson = await vRes.json();
65 const values = vJson && Array.isArray(vJson.values) ? vJson.values : [];
66 if (values.length < 2) {
67 return [];
68 }
69 let maxCol = 0;
70 for (const row of values) {
71 if (Array.isArray(row) && row.length > maxCol) {
72 maxCol = row.length;
73 }
74 }
75 return values.map((row) => {
76 const r = Array.isArray(row) ? row : [];
77 const out = [];
78 for (let c = 0; c < maxCol; c++) {
79 out.push(c < r.length && r[c] != null ? String(r[c]) : '');
80 }
81 return out;
82 });
83 }
84
85 /**
86 * @param {string} input - Spreadsheet id (from the sheet URL, not a filesystem path for typical use)
87 * @param {{ vaultPath: string, outputBase: string, project?: string, tags: string[], dryRun: boolean, sheetsRange?: string }} ctx
88 */
89 export async function importGoogleSheets(input, ctx) {
90 const id = String(input || '').trim();
91 if (!id) {
92 throw new Error('google-sheets: provide a spreadsheet id (the long id in the Google Sheets URL).');
93 }
94 if (/[\\/]/.test(id) || (id.length > 80 && id.includes('.'))) {
95 throw new Error('google-sheets: input should be a spreadsheet id string, not a file path. Paste only the id from the URL.');
96 }
97
98 const sa = loadServiceAccount();
99 const auth = new GoogleAuth({ credentials: sa, scopes: [SCOPE] });
100 const client = await auth.getClient();
101 const token = await client.getAccessToken();
102 const accessToken = token && 'token' in token ? token.token : token;
103 if (!accessToken) {
104 throw new Error('google-sheets: failed to obtain an OAuth access token from the service account.');
105 }
106
107 const matrix = await fetchValuesMatrix(id, accessToken, ctx.sheetsRange);
108 if (!matrix.length) {
109 return { imported: [], count: 0 };
110 }
111 return importStringMatrixToNotes(matrix, ctx, {
112 source: 'google-sheets-import',
113 fileLabel: id,
114 subdir: 'sheets',
115 fileKey: 'spreadsheet_id',
116 });
117 }
File History 2 commits
sha256:65ccb454656ea5acdea0a10e559b78bcde1eb6ff753ecc2911bc99d1c3d7cadd feat(calendar): enforce agent context tiers in retrieval AP… Human minor ⚠ 1 day ago
sha256:9103f98c89257ed2b01c237cea895dabb3e85ea337dccb1161c175e4422355b6 docs: accept Calendar Events v0 spec with Phase 0 security … Human 1 day ago