1. Create Google Cloud Project

   • Go to Google Cloud Console
   • Click "Select a project" → "New Project"
   • Enter project name, click "Create"

2. Enable Google Sheets API

   • In your project, go to "APIs & Services" → "Library"
   • Search for "Google Sheets API"
   • Click on it, then click "Enable"

3. Create Service Account

   • Go to "APIs & Services" → "Credentials"
   • Click "Create Credentials" → "Service Account"
   • Fill in:
     • Service account name: node-red-sheets (or any name)
     • Service account ID: (auto-filled)
   • Click "Create and Continue"
   • Skip the optional steps, click "Done"

4. Generate Key

   • Click on the service account you just created
   • Go to "Keys" tab
   • Click "Add Key" → "Create new key"
   • Choose "JSON" format
   • Click "Create"
   • A JSON file will download - keep this safe!

5. Share Your Spreadsheet

   • Open your Google Spreadsheet
   • Click "Share" button
   • Add the service account email (looks like [email protected])
   • Grant Editor permission (for write access) or Viewer (for read-only)
   • Click "Done"

⚠️ Important: Without sharing, you'll get "Permission denied" errors!

1. Add Service Account Config Node

   • Drag a "Google Sheets" node into your flow
   • Double-click to open settings
   • Click pencil icon next to "Credentials" to create new config
   • Name it (e.g., "Production Service Account")

2. Add Credentials

   • Select "Copy/paste JSON" (recommended)
   • Open the downloaded JSON file
   • Copy entire contents
   • Paste into the "Credentials JSON" field
   • Click "Add"

   Alternative: Enter fields individually (project_id, client_email, private_key)

3. Configure Spreadsheet Node

   • Spreadsheet ID: Copy from URL between /d/ and /edit

     https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
                                              ↑ This is your Spreadsheet ID ↑
     

   • Range: Use A1 notation (e.g., Sheet1!A1:D10)
   • Action: Choose "Get Data", "Set Data", or "Clear Data"
   • Output: Where to store results (default: msg.payload)

4. Deploy and Test

   • Click "Deploy"
   • Inject a message (using an inject node)
   • Check the debug output

[inject] → [Google Sheets] → [debug]

Inject node configuration:

{
  "spreadsheetId": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",
  "range": "Sheet1!A1:C10"
}

Google Sheets node configuration:

• Action: "Get Data"
• Sheet ID: from msg.spreadsheetId
• Range: from msg.range
• Output: msg.payload

Result in msg.payload:

[
  ["Name", "Email", "Age"],
  ["John Doe", "[email protected]", 30],
  ["Jane Smith", "[email protected]", 25]
]

With "First line for labels" enabled:

[
  { "Name": "John Doe", "Email": "[email protected]", "Age": 30 },
  { "Name": "Jane Smith", "Email": "[email protected]", "Age": 25 }
]

Read entire sheet:

Range: Sheet1
Output: 2D array of all data

Read specific range:

Range: Sheet1!A1:D10
Output: [[row1], [row2], ...]

Read with column headers:

Range: Sheet1!A1:C10
Enable: "First line for labels"
Output: [{Name: "John", Age: 30}, ...]

Read with row and column headers:

Range: Sheet1!A1:D5
Enable: "First line for labels" + "First column for labels"
Output: {John: {Age: 30, City: "NYC"}, ...}

Read single column:

Range: Sheet1!B:B
Output: [["value1"], ["value2"], ...]

Read single row:

Range: Sheet1!1:1
Output: [["col1", "col2", "col3"]]

Select specific fields only:

Data: [{Name: "John", Age: 30, City: "NYC", Country: "USA"}]
Selected fields: ["Name", "Age"]
Output: [{Name: "John", Age: 30}]

Get by column (transpose):

Enable: "Get data by column"
Input data (rows):
  [["Name", "Age"], ["John", 30], ["Jane", 25]]
Output (columns):
  [["Name", "John", "Jane"], ["Age", 30, 25]]

Caching for performance:

Cache location: msg._sheet (default)
First read: Calls API
Second read: Uses cached data (instant)
Clear cache: Use "Clear Data" action or delete msg._sheet

Update (modify existing cells):

Method: Update
Range: Sheet1!A2:C3
Input: [["John", 30, "NYC"], ["Jane", 25, "LA"]]
Result: Updates only cells A2:C3, leaves other cells unchanged

Append (add rows at the end):

Method: Append
Range: Sheet1!A:C
Input: [["New Person", 35, "SF"], ["Another", 40, "SEA"]]
Result: Adds rows after last existing row

New (clear then write):

Method: New
Range: Sheet1!A1:C10
Input: [["Header1", "Header2", "Header3"], ["Data1", "Data2", "Data3"]]
Result: Clears A1:C10 first, then writes new data

Array of arrays (standard format):

{
  "payload": [
    ["Name", "Age", "City"],
    ["John", 30, "NYC"],
    ["Jane", 25, "LA"]
  ]
}

Array of objects (auto-converted):

{
  "payload": [
    {"Name": "John", "Age": 30, "City": "NYC"},
    {"Name": "Jane", "Age": 25, "City": "LA"}
  ]
}

With "First line for labels" enabled, keys become column headers automatically.

Single value (writes to one cell):

{
  "payload": "Hello World"
}

Single row:

{
  "payload": ["Value1", "Value2", "Value3"]
}

Clear specific range:

Action: Clear Data
Range: Sheet1!A2:C10
Result: Clears all data in cells A2 through C10

Clear entire sheet:

Range: Sheet1
Result: Clears all data in Sheet1

Clear specific columns:

Range: Sheet1!B:D
Result: Clears columns B, C, D entirely

Note: Clearing data automatically clears the cache for that range.

Instead of hardcoding values in the node, pass them via the message:

Dynamic Spreadsheet ID and Range:

// In a function node or inject
msg.spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms";
msg.range = "Sheet1!A1:C10";
return msg;

Then in Google Sheets node:

• Spreadsheet ID: msg.spreadsheetId
• Range: msg.range

Dynamic data input:

msg.payload = [
  ["Product", "Price", "Stock"],
  ["Item A", 29.99, 100],
  ["Item B", 49.99, 50]
];
return msg;

Using global context for shared configuration:

// Set once in a setup flow
global.set("prodSpreadsheetId", "1BxiMVs...");

// Use in multiple flows
msg.spreadsheetId = global.get("prodSpreadsheetId");

The node automatically converts between formats:

JavaScript → Google Sheets:

// Array of arrays → Writes directly
[["A1", "B1"], ["A2", "B2"]]

// Array of objects → Converts to 2D array
[{Name: "John", Age: 30}]
→ [["John", 30]]

// Single value → Wraps in array
"Hello"
→ [["Hello"]]

// Empty array → Clears range
[]

Google Sheets → JavaScript:

// No labels → Returns 2D array
[["A1", "B1"], ["A2", "B2"]]

// With "First line for labels" → Returns array of objects
[{Name: "John", Age: 30}, {Name: "Jane", Age: 25}]

// With both labels → Returns nested object
{John: {Age: 30, City: "NYC"}, Jane: {Age: 25, City: "LA"}}

// Single cell → Returns value directly
"Hello"

Writing with headers:

// Enable "First line for labels" in node
msg.payload = [
  {Product: "Widget", Price: 10, Stock: 100},
  {Product: "Gadget", Price: 20, Stock: 50}
];

// Writes:
// Row 1: Product | Price | Stock
// Row 2: Widget  | 10    | 100
// Row 3: Gadget  | 20    | 50

Reading with headers:

Spreadsheet:
| Name  | Age | City |
|-------|-----|------|
| John  | 30  | NYC  |
| Jane  | 25  | LA   |

Enable "First line for labels"

Output:
[
  {Name: "John", Age: 30, City: "NYC"},
  {Name: "Jane", Age: 25, City: "LA"}
]

Both row and column headers:

Spreadsheet:
|       | Age | City |
|-------|-----|------|
| John  | 30  | NYC  |
| Jane  | 25  | LA   |

Enable both label options

Output:
{
  John: {Age: 30, City: "NYC"},
  Jane: {Age: 25, City: "LA"}
}

When reading objects with many fields, select only what you need:

Input data:

[
  {
    "Name": "John",
    "Email": "[email protected]",
    "Phone": "555-1234",
    "Address": "123 Main St",
    "City": "NYC",
    "State": "NY",
    "Zip": "10001"
  }
]

Configuration:

• Fields to select: Name, Email, City

Output:

[
  {
    "Name": "John",
    "Email": "[email protected]",
    "City": "NYC"
  }
]

Benefits:

• Reduces data transfer
• Simplifies downstream processing
• Improves performance

Automatic caching:

1. First read from a range → API call, result cached
2. Second read from same range → Instant return from cache
3. Write to that range → Cache automatically cleared
4. Clear data action → Cache cleared

Cache storage locations:

• msg._sheet (default) - Cached per message
• flow._sheet - Shared across all nodes in same flow
• global._sheet - Shared across all flows

What gets cached:

• Raw data from Google Sheets API
• Transformed data (if applicable)
• Timestamp of last fetch

Cache invalidation:

• Automatic on write/clear operations
• Manual via deleting the cache property
• Time-based (optional, via change node)

Per-message cache (default):

Cache location: msg._sheet

Flow:
[read data] → [process] → [read same data] → [process more]
              ↑ API call              ↑ From cache

Flow-level cache (shared across flow):

Cache location: flow._sheet

Multiple nodes can read from cache without redundant API calls.

Global cache (shared across all flows):

Cache location: global._sheet

All flows reading the same data use the same cache.

Manual cache clearing:

// In a function node
delete msg._sheet;  // Clear message cache
delete flow._sheet; // Clear flow cache
delete global._sheet; // Clear global cache
return msg;

Time-based cache invalidation:

// In a function node before reading
const cacheAge = Date.now() - (msg._sheet?.timestamp || 0);
const maxAge = 5 * 60 * 1000; // 5 minutes

if (cacheAge > maxAge) {
  delete msg._sheet; // Force fresh read
}
return msg;

Without caching:

• Every read = API call
• Latency: 200-500ms per call
• API quota consumed on every read

With caching:

• First read = API call (200-500ms)
• Subsequent reads = instant (< 1ms)
• 90%+ reduction in API calls
• Significant quota savings

Example scenario:

Flow processes 1000 messages, each needs to look up reference data:

Without cache:
- 1000 API calls
- ~300 seconds total
- 1000 quota units used

With cache:
- 1 API call
- ~0.3 seconds total
- 1 quota unit used

Best for:

• Reference data that changes infrequently
• Lookup tables
• Configuration data
• Repeated reads in processing loops

Not recommended for:

• Real-time data that changes frequently
• Data that must always be fresh
• Single-read scenarios

Service Account Security:

1. JWT (JSON Web Token) authentication

   • No user passwords involved
   • Tokens are short-lived (1 hour)
   • Automatically refreshed when expired
   • No OAuth consent screens needed

2. Credential encryption:

   • All credentials encrypted by Node-RED
   • Stored in flows_cred.json with encryption
   • Never appear in flows.json
   • Cannot be exported in flows

3. Service account email:

   • Acts as identity: [email protected]
   • Must be explicitly granted access to each spreadsheet
   • Can be revoked from Google Cloud Console

What is encrypted:

• Project ID
• Client email
• Private key (RSA 2048-bit)

What is NOT encrypted:

• Config node name (stored in flows.json)
• OAuth2 scopes list

For Service Accounts:

1. Grant minimum required permissions

   • Read-only use case → Grant "Viewer" permission
   • Read/write use case → Grant "Editor" permission
   • Never grant "Owner" permission

2. Rotate keys regularly

   • Create new key in Google Cloud Console
   • Update Node-RED config
   • Delete old key from Google Cloud Console

3. Use separate spreadsheets for sensitive data

   • Don't mix production and test data
   • Apply different access controls
   • Audit access regularly

For Node-RED:

1. Enable admin authentication

   // in settings.js
   adminAuth: {
     type: "credentials",
     users: [{
       username: "admin",
       password: "$2b$08$...", // bcrypt hash
       permissions: "*"
     }]
   }
   

2. Set strong credentialSecret

   // in settings.js
   credentialSecret: "a-very-long-random-string-here"
   

3. Backup flows securely

   • Always backup both flows.json AND flows_cred.json
   • Store backups encrypted
   • Never commit credentials to git

❌ Don't commit credentials to git

# Add to .gitignore
flows_cred.json
*.json  # If you store service account files locally

❌ Don't share service account keys publicly

• Don't paste in public forums
• Don't include in screenshots
• Don't email in plain text

❌ Don't grant service accounts excessive permissions

• Only grant access to specific spreadsheets
• Don't share entire Google Drive
• Revoke when no longer needed

✅ Do use environment variables in production

// Instead of hardcoding
const credPath = process.env.GOOGLE_SERVICE_ACCOUNT_PATH;

"Authentication failed: JWT authorization failed: No key or keyFile set"

• Cause: Service account credentials not properly configured
• Fix: Verify JSON credentials are correctly pasted, check client_email and private_key fields

"Permission denied"

• Cause: Service account doesn't have access to the spreadsheet
• Fix: Share spreadsheet with service account email, grant Editor or Viewer permission

"Invalid cell range format. Use A1 notation (e.g., A1:B10)"

• Cause: Range format is incorrect
• Fix: Use formats like Sheet1!A1:B10, Sheet1, A:D, 1:10

"Spreadsheet operation failed: Unable to parse range: Sheet1!A1:B"

• Cause: Incomplete range (missing end column/row)
• Fix: Use A1:B10 instead of A1:B, or A:B for entire columns

"Invalid spreadsheet ID"

• Cause: Spreadsheet ID is empty, undefined, or malformed
• Fix: Copy ID from URL, ensure it's a 44-character string

"Unsupported data format"

• Cause: Input data is null, undefined, or invalid type for the operation
• Fix: Ensure msg.payload contains array, object, or primitive value

1. Check node status

The node shows visual status indicators:

• 🔵 Blue dot "Processing..." → Operation in progress
• 🟢 Green dot "Success" → Operation completed
• 🔴 Red ring "Auth error" → Authentication failed
• 🔴 Red ring "Invalid range" → Range format error
• 🔴 Red dot "Error" → Other error

2. Use debug nodes

[inject] → [Google Sheets] → [debug "success"]
                          ↘ [debug "error" (catch)]

3. Enable verbose logging

In settings.js:

logging: {
  console: {
    level: "debug",
    metrics: false,
    audit: false
  }
}

4. Verify inputs

Add a function node before Google Sheets to log inputs:

node.warn("Spreadsheet ID: " + msg.spreadsheetId);
node.warn("Range: " + msg.range);
node.warn("Payload type: " + typeof msg.payload);
node.warn("Payload: " + JSON.stringify(msg.payload));
return msg;

5. Test with simple data first

Start with:

msg.payload = [["Test"]];
msg.range = "Sheet1!A1";

Then gradually increase complexity.

Issue: "Module not found" after installation

Cause: Node-RED hasn't been restarted

Solution:

# Stop Node-RED
# Restart Node-RED
node-red-restart  # or use your system's method

Issue: Nodes don't appear in palette

Cause: Installation in wrong directory or Node-RED using different user directory

Solution:

# Find Node-RED user directory
node-red --version
# Shows: Welcome to Node-RED, v3.1.0, user directory: /home/user/.node-red

# Install there
cd /home/user/.node-red
npm install node-red-contrib-google-spreadsheet-plus

Issue: "Invalid JSON" error when pasting credentials

Solution:

• Copy entire JSON file contents
• Make sure no extra characters at beginning/end
• Check for valid JSON syntax at jsonlint.com

Issue: Can't find service account email

Solution:

• Open JSON credentials file
• Look for "client_email" field
• Email format: [email protected]

Issue: Spreadsheet ID keeps getting rejected

Solution:

URL: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit

ID: 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms
    ↑ Copy exactly this part (44 characters) ↑

Issue: "Permission denied" error

Checklist:

1. ☐ Spreadsheet is shared with service account email
2. ☐ Editor permission granted (for writes)
3. ☐ Service account email is correct
4. ☐ Sharing applies to specific sheet, not just folder

Issue: Data comes back as undefined

Causes & Solutions:

• Empty range: Returns empty array []
• Wrong range: Check sheet name spelling
• No data: Verify data exists in range
• Cache: Clear cache and try again

Issue: Data writes succeed but don't appear in sheet

Debugging steps:

1. Check you're looking at the correct sheet tab
2. Verify range is correct (sheet name + cell range)
3. Check if data is outside visible area (scroll down/right)
4. Verify write method (Update vs Append vs New)
5. Refresh spreadsheet in browser

Issue: Can't find Google Sheets API in library

Solution:

• Make sure you're in the correct project
• Search exactly: "Google Sheets API"
• Click "Enable" button
• Wait 1-2 minutes for activation

Issue: Service account creation fails

Solution:

• Check you have project Owner or Editor role
• Try creating from IAM & Admin → Service Accounts directly
• Verify project billing is enabled (even for free tier)

Issue: Quota exceeded errors

Solution:

• Check quota limits: APIs & Services → Quotas
• Default: 100 requests per 100 seconds per user
• Request quota increase if needed
• Implement rate limiting or caching

Before asking for help, gather this information:

1. Node-RED version: Check in Settings → About
2. Node.js version: node --version
3. Package version: Check in Manage palette
4. Error message: Full text from debug sidebar
5. What you've tried: List troubleshooting steps already taken

Where to get help:

1. GitHub Issues: https://github.com/carefulcomputer/node-red-contrib-google-spreadsheet/issues

   • Search existing issues first
   • Include all information above
   • Attach screenshots if helpful

2. Node-RED Forum: https://discourse.nodered.org

   • Tag with google-sheets
   • Describe your use case
   • Share relevant flow (sanitize credentials!)

When reporting bugs:

• ✅ Include minimal reproducible example
• ✅ Specify exact error message
• ✅ Describe expected vs actual behavior
• ❌ Don't share actual credentials
• ❌ Don't dump entire flow (make it minimal)