Fast Magento 2 Product Import: Automate with Google Sheets
As a team member of the E-commerce division sometimes I find myself helping clients prepare, make sense and structure their product data so that it can be imported into their Magento 2 stores. As part of this process; importing raw product data csv files into Google Sheet is needed, and extensive data manipulation so that product data can be morphed into the Magento 2 Product Import Template because it is rarely the case in which a POS or inventory system will export its data as Magento 2 understands it.
The first time I performed a Magento 2 product import for one of our clients it took me 2 months to import 2,000 products using Google Sheets and crazy-long gibberish Google Sheet Formulas; so many Join and nested Ifs statements. This included creating meaningful category structures and product attribute sets that made sense to the domain of the business. However, I knew the time frame of completion was unrealistic and elongated and not feasible for business that have over thousands of products. One of the challenges with importing the data was that even though I was able to think logically on how I wanted to manipulate the data and the logical statements to do so I was faced by my lack of knowledge using Excel and Google Sheet formulas.
The second time around when I was asked to help import a product data set of 5,000 products I began to question and explore other tools that not only decreased the time it would take me to import data into Magento, but also create a template clients can be used that would automatically morph their raw data to Magento 2’s Product Import template.
“There should be another way I could achieve the same results in a shorter time frame”
That is when I came across with a neat feature that is part of Google Sheets, Google Scripting! Google Scripting allowed me to create my own custom Google Sheet formulas that allowed me manipulate data through coding.
Simple & Clean Sheets for your Product Import
Google Scripting was a perfect way to hide the complexity of the manipulation logic without compromising what it needed to do; no more oneline formula nightmares and dummy columns in your Sheets. You can access any part of a Google Sheets spreadsheet and manipulate data with no problem. Just one call to your custom formula and wait a few seconds and done!
Testing your Magento Product Import Data
Another perk about Google Scripting for Google Sheets was that I was able to test the custom formulas I was creating. Before Google Scripting, I had to trace through my complex Google Sheet formulas and reference cells until I realized what went wrong and the oneline input line was not helpful one bit. With Google Scripting there is a debugging feature that allows you to place breakpoints and step through your code. Giving you access to see what goes on through every step of your logic. One thing I like that to do is create test functions that I can utilize to make sure what I am writing is exactly what I intended.
The addition of Google Scripts in my Magento 2 Product Import Workflow helped create seemingly simple and clean Google Sheets and increased the productivity and efficiency by 120%. If you find yourself overwhelmed by the one-line formula nightmare, or have no knowledge about Google Sheet or Excel formulas, but have a little bit of coding knowledge, take a look at Google Scripts as an alternative for your Product Imports.
If anyone is interested on a tutorial on how to incorporate Google Scripting into your Google Sheets leave a comment below showing your support!
________________________ Endertech is a Los Angeles Web Developer company able to provide solutions for your Magento ecommerce web development needs. Contact us for a free consultation.