Transform object data to individual columns (Woocommerce REST API)

[JorisT]

Hi,

I'm trying to map order information from Woocommerce to our email platform Missive as they have an integration with Retool as described here: Retool Tutorial: Integrating Your Data Into Missive

The example uses a google sheet structured like this as a Resource:

However as source I do not want to use a google sheet but the direct data from an API. Since there is currently direct integration with Woocommerce I'm using the Woocommerce REST API.

The problem is that the email address is embedded under billing. So I cannot use the given code by Missive to fetch the correct data:

The API orders data output is structured as followed:

[
  {
    "id": 220152,
    "parent_id": 0,
    "status": "processing",
    "currency": "EUR",
    "date_created": "2023-04-04T20:49:42",
    "total": "43.80",
    "billing": {
      "first_name": "Name",
      "last_name": "LastName",
      "address_1": "Street 01",
      "city": "'CityName",
      "country": "NL",
      "email": "example@email.com",
    },
    "created_via": "checkout",
    "meta_data": [
      {
        "id": 3819405,
        "key": "_billing_vat_number",
        "value": ""
      },
 ],

(for a full example see: WooCommerce REST API Documentation - WP REST API v3)

How do I transform the API data into a table/data form with a Email column such as the google sheet so that I can fetch the corresponding data according to the current email address in Missive ( fetched through ParentWindow)?

To clearify, The final result should be a KeyValue table as in the example from Missive with f.e. values First Name, Last Name, Order total, Email, etc, based on the CurrentConversatoin

[lamh_bytecode.io]

Hello,

So I created a temp state with dummy data based on the format you gave...unfortunately, i don't see company value anywhere but the process should be straight forward.

Dummy data

Query JSON with SQL, to simulate API call.

Essentially, you just need to enable the Transformer in your API and transform the result to the format you need. In the image above the code in the transformer is as follow.

return data.map(o => {
  return {
    "first_name": o.billing.first_name,
    "last_name": o.billing.last_name,
    "email": o.billing.email,
  }
});

Basically, map function loops through each record and returns a new format.

Hope that makes sense.

[JorisT]

Hi lamh_bytecode.io,

That helps, I got it working now!

Thanks !!

[JorisT]

The pagination from the Woocommerce API only allows to request maximum of 100 items per page. Would it be possible to overcome this limit by using two GET queries and SELECT from these two data sets?

Something like this:

however now the tables are not added because the KeyValue table now results empty with the text sort. I think the data is undefined because it's not structured right.

[lamh_bytecode.io]

There are couple ways of doing this - they must have same format, since yours came from same API then it should be ok.

UNION

select *
from {{getOrder1.value}} a 
-- where
UNION
select *
from {{getOrder2.value}} b
-- where 

note you need the same WHERE clause on both selects

Array Merge

select *
from {{[...getOrder1.value,...getOrder2.value]}}
-- where

[JorisT]

Hi lamh_bytecode.io, sorry i completely forgot to answer but your example helped a lot thanks!