Export and Import “Buy X Get Y” Discount Codes

Shopify "Buy X Get Y" Discount Code

Shopify has a relatively new discount code type “Buy X Get Y“.

You can now export and import that discount code using the Excelify app.

The best way for you to understand which columns need which values are if you enter one such Discount code manually in the Shopify Admin, and then do the export using the Excelify app.

Then you will see what data goes into which columns.

See all the documentation about all the Discounts sheet columns.

Auto-generate Order Name

Auto-Generate Shopify Order Name

When importing new Orders – if you need to generate the Order Name from Shopify, just leave that Name column blank (or don’t have it in the import file at all).

That will make the Excelify App generate your Order Name automatically incremented by Shopify.

To set the boundary where the next Order begins across many rows, use the Number column. For the same Number, the rows will be assigned to the same Order, and the next Order will start when the Number value changes.

Read more in the Orders sheet documentation.

Import from SFTP to Shopify

SFTP to Shopify

This is now to Import data from an SFTP server to Shopify

You can import to Shopify from SFTP by pointing the address exactly to the file which you need to import.

To schedule the import automatically at regular time intervals, do the following.

In the Excelify app, in the Import section:

  1. Paste the link to your SFTP server file path which should be in the following format:
sftp://user:[email protected]:port/path/to/directory/file-name.xlsx

For example:

sftp://foo:[email protected]:22/test/my-file.xlsx

2. Press “Upload from URL” buttonImport to Shopify from SFTP - upload file

3. Wait for the app to download the file for the first time, and let it analyze it (or fix any issues if it says that the URL was not correct).

4. If you want to set up the scheduled or automatically repeated import, press the “Options” button and set it up there.

5. Press the “Import” button (or “Schedule Import” button if you are scheduling import to start at a certain time).

Schedule automated import from SFTP to ShopifyEach time when the next automatic import will run, it will download the fresh file from SFTP again and again.

Good to know

Export from Shopify to SFTP / Import to Shopify from SFTP

SFTP

You can now export data from Shopify to SFTP file server, and import from SFTP to Shopify.

The SFTP address is written in the following format:

sftp://user:[email protected]:port/path/to/directory/file-name.xlsx

See the related tutorials:

 

Refund existing Shopify Orders in bulk

Refund Orders in Bulk

If you have to issue refunds for your existing Shopify Orders, and you have a lot of them, then here’s how you can do it with the Excelify app in bulk.

Summary of steps to bulk refund your Shopify Orders:

1. Export existing Orders with the Excelify app

Include the following details for those Orders only:

  • Orders
    • Line Items
    • Refunds

You can also apply the filter to target more specific Orders.

Here’s how your export checkboxes should look – because you need only this data to make your exports.

Shopify Orders bulk refund export

2. Make the import file from the exported Orders file

Take the exported file and delete any other columns, keeping just those:

  • ID
  • Name
  • Command
  • Line: …
  • Refund: …

Change the following columns:

  • Command: set values “UPDATE” for all lines, so that the app knows you want to update existing orders.
  • Line: Type:
    • Change the “Line Item” rows to “Refund Line” for those lines which you want to refund. You can change the quantity and total amounts for them if you want to refund a smaller amount than paid.
    • Change the “Shipping Line” rows to “Refund Shipping” if you want to refund also the shipping cost.
  • Refund: ID: set any kind of number there, for example, “1” for all the rows – just so that you identify that this is one and the same refund. You can create several refunds by changing the “Refund: ID” number, and group several line items into several refunds if you like.
  • Refund: Note: write any notes you want to add about this refund.
  • Refund: Restock: put the TRUE there if you want to restock the refunded products, and FALSE if you don’t want to restock.
  • Refund: Send Receipt: put the TRUE if you want the customer to receive an e-mail notification about that refund. FALSE – if you don’t.

The final table you import will look something like this:

Bulk refund Shopify Orders with import - table sample3. Import the file using the Excelify app

It should show that X count of Orders got updated.

Shopify Orders updated - refunded

When looking in Admin, your refunded Order will look like this:

Refunded Shopify Order - view in Shopify Admin

If you marked to send the notification receipt of the refund, the customers will get an e-mail like this:

Shopify Order refund notificaiton email receiptWarning!

Always test with just one Order to make sure you have it all set up correctly.

And only when you have tested, import all the bulk changes.

Where to go from here?

Cancel Shopify Orders in Bulk

Cancel Shopify Orders in bulk

To Cancel existing Shopify Orders, do the following steps:

Note that this will not replace the orders – this will truly update the existing ones.

  1. Export the Orders you want to cancel – don’t include any other detailed columns unless you absolutely need them, so you have nice one row per order in the export.
  2. Delete all the columns and rows, leave only the following columns in your Excel file:
    • ID
    • Name
    • Command
    • Cancelled At
    • Cancel Reason
    • Send Receipt
  3. Update the Excel file columns with data:
    1. Set the “Command” column values to “UPDATE“.
    2. Set the “Cancelled At” column values to whatever date (it doesn’t matter what date it is because orders will anyway get cancelled with today’s date).
    3. Set the “Cancel Reason” column values to one of the following: customer, inventory, fraud, declined, other. You can leave this empty, then the app will assume other by default.
    4. Set the “Send Receipt” column to TRUE if you want the customer to receive the e-mail notification about the Order cancelation.
  4. Import the file with the orders you want to cancel.

Good to know

Repeat import from your supplier data feed using Google Sheets

Repeat import from supplier data feed to Shopify

Here you will see an easy trick how you can import to Shopify from any of your supplier files or data feeds.

Set it up once, and then repeat it easily manually or even automatically at any time intervals.

Short summary:

  1. Set up the new Google Sheets document.
  2. Make two sheets there: “Data” and “Products”
  3. Import your original file from your supplier to the Data sheet.
  4. Make the formulas in the Products sheet to take the data from Data sheet.
  5. Import

Now, each time you get the new file from your supplier, you can just re-import it in the Data sheet and your Products sheet will update.

Also, if your supplier gives you the link to download your file, you can use the Google Sheets formula =IMPORTDATA(…) to import that file automatically from the supplier link.

If your mapping from your supplier file is straightforward, you can use the =QUERY(…) function to grab the data from Data sheet, just assign the names to the column using the LABEL clause in the QUERY function.

Good to know

  • You can achieve also the same effect with Excel file – use the =WEBSERVICE function (works only on Windows).
  • To do it semi-manually, you can just copy-paste the data from your supplier into your “Data” sheet – and the rest will work the same.

How to adjust the Inventory Quantity for Shopify Products

Adjust Shopify Inventory Quantity

You can now import the quantity adjustment of the Shopify Product inventory by importing the delta (difference) number.
So, in case you received a new shipment to your warehouse of additional x items for each product, you can just import that x number as an adjustment for your products and variants.

There is the new column in the Products sheet for that “Variant Inventory Adjust“.

If you are importing the “Variant Inventory Adjust” values then, of course, you cannot import the “Variant Inventory Qty” number – as that wouldn’t make sense to set and adjust the quantity at the same time.

For example, if you need to adjust the inventory quantity for some products, you can import the columns:

  • ID
  • Handle
  • Variant ID (or Variant SKU, or Variant Barcode, or Options columns)
  • Variant Inventory Adjust

or, if you want to just update by SKU, you can import the following columns:

  • Variant SKU
  • Variant Inventory Adjust

And you can combine the columns – fill the values for some variants in “Variant Inventory Qty” and for some the “Variant Inventory Adjust”.

Here’s one way the import file can look:

Shopify Adjust Variant Inventory Quantity import

Read more about the columns in the Products Columns Documentation.

Set Order Fulfillment Shipment Status

Shopify Order Fulfillment Shipment Status

Earlier you couldn’t set the specific “Fulfillment: Shipment Status” column for Orders – you could just export it.

Now you can create and update Order Fulfillments so that you can set a specific shipment status for your Orders in the import table, too.

The allowed values for this column are:

  • confirmed
  • in_transit
  • out_for_delivery
  • delivered
  • failure

Read the full Orders columns documentation.

Multi-Location Inventory

Shopify Multi-Locations

In preparation for Shopify Multi-Location Inventory, the Excelify app has implemented all the required changes to support that for all the types of items.

Shopify Admin Settings

When your store has Multi-Location Inventory enabled, your Shopify Admin will have the new section in Settings, called “Locations“. It will look like this:

Shopify Admin - Settings LocationsProducts

From that point, you will see for your variants the following table of the available quantities by each location which is attached to that variant:

Shopify Admin - Product Variant Locations

In the Excelify app you will see an additional checkbox for Product details export “Multi-Location Inventory Levels“:

Export Shopify Multi-Location Inventory Levels columnsChecking this checkbox in your export file will include additional columns “Inventory Available: …” – for each of your locations.

Since each variant can have the quantity available in each location, you will see the quantity column for each location.

Those columns will look like this:

Exported Shopify Multi-Location Inventory Levels

 

Notice that each of those columns have the location name in it (like Office) – that is important because this is how you will tell the app, which quantity you want to change for which location.

Also, there are additional columns “Inventory Available Adjust: …“. In the export, the values for those columns will always be 0. But when importing, you can import these columns to adjust the inventory for each location. To import adjustments, you need to delete the columns “Inventory Available: …” – because those set your inventory to a specific value. While the adjustment columns adjust your inventory quantity – adds or subtracts the number that you specify. To subtract the inventory, import a negative value.

Be aware that exporting those columns will make your export run longer, as the Excelify app has to ask Shopify the inventory levels for each of your products separately.

The “Variant Inventory Qty” column will be still there and will show the total of your inventory across all your locations. Also, if you import this column and no other “Inventory Available: …” columns are in your import file, then the app will assume that you want to change the quantity of your first location.

Importing Inventory Quantity

If your store has just one location, you can continue importing the “Variant Inventory Qty” column, or the “Inventory Available: …” column, or the “Inventory Available Adjust: …” column.

If you import any number different from 0 in the “Inventory Available Adjust: …” column, then you need to remove the “Inventory Available: …” column. Otherwise, it doesn’t make sense to set and adjust the quantity at the same time, right?

If you have many locations but all of those don’t show up in the export, that means that there are no variants which are attached to those locations. You can attach those variants to those locations in bulk, by just adding that respective column of “Inventory Available: …” and put there your location name. You should make sure that you have such a location defined in your Shopify Admin: Settings -> Locations section.

You can read about all the Products columns in the Products documentation.

Orders

Fulfillments

When you fulfill your Orders, you will see in the Admin new field for the Fulfillment where you can change the Location of that fulfillment.

Shopify Admin - Orders Fulfillment LocationWhen you export your Orders using the Excelify app, you will see this location name in the column “Fulfillment: Location“.

Shopify Orders Excel Export - Fulfillment Location

The same with the import – if you need your Order to be fulfilled from the specific location, you need to provide that location name.

If you don’t provide any name, the app will automatically assume that you are fulfilling from the first of your locations.

Refunds

When you issue the Order Refund with restocking, in the Admin you are offered to choose the location to which to restock:

Shopify Admin - Orders Refund with RestockThat same value appears in the Excelify app Orders Export for Refunds, in the column “Refund: Restock Location“:

Export Shopify Orders Refund Restock LocationAlso, when importing Orders, you can specify the Location name in that column. If you will import it empty, the app will always assume the first of your Locations.

You can read about all the Products columns in the Orders documentation.