Magento 2 Knowledge Base Import Magento 2 Products from Google Sheets

Bulk updating data from the Magento Admin is cumbersome and using the built in import system is even worse. To solve this problem we created the DataFlow module, which allows flexible CSV importing through the Admin, via the CLI, in PHP or even via publicly accessible CSV files such as those built in Google Sheets and Google Docs.

Create your CSV File in Google Sheets

DataFlow is a flexible CSV importing system. This means that the CSV template can be anything you want, as long as you include either the 'sku' or 'entity_id' field. Any other data that you want to import you can just add as a column and DataFlow will automatically import it for you.

Get started by creating a new spreadsheet in Google Sheets and include either 'entity_id' or 'sku' as your first column header. You can then add all of the fields that you want to import.

Here's an example from the sheet we use to populate the FishPig.com products:

Our file is much bigger, but we have condensed it for the sake of the screenshot. You can add any attributes that you want to your CSV and DataFlow will know how to import them.

Make Spreadsheet Public

By default Google Sheets are private so only you and people in your organization can access them. To allow Magento 2 to import the file, you will need to make it public. This doesn't mean everyone can see your file, as it will have a random URL, but it does mean that anyone who visits the URL will see the data and this is how Magento 2 will access it.

To do this, select File > Share > Publish to web. When the modal window opens, select the specific sheet that you want to import and then change the export type from web page to Comma-separated values (.csv) and finally click Publish. This will generate a unique URL that you can use to access the CSV file. To test, open a private (incognito) browsing window and visit the URL and you should see the CSV file.

Now that your spreadsheet is setup and contains data, we can look at the 3 ways to import it into Magento 2.

Import from Google Sheets in Magento 2

There are 3 methods to import a Google Sheet into Magento 2 using the DataFlow import/export module:

Import a Google Sheet in Magento 2 from the Admin

This method is first as it is the easiest to test. To import your Google Sheet, take the URL generated when publishing the CSV file and go to Magento 2 Admin > DataFlow > Import > Products and then paste the URL into the URL field and click the Import button. Your file will now be downloaded and imported for you.

Import a Google Sheet in Magento 2 using the Command Line (CLI)

The next option is importing the Google Sheet into Magento 2 using the command line or terminal. This option is great because you can add the command to your CRONTAB to automatically run the import at a set time each day, if you wanted.

You will first need to SSH into your website and go to the Magento root directory. This article assumes you already know how to do this. Once there, you can run the following command, ensuring you enter your own generated URL from Google Sheets:

bin/magento fishpig:flow:import --builder=catalog_product --file=https://docs.google.com/spreadsheets/d/e/ArfdPL-PiIqfNqlNny3eF/pub?gid=1234567890&single=true&output=csv

Just make sure you change the Google Sheets URL for your own URL as the one used in the example above won't work for you.

If you want to set this up on your CRONTAB, you may need to use an absolute path to bin/magento.

Import a Google Sheet in Magento 2 using PHP code

Finally let's look at how to write a PHP script that can download the URL from Google Sheets and import it into Magento 2. This method is more advanced as it requires you to write some PHP code, but it is more powerful as you can use PHP to modify the spreadsheet in some way.

We actually have 2 options here. The first option is to just give DataFlow the URL and let it download the file for us and import it. The second option is to download the file ourselves, manipulate it and then import it.

Import using a Builder

A builder will build the import service for us, based on the inputs we give it. In this case, we will give it a URL and it will download the URL, read it as a CSV and then import that data for us.

/* \FishPig\Flow\Builder\Import\Catalog\ProductBuilderFactory */
$result = $productImportBuilder->create()->import(
    $googleSheetsGeneratedUrl
);

That's not much code that's needed as DataFlow does all of the hard work for us. The above code will download the file from Google Sheets, parse the data and import it. You can inspect $result to see how many records were imported/updated and to see if any errors occured.

Import using a Service

To import using a service, we have to download the URL ourselves, read in the data as a CSV and then import the array. This is useful if you want to manipulate the data before importing.

/* \FishPig\Flow\Service\Import\Catalog\ProductImportFactory */
$data = $productImportBuilder->create()->import(
    \FishPig\Flow\Service\AbstractService::STORE_MODE_STRICT,
    0
)->import(
    $csvData
);

The above does not show how to download the CSV from Google, as this is something you would need to implement on your own using CURL or another download method, but once you have the CSV in $csvData, it will import it for you.

Conclusion

Importing data from public URLs such as Google Sheets or Microsoft Office online is easy with the DataFlow Import/Export module for Magento 2. This article explains how to do it for products, but you can do it for any data really. We use the DataFlow extension to import category and product data into this Magento 2 website and it makes things much easier. We have a command we run via the CLI and this synchronises product data across all of our environments (live, staging, dev etc), which is super useful when debugging issues in dev.

DataFlow is also great because when it updates a record, it triggers a cache flush for the approrpiate URLs. This means that when a poduct is updated via this import, it is flushed from the cache. If the data for a product stays the same, DataFlow doesn't trigger this and the product stays in the cache.