n8nauto
Back to Blog
n8nautomationtutorialautomategooglesheets

Automate Google Sheets with n8n: The Ultimate Tutorial

n8nautomation.cloud TeamMarch 17, 2026

Introduction

Google Sheets remains one of the most versatile tools for data collection, reporting, and lightweight database work. When paired with n8n, an open‑source workflow automation platform, you can turn a static spreadsheet into a living hub that reads, writes, appends, and synchronizes data with CRMs, databases, REST APIs, and many other services. This tutorial walks you through building production‑ready workflows that automate Google Sheets end‑to‑end, covering authentication, node configuration, data mapping, error handling, and scheduling. By the end, you’ll have a set of reusable patterns you can apply to any spreadsheet‑driven process.

Prerequisites

Before you begin, make sure you have the following items ready:

  • An active n8n instance (self‑hosted, Docker, or managed via n8nautomation.cloud).
  • A Google account with access to Google Drive and Google Sheets.
  • Permission to create a project in the Google Cloud Console.
  • Basic familiarity with JSON data structures and HTTP concepts (helpful but not required).
  • A target spreadsheet (you can create a new one for testing) with at least one sheet named Sheet1.
  • Optional: A CRM or API you want to sync with (examples use HubSpot, but the pattern applies to any service).

Step 1: Create a Google Cloud Project and Enable the Google Sheets API

All interactions with Google Sheets from n8n require OAuth2 credentials tied to a Google Cloud project.

  1. Open the Google Cloud Console and click Select a project → New Project.
  2. Give the project a name (e.g., n8n‑Sheets‑Automation) and click Create.
  3. With the project selected, navigate to APIs & Services → Library.
  4. Search for Google Sheets API and click Enable.
  5. Repeat the search and enable Google Drive API (required for file‑level permissions).
  6. Now go to APIs & Services → Credentials.
  7. Click Create Credentials → OAuth client ID.
  8. Choose Application type = Web application.
  9. Name it something like n8n OAuth2.
  10. Under Authorized redirect URIs, add the n8n OAuth2 callback URL. If you are using n8nautomation.cloud, the URL is https://<your‑subdomain>.n8nautomation.cloud/oauth2-credential/callback. For a self‑hosted instance replace the domain accordingly.
  11. Click Create. Google will generate a Client ID and Client Secret – copy these values; you’ll need them in the next step.

Step 2: Configure OAuth2 Credentials in n8n

Now that you have Google credentials, add them to n8n so the Google Sheets node can authenticate.

  1. Log in to your n8n instance.
  2. In the left‑hand menu, select Credentials → New Credential.
  3. Search for Google OAuth2 and select it.
  4. Fill in the fields:
    • Name: Google Sheets OAuth2 (or any descriptive name).
    • Client ID: paste the Client ID from Step 1.
    • Client Secret: paste the Client Secret.
    • Auth URI: https://accounts.google.com/o/oauth2/auth (pre‑filled).
    • Access Token URI: https://oauth2.googleapis.com/token (pre‑filled).
    • Scope: enter the scopes separated by spaces or commas: https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/drive.file. The Drive scope lets n8n create or modify files if needed.
    • Grant Type: keep Authorization Code.
  5. Click Save.
  6. You’ll be prompted to authenticate with Google. Click Authenticate, choose the Google account that owns the target spreadsheet, and grant the requested permissions.
  7. After successful authentication, the credential status should show Active.

Step 3: Build a Basic Workflow to Read Data from Google Sheets

We’ll start with a simple workflow that fetches all rows from a sheet and outputs them as JSON.

3.1 Create the Workflow

  1. In n8n, click Workflows → New Workflow.
  2. Give it a name like Read Google Sheets.

3.2 Add the Trigger

For demonstration we’ll use a manual trigger; later you can replace it with a schedule or webhook.

  1. Click the + node → search for Manual Trigger → add it.
  2. Leave the default settings (no fields needed).

3.3 Add the Google Sheets Node

  1. Click the + after the Manual Trigger → search for Google Sheets → add it.
  2. In the node’s Credentials dropdown, select the Google Sheets OAuth2 credential you created.
  3. Set Operation to Read.
  4. Under Spreadsheet ID, click the folder icon to open the picker, locate your test spreadsheet, and select it. The ID will be auto‑filled (a long alphanumeric string).
  5. Set Sheet Name to Sheet1 (or whatever your sheet is called).
  6. Leave Range blank to read the entire sheet, or specify a range like A1:D100 if you want to limit columns/rows.
  7. Set Return All to true (this returns every row as an array of objects).
  8. In Options, enable Include Header Row so the first row becomes property names.

3.4 Add a Output Node for Inspection

  1. Click the + after the Google Sheets node → search for Set (or IF if you want to filter) → add a Set node.
  2. Rename it to Inspect Data.
  3. In the Fields to Set section, click Add Value → Name: firstRow → Value: {{$json[0]}} (expressions must be wrapped in {{ }}). This extracts the first record for quick viewing.

3.5 Execute and Verify

  1. Click Execute Workflow (top‑right).
  2. When the execution finishes, click on the Inspect Data node to see the output. You should see the first row of your sheet as a JSON object.
  3. Check the Google Sheets node’s output to confirm the full array of rows is present.

Tip: If you see an error like Invalid Value for Spreadsheet ID, double‑check that the spreadsheet is shared with the service account email shown in the OAuth consent screen (or that the user you authenticated with has edit access).

Step 4: Append New Rows to Google Sheets

Appending is a common pattern when you want to log events, form submissions, or API responses.

4.1 Workflow Overview

We’ll extend the previous workflow: after reading (or instead of reading), we’ll take data from a webhook, transform it to match the sheet’s columns, and append it.

4.2 Add a Webhook Trigger

  1. Create a new workflow named Append to Google Sheets.
  2. Add a Webhook node (search for “Webhook”).
  3. Set HTTP Method to POST.
  4. Leave Path blank; n8n will generate a unique URL (you’ll see it after saving).
  5. Optionally enable Response Mode = On Received to return a quick acknowledgment.

4.3 Prepare the Data

Assume your sheet has columns: Timestamp, Name, Email, Message. The webhook will receive a JSON payload with name, email, message.

  1. Add a Set node after the Webhook.
  2. Rename it to Format for Sheet.
  3. In Fields to Set, add four entries:
    • Timestamp → {{ $now }} (returns ISO timestamp).
    • Name → {{ $json["name"] }}.
    • Email → {{ $json["email"] }}.
    • Message → {{ $json["message"] }}.
  4. Set Keep Only Set to true so the node outputs only these four fields.

4.4 Add the Google Sheets Node (Append Operation)

  1. Add a Google Sheets node after the Set node.
  2. Select the same OAuth2 credential.
  3. Set Operation to Append.
  4. Choose the same spreadsheet and sheet (Sheet1).
  5. Leave Range empty (append works from the first empty row).
  6. Under Value Input Mode, choose RAW (this writes the exact values you provide).
  7. In Fields to Set, click Add Value → Name: Values → Value: {{ [$json["Timestamp"], $json["Name"], $json["Email"], $json["Message"]] }}. Note the outer array: Google Sheets expects a 2‑dimensional array where each inner array is a row.

4.5 Test the Workflow

  1. Save the workflow.
  2. Click the webhook node’s Copy URL button.
  3. Use curl or Postman to POST a JSON body:
curl -X POST https://<your‑n8n‑domain>/webhook/<webhook‑id> \
  -H "Content-Type: application/json" \
  -d '{"name":"Alice Smith","email":"alice@example.com","message":"Hello from n8n!"}'
  1. Return to n8n and open the execution list. You should see a successful run.
  2. Open your Google Sheet; a new row should appear with the timestamp, name, email, and message.

Tip: If you need to append multiple rows at once (e.g., from an API that returns an array), use the Items mode in the Google Sheets node: set Operation to Append and map the input array directly ({{$json}}) – n8n will iterate over each item and append a row per item.

Step 5: Update Existing Rows Based on a Key

Sometimes you need to modify a row (e.g., mark a lead as contacted). This requires locating the row by a unique identifier and then updating specific cells.

5.1 Workflow Design

We’ll look up a row where column ID matches a value from the incoming payload, then update the Status column.

5.2 Add a Manual Trigger (or Webhook) for Testing

  1. Create a workflow named Update Google Sheets Row.
  2. Add a Manual Trigger node.

5.3 Read the Sheet to Find the Row

  1. Add a Google Sheets node after the trigger.
  2. Set Operation to Read.
  3. Select the spreadsheet and sheet.
  4. Enable Return All.
  5. Enable Include Header Row.

5.4 Filter the Desired Row

  1. Add an IF node after the Google Sheets node.
  2. Set the condition to check each item for a matching ID. Use the expression:
{{ $json["ID"] === $node["Manual Trigger"].json["targetId"] }}


Explanation: $json refers to the current row from the Sheets node; $node[“Manual Trigger”].json[“targetId”] pulls the ID we supplied via the manual trigger (we’ll add that field next).


    

  1. In the Manual Trigger node, click Add Field → Name: targetId → Type: Number (or Text, matching your ID column).
    2. 

  2. Now the IF node will have two outputs: True (matching rows) and False (non‑matching). Connect the True output to the next step.
    3. 



5.5 Prepare the Update Payload


    

  1. Add a Set node after the IF’s true branch.
    2. 

  2. Rename it to Build Update.
    3. 

  3. We need to tell Google Sheets which row to update and what values to write. Use two fields:
    4. 

      

    • Row Number → {{ $node[“Google Sheets”].json[“_rowIndex”] + 1 }}. The Sheets node adds a hidden _rowIndex field (zero‑based) when Include Header Row is enabled; adding 1 gives the actual row number in the sheet.
      • 

    • Status → {{ $json["newStatus"] }} (we’ll add this field to the trigger).
      • 

    

  4. Add another field to the Manual Trigger: Name: newStatus → Type: Text.
    5. 



5.6 Add the Google Sheets Node (Update Operation)


    

  1. Add a Google Sheets node after the Set node.
    2. 

  2. Set Operation to Update.
    3. 

  3. Select the same spreadsheet and sheet.
    4. 

  4. In Range, we will use a dynamic range: Sheet1!{{ $json["Row Number"] }}:{{ $json["Row Number"] }}. This tells n8n to update exactly that row.
    5. 

  5. Under Value Input Mode, choose RAW.
    6. 

  6. In Fields to Set, add:
    7. 

      

    • Values → {{ [$json["Status"]] }} (again a 2‑dimensional array; we only update one column, but you can add more columns by extending the inner array).
      • 

    



5.7 Test the Update


    

  1. Save the workflow.
    2. 

  2. Execute the Manual Trigger.
    3. 

  3. Fill in targetId (e.g., 42) and newStatus (e.g., Contacted).
    4. 

  4. Run the workflow.
    5. 

  5. Check the sheet: the row with ID 42 should now have Status = Contacted.
    6. 



Tip: If your sheet does not contain a hidden _rowIndex field (older n8n versions), you can instead use the Find operation to locate the row and then use its rowId output directly in the Update node’s Row ID field.



Step 6: Sync Google Sheets with a CRM (HubSpot Example)


Many teams keep a master list of contacts in Google Sheets while the CRM holds the official record. This step shows a bidirectional sync: new rows in the sheet create HubSpot contacts, and updates to HubSpot contacts are written back to the sheet.


6.1 Prerequisites for HubSpot


    

  • A HubSpot account (free tier works).
    • 

  • An API key with CRM scope (Settings → Integrations → API key).
    • 

  • Knowledge of the HubSpot contact properties you want to map (e.g., firstname, lastname, email).
    • 



6.2 Create HubSpot Credential in n8n


    

  1. In n8n, go to Credentials → New Credential → HubSpot API Key.
    2. 

  2. Name it HubSpot API.
    3. 

  3. Paste your HubSpot API key.
    4. 

  4. Save.
    5. 



6.3 Workflow: Sheet → HubSpot (Create Contact)


    

  1. Create a new workflow named Sheet to HubSpot Sync.
    2. 

  2. Add a Cron trigger (schedule every 15 minutes) or a Webhook if you prefer real‑time.
    3. 

  3. Add a Google Sheets node (Read operation) to fetch new rows. To avoid processing the same rows repeatedly, add a helper column like Processed (initially blank).
    4. 

  4. Set the Google Sheets node to read only rows where Processed is empty: use the Filter option (Filter By Formula) with =LEN(Processed)=0 (Google Sheets formula syntax).
    5. 

  5. Add a Set node to map sheet columns to HubSpot contact properties. Example mapping:
    6. 

      

    • firstname → {{ $json["First Name"] }}
      • 

    • lastname → {{ $json["Last Name"] }}
      • 

    • email → {{ $json["Email"] }}
      • 

    • phone → {{ $json["Phone"] }} (optional)
      • 

    

  6. Add a HubSpot node after the Set.
    7. 

  7. Set Resource to Contact, Operation to Create.
    8. 

  8. Choose the HubSpot credential.
    9. 

  9. In Fields to Set, map the properties from the previous Set node (n8n will auto‑suggest matches).
    10. 

  10. Add another Set node after HubSpot to mark the sheet row as processed.
    11. 

  11. Set Fields to Set:
    12. 

      

    • Processed → {{ $now }} (timestamp) or simply TRUE.
      • 

    

  12. Add a final Google Sheets node (Update operation) to write the Processed flag back to the same row. Use the _rowIndex trick from Step 5 to target the exact row.
    13. 



When the workflow runs, any new contact rows in the sheet will be created in HubSpot and flagged as processed.


6.4 Workflow: HubSpot → Sheet (Update Contact Changes)


To keep the sheet in sync when contacts are edited in HubSpot, we can poll HubSpot for recently updated contacts and write changes back.


    

  1. Create a second workflow named HubSpot to Sheet Sync.
    2. 

  2. Add a Cron trigger (e.g., every 30 minutes).
    3. 

  3. Add a HubSpot node:
    4. 

      

    • Resource: Contact
      • 

    • Operation: Get Many (or Get All with filters).
      • 

    • Set Properties to return: firstname,lastname,email,phone,lastmodifieddate.
      • 

    • Optionally add a filter: lastmodifieddate greater than the timestamp of the last run (store that timestamp in a workflow environment variable or a small helper sheet).
      • 

    

  4. Add a Set node to rename HubSpot properties to match your sheet columns (reverse of the earlier mapping).
    5. 

  5. Add a Google Sheets node (Update operation). Use a lookup column like Email to find the matching row:
    6. 

      

    • First, add a Google Sheets node (Read) to pull the entire sheet (or just the Email column).
      • 

    • Use an IF node to compare each HubSpot email with the sheet’s email column ({{ $json["email"] === $node["Google Sheets"].json["Email"] }}).
      • 

    • When true, forward the row’s _rowIndex to a Set node that builds the update payload (similar to Step 5).
      • 

    • Finally, a Google Sheets Update node writes the new values back to the matched row.
      • 

    



Tip: For large sheets, avoid reading the entire sheet on every poll. Instead, maintain a separate “lookup” sheet that only contains the Email and row number, or use HubSpot’s unique vid (contact ID) stored in a hidden column.



Step 7: Error Handling, Retries, and Notifications


Production workflows need resilience. n8n provides built‑in error workflows, retry policies, and notification nodes.


7.1 Enable Retry on Nodes


Most nodes (Google Sheets, HubSpot, HTTP Request) have a Retry On Fail toggle.


    

  1. Open the node’s settings (gear icon).
    2. 

  2. Under Retry On Fail, set:
    3. 

      

    • Attempts: 3 (initial try + 2 retries).
      • 

    • Delay: 1000 ms (exponential backoff can be enabled).
      • 

    • Error Types: leave blank to retry on any error, or specify ECONNRESET, ETIMEDOUT, etc.
      • 

    



7.2 Attach an Error Workflow


    

  1. In the workflow editor, click the Workflow Settings (gear icon top‑right).
    2. 

  2. Under Error Workflow, select Create New.
    3. 

  3. Name it Global Error Handler.
    4. 

  4. Add nodes that suit your alerting strategy:
    5. 

      

    • Email Send (or Slack, Microsoft Teams) to notify admins.
      • 

    • Set to capture error details: {{ $error.message }}, {{ $error.stack }}, {{ $workflow.name }}.
      • 

    • Optionally a Google Sheets node to log errors to a dedicated “Error Log” sheet for later analysis.
      • 

    



Now any node that fails and exhausts its retries will trigger this error workflow.


7.3 Example: Slack Notification on Failure


    

  1. Add a Slack node (Credential: Slack OAuth2 or Bot Token).
    2. 

  2. Set Resource to Message, Operation to Post.
    3. 

  3. Choose a channel (e.g., #automation‑alerts).
    4. 

  4. In the Text field, use:
    5. 



:warning: *Workflow Error* 
*Workflow*: {{$workflow.name}}
*Node*: {{$node.name}}
*Message*: {{$error.message}}
*Time*: {{$now}}



This gives you immediate visibility when something goes wrong.



Step 8: Schedule, Deploy, and Monitor


Once your workflows are tested, you’ll want them to run automatically.


8.1 Using the Cron Trigger


The Cron trigger lets you define precise schedules using standard cron syntax.


    

  1. Add a Cron node at the start of your workflow.
    2. 

  2. In the Cron Expression field, enter something like:
    3. 

      

    • 0 */15 * * * * – every 15 minutes.
      • 

    • 0 0 * * * – at midnight each day.
      • 

    • 0 0 * * MON – every Monday at midnight.
      • 

    



8.2 Exporting and Importing Workflows


To move workflows between environments (e.g., from a dev instance to production), use the export/import feature.


    

  1. Open the workflow, click the three‑dot menu → Export → download the JSON file.
    2. 

  2. On the target instance, click Import → upload the JSON.
    3. 

  3. Make sure to recreate any credentials (or use the Credential ID mapping feature).
    4. 



8.3 Monitoring Executions


n8n’s execution list shows each run’s status, duration, and data flow.


    

  1. Click Executions in the left sidebar.
    2. 

  2. Filter by workflow name, status (success/error), or time range.
    3. 

  3. Click an execution to view the Execution Data – you can see the exact input/output of each node, which is invaluable for debugging.
    4. 



8.4 Scaling on a Managed Host


If you prefer not to manage updates, backups, or scaling yourself, consider a managed n8n hosting provider such as n8nautomation.cloud. They handle the underlying infrastructure, provide automated backups, SSL, and easy credential management, letting you focus purely on building automations.



Testing Your Automation


Before declaring a workflow production‑ready, run through these checks:


    

  1. Unit‑test each node with sample data (use the Execute Node option).
    2. 

  2. Run the full workflow with a variety of edge cases: empty rows, duplicate keys, special characters, and large payloads.
    3. 

  3. Verify that retries and error workflows fire as expected by temporarily breaking a credential or network connection.
    4. 

  4. Check that scheduled triggers respect your timezone (n8n uses UTC by default; adjust the Cron expression or set the TIMEZONE environment variable).
    5. 

  5. Confirm that data written to Google Sheets appears correctly (no extra quotes, correct data types).
    6. 

  6. Review the execution logs for any warnings (e.g., deprecated field usage).
    7. 




Common Issues & Troubleshooting


Below are frequent problems you may encounter and how to resolve them.



1. “Invalid Value for Spreadsheet ID”


Cause: The ID you entered is incorrect or the spreadsheet isn’t shared with the authenticating user.


Fix: Open the spreadsheet in Google Sheets; the ID is the long string between /d/ and /edit in the URL. Ensure the user you authenticated with (or the service account linked to the OAuth consent screen) has at least Viewer access.



2. OAuth2 Consent Screen Shows “This app isn’t verified”


Cause:** Google marks new OAuth clients as unverified until you go through verification (optional for internal use).


Fix:** For testing, click Advanced → Go to [your project] (unsafe). For production, follow Google’s verification process or set the app to Internal if only users in your Google Workspace domain will use it.



3. Duplicate Rows When Using Append


Cause:** The workflow runs multiple times and each execution appends the same data.


Fix:** Add a deduplication step (e.g., compute a hash of the row and check against a “processed” column) or use a trigger that only fires on new data (like a webhook from the source system).



4. Empty Cells Appear as “null” in Sheets


Cause:** n8n writes the JavaScript null value, which Google Sheets displays as the word null.


Fix:** In the Set node before the Sheets node, convert nulls to empty strings: {{ $json["field"] === null ? "" : $json["field"] }}.



5. Permission Errors When Updating a Row


Cause:** The OAuth token lacks the https://www.googleapis.com/auth/spreadsheets scope or the sheet is protected.


Fix:** Re‑authenticate with the correct scopes (see Step 2) and ensure the sheet isn’t protected (Data → Protect sheets and ranges).



Conclusion


You now have a complete toolkit for automating Google Sheets with n8n: from setting up OAuth2 credentials, reading and writing data, appending logs, updating records, syncing with CRMs like HubSpot, to adding robust error handling, scheduling, and monitoring. By combining the flexibility of n8n’s node‑based workflow engine with the ubiquitous spreadsheet format, you can create powerful, maintainable automations that keep your data flowing across systems without manual intervention.


Remember to test thoroughly, leverage retries and error workflows, and consider a managed hosting solution like n8nautomation.cloud for hassle‑free operations, backups, and scaling. Happy automating!

Ready to automate with n8n?

Get affordable managed n8n hosting with 24/7 support.