1import { Actor, log } from 'apify';
2import { GoogleClient, GoogleError, parseServiceAccount, parseSpreadsheetId, parseGid, q, colLetter } from './google.js';
3import { flatten, cell, headerFor, keyOf, rowsToObjects, rangeStartCol, chunkRows } from './transform.js';
4
5const EV_READ = 'row-read';
6const EV_WRITE = 'row-written';
7const LOCK_KEY = 'apifyOwnerUserId';
8const MAX_CELLS = 10_000_000;
9class InputError extends Error {}
10
11await Actor.init();
12let sa = null; let usingOwnKey = false;
13try {
14 const input = (await Actor.getInput()) ?? {};
15 const {
16 operation = 'write', spreadsheet, sheetName = '', range = '',
17 datasetId: datasetIdIn = '', rawData = null, writeMode = 'append',
18 headerRow = true, columns = [], deduplicateBy = [], arrayHandling = 'join',
19 valueInputOption = 'RAW', valueRender = 'FORMATTED_VALUE', skipEmptyRows = true,
20 maxRows = 0, serviceAccountKey = '',
21 } = input;
22
23
24 if (serviceAccountKey && String(serviceAccountKey).trim()) {
25 try { sa = parseServiceAccount(serviceAccountKey); } catch (e) { throw new InputError(e.message); }
26 usingOwnKey = true;
27 } else {
28 sa = parseServiceAccount(process.env.GOOGLE_SA_JSON);
29 if (!sa) throw new Error('This actor is missing its built-in Google credentials. Provide your own service account key in "Your own service account key".');
30 }
31 const g = new GoogleClient(sa);
32 log.info(`Using service account ${g.email}${usingOwnKey ? ' (your own key)' : ''}.`);
33
34 const id = parseSpreadsheetId(spreadsheet);
35 if (!id) throw new InputError('Paste the spreadsheet URL (https://docs.google.com/spreadsheets/d/...) or its ID in "Spreadsheet".');
36 const gid = parseGid(spreadsheet);
37 let op = String(operation).toLowerCase();
38 const hasWriteSource = !!(String(datasetIdIn || input?.payload?.resource?.defaultDatasetId || input?.resource?.defaultDatasetId || '').trim()
39 || (Array.isArray(rawData) && rawData.length));
40 if (op === 'write' && !hasWriteSource) {
41
42 op = 'read';
43 log.warning('Nothing to write (no datasetId or rawData), so reading the sheet instead. To export, pass a datasetId or rawData.');
44 }
45 if (!['read', 'write'].includes(op)) throw new InputError('Operation must be "read" (sheet to dataset) or "write" (dataset to sheet).');
46
47
48 let file;
49 try { file = await g.drive(`/files/${id}?fields=name,capabilities(canEdit)&supportsAllDrives=true`); }
50 catch (e) { throw accessError(e, g.email, id, op); }
51 const canEdit = !!file.capabilities?.canEdit;
52 if (op === 'write' && !canEdit) throw new InputError(`The service account can open "${file.name}" but cannot edit it: share the sheet with ${g.email} as Editor (not Viewer), then run again.`);
53 let meta;
54 try { meta = await g.sheets(`/${id}?fields=spreadsheetUrl,properties(title),sheets(properties(sheetId,title,index,gridProperties))`); }
55 catch (e) { throw accessError(e, g.email, id, op); }
56
57
58 const userId = process.env.APIFY_USER_ID || Actor.getEnv().userId;
59 if (!usingOwnKey && userId) await enforceLock(g, id, userId, canEdit, file.name);
60
61 let sheet = pickSheet(meta.sheets, sheetName, gid);
62 const summary = { operation: op, spreadsheetId: id, spreadsheetTitle: meta.properties?.title, spreadsheetUrl: meta.spreadsheetUrl, serviceAccount: g.email };
63
64 if (op === 'read') {
65 if (!sheet) throw new InputError(`Sheet tab "${sheetName}" not found. Tabs in this spreadsheet: ${meta.sheets.map((s) => s.properties.title).join(', ')}.`);
66 const a1 = range ? `${q(sheet.properties.title)}!${String(range).replace(/^.*!/, '')}` : q(sheet.properties.title);
67 const res = await g.sheets(`/${id}/values/${encodeURIComponent(a1)}?valueRenderOption=${valueRender === 'UNFORMATTED_VALUE' ? 'UNFORMATTED_VALUE' : 'FORMATTED_VALUE'}&dateTimeRenderOption=FORMATTED_STRING`);
68 let { headers, rows } = rowsToObjects(res.values ?? [], { header: headerRow, skipEmpty: skipEmptyRows, startCol: rangeStartCol(range) });
69 if (columns?.length) rows = rows.map((r) => Object.fromEntries(columns.map((c) => [c, r[c] ?? ''])));
70 let dupes = 0;
71 if (deduplicateBy?.length) {
72 const missing = deduplicateBy.filter((c) => !(columns?.length ? columns : headers).includes(c));
73 if (missing.length) throw new InputError(`Deduplicate column(s) not found in the sheet header: ${missing.join(', ')}. Header: ${headers.join(', ')}`);
74 const seen = new Set(); const kept = [];
75 for (const r of rows) { const k = keyOf(r, deduplicateBy); if (seen.has(k)) { dupes++; continue; } seen.add(k); kept.push(r); }
76 rows = kept;
77 }
78 if (maxRows > 0) rows = rows.slice(0, maxRows);
79 const beforeLimit = rows.length;
80 rows = rows.slice(0, chargeLimit(EV_READ, rows.length));
81 const cutByCost = beforeLimit - rows.length;
82 for (const chunk of chunkRows(rows, { maxRows: 1000, maxBytes: 5_000_000 })) {
83 await Actor.pushData(chunk);
84 await Actor.charge({ eventName: EV_READ, count: chunk.length });
85 }
86 const zeroResultsReason = rows.length ? null : (cutByCost ? 'Your maximum cost per run allows 0 rows.' : `The tab "${sheet.properties.title}"${range ? ` (range ${range})` : ''} has no data rows${headerRow ? ' below the header row' : ''}${dupes ? ` after removing ${dupes} duplicates` : ''}.`);
87 if (!rows.length) await Actor.pushData({ status: 'no_results', reason: zeroResultsReason, spreadsheetUrl: meta.spreadsheetUrl, sheetName: sheet.properties.title });
88 Object.assign(summary, { sheetName: sheet.properties.title, range: res.range, columns: columns?.length ? columns : headers, rowsRead: rows.length, duplicatesSkipped: dupes, rowsSkippedByCostLimit: cutByCost, zeroResultsReason });
89 log.info(`Read ${rows.length} rows from "${sheet.properties.title}" into the dataset.`);
90 await Actor.setValue('OUTPUT', summary);
91 await Actor.setStatusMessage(rows.length ? `Read ${rows.length} rows from ${sheet.properties.title}${cutByCost ? `; ${cutByCost} more rows skipped because of your maximum cost per run` : ''}` : `0 results: ${zeroResultsReason}`, { isStatusMessageTerminal: true });
92 } else {
93
94 const dsId = String(datasetIdIn || input?.payload?.resource?.defaultDatasetId || input?.resource?.defaultDatasetId || '').trim();
95 let items = [];
96 if (Array.isArray(rawData) && rawData.length) items = rawData;
97 else if (dsId) items = await loadDataset(dsId, maxRows);
98 else throw new InputError('Provide a dataset ID (e.g. from another actor run) or JSON rows in "Raw data" to write.');
99 if (maxRows > 0) items = items.slice(0, maxRows);
100 const flat = items.map((it) => flatten(it, { arrays: arrayHandling }));
101
102
103 let created = false;
104 if (!sheet) {
105 const title = (sheetName || 'Sheet1').slice(0, 100);
106 const r = await g.sheets(`/${id}:batchUpdate`, 'POST', { requests: [{ addSheet: { properties: { title } } }] });
107 sheet = { properties: r.replies[0].addSheet.properties }; created = true;
108 log.info(`Created sheet tab "${title}".`);
109 }
110 const title = sheet.properties.title; const sid = sheet.properties.sheetId;
111
112 let existing = [];
113 if (!created) {
114 const res = await g.sheets(`/${id}/values/${encodeURIComponent(q(title))}?valueRenderOption=UNFORMATTED_VALUE`);
115 existing = res.values ?? [];
116 }
117 const mode = String(writeMode).toLowerCase();
118 if (!['append', 'replace'].includes(mode)) throw new InputError('Write mode must be "append" or "replace".');
119
120 let header; let startRow; let dupes = 0; let writeHeader = headerRow;
121 const existingHeader = headerRow && mode === 'append' && existing.length ? (existing[0] ?? []).map((h) => String(h ?? '').trim()) : null;
122 if (existingHeader && existingHeader.some(Boolean)) {
123 header = [...existingHeader];
124 for (const k of headerFor(flat, columns)) if (!header.includes(k)) header.push(k);
125 } else header = headerFor(flat, columns);
126 if (!header.length) header = ['value'];
127
128 let rowsOut = flat;
129 if (deduplicateBy?.length) {
130 const missing = deduplicateBy.filter((c) => !header.includes(c));
131 if (missing.length) throw new InputError(`Deduplicate column(s) not found in the data: ${missing.join(', ')}.`);
132 const seen = new Set();
133 if (mode === 'append' && existingHeader) {
134 const { rows: old } = rowsToObjects(existing, { header: true, skipEmpty: true });
135 for (const o of old) seen.add(keyOf(o, deduplicateBy));
136 }
137 rowsOut = [];
138 for (const r of flat) { const k = keyOf(r, deduplicateBy); if (seen.has(k)) { dupes++; continue; } seen.add(k); rowsOut.push(r); }
139 }
140 const beforeLimit = rowsOut.length;
141 rowsOut = rowsOut.slice(0, chargeLimit(EV_WRITE, rowsOut.length));
142 const cutByCost = beforeLimit - rowsOut.length;
143
144 let truncated = 0;
145 const matrix = rowsOut.map((r) => header.map((h) => { const [v, t] = cell(r[h]); if (t) truncated++; return v; }));
146
147 if (mode === 'append') {
148 startRow = existing.length;
149 if (headerRow && !(existingHeader && existingHeader.some(Boolean))) { writeHeader = true; }
150 else if (headerRow) writeHeader = header.length !== existingHeader.length;
151 else writeHeader = false;
152 if (headerRow && startRow === 0) startRow = 0;
153 } else startRow = 0;
154
155 const needRows = (mode === 'replace' ? 0 : startRow) + matrix.length + (headerRow && (mode === 'replace' || startRow === 0) ? 1 : 0);
156 const needCols = header.length;
157 const grid = sheet.properties.gridProperties ?? { rowCount: 1000, columnCount: 26 };
158 const otherCells = meta.sheets.filter((s) => s.properties.sheetId !== sid).reduce((a, s) => a + (s.properties.gridProperties?.rowCount ?? 0) * (s.properties.gridProperties?.columnCount ?? 0), 0);
159 const newRows = Math.max(grid.rowCount, needRows); const newCols = Math.max(grid.columnCount, needCols);
160 if (otherCells + newRows * newCols > MAX_CELLS) throw new InputError(`This write needs ${newRows.toLocaleString()} x ${newCols} cells, which would exceed Google Sheets' 10 million cell limit per spreadsheet. Use a new spreadsheet, fewer columns ("Columns") or "Max rows".`);
161 if (newRows > grid.rowCount || newCols > grid.columnCount) {
162 await g.sheets(`/${id}:batchUpdate`, 'POST', { requests: [{ updateSheetProperties: { properties: { sheetId: sid, gridProperties: { rowCount: newRows, columnCount: newCols } }, fields: 'gridProperties.rowCount,gridProperties.columnCount' } }] });
163 }
164
165 const vio = valueInputOption === 'USER_ENTERED' ? 'USER_ENTERED' : 'RAW';
166 const put = async (row0, values) => {
167 const a1 = `${q(title)}!A${row0 + 1}:${colLetter(Math.max(0, header.length - 1))}${row0 + values.length}`;
168 await g.sheets(`/${id}/values/${encodeURIComponent(a1)}?valueInputOption=${vio}`, 'PUT', { range: a1, majorDimension: 'ROWS', values });
169 };
170
171 let written = 0; let cursor;
172 if (mode === 'replace') {
173 cursor = 0;
174 if (headerRow) { await put(0, [header]); cursor = 1; }
175 for (const chunk of chunkRows(matrix)) { await put(cursor, chunk); cursor += chunk.length; written += chunk.length; await Actor.charge({ eventName: EV_WRITE, count: chunk.length }); }
176
177 const oldRows = existing.length; const oldCols = Math.max(0, ...existing.map((r) => r.length));
178 const clears = [];
179 if (oldRows > cursor) clears.push(`${q(title)}!A${cursor + 1}:${colLetter(Math.max(oldCols, header.length) - 1)}${oldRows}`);
180 if (oldCols > header.length) clears.push(`${q(title)}!${colLetter(header.length)}1:${colLetter(oldCols - 1)}${Math.min(cursor, oldRows) || 1}`);
181 if (clears.length) await g.sheets(`/${id}/values:batchClear`, 'POST', { ranges: clears });
182 } else {
183 cursor = startRow;
184 if (headerRow && startRow === 0) { await put(0, [header]); cursor = 1; }
185 else if (writeHeader) await put(0, [header]);
186 for (const chunk of chunkRows(matrix)) { await put(cursor, chunk); cursor += chunk.length; written += chunk.length; await Actor.charge({ eventName: EV_WRITE, count: chunk.length }); }
187 }
188 if (truncated) log.warning(`${truncated} cell(s) were longer than 50,000 characters (Google's per-cell limit) and were truncated.`);
189 const zeroResultsReason = written ? null : (!items.length ? `The source ${dsId ? `dataset ${dsId}` : 'rawData'} has no rows.` : dupes === items.length ? `All ${dupes} rows already exist in the sheet (deduplicateBy: ${deduplicateBy.join(', ')}).` : cutByCost ? 'Your maximum cost per run allows 0 rows.' : 'Nothing to write.');
190 Object.assign(summary, { sheetName: title, sheetCreated: created, writeMode: mode, datasetId: dsId || null, inputRows: items.length, rowsWritten: written, duplicatesSkipped: dupes, rowsSkippedByCostLimit: cutByCost, columns: header, truncatedCells: truncated, lastRow: cursor, zeroResultsReason });
191 log.info(`Wrote ${written} rows to "${title}" (${mode}); ${dupes} duplicates skipped.`);
192 await Actor.setValue('OUTPUT', summary);
193 await Actor.setStatusMessage(written ? `Wrote ${written} rows to ${title} (${mode})${dupes ? `, ${dupes} duplicates skipped` : ''}${cutByCost ? `; ${cutByCost} rows not written because of your maximum cost per run` : ''}` : `0 rows written: ${zeroResultsReason}`, { isStatusMessageTerminal: true });
194 }
195 await Actor.exit();
196} catch (e) {
197 const msg = e instanceof InputError || e instanceof GoogleError ? e.message : `Unexpected error: ${e.message}`;
198 log.error(msg);
199 await Actor.setValue('OUTPUT', { error: msg }).catch(() => {});
200 await Actor.fail(msg);
201}
202
203function pickSheet(sheets, name, gid) {
204 if (name) return sheets.find((s) => s.properties.title === name) ?? sheets.find((s) => s.properties.title.toLowerCase() === String(name).toLowerCase().trim()) ?? null;
205 if (gid !== null) { const s = sheets.find((x) => x.properties.sheetId === gid); if (s) return s; }
206 return [...sheets].sort((a, b) => a.properties.index - b.properties.index)[0] ?? null;
207}
208
209function accessError(e, email, id, op) {
210 if (e.status === 404 || e.status === 403) {
211 return new InputError(`Can't open spreadsheet ${id}: share the sheet with ${email} as ${op === 'write' ? 'Editor' : 'Editor (or Viewer for read-only)'} (Share button in Google Sheets, paste the email, untick "Notify people"), and check the URL/ID. AI agents: ask the user to share the sheet with ${email}, then run again.`);
212 }
213 return e;
214}
215
216async function enforceLock(g, id, userId, canEdit, name) {
217 const r = await g.sheets(`/${id}/developerMetadata:search`, 'POST', { dataFilters: [{ developerMetadataLookup: { metadataKey: LOCK_KEY, locationType: 'SPREADSHEET' } }] });
218 const owners = (r.matchedDeveloperMetadata ?? []).map((m) => m.developerMetadata.metadataValue);
219 if (owners.length) {
220 if (!owners.includes(userId)) throw new InputError(`"${name}" is locked to another Apify account. For your security, a spreadsheet shared with this actor's service account can only be used by the first Apify account that used it. Use your own service account key, or ask the sheet owner.`);
221 return;
222 }
223 if (canEdit) {
224 await g.sheets(`/${id}:batchUpdate`, 'POST', { requests: [{ createDeveloperMetadata: { developerMetadata: { metadataKey: LOCK_KEY, metadataValue: userId, location: { spreadsheet: true }, visibility: 'PROJECT' } } }] });
225 log.info('Locked this spreadsheet to your Apify account (only your account can use it through this actor).');
226 } else {
227 log.warning('Shared as Viewer: the sheet lock needs Editor access. Share as Editor so that only your Apify account can use this spreadsheet through the actor.');
228 }
229}
230
231function chargeLimit(eventName, wanted) {
232 try {
233 const cm = Actor.getChargingManager?.();
234 const n = cm?.calculateMaxEventChargeCountWithinLimit?.(eventName);
235 if (typeof n === 'number' && Number.isFinite(n) && n < wanted) { log.warning(`Your max cost per run allows ${n} of ${wanted} rows; processing ${n}.`); return Math.max(0, n); }
236 } catch { }
237 return wanted;
238}
239
240async function loadDataset(dsId, maxRows) {
241 const client = Actor.apifyClient; const out = []; let offset = 0; const limit = 10000;
242 try { await client.dataset(dsId).get().then((d) => { if (!d) throw new Error('nf'); }); }
243 catch { throw new InputError(`Dataset "${dsId}" not found or not accessible from your account. Use the dataset ID from the run's Storage tab (or {{resource.defaultDatasetId}} in an integration).`); }
244 for (;;) {
245 const { items } = await client.dataset(dsId).listItems({ offset, limit, clean: true });
246 out.push(...items); offset += items.length;
247 if (items.length < limit || (maxRows > 0 && out.length >= maxRows)) break;
248 }
249 return out;
250}
