How to Connect Google Sheets to Odoo: Data Sync and Reporting Guide

Why Connect Google Sheets to Odoo?

Odoo has robust reporting, but teams still reach for Google Sheets. The reasons are practical: Sheets is collaborative (everyone can edit simultaneously), it is familiar (no learning curve), it supports custom formulas and pivot tables, and it is the default tool for ad-hoc analysis. Rather than fighting this habit, connect Sheets to Odoo so data flows automatically between both systems.

Common Use Cases

• Custom dashboards: Pull Odoo data into Sheets for charts and KPIs that non-Odoo users can view
• Bulk data import: Prepare data in Sheets (products, contacts, price lists) and push to Odoo
• Collaborative editing: Sales team updates a Sheet, changes sync to Odoo CRM
• Data validation: Export Odoo data to Sheets for review before committing changes
• Cross-system reporting: Combine Odoo data with data from other sources in one Sheet

Method 1: Google Sheets + Odoo API (Direct)

Step 1: Get Odoo API Credentials

1. In Odoo, go to your user profile → Account Security
2. Generate a new API Key
3. Note your Odoo URL and database name

Step 2: Use Google Apps Script

Google Sheets has a built-in scripting engine (Apps Script) that can call Odoo's XML-RPC API:

1. Open your Google Sheet
2. Go to Extensions → Apps Script
3. Create a script that calls Odoo's JSON-RPC endpoint:

function fetchOdooData() {
  var url = 'https://your-odoo.com/jsonrpc';
  var db = 'your-database';
  var uid = 2; // your user ID
  var apiKey = 'your-api-key';
  
  var payload = {
    'jsonrpc': '2.0',
    'method': 'call',
    'params': {
      'service': 'object',
      'method': 'execute_kw',
      'args': [db, uid, apiKey, 'sale.order', 'search_read',
        [[['state', '=', 'sale']]],
        {'fields': ['name', 'partner_id', 'amount_total', 'date_order'], 'limit': 100}
      ]
    }
  };
  
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };
  
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  var orders = data.result;
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(['Order', 'Customer', 'Total', 'Date']);
  
  orders.forEach(function(order) {
    sheet.appendRow([
      order.name,
      order.partner_id[1],
      order.amount_total,
      order.date_order
    ]);
  });
}

Step 3: Schedule Automatic Refresh

1. In Apps Script, go to Triggers
2. Add a time-driven trigger for your function
3. Set it to run hourly, daily, or weekly
4. The Sheet updates automatically with fresh Odoo data

Method 2: Google Sheets + Zapier

For a no-code approach, use Zapier as the bridge:

Odoo → Google Sheets

1. Create a Zap with trigger: Odoo → New Sale Order
2. Action: Google Sheets → Create Spreadsheet Row
3. Map Odoo fields to Sheet columns
4. Every new sale order automatically adds a row

Google Sheets → Odoo

1. Trigger: Google Sheets → New or Updated Row
2. Action: Odoo → Create Record (e.g., create a contact)
3. Map Sheet columns to Odoo fields
4. Adding a row in Sheets creates a record in Odoo

Method 3: Odoo Export + Google Sheets IMPORTDATA

For simple one-way data pulls:

1. Create a custom Odoo controller that returns CSV data
2. In Google Sheets, use: =IMPORTDATA("https://your-odoo.com/api/export/sales.csv?key=YOUR_KEY")
3. The Sheet auto-refreshes approximately every hour

Bulk Import from Sheets to Odoo

For one-time or periodic data loads:

1. Prepare data in Google Sheets with Odoo-compatible headers
2. Download as CSV
3. In Odoo, go to the target list view → Favorites → Import Records
4. Upload the CSV and map columns
5. Preview and import

Tip: Use Odoo's export function first to see the exact column headers and format Odoo expects. Then match your Sheet to that format.

Troubleshooting

Apps Script Timeout

Google Apps Script has a 6-minute execution limit. For large data pulls (thousands of records), paginate your Odoo API calls using offset and limit parameters. Process data in batches of 500 records.

Authentication Errors

Odoo's JSON-RPC requires the numeric user ID (not email). Find your user ID in Settings → Users → open your user record → check the URL for the ID number. API key authentication is more reliable than password-based auth for automated scripts.

Data Not Refreshing

IMPORTDATA refreshes at Google's discretion (roughly hourly). You cannot force a specific refresh interval. For controlled refresh timing, use Apps Script with time-driven triggers instead.

DeployMonkey Sheets Integration

DeployMonkey instances provide stable API endpoints that Google Sheets Apps Scripts can connect to reliably. Our AI agent can generate the Apps Script code for your specific data needs — just describe what data you want in your Sheet.