Integration of 1C-Bitrix with Google Looker Studio

Integration of 1C-Bitrix with Google Looker Studio

Google Looker Studio (formerly Google Data Studio) is a data visualization tool that builds dashboards from various sources. Data from Bitrix can be transmitted to Looker Studio in three ways: through Google Sheets as an intermediate layer, through BigQuery, or through a custom connector. The choice depends on data volume and update frequency.

Data Transfer Architecture

Scheme 1—through Google Sheets (for small volumes):

Bitrix → PHP agent → Google Sheets API → Looker Studio

Suitable for 5,000–50,000 rows, updates every few hours. The fastest path to a dashboard without complex infrastructure.

Scheme 2—through BigQuery (for large volumes):

Bitrix → PHP agent → BigQuery API → Looker Studio

BigQuery is optimal for millions of rows (order history over several years, behavioral analytics events). Looker Studio has a native connector to BigQuery.

Scheme 3—custom Looker Studio connector:

Looker Studio → Bitrix REST API → Looker Studio

Looker Studio requests data directly from the API on schedule. Does not require intermediate storage, but loads the Bitrix server with frequent requests.

Implementation through Google Sheets

The most practical option for e-commerce projects. Google Sheets API accepts data via OAuth 2.0 service account.

Step 1. Create a service account in Google Cloud Console, download JSON with keys, grant access to the required sheet.

Step 2. Install the library via Composer or connect manually:

composer require google/apiclient

Step 3. A Bitrix agent collects data and sends it to Sheets:

function syncOrdersToSheetsAgent(): string
{
    $ordersData = collectOrdersData(); // data array from b_sale_order
    updateGoogleSheet(SHEETS_SPREADSHEET_ID, 'Orders!A1', $ordersData);
    return __FUNCTION__ . '();';
}

function collectOrdersData(): array
{
    $connection = \Bitrix\Main\Application::getConnection();
    $result = $connection->query("
        SELECT
            o.ID,
            o.DATE_INSERT,
            o.PRICE,
            o.CURRENCY,
            o.STATUS_ID,
            o.USER_ID,
            u.LOGIN,
            u.EMAIL
        FROM b_sale_order o
        LEFT JOIN b_user u ON u.ID = o.USER_ID
        WHERE o.DATE_INSERT >= DATE_SUB(NOW(), INTERVAL 90 DAY)
        ORDER BY o.DATE_INSERT DESC
        LIMIT 10000
    ");

    $rows = [['ID', 'Date', 'Amount', 'Currency', 'Status', 'Customer ID', 'Login', 'Email']];
    while ($row = $result->fetch()) {
        $rows[] = array_values($row);
    }
    return $rows;
}

function updateGoogleSheet(string $spreadsheetId, string $range, array $data): void
{
    $client = new \Google\Client();
    $client->setAuthConfig(APPLICATION_ROOT . '/local/config/google-service-account.json');
    $client->addScope(\Google\Service\Sheets::SPREADSHEETS);

    $service   = new \Google\Service\Sheets($client);
    $body      = new \Google\Service\Sheets\ValueRange(['values' => $data]);
    $params    = ['valueInputOption' => 'USER_ENTERED'];

    $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
}

Data Structure for Typical Dashboards

For an e-commerce dashboard in Looker Studio, the following sheets in Google Sheets are typically needed:

For a CRM dashboard:

Looker Studio Configuration

1. Open lookerstudio.google.com → create a data source
2. Select Google Sheets connector
3. Specify the table and sheet with Bitrix data
4. Looker Studio will determine column types: numbers, text, dates
5. Create a report with the desired charts

Important settings in Looker Studio:

• Date field (DATE_INSERT) should have type "Date and time"—Looker Studio will automatically detect if format is YYYY-MM-DD HH:MM:SS
• Amount field (PRICE)—type "Number", format "Currency"
• For aggregation by periods add a calculated field DATE_TRUNC(DATE_INSERT, MONTH)

Automatic Data Update

A Bitrix agent runs on schedule:

// Register agent in init.php or module installation
\CAgent::AddAgent(
    'syncOrdersToSheetsAgent();',
    'my_analytics',
    'N',
    3600,            // every hour
    '',
    'Y',
    \ConvertTimeStamp(time() + 3600, 'FULL')
);

When updating incrementally (only new data), add WHERE o.DATE_INSERT >= ? to the query with the last sync date stored in b_option.

Security

The service account JSON key is a confidential file. Store in /local/config/ with HTTP access prohibited via .htaccess:

<Files "google-service-account.json">
    Deny from all
</Files>

In Google Cloud Console, restrict the service account rights: only roles/sheets.editor on the specific sheet, not the entire project.

Implementation Timelines