Magento 2 Google Sheets Integration Guide

Have you ever known that importing data to Magento 2 from Google Sheets is possible? It is not a joke, and we will explore the Magento Google Sheets integration below.

Although you will never find the corresponding option in your Magento 2 administration, a robust third-party Magento 2 importer can do the job. The default import and export tools of your e-commerce website are scarce. You can perform only several basic data transfers with them. Therefore, it is necessary to leverage a third-party extension that replaces the default import and export functionality. So meet the Improved Import & Export module for Magento 2. This tool dramatically enhances the basic features associated with moving tables to and from your website. And, of course, it delivers the Magento 2 Google Sheets integration to the platform. 

In the following article, we shed light on the basic features of the Improved Import & Export extension that can help you with the Magento 2 Google Sheets integration. After that, you will find a link to our Master Import Table for the Magento 2 Google Sheets import along with detailed descriptions.

With this information, we proceed to the use cases associated with the Magento 2 Google Sheets import. You will learn to import data from a spreadsheet with multiple tabs, automate the corresponding transfers, move configurable products to your store, work with tier prices, save category positions, copy the existing reviews to your new website, etc. 

GET IMPROVED IMPORT EXPORT MAGENTO 2 EXTENSION

Improved Import and Export Features for Magento 2 Google Sheets Integration

In addition to the full support for the Magento 2 Google Sheets integration – paste links and import directly from a spreadsheet – the Improved Import and Export extension brings in a sockful of features:

• Scheduling import and export jobs with cron from the administrator panel. Automate your Magento 2 Google Sheets import and export processes;
• Magento 2 event system for export jobs: set up the jobs to export data automatically to a Google Sheets spreadsheet upon three Magento 2 events: order placed or saved and product saved;
• Support of CSV, XML, Json, Exel XLSX, and OpenOffice ODS tables;
• Tier price import with products: imports both products and tier prices from a single file or a Google Sheets spreadsheet to Magento 2;
• Magento 2 Order import and export: migrate from other ecommerce platforms or integrate with your CRM and ERP systems;
• Magento 2 Category import and export: select the root category and import any categories you want with category mapping;
• Magento 2 product EAV attribute import and export: import products, and create attribute sets and groups automatically;
• REST and SOAP API support: you can now connect your Magento 2 to any CRM and ERP systems;
• Configurable products can be created on the fly: specify the SKU of the parent product for a simple product and a configurable one will be created automatically;
• Price adjustments for imported products: you can increase and decrease product prices by flat or percent and round them to .49 or .99 automatically;
• Asynchronous import and export process for data-heavy tables: now you can easily import and export data-heavy tables between Magento 2 and Google Sheets ;
• Full Magento 2 and Adobe compatibility: Open Source, Commerce, and Cloud;
• Generate unique URLs if there are duplicates in a Google Sheets spreadsheet;
• Import/export mapping and filters for a better Magento Google Sheets integration.

Magento 2 Google Sheets Import

With the Improved Import & Export, Magento 2 Google Sheets import looks as follows:

1. First of all, create a new import profile under System -> Improved Import / Export -> Import Jobs > Add New Job;
   
   
2. Next, create a schedule of updates if you plan to automate your Magento 2 Google Sheets import;
   
   
3. Specify an entity you want to import: products, customers, orders, etc.;
   
   
4. Configure an import behavior and specify separators if your spreadsheet contains unusual separators;
   
5. Now, you need to select a file type and source. It is only necessary to paste a file URL in a corresponding field.
   
   When composing a Google Sheet table you can use as many tabs as you want. Follow these steps to copy the Google Sheets tab URL:- Click on the tab you want to import;
   – Google Sheets will change the URL of the table to include the tab;
   – Copy the URL of the tab while you at its page;
   – Paste the URL to the import job.
6. Press the Validate file button.
7. Map third-party attributes to ones used on your e-commerce website. You prepare your custom Google Sheets spreadsheet to Magento 2 requirements at this step. No matter what column titles your import table has. You can replace them with the required Magento 2 attributes during the import.
   
   Note that you can also edit attribute values at this stage.
8. Now, save the job, and run the Magento 2 import from Google Sheets by clicking the ‘Save & Run’ button.

Magento 2 Google Sheets Export

Since the Magento 2 export is a little bit more complicated, we’ve prepared a separate guide on how to configure this part of the Magento Google Sheets integration: How to export data from Magento 2 to Google Sheets.

Magento Google Sheets Integration with Master Import Table

To top it all with a cherry, and provide the store owners with a simple import solution, Firebear Studio introduces Master Google Sheet Import Table for Magento 2.

Master Import Table for Magento 2 is a simple Google Sheet that you can edit and share the way you want:

• Copy the table to your Google Drive, or download it as a CSV or any convenient format;
• Edit the table using Excel, OpenOffice, or Google Sheets functionality;
• Share the Master table with your suppliers, colleagues, and other responsible persons. Allow them to make edits to the table;
• Configure the Import Job, to automatically upload the data from the table for each appropriate period.

What makes the Master Table so important

It is a new level of importing products to Magento 2, along with the support of Google Sheets introduced in the Improved Import and Export extension.

What it means is:

1. This is a Google Sheet – the most convenient tool for editing CSV files;
2. You can work on a single import table with your colleagues at the same time;
3. A single-step import.
4. Schedule the import job to upload table contents to your store automatically every set period, with the help of cron.
5. You can add and remove columns and create new sheets, relevant to your current tasks.
6. It is a one-stop solution. Forget about Excel, OpenOffice, and others. You only need a Google account to make the import process work.

The table is an import draft at your disposal. It aims to provide store owners with a comprehensive sample data file.

Sample products in the table introduce the store owner to the peculiarities of the Magento 2 product types. Moreover, every product type is properly color-coded, to provide a unique identity throughout the entire document. The products from the table are imported as ‘disabled’ to prevent sample data from hitting the live store and let the store owner study the import process.

All product attributes in the table come with a short description. The description points to where to seek the attribute values and what value, numeral or text, is expected in the relevant cell. Almost every product attribute in the table comes with a dummy value. Such values have been added to provide the store owner with a live example of how the attribute’s value should look like.

In addition, some cells come with data validation, where the store owner can choose from the predefined values. Such values cover ALL available options for the Magento 2, no other values can be entered for these attributes.

As you can see, we have tried to cover everything the store owner may need to get acquainted with the Improved Import and Export extension. And make sure, no browser tabs are switched when trying to understand what attribute does what.

Let’s break down the table and see what it looks like and what you can do with it.

Master table view

The table represents a set of Google Sheets spreadsheets. In each spreadsheet, the top row lists all available Magento 2 product attributes, while each consecutive row is a separate product these attributes belong to. So editing product attributes is pretty simple:

• Find the product row;
• Refer to the attribute at the top;
• Edit the corresponding cell.

Some of the attributes come with the data validation in the cell. That’s when you can select one of the values available.

For example, at the gif below you can see how the product type is selected in the product_type column.

If you are not sure what a single attribute does, you can always hover the mouse over the attribute name to get a clue in the short description.

In addition, such short descriptions may contain instructions on what value you are expected to submit to the column. Pay close attention to it, as incorrect values, for example: text value, instead of numerals; can lead to errors during the import process.

Master table content

Master Table is your guide to Magento 2 import. Follow the tips and patterns from each entity sheet, and master the specifics of all Magento 2 data.

Magento 2 Google Sheets Integration Use Cases

The table holds ALL product attributes for ALL product types one may need during the Magento 2 Google Sheets import. You will also find other entities there. It is useful for getting acquainted with the import process, and identifying your needs for the import jobs scheduling. However, you will be importing only particular attributes daily. That’s where you need to remember the most important tip:

Only the SKU attribute is mandatory. As it identifies the product entity. This means you can delete, other attributes, that are irrelevant to your current tasks from the table.

Using a single Google Sheets spreadsheet with multiple tabs

The Improved Import and Export extension lets you store ALL tables in a single Google Sheets file with multiple tabs.

To import a particular tab you need to select it, copy the URL, and paste it to the import job. Yes, Google Sheets changes its URL depending on the tab (sheet) selected. So you don’t even need to create a dedicated Google Sheet for every table. You can store multiple tables in a single Google Sheet file.

Fully automate Magento 2 Google Sheets import

Let’s suppose, your supplier ships you several products every Monday. These products have slight price changes and, of course, quantity changes.

To track the product updates, you share a single Google Sheet with your supplier, where they submit all the changes.

With the help of Improved Import and Export extension, the process can be fully automated:

1. Create an Import Job;
2. Schedule the job to run automatically every Monday;
3. Paste the link to the Google Sheet;
4. Save the job.

That’s it. It took you two minutes to completely automate the update process.

Import data from Google Sheets to Magento 2 to create Configurable Products on the fly

Improved Import and Export extension introduces a fast way of importing configurable products. Products can now be imported by any column in an import table: a dedicated column or a part of the SKU column by values before or after a certain delimiter or number of symbols from the beginning or end of the line. You can find more information on how configurable products can be imported in the Improved Import and Export manual.

What does it mean in terms of the Master Table? A corresponding table comes with an additional column, where you can specify the Configurable Product SKU, to which simple products should be assigned.

As you can see, this new attribute has been placed at the beginning of the table, next to the mandatory sku column. This has been done to draw your attention to the new functionality.

In the ‘group’ column we put the SKU of the configurable product, to which the simple products should be assigned.

The process is simple:

1. Add the simple products to the Master Table and fill in all required attributes;
2. Add a new column or use the existing one ‘group’ to specify the SKU of the parent configurable product that should take care of your simple products;
3. Navigate to the Import Job you will be using to import the products, scroll to the Custom logic for creation of configurable products section, and follow these steps:
   1. Enable the feature;
   2. Set Create Configurable Products to ‘Yes’;
   3. In the Configurable Product Condition field select ‘Create config products by same attribute of simple’
   4. In the Attribute/column name on the file field select the column where you have specified the SKU of the parent product in Step 2.
4. Run the job.

That’s it, you have imported the simple products and assigned them to the new configurable product. Optionally you can specify with which product attributes the simple products should be tied with to the configurable in the Product attributes for variations table a scroll above.

Import Tier Prices to Magento 2 from Google Sheets along with products

2.1.1 version of the Improved Import and Export extension introduced another interesting feature – the import of tier prices with products. Now, you only need to run a single import job to import both products and tier prices. Let’s see how it is done.

As you can see, the process is somewhat similar to the import of the configurable products. You have a new attribute – tier_prices (do not mistake it for tier_pricE) –  available only for the extension owners, where you specify the tier prices on a certain pattern.

For example, the value:

General,360,0,3,All|Retailer,540,0,5,All

Will get you:

3% discount for group General when purchasing 360 items at any Website. And 5% discount for group Retailer when purchasing 540 items at any Website.

For more information, follow this guide: Magento 2 Advanced Pricing Import.

Magento 2 Import of Product Category Positions from Google Sheets

You can add a new column to your Google Sheets table to determine a specific place for a product within a category. The column doubles the ‘Categories’ attribute with a single exception: the line must end with a numeral indicating the position where the product will be displayed on a category page.

Follow these steps to import the products with particular category positions:

1. Create an import job.
2. Select ‘Products’ as an entity.
3. Configure the job.
4. Save and run the job.

Magento Google Sheets Integration FAQ

Final Words

Although Magento 2 doesn’t provide any built-in opportunity for the Google Sheets import and export, you can easily get the missing functionality with the Improved Import & Export extension. This module lets you establish a fully-featured Magento Google Sheets integration. Our Master Table, in turn, represents the easiest way to master the integration. It can get you right through the product import process, with all columns explained. Just make sure you pay attention to all the sample data and clues left there. Follow this link and contact our support to get more information on the Magento 2 Google Sheets integration:

GET IMPROVED IMPORT EXPORT MAGENTO 2 EXTENSION