Typescriptle google-spreadsheet ile google sheet duzenlemek

sheet, typescript, programming, google-spreadsheet

• https://github.com/theoephraim/node-google-spreadsheet kutuphanesi ile guncelleme islemlerini yapabiliriz

• Dokumantasyonu icin buraya bakabilirsiniz, auth islemleri

`worksheet` kullanmadan once `load` etmeniz gerekir, aksi halde hata verir

await worksheet.loadHeaderRow()
const headers = worksheet.headerValues.entries()

await worksheet.loadCells({
	startRowIndex: rowIndex,
	endRowIndex: rowIndex + 1,
	startColumnIndex: colIndex,
	endColumnIndex: colIndex + 2,
})
const assetCell = worksheet.getCell(rowIndex, colIndex + assetOffset)

Ilk `row` ve `col` degerini header row olarak aldigi icin, $row_{sheet} = row_{code} + 1$

- Yani `sheet` de yer alan `2` satir icin code icerisinden `1` indeksi ile erisirsiniz - Ayni durum `col` indeks hesabi icin de gecerlidir

`value = x` atamalarindan sonra `save` yapmayi unutmayin

const assetCell = worksheet.getCell(rowIndex, colIndex + assetOffset)
assetCell.value = 4
await assetCell.save() // Bunu yapmazsaniz sheet'e yazmaz

Periyodik Olarak Worksheet’e veri Gondermek

import { JWT } from "google-auth-library"
import {
	GoogleSpreadsheet,
	type GoogleSpreadsheetWorksheet,
} from "google-spreadsheet"

export type GoogleSheetCredential = {
	client_email: string
	private_key: string
	spreadsheetId: string
	worksheetName: string
}
export type GoogleSheetConfig = { sheetIntervalHour?: number }
export type GoogleSheetManagerInit = GoogleSheetCredential & GoogleSheetConfig
export class GoogleSheetManager {
	private readonly spreadSheet: GoogleSpreadsheet

	private readonly worksheetName: string
	private readonly sheetIntervalHour: number

	intervalId?: NodeJS.Timeout

	constructor(init: GoogleSheetManagerInit) {
		const { spreadsheetId, client_email, private_key } = init
		const auth = new JWT({
			email: client_email,
			key: private_key,
			scopes: [
				"https://www.googleapis.com/auth/spreadsheets",
				"https://www.googleapis.com/auth/drive.file",
			],
		})
		this.spreadSheet = new GoogleSpreadsheet(spreadsheetId, auth)

		this.worksheetName = init.worksheetName
		this.sheetIntervalHour = init.sheetIntervalHour ?? 1
	}

	static async start(
		init: GoogleSheetManagerInit & {
			onPushInterval: (worksheet: GoogleSpreadsheetWorksheet) => Promise<void>
		}
	): Promise<GoogleSheetManager> {
		const sheetManager = new GoogleSheetManager(init)
		await sheetManager.spreadSheet.loadInfo()

		const worksheet =
			sheetManager.spreadSheet.sheetsByTitle[sheetManager.worksheetName]
		await worksheet.loadHeaderRow()

		await init.onPushInterval(worksheet)
		sheetManager.intervalId = setInterval(async () => {
			await init.onPushInterval(worksheet)
		}, sheetManager.sheetIntervalHour * 60 * 60 * 1000)
		return sheetManager
	}
}