Model Context Protocol (MCP) finally gives AI models a way to access the business data needed to make them really useful at work. CData MCP Servers have the depth and performance to make sure AI has access to all of the answers.
Try them now for free →
How to Import Data from Odoo into Google Sheets
Import Odoo data into a Google spreadsheet that is automatically refreshed.
The CData API Server, when paired with the ADO.NET Provider for Odoo (or any of 200+ other ADO.NET Providers), connects cloud applications, mobile devices, and other online applications to Odoo data via Web services, the standards that enable real-time access to external data. Use Google Sheets' built-in support for Web services to rely on up-to-date Odoo data, automate manual processes like rekeying data, and avoid uploading files.
What is The API Server?
The API Server is a lightweight Web application that runs on your server and produces secure feeds of any of 200+ data sources. The .NET edition can be deployed to Azure in 3 steps. The Java edition can be deployed to Heroku as well as any Java servlet container. You can also easily deploy on an Amazon EC2 AMI.
About Odoo Data Integration
Accessing and integrating live data from Odoo has never been easier with CData. Customers rely on CData connectivity to:
- Access live data from both Odoo API 8.0+ and Odoo.sh Cloud ERP.
-
Extend the native Odoo features with intelligent handling of many-to-one, one-to-many, and many-to-many data properties. CData's connectivity solutions also intelligently handle complex data properties within Odoo. In addition to columns with simple values like text and dates, there are also columns that contain multiple values on each row. The driver decodes these kinds of values differently, depending upon the type of column the value comes from:
- Many-to-one columns are references to a single row within another model. Within CData solutions, many-to-one columns are represented as integers, whose value is the ID to which they refer in the other model.
- Many-to-many columns are references to many rows within another model. Within CData solutions, many-to-many columns are represented as text containing a comma-separated list of integers. Each value in that list is the ID of a row that is being referenced.
- One-to-many columns are references to many rows within another model - they are similar to many-to-many columns (comma-separated lists of integers), except that each row in the referenced model must belong to only one in the main model.
- Use SQL stored procedures to call server-side RFCs within Odoo.
Users frequently integrate Odoo with analytics tools such as Power BI and Qlik Sense, and leverage our tools to replicate Odoo data to databases or data warehouses.
Getting Started
Why Web Services?
Google Sheets is one of the easiest ways to collaborate in real time, so it should be easy to collaboratively work with Odoo data and your other data sources in Google Sheets. However, it can be difficult to get data into Google Sheets. Often, a manual process is required, such as rekeying data or uploading CSV files. Working with a copy rather than the external data, the spreadsheet quickly becomes out of date.
The API Server provides an alternative to physically transferring data into and out of Google Sheets. The API Server provides native read/write connectivity to external Odoo data from both built-in formulas and Google Apps Script.
Instead of manually copying your data, formatting it, and uploading a spreadsheet, retrieving Odoo data into Google Sheets is as simple as using the ImportData formula to make calls to the API Server. The API Server exposes the capabilities of the Odoo API as standard OData queries. The sheet will automatically retrieve updates by periodically executing the query. Below is an example of a search.
https://MyServer:MyPort/api.rsc/res_users/?filter=id eq '1'
See the API page in the API Server administration console for more information on the supported OData.
Are Web Services Secure?
The CData API Server, when paired with any of 200+ other ADO.NET Providers, helps you control access to your data. Unlike data integration services hosted in the cloud, the API Server runs on servers you control; it can run inside or outside the firewall.
You can also use the API Server to take advantage of standard technology for protecting the confidentiality, authenticity, and intended recipients of your data. The API Server supports TLS/SSL and the major forms of authentication. The .NET edition supports standard ASP.NET security. The Java edition is integrated with J2EE security.
How to Retrieve External Odoo Data in Google Sheets
You can consume Odoo data in Google Sheets in 3 steps:
If you have not already connected successfully in the API Server administration console, see the "Getting Started" chapter in the help documentation for a guide.
Authenticate your query with the authtoken of a user authorized to access the OData endpoint of the API Server. The API Server also restricts access based on IP address; you will need to enable access from Google's servers. You can configure access controls on the Security tab.
The ImportData function takes one parameter, the URL, so authentication must be supplied in the URL. For security reasons, the API Server does not allow setting the authtoken in the URL by default, so you will need to enable this by adding the following in settings.cfg. If you would like to use another authentication scheme, like HTTP Basic, see the Odoo and Google Apps Script how-to.
[Application] AllowAuthTokenInUrl = trueThe settings.cfg file is located in the data directory. In the .NET edition, the data directory is the app_data folder under the www folder. In the Java edition, the data directory's location depends on the operation system:
- Windows: C:\ProgramData\CData\Odoo\
- Unix or Mac OS X: ~/cdata/Odoo/
In a new Google sheet, use the ImportData formula to request the CSV file from the OData endpoint of the API Server. Specify the format of the response with the @csv query string parameter. Google will periodically update the results of the formula, ensuring that the sheet contains up-to-date data. You can request the entire res_users table with a formula like the one below:
=ImportData("https://your-server/api.rsc/res_users?@csv&@authtoken=your-authtoken")
