Magento 2 Google Sheet Import

Did you ever know that it was possible to import data to Magento 2 right from Google Sheets? It is not a joke. However, you will never find the corresponding option in your Magento 2 administration. 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. 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 Sheet import to the platform. 

In the following article, we shed light on the basic features of the Improved Import & Export extension. After that, you will find a link to our Master Import Table for Magento 2, as well as its detailed description. With this information, we proceed to the use cases associated with the Magento 2 Google Sheet import. You will learn how to import data from a sheet 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. 

DOWNLOAD/BUY IMPROVED IMPORT EXPORT MAGENTO 2 EXTENSION

Improved Import and Export Extension Features

The new version of Improved Import and Export extension brings in a sockful of features:

• Full support of Google Sheets – paste links and import directly from the sheets;
• Scheduling import and export jobs with cron from administrator panel;
• Magento 2 event system for export jobs – set up the jobs to export data automatically 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;
• 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 root category and import any categories you want with category mapping.
• Magento 2 product EAV attribute import and export – import products, 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 SKU of parent product for a simple product and configurable 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 from your browser;
• Full Magento 2 compatibility, Open Source, Commerce and Commerce Cloud;
• Language and Locale settings per import/export job;
• ‘Only update’ import job behavior added;
• ‘Reindex after import’ behavior added to import jobs;
• ‘Generate unique URL if duplicate’ behavior added to import jobs;
• Dozens of import/export mapping and filters fixes and improvements;
• Models refactoring according to Magento Marketplace Extension Quality Program;
• Fixed product tax import;
• Category id on files instead of name.

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.

[embedded content]

Master Import Table for Magento 2 is a simple Google Sheet which 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 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 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 other. You only need a Google account to make import process work.

The table is an import draft at your disposal. It aims to provide the store owners with a single, 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 make sure a unique identity is preserved through the whole document. The products from the table are imported as ‘disabled’ to prevent sample data hitting live store and let the store owner study the import process.

All product attributes in the table come with the short description. The description points where to seek for 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 how it looks like and what can you do with it.

Master table view

The table represents a simple Google Sheet. 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;
• Make reference for the attribute in the top;
• Edit 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 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 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 import process.

Master table content

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

Use cases

The table itself holds ALL product attributes for ALL product types one may need during the import process. The table is in the endless growth and contains many other supported entities as well. It is useful for getting acquainted with the import process, and identifying your needs for the import jobs scheduling. However, on a daily basis you will be importing only particular attributes.

That’s where you need to remember the most important tip:

Only SKU attribute is mandatory. As it identifies the product entity. Meaning you can delete, other attributes, which are irrelevant for your current tasks from the table.

Using single Google Sheet with multiple tabs:

With Improved Import and Export extension you can store ALL your tables in a single Google Sheet using 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 change its URL depending on the tab (sheet) selected. So you don’t even need creating dedicated Google Sheet for every table. You can store multiple tables in a single Google Sheet.

Fully automate import:

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 he submits 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 and create Configurable Products on the fly

Improved Import and Export extension introduces a new, fast way of importing configurable products. The products can now be imported by any column in the import table file, either a new, dedicated column, or using part of the SKU column: by values before or after certain delimiter or number of symbols from the beginning or end of the line. For more information on how configurable products can be imported read Improved Import and Export manual.

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

As you can see, this new attribute has been placed in 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 new column, or use the existing one ‘group’, in which 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:
   1. Enable it;
   2. Set Create Configurables 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 file field select the column where you have specified 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 along with the products

2.1.1 version of Improved Import and Export extension introduced another interesting feature, import of the tier prices along with the products, in the same table file. You now only need to run a single import job to import both: product catalog 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.

As you can see, you set the Price here to 0.

For more information on the pattern and how you can use the new attribute.

Magento 1 to Magento 2 Migration

You can migrate the data from your previous Magento 1 store to Magento 2 by means of the Improved Import and Export extension and the migration add-on. 

As Magento 1 attributes do not fully correspond those of Magento 2, follow our templates from the Magento 1 > Magento 2 and Magento 1 Customers and Addresses import sheets to create your own tables.

• If you import data via the command line or backend interface of the migration add-on, just specify the path to the file with the data and our software will do the rest.
• In case you migrate via the standard Improved Import and Export interface, select Magento 1 as the source platform. Add the data table in any format supported by the extension via any connection source. The built-in presets will process your Magento 1 data without fails.

Import of Product Category Position

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

To import the products with particular category position:

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

MSI Import

The owners of Magento 2 Commerce and Cloud have already tried the extremely useful tool for warehouse management — MSI, or Multisource Inventory. The tool helps to keep track of numerous warehouses with all the related data. 

Taking into account the constantly changing nature of stocks, Improved Import and Export has been implemented with two separate logics for stock sources and stock quantity. To import the whole MSI, you need to import two sheets with the data corresponding to each type of logic.

1. Create an import job.
2. Select the ‘Stock Sources’ entity to import the detailed info about the warehouses. Or, choose ‘Stock Sources Qty’ to transfer the values of product stocks kept in your warehouses.
3. Define the import behavior.
4. Map attributes if needed.
5. Save and run the job.

Review Import

If you move your data from one store to another, all the data must be kept safe to re-create a site identical to the previous one. So, while moving products, you mustn’t forget about reviews — the entity that defines your products for your customers and influence their decision greatly. 

Reviews are bound to a product SKU, so you don’t need to worry some of them will be lost. Review ID, reviewer name, review status, its title and content, date of submitting and rating parameters are parts of the corresponding import table.

Configuration for review import is similar to the other entity import.

1. Add a job.
2. Choose ‘Reviews’ as an entity for transfer.
3. Specify import behavior and import source.
4. Save and run the job. 

What can you do with the table

The Master Table is the easy and educational. It can get you right through the product import process, with all columns explained. Just make sure to pay attention to all the sample data, clues left along it, and you will be fine.

How do we suggest to use it:

1. Copy it to your Google Drive – having it on our side is fine, however, with the table at hand you can introduce changes and learn how to use attributes.
2. Use it as a base for your future import jobs – copy the columns that are relevant to your current tasks and compose new tables, just remember to have ‘sku’ column first, as it is mandatory.
3. Take advantage of the simple data – learn how to compose complex attribute values, like tier prices, or or assigning simple products to configurables.
4. Adjust and improve – we believe that you can find more uses to the table, and expect you to share in comments whether you find anything lacking or if you want to add something. The community is what makes the Magento, so share your thoughts, would you?

As for now, we are leaving you with the table. Get a fine use to it!

DOWNLOAD/BUY IMPROVED IMPORT EXPORT MAGENTO 2 EXTENSION

Magento 2 Google Sheet Import FAQ