How to export Magento 2 data to Google Sheets

- E-Commerce, Magento 2, Our extensions

FireBear keeps building up Improved Import and Export with powerful updates. Stay focused as with version 3.3.0 of the extension we introduce a long-awaited export of Magento 2 data to Google Sheets. The feature implementation marks the completion of the full-cycle Magento 2 and Google Sheets synchronization project. Now you are able to both import and export files from the source and arrange a highly manageable workflow.

How to do so? Follow this guide to learn more.

'

Overview

Google Sheets has become an invaluable data source for those supporting Magento 2 with Improved Import and Export. The service is friendly to users of all levels of proficiency with the platform and doesn’t require much in terms of hardware performance. It’s accessible anytime from any part of the world and even offers offline support of spreadsheets existing in Google Drive. 

No wonder our team has turned to Google Sheets to deliver insights on creating tables with Magento 2 entity attributes. Thanks to the advanced sharing settings provided in all Google services, we are able to provide users with access to the Master Import Table and keep it up to date. With the Magento 2 and Google Sheets integration, you are able to export up to 40k products for all the sheets in a file.

The Google Sheet Export feature complements the previously developed Google Sheet Import. Using the functionality combination, you can solve multiple issues related to continuous data exchange, database maintaining, and workflow automation.

Use Cases

Bidirectional synchronization of Magento 2 and Google Sheets allows for collective data management. Exporting data to a Google Sheet, you can stay independent of hard storage size and keep the files available in the cloud. The shared access to Google Sheets is the key to managing files via multiple accounts and organizing effective database update practices with reduced error cases.

Exclusive opportunities for PIM (product information management) come with the enabled Magento 2 and Google Sheets synchronization. Exporting products from Magento 2, you provide the data for open modifications. All types of media, text, links are easily organized within a spreadsheet so that several of your content-makers can edit categories or any other attribute in the table simultaneously. The changes made can be implemented into the database via the improved import functionality automatically with cron.

If you select Improved Import and Export for Magento 2 integrations with any platform or application, the Google Sheets export can serve as an extremely user-friendly way to extract and store the data for future implementation into a foreign environment. 

Google API configuration

To get access to the native Google API, you need to add the relevant libraries to your store. 

1) Navigate to your store root folder in the SSH console of your server:

2) Run the next commands:

API Library Installation

The connection between a Magento 2 store and the Google Sheets service requires Google API enabled. You can get all the necessary credentials by logging into your Google account and visiting the Google Cloud Platform console

API Connection Establishment

Each project based on the enabled API possesses a unique email address and service account key. Use the first one to share the access to an export file or a folder; the latter guarantees the API authorization from the Magento 2 side will be completed.

Enabling Google Drive API:

Go to the Google Drive API library page.

1. Create a new project or select an existing one at the blue console panel at the top of the page.

2. Click the ‘Enable’ button for API activation.

Generating a Service Account Key:

1. Open the Google Cloud Console

2. Go to APIs & Services > Credentials.

3. Select Create Credentials > Service Account Key.

4. Define your account with a title and description.

5. Skip the optional settings for account roles and go to Grant user access to this service account

6. Create a service account key under the JSON type.

7. Click the ‘Create’ button.

7.1. The key will be downloaded to the file system automatically.

Configuring a Service Account

1. Open the Google Cloud Console 

2. Go to IAM & admin > Service Accounts.

3. Click the ‘Edit’ button for a specific account to get the email address that will be used for sharing access to a Google Drive folder.

4. Copy the Service Account email.

Sharing a Google Drive Folder

1. Go to https://drive.google.com/drive/my-drive.

2. Open the ‘Share’ settings for the specific folder.

3. Click the ‘Advanced’ button.

4. Enter the Service Account email you’ve previously got from the Google Console in the ‘Invite People’ input.

5. Uncheck the ‘Notify People’ checkbox.

6. Click the ‘OK’ button.

Once again, the service account key is a JSON file that is automatically saved to your device once you generate it. You can’t restore the file so make sure not to lose it. 

Google Sheets Export

You can create a job for Google Sheets export at System > Improved Import/Export > Export Jobs

The configuration starts with the general settings. Here you give a title to a job and decide whether you are going to export data manually or automatically (no worries, a job setting can be changed anytime). Automatic export can be enabled by both cron scheduling and trigger Magento 2 events. Once configured, the job with automatic runs won’t require any extra management.

Consecutive export will reduce the time you spend on export. With the feature, any database updates made after the last job run will be transferred right to Google Sheets without copying previously exported units or running the export from the beginning.

Selecting the entity for extraction is the required setting. You have full control over the data you get as you can decide what parts to export depending on the entity type.

To establish a connection with the export source, you first need to select the appropriate data format. Google Sheets as the export source can be enabled in the file format settings along with other supported formats, including CSV, XML, XLSX, Json, ODS, ZIP, and TAR. 

The export source settings present UI features unique to each source type, including Google Sheets. To run Google Sheets export successfully, first, you need to specify the ID of a spreadsheet you want to export to. File or a folder with the file must be shared with the improved export functionality with the permission to edit the data. The sheet ID section allows deciding what sheet will be filled with the exported data.

The upload of a service account key you previously received at the Google Cloud console is the next step in enabling the synchronization with Google Sheets.

If there’s a need to modify some of the attribute values, Improved Export provides the mapping tool. With its help, you are able to compose unique data tables for a wide range of use. Save the configured job and run it manually or leave to cron for running according to the schedule specified.

Note! that Google Sheets export is limited to 4 million table units for all the tabs in a file. If you face an issue when your products are not exported fully, keep in mind that the data might have gone over the limit.

Full-cycle synchronization between Magento 2 and Google Sheets brings new prospects of exploiting data transfers within and outside the platform. Want to learn what you can achieve with the type of connection available exclusively with Improved Import and Export? Explore the extension page.

Guide List

Visit our guide list to find trustable information on working with Magento 2 import and export. Get to know the specifics of the Magento 2 Google Drive import and export to understand how Improved Import and Export supports the integration between the systems. Make sure to look through the Magento 2 Google Sheet Import manual to imagine the whole picture of Magento 2 and Google Sheets connection.

'