script.liquid

{% assign only_export_products_matching_this_query = options.only_export_products_matching_this_query %}
{% assign run_every_x_hours = options.run_every_x_hours__number %}
{% assign sftp_host = options.sftp_host__required %}
{% assign sftp_port = options.sftp_port__required_number %}
{% assign sftp_user = options.sftp_user__required %}
{% assign sftp_password = options.sftp_password__required %}
{% assign sftp_upload_directory = options.sftp_upload_directory %}

{% comment %}
-- validate options
{% endcomment %}

{% if run_every_x_hours != blank %}
  {% assign valid_hours = array %}
  {% assign valid_hours[valid_hours.size] = 1 %}
  {% assign valid_hours[valid_hours.size] = 2 %}
  {% assign valid_hours[valid_hours.size] = 3 %}
  {% assign valid_hours[valid_hours.size] = 4 %}
  {% assign valid_hours[valid_hours.size] = 6 %}
  {% assign valid_hours[valid_hours.size] = 12 %}
  {% assign valid_hours[valid_hours.size] = 24 %}

  {% unless valid_hours contains run_every_x_hours %}
    {% error "If set, 'Run interval in hours' must be 1, 2, 3, 4, 6, 12, or 24." %}
  {% endunless %}
{% endif %}

{% assign ok_to_run = false %}

{% if event.topic == "mechanic/user/trigger" or event.topic == "mechanic/scheduler/daily" %}
  {% assign ok_to_run = true %}

{% elsif event.topic == "mechanic/scheduler/hourly" and run_every_x_hours != blank %}
  {% assign hour_mod = "now" | date: "%H" | modulo: run_every_x_hours %}

  {% if event.preview or hour_mod == 0 %}
    {% assign ok_to_run = true %}

  {% else %}
    {% log
      message: "The current hour does not fall on the configured interval; skipping",
      hour_interval: run_every_x_hours,
      current_hour: hour_mod
    %}
  {% endif %}
{% endif %}

{% if ok_to_run %}
  {% capture bulk_operation_query %}
    query {
      products(
        reverse: true
        query: {{ only_export_products_matching_this_query | json }}
      ) {
        edges {
          node {
            id
            __typename
            descriptionHtml
            handle
            isGiftCard
            onlineStoreUrl
            options {
              id
              __typename
              name
              position
              values
            }
            productType
            seo {
              description
              title
            }
            tags
            title
            variants {
              edges {
                node {
                  id
                  __typename
                  barcode
                  compareAtPrice
                  image {
                    id
                    __typename
                    altText
                    url
                  }
                  inventoryItem {
                    id
                    __typename
                    measurement {
                      weight {
                        unit
                        value
                      }
                    }
                    requiresShipping
                    tracked
                    unitCost {
                      amount
                    }
                  }
                  inventoryPolicy
                  position
                  price
                  product {
                    handle
                  }
                  selectedOptions {
                    name
                    value
                  }
                  sku
                  taxable
                  taxCode
                  title

                }
              }
            }
            vendor
            images {
              edges {
                node {
                  id
                  __typename
                  altText
                  url
                }
              }
            }
          }
        }
      }
    }
  {% endcapture %}

  {% action "shopify" %}
    mutation {
      bulkOperationRunQuery(
        query: {{ bulk_operation_query | json }}
      ) {
        bulkOperation {
          id
          status
        }
        userErrors {
          field
          message
        }
      }
    }
  {% endaction %}

{% elsif event.topic == "mechanic/shopify/bulk_operation" %}
  {% if event.preview %}
    {% capture jsonl_string %}
      {"id":"gid:\/\/shopify\/Product\/1234567890","__typename":"Product","descriptionHtml":"Preview product","handle":"preview-product","isGiftCard":false,"onlineStoreUrl":null,"options":[{"id":"gid:\/\/shopify\/ProductOption\/1234567890","__typename":"ProductOption","name":"Title","position":1,"values":["Default Title"]}],"productType":"Indoor","seo":{"description":null,"title":null},"tags":["preview"],"title":"Preview product","vendor":"ACME"}
      {"id":"gid:\/\/shopify\/ProductImage\/37002970530040","__typename":"Image","altText":"Preview product","url":"https:\/\/cdn.shopify.com\/s\/files\/1\/1337\/5150\/products\/preview-productr.jpg?v=1234567890","__parentId":"gid:\/\/shopify\/Product\/1234567890"}
      {"id":"gid:\/\/shopify\/ProductVariant\/1234567890","__typename":"ProductVariant","barcode":null,"compareAtPrice":"30.00","image":{"id":"gid:\/\/shopify\/ProductImage\/1234567890","__typename":"Image","altText":"Preview product","url":"https:\/\/cdn.shopify.com\/s\/files\/1\/1337\/5150\/products\/preview-product.jpg?v=1234567890"},"inventoryItem":{"id":"gid:\/\/shopify\/InventoryItem\/1234567890","__typename":"InventoryItem","measurement":{"weight":{"unit":"KILOGRAMS","value":0.0}},"requiresShipping":true,"tracked":true,"unitCost":null},"inventoryPolicy":"DENY","position":1,"price":"15.99","product":{"handle":"preview-product"},"selectedOptions":[{"name":"Title","value":"Default Title"}],"sku":"PREVIEW-123","taxable":true,"taxCode":null,"title":"Default Title","__parentId":"gid:\/\/shopify\/Product\/1234567890"}
    {% endcapture %}

    {% assign bulkOperation = hash %}
    {% assign bulkOperation["objects"] = jsonl_string | parse_jsonl %}
  {% endif %}

  {% comment %}
  -- csv required fields, in this order
  {% endcomment %}

  {% assign columns = "Handle,Title,Body (HTML),Vendor,Type,Tags,Published,Option1 Name,Option1 Value,Option2 Name,Option2 Value,Option3 Name,Option3 Value,Variant SKU,Variant Grams,Variant Inventory Tracker,Variant Inventory Policy,Variant Fulfillment Service,Variant Price,Variant Compare At Price,Variant Requires Shipping,Variant Taxable,Variant Barcode,Image Src,Image Position,Image Alt Text,Gift Card,SEO Title,SEO Description,Google Shopping / Google Product Category,Google Shopping / Gender,Google Shopping / Age Group,Google Shopping / MPN,Google Shopping / AdWords Grouping,Google Shopping / AdWords Labels,Google Shopping / Condition,Google Shopping / Custom Product,Google Shopping / Custom Label 0,Google Shopping / Custom Label 1,Google Shopping / Custom Label 2,Google Shopping / Custom Label 3,Google Shopping / Custom Label 4,Variant Image,Variant Weight Unit,Variant Tax Code,Cost per item" | split: "," %}

  {% comment %}
  -- setup 2d array required by csv filter, and add the columns as a header row
  {% endcomment %}

  {% assign rows = array %}
  {% assign rows[0] = columns %}

  {% comment %}
  -- loop through the lines (JSONL) returned by the bulk operation
  {% endcomment %}

  {% assign products_by_id = hash %}

  {% for object in bulkOperation.objects %}
    {% case object.__typename %}
      {% when "Product" %}
        {% comment %}-- clone the object to allow modification --{% endcomment %}
        {% assign product = object | json | parse_json %}
        {% assign product["variants"] = array %}
        {% assign product["images"] = array %}
        {% assign products_by_id[product.id] = product %}

      {% when "ProductVariant" %}
        {% assign variant = object %}
        {% assign product_id = variant.__parentId %}
        {% assign product = products_by_id[product_id] %}
        {% assign product["variants"][product.variants.size] = variant %}

      {% when "Image" %}
        {% assign image = object %}
        {% assign product_id = image.__parentId %}
        {% assign product = products_by_id[product_id] %}
        {% assign images = product.images %}
        {% assign product["images"][images.size] = image %}

      {% else %}
        {% log message: "Unexpected object type in JSONL", object_type: object.__typename, object: object %}
    {% endcase %}
  {% endfor %}

  {% comment %}
  -- loop through products_by_id to build csv rows
  {% endcomment %}

  {% for pair in products_by_id %}
    {% assign product = pair[1] %}
    {% assign product_rows = array %}

    {% comment %}
    -- loop through product variants to build csv rows, one row per variant
    {% endcomment %}

    {% for variant in product.variants %}
      {% assign variant_row = hash %}
      {% for column in columns %}
        {% assign variant_row[column] = nil %}
      {% endfor %}

      {% comment %}
      -- the first variant row contains the details for the product itself
      {% endcomment %}

      {% if variant.position == 1 %}
        {% assign variant_row["Body (HTML)"] = product.descriptionHtml %}
        {% assign variant_row["Gift Card"] = product.isGiftCard %}
        {% assign variant_row["Handle"] = product.handle %}
        {% assign variant_row["SEO Description"] = product.seo.description %}
        {% assign variant_row["SEO Title"] = product.seo.title %}
        {% assign variant_row["Tags"] = product.tags | join: ", " %}
        {% assign variant_row["Title"] = product.title %}
        {% assign variant_row["Type"] = product.productType %}
        {% assign variant_row["Vendor"] = product.vendor %}

        {% assign published = false %}
        {% if product.onlineStoreUrl != blank %}
          {% assign published = true %}
        {% endif%}
        {% assign variant_row["Published"] = published %}

        {% for option in product.options %}
          {% assign option_name_key = "Option" | append: option.position | append: " Name" %}
          {% assign option_value_key = "Option" | append: option.position | append: " Value" %}
          {% assign variant_row[option_name_key] = option.name %}
          {% assign variant_row[option_value_key] = option.values[0] %}
        {% endfor %}
      {% endif %}

      {% assign variant_row["Cost per item"] = variant.inventoryItem.unitCost.amount %}
      {% assign variant_row["Handle"] = variant.product.handle %}
      {% assign variant_row["Variant Barcode"] = variant.barcode %}
      {% assign variant_row["Variant Compare At Price"] = variant.compareAtPrice %}
      {% assign variant_row["Variant Fulfillment Service"] = "manual" %}
      {% assign variant_row["Variant Inventory Policy"] = variant.inventoryPolicy | downcase %}
      {% assign variant_row["Variant Price"] = variant.price %}
      {% assign variant_row["Variant Requires Shipping"] = variant.inventoryItem.requiresShipping %}
      {% assign variant_row["Variant SKU"] = variant.sku %}
      {% assign variant_row["Variant Taxable"] = variant.taxable %}
      {% assign variant_row["Variant Tax Code"] = variant.taxCode %}

      {% for option in variant.selectedOptions %}
        {% assign option_value_key = "Option" | append: forloop.index | append: " Value" %}
        {% assign variant_row[option_value_key] = option.value %}
      {% endfor %}

      {% if variant.image %}
        {% assign variant_row["Variant Image"] = variant.image.url %}
      {% endif  %}

      {% if variant.inventoryItem.tracked %}
        {% assign variant_row["Variant Inventory Tracker"] = "shopify" %}
      {% endif %}

      {% case variant.inventoryItem.measurement.weight.unit %}
        {% when "GRAMS" %}
          {% assign variant_row["Variant Weight Unit"] = "g" %}
          {% assign variant_row["Variant Grams"] = variant.inventoryItem.measurement.weight.value %}
        {% when "KILOGRAMS" %}
          {% assign variant_row["Variant Weight Unit"] = "kg" %}
          {% assign variant_row["Variant Grams"] = variant.inventoryItem.measurement.weight.value | times: 1000 %}
        {% when "OUNCES" %}
          {% assign variant_row["Variant Weight Unit"] = "oz" %}
          {% assign variant_row["Variant Grams"] = variant.inventoryItem.measurement.weight.value | times: 28.34952 %}
        {% when "POUNDS" %}
          {% assign variant_row["Variant Weight Unit"] = "lb" %}
          {% assign variant_row["Variant Grams"] = variant.inventoryItem.measurement.weight.value | times: 453.59237 %}
      {% endcase %}

      {% assign product_rows[product_rows.size] = variant_row %}
    {% endfor %}{% comment %}-- end variant loop --{% endcomment %}

    {% comment %}
    -- loop through product images to build csv rows
    {% endcomment %}

    {% for image in product.images %}
      {% comment %}
      -- images should be assigned sequentially to existing product rows, regardless of any
      -- pairing with variant images, because... ¯\_(ツ)_/¯
      {% endcomment %}

      {% if product_rows[forloop.index0] %}
        {% assign image_row = product_rows[forloop.index0] %}
        {% assign image_row["Image Src"] = image.url %}
        {% assign image_row["Image Position"] = forloop.index %}
        {% assign image_row["Image Alt Text"] = image.altText %}
        {% assign product_rows[forloop.index0] = image_row %}

      {% else %}
        {% assign image_row = hash %}
        {% for column in columns %}
          {% assign image_row[column] = nil %}
        {% endfor %}

        {% assign image_row["Handle"] = product.handle %}
        {% assign image_row["Image Src"] = image.url %}
        {% assign image_row["Image Position"] = forloop.index %}
        {% assign image_row["Image Alt Text"] = image.altText %}

        {% assign product_rows[product_rows.size] = image_row %}
      {% endif %}
    {% endfor %}{% comment %}-- end images loop --{% endcomment %}

    {% for product_row in product_rows %}
      {% comment %}
      -- flatten the product rows hash into an array of values
      {% endcomment %}

      {% assign row = array %}
      {% for pair in product_row %}
        {% assign row[forloop.index0] = pair[1] %}
      {% endfor %}

      {% comment %}
      -- add the row to 2d rows array
      {% endcomment %}

      {% assign rows[rows.size] = row %}
    {% endfor %}
  {% endfor %}{% comment %}-- end product loop --{% endcomment %}

  {% comment %}
  -- convert 2d array into csv format
  {% endcomment %}

  {% assign csv = rows | csv %}

  {% if event.preview %}
    {% log csv %}
  {% endif %}

  {% capture upload_path %}products__{{ "now" | date: "%Y-%m-%d_T%H-%M-%S_%Z", tz: "UTC" }}.csv{% endcapture %}

  {% comment %}
  -- directory paths may or may not have a leading slash (if they do, they're absolute;
  -- if they don't, they're relative), but we always need a trailing slash
  {% endcomment %}

  {% if sftp_upload_directory != blank %}
    {% assign directory = sftp_upload_directory %}

    {% if directory.last != "/" %}
      {% assign directory = directory | append: "/" %}
    {% endif %}

    {% assign upload_path = directory | append: upload_path %}
  {% endif%}

  {% action "ftp" %}
    {
      "protocol": "sftp",
      "host": {{ sftp_host | json }},
      "port": {{ sftp_port | json }},
      "user": {{ sftp_user | json }},
      "password": {{ sftp_password | json }},
      "uploads": {
        {{ upload_path | json }}: {{ csv | json }}
      }
    }
  {% endaction %}
{% endif %}