100 lines
3.9 KiB
JavaScript
100 lines
3.9 KiB
JavaScript
import { google } from 'googleapis';
|
|
|
|
export class RateLimiter {
|
|
constructor() {
|
|
this.nextAllowedAt = 0;
|
|
}
|
|
|
|
async run(requestsPerMinute, operation) {
|
|
for (let attempt = 0; ; attempt += 1) {
|
|
const interval = Math.ceil(60_000 / Math.max(1, Number(requestsPerMinute) || 45));
|
|
const scheduledAt = Math.max(Date.now(), this.nextAllowedAt);
|
|
this.nextAllowedAt = scheduledAt + interval;
|
|
const wait = scheduledAt - Date.now();
|
|
if (wait > 0) await new Promise((resolve) => setTimeout(resolve, wait));
|
|
try {
|
|
return await operation();
|
|
} catch (error) {
|
|
const status = error.code || error.response?.status;
|
|
if (attempt >= 2 || (status !== 429 && (status < 500 || status > 599))) throw error;
|
|
const retryAfter = retryAfterMilliseconds(error);
|
|
const backoff = retryAfter ?? 1_000 * 2 ** attempt;
|
|
this.nextAllowedAt = Math.max(this.nextAllowedAt, Date.now() + backoff);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
function retryAfterMilliseconds(error) {
|
|
const value = error.response?.headers?.get?.('retry-after') || error.response?.headers?.['retry-after'];
|
|
if (!value) return null;
|
|
const seconds = Number(value);
|
|
if (Number.isFinite(seconds)) return Math.max(0, seconds * 1_000);
|
|
const date = Date.parse(value);
|
|
return Number.isNaN(date) ? null : Math.max(0, date - Date.now());
|
|
}
|
|
|
|
export function createSheetsClient(googleConfig) {
|
|
if (googleConfig.mode === 'serviceAccount') {
|
|
const credentials = JSON.parse(googleConfig.serviceAccountJson);
|
|
const auth = new google.auth.GoogleAuth({
|
|
credentials,
|
|
scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly']
|
|
});
|
|
return google.sheets({ version: 'v4', auth });
|
|
}
|
|
|
|
if (!googleConfig.apiKey) throw new Error('A Google API key is required.');
|
|
return google.sheets({ version: 'v4', auth: googleConfig.apiKey });
|
|
}
|
|
|
|
export async function listTabs(client, spreadsheetId, limiter, requestsPerMinute) {
|
|
const response = await limiter.run(requestsPerMinute, () => client.spreadsheets.get({
|
|
spreadsheetId,
|
|
fields: 'properties.title,sheets.properties(sheetId,title)'
|
|
}));
|
|
return {
|
|
title: response.data.properties?.title ?? spreadsheetId,
|
|
sheets: (response.data.sheets ?? []).map(({ properties }) => ({
|
|
id: properties.sheetId,
|
|
title: properties.title
|
|
}))
|
|
};
|
|
}
|
|
|
|
export async function readTabs(client, publication, limiter, requestsPerMinute) {
|
|
const maxRows = Math.max(1, Math.min(100_000, Number(publication.maxRows) || 10_000));
|
|
const ranges = publication.sheets.map((sheet) => {
|
|
const title = `'${sheet.title.replaceAll("'", "''")}'`;
|
|
return publication.range?.trim() ? `${title}!${publication.range.trim()}` : `${title}!1:${maxRows}`;
|
|
});
|
|
const response = await limiter.run(requestsPerMinute, () => client.spreadsheets.values.batchGet({
|
|
spreadsheetId: publication.spreadsheetId,
|
|
ranges,
|
|
majorDimension: 'ROWS'
|
|
}));
|
|
|
|
return (response.data.valueRanges ?? []).map((valueRange, index) => formatSheet(
|
|
publication.sheets[index].title,
|
|
valueRange.values ?? [],
|
|
publication
|
|
));
|
|
}
|
|
|
|
function formatSheet(sheet, values, publication) {
|
|
const maxRows = Math.max(1, Math.min(100_000, Number(publication.maxRows) || 10_000));
|
|
const limitedValues = values.slice(0, maxRows);
|
|
const rows = publication.omitBlankRows
|
|
? limitedValues.filter((row) => row.some((cell) => String(cell).trim() !== ''))
|
|
: limitedValues;
|
|
if (publication.dataFormat !== 'objects') return { sheet, values: rows };
|
|
const [headers = [], ...records] = rows;
|
|
const normalizedHeaders = headers.map((header) => String(header).trim());
|
|
if (normalizedHeaders.some((header) => !header) || new Set(normalizedHeaders).size !== normalizedHeaders.length) {
|
|
throw new Error(`Object output for "${sheet}" requires non-empty, unique header names.`);
|
|
}
|
|
return {
|
|
sheet,
|
|
rows: records.map((row) => Object.fromEntries(normalizedHeaders.map((header, index) => [header, row[index] ?? ''])))
|
|
};
|
|
} |