A helper library for interacting with the Google Sheets API using Node.js.
npm install @ianmaleney/sheets-api-helperA production-ready authentication system for accessing Google Sheets data using service account credentials.
ā
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
```
.
āāā 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
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
$3
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
`bash
bun run example.js
`$3
`bash
bun test
`š API Reference
$3
Initialize the Google Sheets API client. Call this once at app startup.`javascript
import { initializeAuth } from './index.js';await initializeAuth();
`$3
Fetch data from a single spreadsheet range.`javascript
const rows = await getSheetData(
'1ZEs6v0-izIyVNFdBb7B4PXOGXsdsjuTq0tAXJBg_kDU',
'Sheet1!A1:D10'
);console.log(rows); // Array of rows
`$3
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
$3
`env
TEST_SHEET_ID=your_spreadsheet_id
`Get your spreadsheet ID from the URL:
`
https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit
`$3
Your Google Cloud service account JSON key. Already in .gitignore to prevent accidental commits.š Troubleshooting
$3
Cause: Spreadsheet not shared with service accountSolution:
1. Get service account email from
service_key.json ā client_email
2. Share the spreadsheet with this email
3. Grant at least Viewer access$3
Cause: service_key.json not in project rootSolution:
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)$3
Cause: Normal for first requestSolution: This is expected. Subsequent requests are faster due to caching.
$3
Cause: Empty sheet or invalid rangeSolution:
- 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
- Implementation Details: See AUTH_IMPLEMENTATION.md
- Code Examples: See example.js
- Google Sheets API Docs: 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