// Google Sheets API Service
import { google } from 'googleapis';
import type { OAuth2Client } from 'google-auth-library';
import type { sheets_v4 } from 'googleapis';
import { DriveService } from './drive.js';
export class SheetsService {
private sheets: sheets_v4.Sheets;
private driveService: DriveService;
private authClient: OAuth2Client;
constructor(authClient: OAuth2Client, driveService: DriveService) {
this.sheets = google.sheets({ version: 'v4', auth: authClient });
this.driveService = driveService;
this.authClient = authClient;
}
/**
* Create a new Google Sheet with initial data
*/
async createSheet(
name: string,
data: string[][],
parentFolder?: string
): Promise<{ id: string; name: string; webViewLink: string }> {
// Create spreadsheet
const spreadsheet = await this.sheets.spreadsheets.create({
requestBody: {
properties: { title: name },
sheets: [{
properties: {
sheetId: 0,
title: 'Sheet1',
gridProperties: {
rowCount: Math.max(data.length, 1000),
columnCount: Math.max(data[0]?.length || 0, 26)
}
}
}]
}
});
const spreadsheetId = spreadsheet.data.spreadsheetId!;
// Move to parent folder if specified
if (parentFolder) {
const drive = google.drive({ version: 'v3', auth: this.authClient });
const parentId = await this.driveService.api.files.list({
q: `name = '${parentFolder.split('/').pop()}' and mimeType = 'application/vnd.google-apps.folder'`,
fields: 'files(id)',
}).then(r => r.data.files?.[0]?.id || 'root');
await drive.files.update({
fileId: spreadsheetId,
addParents: parentId,
removeParents: 'root',
supportsAllDrives: true,
});
}
// Insert data
if (data.length > 0) {
await this.sheets.spreadsheets.values.update({
spreadsheetId,
range: 'Sheet1!A1',
valueInputOption: 'RAW',
requestBody: { values: data }
});
}
return {
id: spreadsheetId,
name: spreadsheet.data.properties?.title || name,
webViewLink: `https://docs.google.com/spreadsheets/d/${spreadsheetId}`
};
}
/**
* Update cells in a Google Sheet
*/
async updateSheet(
spreadsheetId: string,
range: string,
data: string[][]
): Promise<{ updatedCells: number }> {
const response = await this.sheets.spreadsheets.values.update({
spreadsheetId,
range,
valueInputOption: 'RAW',
requestBody: { values: data }
});
return { updatedCells: response.data.updatedCells || 0 };
}
/**
* Get content of a Google Sheet
*/
async getSheetContent(
spreadsheetId: string,
range: string
): Promise<{ values: string[][]; range: string }> {
const response = await this.sheets.spreadsheets.values.get({
spreadsheetId,
range,
});
return {
values: (response.data.values as string[][]) || [],
range: response.data.range || range
};
}
/**
* Format cells (background, alignment, wrap)
*/
async formatCells(
spreadsheetId: string,
range: string,
format: {
backgroundColor?: { red?: number; green?: number; blue?: number };
horizontalAlignment?: 'LEFT' | 'CENTER' | 'RIGHT';
verticalAlignment?: 'TOP' | 'MIDDLE' | 'BOTTOM';
wrapStrategy?: 'OVERFLOW_CELL' | 'CLIP' | 'WRAP';
}
): Promise<void> {
const gridRange = await this.convertA1ToGridRange(spreadsheetId, range);
const cellFormat: sheets_v4.Schema$CellFormat = {};
const fields: string[] = [];
if (format.backgroundColor) {
cellFormat.backgroundColor = format.backgroundColor;
fields.push('userEnteredFormat.backgroundColor');
}
if (format.horizontalAlignment) {
cellFormat.horizontalAlignment = format.horizontalAlignment;
fields.push('userEnteredFormat.horizontalAlignment');
}
if (format.verticalAlignment) {
cellFormat.verticalAlignment = format.verticalAlignment;
fields.push('userEnteredFormat.verticalAlignment');
}
if (format.wrapStrategy) {
cellFormat.wrapStrategy = format.wrapStrategy;
fields.push('userEnteredFormat.wrapStrategy');
}
await this.sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
repeatCell: {
range: gridRange,
cell: { userEnteredFormat: cellFormat },
fields: fields.join(',')
}
}]
}
});
}
/**
* Format text in cells (bold, italic, font, color)
*/
async formatText(
spreadsheetId: string,
range: string,
format: {
bold?: boolean;
italic?: boolean;
strikethrough?: boolean;
underline?: boolean;
fontSize?: number;
fontFamily?: string;
foregroundColor?: { red?: number; green?: number; blue?: number };
}
): Promise<void> {
const gridRange = await this.convertA1ToGridRange(spreadsheetId, range);
const textFormat: sheets_v4.Schema$TextFormat = {};
const fields: string[] = [];
if (format.bold !== undefined) {
textFormat.bold = format.bold;
fields.push('userEnteredFormat.textFormat.bold');
}
if (format.italic !== undefined) {
textFormat.italic = format.italic;
fields.push('userEnteredFormat.textFormat.italic');
}
if (format.strikethrough !== undefined) {
textFormat.strikethrough = format.strikethrough;
fields.push('userEnteredFormat.textFormat.strikethrough');
}
if (format.underline !== undefined) {
textFormat.underline = format.underline;
fields.push('userEnteredFormat.textFormat.underline');
}
if (format.fontSize !== undefined) {
textFormat.fontSize = format.fontSize;
fields.push('userEnteredFormat.textFormat.fontSize');
}
if (format.fontFamily) {
textFormat.fontFamily = format.fontFamily;
fields.push('userEnteredFormat.textFormat.fontFamily');
}
if (format.foregroundColor) {
textFormat.foregroundColor = format.foregroundColor;
fields.push('userEnteredFormat.textFormat.foregroundColor');
}
await this.sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
repeatCell: {
range: gridRange,
cell: { userEnteredFormat: { textFormat } },
fields: fields.join(',')
}
}]
}
});
}
/**
* Format numbers in cells
*/
async formatNumbers(
spreadsheetId: string,
range: string,
pattern: string
): Promise<void> {
const gridRange = await this.convertA1ToGridRange(spreadsheetId, range);
await this.sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
repeatCell: {
range: gridRange,
cell: {
userEnteredFormat: {
numberFormat: { type: 'NUMBER', pattern }
}
},
fields: 'userEnteredFormat.numberFormat'
}
}]
}
});
}
/**
* Set borders on cells
*/
async setBorders(
spreadsheetId: string,
range: string,
style: 'SOLID' | 'DASHED' | 'DOTTED' | 'DOUBLE',
color?: { red?: number; green?: number; blue?: number }
): Promise<void> {
const gridRange = await this.convertA1ToGridRange(spreadsheetId, range);
const border: sheets_v4.Schema$Border = {
style,
color: color || { red: 0, green: 0, blue: 0 }
};
await this.sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
updateBorders: {
range: gridRange,
top: border,
bottom: border,
left: border,
right: border,
innerHorizontal: border,
innerVertical: border
}
}]
}
});
}
/**
* Merge cells
*/
async mergeCells(
spreadsheetId: string,
range: string,
mergeType: 'MERGE_ALL' | 'MERGE_COLUMNS' | 'MERGE_ROWS' = 'MERGE_ALL'
): Promise<void> {
const gridRange = await this.convertA1ToGridRange(spreadsheetId, range);
await this.sheets.spreadsheets.batchUpdate({
spreadsheetId,
requestBody: {
requests: [{
mergeCells: {
range: gridRange,
mergeType
}
}]
}
});
}
/**
* Convert A1 notation to GridRange
*/
private async convertA1ToGridRange(
spreadsheetId: string,
a1Notation: string
): Promise<sheets_v4.Schema$GridRange> {
// Get sheet info
const spreadsheet = await this.sheets.spreadsheets.get({ spreadsheetId });
const sheetName = a1Notation.includes('!') ? a1Notation.split('!')[0] : 'Sheet1';
const sheet = spreadsheet.data.sheets?.find(s => s.properties?.title === sheetName);
const sheetId = sheet?.properties?.sheetId || 0;
// Parse range (e.g., "A1:C10" or "Sheet1!A1:C10")
const rangeStr = a1Notation.includes('!') ? a1Notation.split('!')[1] : a1Notation;
const [start, end] = rangeStr.split(':');
const parseCell = (cell: string) => {
const colMatch = cell.match(/^([A-Z]+)/);
const rowMatch = cell.match(/(\d+)$/);
const col = colMatch ? this.columnToIndex(colMatch[1]) : 0;
const row = rowMatch ? parseInt(rowMatch[1]) - 1 : 0;
return { col, row };
};
const startCell = parseCell(start);
const endCell = end ? parseCell(end) : startCell;
return {
sheetId,
startRowIndex: startCell.row,
endRowIndex: endCell.row + 1,
startColumnIndex: startCell.col,
endColumnIndex: endCell.col + 1
};
}
private columnToIndex(col: string): number {
let index = 0;
for (let i = 0; i < col.length; i++) {
index = index * 26 + (col.charCodeAt(i) - 64);
}
return index - 1;
}
}
