Creating base Product Import sheet from existing products and variants [Zapier]

Hi people

I am working on a zap to get active product/variants into a google sheet in the correct format to be used as a base for the product importer, enabling existing users with lots of products to start using this as their base product handling.

Given my technical limitations, my current plan is to create this zap, and then just turn it on for an hour or so at an agreed time, while they go and edit the variants that they want to get picked up. then the sheet becomes their ‘source of truth’ and doesn’t get updated again from OFN

[NB. If I can wrangle someone to write me a query that just fetches all the variants we need that we can trigger whenever we want that would obviously be better as then I could just run it] but for now working with this

So i’m almost there, but missing some critical fields - particularly PRICE and ideally on_hand and on_demand - can anyone help me find these?

This is what I have so far

I get the Spree_Variants table for edited variant

Then I use the Product_ID returned from that to lookup the Products table

Then I use the Supplier_ID to look up the Enterprises table

Then the Primary Taxon ID to get the Product Category

and I get the Shipping Category

and then perhaps tax - will come back to that

I am getting the option_value via the variant_ID and have two further lookups to get me 1kg. This isn’t quite right but I think I’m on the right track . .

So then I am setting up my ‘Create Spreadsheet Row’ which is all good and working (needs more fiddling with different variant types get everything in the right place)

Where can I find values for these critical fields? They are not in any of the above tables that I can see?

i.e. where it actually says g or kg, price, on_hand or on_demand

The most important missing data here is the price … I think we can work with everything else (as this user mostly uses on demand), but it would be really annoying to have to cross-check to get all the current prices.

Any advice on where I can get the price on a variant pretty please!! @maikel @luisramos0 @sauloperez @Matt-Yorkley

FYI @tschumilas @lin_d_hop @Rachel as people who might be interested in trying to do something like this

NB. This is a lot of tasks and will be quite zap expensive. Preferably only ever run it once for each user and likely need to charge them to do so

hmmm, outside of test zone this isn’t working - which makes me wonder if spree_variants is totally the wrong table, as in when I make a change to a variant it doesn’t seem to be picking up at all . . hmmm

This would be SOOOOOO amazing! As I understand it (correct if I don’t get this) - a user could/would decide to manage their products via spreadsheet using the import tool. I (super admin) could get them started by doing this zap to get them their source of truth spreadsheet to start. Then, as long as they don’t do any editing on-line in their product list, this will always be their source to make changes. People will love this. Frankly - I would probably learn to run it, and then I’d do it for users and just send them the spreadsheet (easier that way, I’m guessing - unless this is really easy to run.,) I’m just not confident in my ability to replicate it here - but maybe its something that can be transferred in some way? Big kisses on this one!!!

One question - what products would it pull? Would it pull all products the user has permission to access (so for a hub - it would pull all supplier’s products) Or would it only pull products that have had stock assigned? (Several bigger users here with 300 plus products, have ALL their season’s products entered at the beginning of the season, and then they just set stock to control what shows up in shops. They would want the full product lists offline to manage, not just currently active products with stock.)

I think all . . given my limited / non-existent tech skills I’m working with a pretty clumsy idea of how to trigger it!

Is it a producer or a shop enterprise? Do they use inventory?

By default, prices are in spree_prices but they can also be in variant_overrides. You can find a code representation of the database structure at https://github.com/openfoodfoundation/openfoodnetwork/blob/master/db/schema.rb.

I’m worried that this is an example where Zappiness is more expensive than building in OFN.

I am very confident that this is a case where zappiness is more expensive. But I have ability to work with zappiness!