Google Sheet API Client

ຫໍສະໝຸດ npm ທີ່ງ່າຍດາຍ ສະອາດ ແລະ ມີປະສິດທິພາບສຳລັບການເຮັດວຽກກັບ Google Sheets API

ຄຸນສົມບັດ

• ✅ ໃຊ້ງານງ່າຍ ດ້ວຍ API ທີ່ສະອາດ
• ✅ ຮອງຮັບ TypeScript
• ✅ CRUD operations ຄົບຖ້ວນ
• ✅ Batch operations (insertMany, updateMany, deleteMany)
• ✅ ຈັດການ Sheet (ສ້າງ/ປ່ຽນຊື່/ລຶບ)
• ✅ Authentication (login, register) - ໃໝ່!
• ✅ ອັບໂຫຼດໄຟລ໌ໄປ Google Drive
• ✅ Promise-based API
• ✅ Error handling ທີ່ດີ ດ້ວຍ retry mechanism

ການຕິດຕັ້ງ

npm install google-sheet-api-client

ການຕັ້ງຄ່າ Google Apps Script

1. ເປີດ Google Sheets ຂອງເຈົ້າ
2. ໄປທີ່ Extensions > Apps Script
3. Copy ໂຄດຈາກ app script/Code.gs ໃສ່
4. Deploy ເປັນ Web App:
   • ກົດ Deploy > New deployment
   • ເລືອກ Web app
   • Execute as: Me
   • Who has access: Anyone
5. Copy URL ທີ່ໄດ້ມາ

ການໃຊ້ງານ

ການເລີ່ມຕົ້ນ

import { GoogleSheetClient } from 'google-sheet-api-client';

const client = new GoogleSheetClient({
  apiUrl: 'YOUR_DEPLOYED_WEB_APP_URL',
  sheetKey: 'YOUR_GOOGLE_SHEET_KEY'
});

ດຶງຂໍ້ມູນ

const response = await client.getData('Sheet1');

if (response.status === 'success') {
  console.log(response.data);
  // [
  //   { name: 'John', age: '30', email: '[email protected]' },
  //   { name: 'Jane', age: '25', email: '[email protected]' }
  // ]
}

ເພີ່ມຂໍ້ມູນ

const response = await client.insertData('Sheet1', {
  name: 'Bob',
  age: '28',
  email: '[email protected]'
});

if (response.status === 'success') {
  console.log('Data inserted successfully');
}

ແກ້ໄຂຂໍ້ມູນ

const response = await client.updateData('Sheet1', 0, {
  name: 'Bob Updated',
  age: '29'
});

if (response.status === 'success') {
  console.log('Data updated successfully');
}

ລຶບຂໍ້ມູນ

const response = await client.deleteData('Sheet1', 0);

if (response.status === 'success') {
  console.log('Data deleted successfully');
}

Batch Operations (ໃໝ່ໃນ v1.1.0)

// ເພີ່ມຫຼາຍແຖວພ້ອມກັນ
const response = await client.insertMany('Sheet1', [
  { name: 'User1', age: '25' },
  { name: 'User2', age: '30' },
  { name: 'User3', age: '35' }
]);

// ອັບເດດຫຼາຍແຖວພ້ອມກັນ
const response = await client.updateMany('Sheet1', [
  { index: 0, data: { name: 'Updated1' } },
  { index: 1, data: { name: 'Updated2' } }
]);

// ລຶບຫຼາຍແຖວພ້ອມກັນ
const response = await client.deleteMany('Sheet1', [0, 1, 2]);

// ລຶບຂໍ້ມູນທັງໝົດໃນ sheet
const response = await client.clearSheet('Sheet1');

Pagination ແລະ Search (ໃໝ່ໃນ v1.3.0)

// Pagination - ສຳລັບຂໍ້ມູນຂະໜາດໃຫຍ່
const response = await client.getDataWithPagination('Sheet1', {
  limit: 50,           // ຈຳນວນ rows ຕໍ່ໜ້າ
  offset: 0,           // ເລີ່ມຈາກ row ໃດ
  sortBy: 'name',      // ຈັດລຽງຕາມ column
  sortOrder: 'asc'     // asc ຫຼື desc
});
// Returns: { status, data, pagination: { total, hasMore, currentPage, ... } }

// Search - ຄົ້ນຫາຂໍ້ມູນ
const response = await client.searchData('Sheet1', {
  query: 'John',                    // ຄຳຄົ້ນຫາ
  columns: ['name', 'email'],       // ຄົ້ນໃນ columns ໃດ (optional)
  limit: 100,                       // ຈຳກັດຜົນລັບ
  exactMatch: false                 // ຄົ້ນຫາແບບກົງກັນທັງໝົດ
});

// Filter - filter ດ້ວຍເງື່ອນໄຂ
const response = await client.getDataByFilter('Sheet1', {
  filters: [
    { column: 'status', operator: 'equals', value: 'active' },
    { column: 'age', operator: 'greaterThan', value: '18' }
  ],
  limit: 50,
  offset: 0
});

// Count rows
const response = await client.countRows('Sheet1');
// Returns: { status: 'success', count: 1000 }

ຈັດການ Sheets

// ດຶງລາຍຊື່ sheets ທັງໝົດ
const sheets = await client.getAllSheetNames();
console.log(sheets.sheetNames);

// ສ້າງ sheet ໃໝ່
await client.createSheet('NewSheet');

// ສ້າງ sheet ໃໝ່ພ້ອມ headers (ໃໝ່!)
const headers = ['id', 'name', 'email', 'phone', 'status'];
await client.createSheetWithHeaders('MySheet', headers);

// ປ່ຽນຊື່ sheet
await client.renameSheet('OldName', 'NewName');

// ລຶບ sheet
await client.deleteSheet('SheetToDelete');

ອັບໂຫຼດໄຟລ໌

const response = await client.uploadFile({
  folderKey: 'YOUR_FOLDER_ID', // optional, default: 'root'
  mimeType: 'image/png',
  filename: 'image.png',
  contents: base64String
});

if (response.status === 'success') {
  console.log('File URL:', response.fileUrl);
  console.log('File ID:', response.fileId);
}

Authentication (ໃໝ່ໃນ v1.2.0)

// ລົງທະບຽນຜູ້ໃຊ້ໃໝ່
const response = await client.register('username', 'password', {
  email: '[email protected]'
});

// ເຂົ້າສູ່ລະບົບ
const response = await client.login('username', 'password');
if (response.status === 'success') {
  console.log('User:', response.data);
  // { id, username, email, status, lastLogin, createdAt }
}

// ດຶງຂໍ້ມູນຜູ້ໃຊ້
const response = await client.getUserByUsername('username');

ສຳຄັນ: ກ່ອນໃຊ້ authentication, ອ່ານ AUTHENTICATION.md ສຳລັບການຕັ້ງຄ່າ Users sheet ແລະ Google Apps Script.

API Reference

GoogleSheetClient

Constructor

new GoogleSheetClient(config: GoogleSheetConfig)

Parameters:

• config.apiUrl - URL ຂອງ Google Apps Script Web App
• config.sheetKey - Google Sheet ID

Methods

getData(sheetName: string)

ດຶງຂໍ້ມູນທັງໝົດຈາກ sheet

insertData(sheetName: string, data: SheetData)

ເພີ່ມແຖວໃໝ່ເຂົ້າໃນ sheet

updateData(sheetName: string, index: number, data: SheetData)

ແກ້ໄຂແຖວທີ່ລະບຸ (index ເລີ່ມຈາກ 0)

deleteData(sheetName: string, index: number)

ລຶບແຖວທີ່ລະບຸ (index ເລີ່ມຈາກ 0)

getAllSheetNames()

ດຶງລາຍຊື່ sheets ທັງໝົດ

createSheet(sheetName: string)

ສ້າງ sheet ໃໝ່

renameSheet(oldName: string, newName: string)

ປ່ຽນຊື່ sheet

deleteSheet(sheetName: string)

ລຶບ sheet

uploadFile(options: FileUploadOptions)

ອັບໂຫຼດໄຟລ໌ໄປ Google Drive

Response Format

ທຸກ method ຈະ return Promise ທີ່ມີໂຄງສ້າງດັ່ງນີ້:

{
  status: 'success' | 'error',
  message?: string,
  data?: any,
  sheetNames?: string[],
  fileUrl?: string,
  fileId?: string
}

ຕົວຢ່າງການໃຊ້ງານແບບສົມບູນ

import { GoogleSheetClient } from 'google-sheet-api-client';

async function main() {
  const client = new GoogleSheetClient({
    apiUrl: 'https://script.google.com/macros/s/YOUR_SCRIPT_ID/exec',
    sheetKey: '1ABC...XYZ'
  });

  try {
    // ດຶງຂໍ້ມູນ
    const data = await client.getData('users');
    console.log('Users:', data.data);

    // ເພີ່ມຂໍ້ມູນໃໝ່
    await client.insertData('users', {
      name: 'Alice',
      email: '[email protected]',
      role: 'admin'
    });

    // ແກ້ໄຂຂໍ້ມູນ
    await client.updateData('users', 0, {
      role: 'superadmin'
    });

    // ດຶງລາຍຊື່ sheets
    const sheets = await client.getAllSheetNames();
    console.log('Available sheets:', sheets.sheetNames);

  } catch (error) {
    console.error('Error:', error);
  }
}

main();

ການຊອກຫາ Sheet Key

Sheet Key ແມ່ນສ່ວນໜຶ່ງຂອງ URL:

https://docs.google.com/spreadsheets/d/[SHEET_KEY]/edit

License

MIT

ການປັບປຸງຈາກເວີຊັນເກົ່າ

• ✅ ລຶບ eval() ອອກ (ບໍ່ປອດໄພ)
• ✅ ໃຊ້ JSON.parse() ແທນ
• ✅ ເພີ່ມ TypeScript support
• ✅ ເພີ່ມ error handling ທີ່ດີກວ່າ
• ✅ Response format ເປັນມາດຕະຖານ
• ✅ ໂຄດສະອາດ ອ່ານງ່າຍ
• ✅ ໃຊ້ Promise-based API