Bidirectional Google Sheets Sync — Complete Guide

The DataFirefly Sheet Sync module keeps your PrestaShop catalog in sync with a Google Sheet in both directions: you edit the price, stock quantity, title and active status of your products in the sheet, and the module pushes them into PrestaShop; conversely, any change made in the back office flows back into the Sheet automatically. This guide covers installation, creating the Google service account, sharing the sheet, configuration, how synchronization works, the cron and troubleshooting.

Requirements

• PrestaShop 8.0 to 9.x.
• PHP 7.4 to 8.3 with the cURL and OpenSSL extensions enabled.
• A Google account with access to the Google Cloud console to create a service account (free).
• Cron access (a server scheduled task or an external cron service) for automatic synchronization.

Installation

1. In the back office, open Modules > Module Manager, then Upload a module and drop the dfsheetsync.zip file.
2. Once installed, open the configuration page via the Configure button.

On installation, the module creates its sync state table, registers its default values and automatically generates a cron token.

Create the Google service account

A service account is a Google identity the module uses to read from and write to your sheet, with no OAuth flow and no consent screen.

1. In the Google Cloud console, create (or select) a project.
2. Enable the Google Sheets API for this project (menu APIs & Services > Library, search for “Google Sheets API”, then Enable).
3. Open APIs & Services > Credentials, click Create credentials > Service account, give it a name and confirm.
4. Open the created service account, Keys tab, then Add key > Create new key > JSON. A .json file is downloaded: this is the key you will paste into the module.

Share the sheet with the service account

The JSON file contains a client_email field of the form name@project.iam.gserviceaccount.com. That is the address to authorize.

1. Open (or create) your Google Sheet.
2. Click Share and add the service account’s client_email address with the Editor role.

Configuration

The configuration page gathers the following settings:

• Service account JSON: paste the full contents of the .json file here. Leave the field empty to keep the previously saved key.
• Spreadsheet ID or URL: paste the sheet’s ID or its full URL — the ID is extracted automatically.
• Sheet (tab) name: the tab to use inside the spreadsheet (default Products). It is filled with a header row on the first run.
• Language for titles: the language in which product titles are read and written.
• Conflict priority: decides which side wins when a product was edited on both sides (the Google Sheet, or PrestaShop).
• Only export active products: limits synchronization to active products.

Save, then check the status panel: it shows the connected service account e-mail, the cron URL, the last sync date and a direct link to the Sheet.

Sheet structure

The sheet has six columns, whose header is created automatically on the first run:

1. ID — the PrestaShop product ID. Do not edit.
2. Reference — product reference (read-only for the sync).
3. Name — the product title in the configured language.
4. Price (excl. tax) — the base price excluding tax.
5. Quantity — the global stock quantity.
6. Active — 1 for active, 0 for inactive.

How synchronization works

The module stores the fingerprint (checksum) of each product’s last synchronized state. On every run, it compares the shop’s current state and the sheet’s state against that fingerprint to determine what changed, and on which side:

• Sheet changed, shop unchanged → the sheet’s values are applied to the product.
• Shop changed, sheet unchanged → the shop’s values are written into the sheet.
• Both changed → the configured priority rule decides (the Sheet wins, or the shop wins), and the decision is logged.
• Product missing from the sheet → it is appended automatically, with the shop as the reference.

Only the rows that actually changed are written, in the right direction; writes to Google are batched to respect the API quotas and stay fast even on a large catalog.

Manual sync and reset

Two buttons are available in the configuration:

• Sync now: immediately runs a full synchronization and displays a report (rows pushed to the Sheet, products updated, rows appended, conflicts resolved, rows skipped).
• Reset state: empties the sync state table. On the next run, the shop is treated as the source of truth for every product. Useful after a manual reorganization of the sheet.

Cron setup

For automatic synchronization, schedule the cron URL shown in the configuration, every 5 to 15 minutes:

curl "https://your-shop.com/module/dfsheetsync/cron?token=YOUR_TOKEN"

The token is generated on installation and secures the endpoint. The call returns a JSON report of the synchronization.

Data validation

Before writing to the shop, the module validates each row of the sheet. A row is skipped and logged (without interrupting the rest of the synchronization) in the following cases:

• empty product name, or a name containing disallowed characters;
• negative price;
• ID matching no product in the shop.

The sync report shows the number of skipped rows, and the detail is available in the PrestaShop logs.

Synchronized fields and limitations

Version 1.0.0 synchronizes, for each product: the title (in the configured language), the base price excluding tax, the global stock quantity and the active/inactive status.

Troubleshooting

• “Permission denied” error: check that the sheet is shared as Editor with the service account’s client_email address.
• “Invalid JSON” error: the pasted content must be the full key file, containing at least client_email and private_key.
• No synchronization: check that the spreadsheet ID is set and the cron actually runs, or trigger a manual sync.
• Google authentication error: make sure the Google Sheets API is enabled for the project and the server clock is accurate (the JWT is time-stamped).
• A row is not applied: empty/invalid name or negative price; fix the row in the sheet.
• A product reverts to the old value: check the conflict priority rule; the priority side overwrites the other when both changed.

Uninstalling

Uninstalling removes the sync state table and the module’s configuration (including the service account key and the cron token). Your products and your Google Sheet are not modified. For a simple update, replacing the module files is enough: the sync state is preserved.