SAP Business One and Google Sheets integration

What moves between them

The primary flow is from SAP Business One into Google Sheets. ml-connector polls purchase orders, invoices, journal entries, and business partner records on a schedule you set (typically every 4-24 hours depending on your reporting cadence), and writes or updates rows in a Sheets tab based on the document number from SAP B1. Each record includes source data such as document date, vendor name, amount, and GL account to support financial analysis and cross-functional visibility. Updates are idempotent: if a sync is interrupted and retried, rows are matched by document number and updated in place rather than duplicated.

How ml-connector handles it

ml-connector maintains a B1SESSION token by logging in to SAP Business One at the customer-supplied Service Layer URL and refreshing the session every 25 minutes to stay well ahead of the 30-minute inactivity timeout. Since SAP Business One does not publish idempotency keys, ml-connector deduplicates on document number (DocNum) before inserting or updating rows in Google Sheets, preventing duplicates if a previous sync attempt failed partway through. On the Sheets side, ml-connector uses OAuth 2.0 credentials (either user-delegated or Service Account) to authenticate and writes data using the Sheets API's batch update operations. The flow respects OData query limitations by using separate API calls for associated data when $expand is unavailable, and it tracks the last sync timestamp to poll incrementally via the UpdateDate filter rather than refetching all records on each run.

A real-world example

A mid-sized manufacturing company runs SAP Business One on-premise for procurement and accounting. The operations team needs weekly visibility into purchase orders by supplier and cost center for cash flow planning, and the finance team must reconcile supplier invoices against SAP B1 records before month-end. Previously, a purchasing analyst exported PO and invoice reports from SAP B1 by hand each Monday and pasted them into a shared Google Sheet for the team to review and analyze. With SAP Business One and Google Sheets connected, the export and paste steps are gone: every Monday morning the sheet updates automatically with the latest purchase orders and invoices, filtered by date and supplier, so the operations team has current data to build the cash flow forecast, and the finance team starts the month-end close with SAP B1 data already loaded into their reconciliation sheet.

What you can do

• •Sync purchase orders, invoices, and payments from SAP Business One to Google Sheets on a configurable schedule.
• •Authenticate SAP Business One using the B1SESSION token and refresh it every 25 minutes to prevent timeout.
• •Deduplicate records by document number so retried syncs do not create duplicate rows in Sheets.
• •Map SAP Business One business partners, cost centers, and GL accounts to human-readable columns in your Sheets schema.
• •Handle OData pagination and $expand limitations by fetching associated data via separate API calls when needed.

Questions

Does the integration work with on-premise SAP Business One only?

Yes. SAP Business One Service Layer instances are deployed on-premise or partner-hosted, and each customer provides their own base URL and port. ml-connector connects to the customer-supplied URL (typically https://<server>:50001/b1s/v2/), so your network must allow inbound connections from the ml-connector service to your SAP B1 instance. Self-signed TLS certificates are common on B1 instances; ml-connector handles certificate pinning or CA verification per your security requirements.

What happens if SAP Business One's session times out mid-sync?

ml-connector refreshes the B1SESSION token every 25 minutes, before the 30-minute inactivity timeout can occur. If a refresh fails (for example, due to a network interruption), ml-connector logs in again using the stored SAP B1 credentials and obtains a new session token. The sync resumes from where it left off using the last sync timestamp, so no records are lost or duplicated.

How does the integration prevent duplicate rows in Google Sheets if a sync is interrupted?

Each SAP Business One document has a unique DocNum (document number). ml-connector checks if a row with that DocNum already exists in the Sheets tab before inserting; if it exists, the row is updated in place instead. This deduplication strategy ensures that even if a sync is retried after a failure, Sheets contains one row per SAP B1 document, not multiple copies.