TLDR, I want to make our Shopify stock levels completely automated using supplier emailed CSV files.
Basically, we are trying to take something like a CSV file that is sent from our suppliers and will usually contain 3 columns such as stock code, current qty and dates of when any out-of-stock items are coming back into stock
Then we are going to manipulate this data because this stock will make up more complicated stock, i.e. one part/product on one line in this csv might mean the same part/product used in a product b, product c, product d, product e, etc.
The way we do it now would be Excel with Vlookup formula to populate tabs with different offshoot products like this.
Matrixify integrates with Shopify and it has an option to batch import via a monitored FTP/SFTP folder and import any files automatically.
Power Automate feeds CSV into Excel table possibly by saving to Sharepoint first
Table vlookups into necessary tabs within Excel
The Excel file then needs to be a single tab worksheet so perhaps Power Automate creates a new worksheet
Then the worksheet is saved onto the SFTP server and Matrixify is pointed towards this on a schedule
There would need to be some sort of control and errors will crop up if codes aren’t spot on, etc. but I am essentially trying to remove the human element from a fairly rote process (sorry humans).
Has anyone done anything similar to this. Perhaps there is a much easier approach? Any advice is appreciated.
Note there is currently no api for shopifys native transfer system. So any frontend displays that need incoming dates coming from a programmatic source need to basically be put into variant metafields, location metafilds, etc.
If matrixy is already in usage that’s pretty much the high points of doing such a a thing.
Especially if you are also trying to keep as much logic as possible in the backoffice desktop/server software such as excel.
For ETL apps like matrixify, ez-exporter, etc when dealing with the input|import and if trying to remove sftp from the process always double check if there’s a direct email service provided in the feature set, or sending to cloud services to google sheets,etc
There are also scriptable automation tools like mechanic where there is a direct email address for a store to send data to and then do more api-like actions and logic or setup up some sort of monitoring.
If you need end to end process mgmt validating data and results and dealing with issues that may indicate a custom app is needed and skip most of the bullet points altogether.
This indeed is an interesting task to try to achieve.
The SFTP option indeed sounds interesting though still includes quite a lot of steps.
Another approach that you might look into is using Google Sheets. So could try to look into what integrations you could create from your email to get this data into the Google Sheets spreadsheet. Ideally, if your supplier can offer a static link to the file instead of sending over the email, then you could use that CSV link in Google Sheets IMPORTDATA formula. Then have another sheet/tab in the same file where with formulas you convert that data to Matrixify column headers. From there you can set up automated import from Google Sheets to your store with Matrixify.
The Excel file then needs to be a single tab worksheet so perhaps Power Automate creates a new worksheet
Excel file can have multiple sheets/tabs in the file for import into Matrixify. Just make sure that the sheet you wish to import is named “Products”. In case of multiple sheets/tabs app will ignore sheets with names that do not match the entity being imported. For example, if you have two sheets “Products” and “Discounts” then app will import both entities with the same job, but if you have “Products” and “Data” then app will import Products and ignore the “Data” sheet.
Hope this helps and might be another solution to look into!
Thanks for this. I am going to look at this and PaulNewton’s option as well. I will mark his as Solution first but I’d like to mark them both as Solution really. I’m not sure if I can.