How to export Magento 2 data to Google Sheets

- E-Commerce, Magento 2, Our extensions

Transferring data between Magento 2 and Google Sheets seems a very efficient and time-saving way of synchronizing your e-commerce website with external platforms and other online storefronts. However, Magento 2 doesn’t let you do that if you rely on its default tools. If you need to import data, it is possible to create a CSV output of a corresponding Google Sheets table and then import it to your store. For the export purposes, you need to generate the output from your website and then open it in Google Sheets. Thus, instead of spending less time on daily duties, you have to perform a time-consuming routine. Luckily, there is a better way of synchronizing Magento 2 and Google Sheets.

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. Below, you will find an overview of the Magento 2 Google Sheets integration project as well as the most frequent use cases. Next, we describe Google API configuration, including such aspects as API library installation, API connection establishment, enabling Google Drive API, generating a Service Account Key, configuring a Service Account, and sharing the corresponding Google Drive folder. After that, we proceed to the Magento 2 Google Sheets export procedure.

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.

Magento 2 Google Sheets Integration FAQ

Below, we share several other nuances of the Magento 2 Google Sheets integration.

How to import and export customers between Magento 2 and Google Sheets?

To move customers between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Customers as your import or export entity in the corresponding settings section of each import and export profile. That’s it! Note that you can transfer all customer data, including addresses and passwords.

How to import and export products between Magento 2 and Google Sheets?

To move products between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Products as your import or export entity in the corresponding import settings section. Our module works with all product types: simple, configurable, grouped, bundle, virtual, downloadable, and even gift cards!

How to import and export orders between Magento 2 and Google Sheets?

To move orders between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Orders as your import or export entity in the corresponding import settings section. That’s it! You can transfer orders along with all the related documents.

How to import and export categories between Magento 2 and Google Sheets?

To move categories between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Categories as your import or export entity in the corresponding import settings section. Our module lets you map imported categories to ones that are already represented in your category tree.

How to import and export prices between Magento 2 and Google Sheets?

To move advanced pricing between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Products as your import or export entity in the corresponding import settings section, transferring advanced and tier prices along with them. Note that it is necessary to edit the product data table in a specific manner to enable the price update.

How to import and export product attributes between Magento 2 and Google Sheets?

To move product attributes between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Product Attributes as your import or export entity in the corresponding import settings section. Note that the module provides the ability to move product attributes, attribute sets, and attribute groups as dedicated entities.

How to import and export newsletter subscribers between Magento 2 and Google Sheets?

To move newsletter subscribers between Magento 2 and Google Sheets, you need to establish a connection between the two systems following the instructions provided in this article. It is necessary to create an import job and a corresponding export job to move data in both directions. Next, choose Newsletter Subscribers as your import or export entity in the corresponding import settings section. Thus, you can subscribe the existing customers to your new newsletter as well as add new subscribers.

How to import and export data between Magento 2 and Google Sheets automatically?

You can utilize the Improved Import & Export module to automate your data synchronization between Magento 2 and Google Sheets. The extension uses a cron scheduler, providing the ability to choose or create intervals for automated data transfers. Specify a period of updates, and the module will do everything for you.

How to import data with third-party attributes from Google Sheets to Magento 2?

Although Magento 2 doesn’t let you import data with unsupported attributes, you can easily achieve this goal with Improved Import & Export. The plugin offers a mapping section where you match the unsupported attributes to ones used in Magento 2. Thus, you teach the system what designations to use to replace the unsupported data and seamlessly run the import process, transferring information from Google Sheets or other external sources.

Post Tags: