import { google, sheets_v4 } from "googleapis";
let sheetsClient: sheets_v4.Sheets | null = null;
export function initializeSheetsClient(): void {
const oauthCredentials = process.env.GOOGLE_OAUTH_CREDENTIALS;
const serviceAccountKey = process.env.GOOGLE_SERVICE_ACCOUNT_KEY;
if (oauthCredentials) {
// Use OAuth 2.0 credentials (acts as user)
const { client_id, client_secret, refresh_token } = JSON.parse(oauthCredentials);
const oauth2Client = new google.auth.OAuth2(client_id, client_secret);
oauth2Client.setCredentials({ refresh_token });
sheetsClient = google.sheets({ version: "v4", auth: oauth2Client });
console.log("Initialized Sheets client with OAuth credentials");
} else if (serviceAccountKey) {
// Fall back to service account
const auth = new google.auth.GoogleAuth({
credentials: JSON.parse(serviceAccountKey),
scopes: ["https://www.googleapis.com/auth/spreadsheets.readonly"],
});
sheetsClient = google.sheets({ version: "v4", auth });
console.log("Initialized Sheets client with service account");
} else {
throw new Error(
"Either GOOGLE_OAUTH_CREDENTIALS or GOOGLE_SERVICE_ACCOUNT_KEY environment variable is required"
);
}
}
function getClient(): sheets_v4.Sheets {
if (!sheetsClient) {
initializeSheetsClient();
}
return sheetsClient!;
}
export async function getSheetNames(spreadsheetId: string): Promise<string[]> {
const client = getClient();
const response = await client.spreadsheets.get({
spreadsheetId,
fields: "sheets.properties.title",
});
const sheets = response.data.sheets || [];
return sheets.map((sheet) => sheet.properties?.title || "").filter(Boolean);
}
export async function getSheetSchema(
spreadsheetId: string,
sheetName: string
): Promise<string[]> {
const client = getClient();
const response = await client.spreadsheets.values.get({
spreadsheetId,
range: `'${sheetName}'!1:1`,
});
const values = response.data.values;
if (!values || values.length === 0) {
return [];
}
return values[0].map((cell: unknown) => String(cell ?? ""));
}
export interface SheetRow {
[column: string]: string | number | boolean | null;
}
export async function getSheetRows(
spreadsheetId: string,
sheetName: string,
limit?: number,
offset?: number
): Promise<SheetRow[]> {
const client = getClient();
// First get the headers
const headers = await getSheetSchema(spreadsheetId, sheetName);
if (headers.length === 0) {
return [];
}
// Calculate the range - start from row 2 (after headers)
const startRow = 2 + (offset || 0);
const endRow = limit ? startRow + limit - 1 : undefined;
const range = endRow
? `'${sheetName}'!${startRow}:${endRow}`
: `'${sheetName}'!${startRow}:${startRow + 1000}`; // Default max 1000 rows
const response = await client.spreadsheets.values.get({
spreadsheetId,
range,
});
const values = response.data.values || [];
// Apply limit if we fetched default 1000 rows
const rowsToProcess = limit ? values.slice(0, limit) : values;
return rowsToProcess.map((row) => {
const obj: SheetRow = {};
headers.forEach((header, index) => {
const value = row[index];
if (value === undefined || value === null || value === "") {
obj[header] = null;
} else if (!isNaN(Number(value)) && value !== "") {
obj[header] = Number(value);
} else if (value === "true" || value === "TRUE") {
obj[header] = true;
} else if (value === "false" || value === "FALSE") {
obj[header] = false;
} else {
obj[header] = String(value);
}
});
return obj;
});
}
