npm package: @ianmaleney/sheets-api-helper
Description: A helper library for interacting with the Google Sheets API using Node.js.
Author: Ian Maleney
Latest version: 1.0.0

# Google Sheets API - Complete Authentication Flow

A production-ready authentication system for accessing Google Sheets data using service account credentials.

## 🎯 What This Provides

✅ **Service Account Authentication** - Secure, non-interactive API access  
✅ **Automatic Initialization** - Auth is set up automatically on first use  
✅ **Cached Client** - Avoids re-authentication for every request  
✅ **Error Handling** - Clear error messages for debugging  
✅ **Batch Operations** - Fetch multiple ranges efficiently  
✅ **Integration Tests** - Verify API connectivity  
✅ **Setup Verification** - Script to check your configuration  

## 📁 Project Structure

```
.
├── index.js                    # Main API implementation
├── index.test.js              # Integration tests
├── example.js                 # Usage examples
├── setup-check.js             # Configuration verification script
├── SETUP.md                   # Detailed setup guide
├── AUTH_IMPLEMENTATION.md     # Implementation details
├── README.md                  # This file
├── .env                       # Environment variables (contains TEST_SHEET_ID)
├── .gitignore                 # Git ignore (keeps credentials safe)
├── service_key.json           # Service account key (DO NOT COMMIT)
└── package.json               # Project dependencies
```

## 🚀 Quick Start

### 1. Verify Setup
```bash
node setup-check.js
```

This will check:
- ✓ service_key.json exists and is valid
- ✓ TEST_SHEET_ID is set in .env
- ✓ Dependencies are installed
- ✓ Credentials are not committed to git

### 2. Share Your Spreadsheet

1. Get the service account email from the output above
2. Open your Google Sheet
3. Click **Share**
4. Paste the service account email
5. Give it **Viewer** access (or **Editor** if needed)

### 3. Run Example
```bash
bun run example.js
```

### 4. Run Tests
```bash
bun test
```

## 📖 API Reference

### `initializeAuth()`
Initialize the Google Sheets API client. Call this once at app startup.

```javascript
import { initializeAuth } from './index.js';

await initializeAuth();
```

### `getSheetData(spreadsheetId, range)`
Fetch data from a single spreadsheet range.

```javascript
const rows = await getSheetData(
  '1ZEs6v0-izIyVNFdBb7B4PXOGXsdsjuTq0tAXJBg_kDU',
  'Sheet1!A1:D10'
);

console.log(rows); // Array of rows
```

### `getMultipleRanges(spreadsheetId, ranges)`
Fetch multiple ranges efficiently in one API call.

```javascript
const data = await getMultipleRanges(
  '1ZEs6v0-izIyVNFdBb7B4PXOGXsdsjuTq0tAXJBg_kDU',
  ['Sheet1!A1:D10', 'Sheet2!A1:B5']
);

console.log(data['Sheet1!A1:D10']); // Array of rows
console.log(data['Sheet2!A1:B5']);  // Array of rows
```

## 🔧 Configuration

### Environment Variables (.env)
```env
TEST_SHEET_ID=your_spreadsheet_id
```

Get your spreadsheet ID from the URL:
```
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit
```

### Service Key (service_key.json)
Your Google Cloud service account JSON key. Already in `.gitignore` to prevent accidental commits.

## 🐛 Troubleshooting

### "The caller does not have permission"
**Cause:** Spreadsheet not shared with service account

**Solution:**
1. Get service account email from `service_key.json` → `client_email`
2. Share the spreadsheet with this email
3. Grant at least **Viewer** access

### "Service key not found"
**Cause:** `service_key.json` not in project root

**Solution:**
1. Download your service account key from Google Cloud Console
2. Save it as `service_key.json` in the project root
3. Never commit it (already in `.gitignore`)

### Authentication Timeout
**Cause:** Normal for first request

**Solution:** This is expected. Subsequent requests are faster due to caching.

### No data returned
**Cause:** Empty sheet or invalid range

**Solution:**
- Check that the range contains data
- Verify spreadsheet ID is correct
- Try a different range like `Sheet1!A1:Z100`

## 📚 More Information

- **Setup Instructions**: See [SETUP.md](SETUP.md)
- **Implementation Details**: See [AUTH_IMPLEMENTATION.md](AUTH_IMPLEMENTATION.md)
- **Code Examples**: See [example.js](example.js)
- **Google Sheets API Docs**: [https://developers.google.com/sheets/api](https://developers.google.com/sheets/api)

## 🔐 Security

- ✓ `service_key.json` is in `.gitignore`
- ✓ Credentials never logged in production
- ✓ Uses Google Cloud's official libraries
- ✓ Service account is recommended over user authentication
- ✓ Use environment variables for sensitive data

## 🔄 Workflow Summary

```javascript
// 1. Import once in your app
import { initializeAuth, getSheetData } from './index.js';

// 2. Initialize once at startup
await initializeAuth();

// 3. Use anytime, anywhere
const rows = await getSheetData('spreadsheet-id', 'Sheet1!A1:D10');
```

## 💡 Tips

- **Batch Reads**: Use `getMultipleRanges()` instead of multiple `getSheetData()` calls
- **Range Format**: Use A1 notation like `Sheet1!A1:D10` or `'Sheet1'!A1:D10`
- **Large Datasets**: For very large sheets, consider using pagination
- **Caching**: The auth client is cached automatically - no need to reinitialize

## 🤝 Need Help?

1. Run `node setup-check.js` to verify configuration
2. Check [SETUP.md](SETUP.md) for detailed instructions
3. See [example.js](example.js) for working code
4. Review error messages - they indicate the exact issue