QAD and Google Sheets integration

What moves between them

The main flow runs from QAD into Google Sheets. On a schedule, ml-connector reads QAD records such as supplier invoices, purchase orders, suppliers, and GL accounts and appends or updates them as rows in the matching tab, for example an Invoices or PurchaseOrders sheet. Where a team maintains data in a sheet that QAD can accept, such as a vendor list or a coding table, ml-connector reads those rows and writes them into QAD. The cadence is set per flow, commonly every few minutes to hourly, since neither side pushes events to the other.

How ml-connector handles it

ml-connector stores both credential sets encrypted, accepts the full QAD tenant URL per customer since QAD publishes no shared base address, and refreshes the QAD bearer token when a call returns 401. On the Google side it runs the OAuth2 authorization-code flow or a service account and refreshes the access token automatically; for a service account the target spreadsheet must be shared with the service account email, the most common cause of a 403. Because both systems are pull-only for connectors, it polls on the schedule you set rather than waiting for a push, and it can use a Drive watch channel only where near-real-time latency is needed, re-registering before the 24-hour expiry. It reads each tab's header row to map columns to QAD fields, pads trailing empty cells, and reads unformatted values so numbers and dates do not arrive as plain strings. Appends are not idempotent in Sheets, so it tracks a last-synced cursor and dedups before writing to avoid duplicate rows, batches reads and writes to stay inside the per-project 300-requests-per-minute quota, and backs off on 429. Every record carries a full audit trail and can be replayed if a downstream call fails.

A real-world example

A mid-sized contract manufacturer runs QAD Adaptive ERP for procurement and finance, while the AP team tracks open supplier invoices and approval status in a shared Google Sheet because it is faster to filter and comment on than the ERP screens. Before the integration, someone exported invoice and PO data from QAD every morning and pasted it into the sheet, and the sheet drifted out of date by mid-afternoon. With QAD and Google Sheets connected, the Invoices and PurchaseOrders tabs refresh automatically from QAD on a schedule, and the vendor coding the team maintains in the sheet flows back into QAD, so the spreadsheet stays current and the manual export step is gone.

What you can do

• •Write QAD supplier invoices, purchase orders, suppliers, and GL accounts into named Google Sheets tabs on a schedule.
• •Read structured data a team maintains in a sheet, such as a vendor or coding table, back into QAD where the API allows.
• •Map each tab's header row to QAD fields so columns line up without hardcoded cell references.
• •Authenticate Google Sheets with OAuth2 or a service account and QAD with its tenant-specific token, refreshing both automatically.
• •Poll on the cadence you set, with cursor-based dedup, batching to stay inside Google quotas, retries, and a full audit trail.

Questions

Which direction does data move between QAD and Google Sheets?

The main flow is QAD into Google Sheets, where ml-connector writes records such as supplier invoices, purchase orders, suppliers, and GL accounts as rows in named tabs. Where a team maintains data in a sheet that QAD can accept, such as a vendor or coding table, those rows can flow back into QAD. Each flow sets its own direction and schedule, and QAD remains the system of record for finance.

Does Google Sheets support real-time webhooks for QAD changes?

Not directly. The Sheets API has no native webhook, and QAD cloud does not push events to connectors either, so the integration runs on scheduled polling. Push from Google is only possible indirectly through Drive watch channels that expire within 24 hours and carry no signed payload, so ml-connector uses polling by default and only adds a watch channel when near-real-time latency is required.

How does ml-connector avoid duplicate rows when writing to a sheet?

Google Sheets appends are not idempotent, so re-running an append would add duplicate rows. ml-connector tracks a last-synced cursor, such as a row count or a timestamp column, and dedups against it before writing so each QAD record lands once. It also batches reads and writes into single API calls to stay inside Google's per-project rate limits and backs off when a call returns 429.