Daily pull of preorder revenue data from Purple Dot to Google Spreadsheet using webhook

Hi @TWKND! You definitely came to the right place to get help with this!

Can you confirm that it’s collecting just the pre-orders you want (ie. from the last 24 hours rather than the entire history of pre-orders)?

Are you using the data from data > orders > total_price?

And can you clarify: is the intent that each order is on its own row in Google Sheets?

A couple of considerations:

1. If you are placing new pre-orders on their own row in Google Sheets, you may want to consider doing it as they come in rather than once per day. Both options are possible, but just handling them as they come in tends to be simpler. You can do this by setting up webhooks in Purple Dot: https://docs.getpurpledot.com/docs/platform-integration-references/webhooks
2. If you stick with doing it once per day, you will likely end up needing to use Looping by Zapier to loop through the line items and grab each of their prices out one by one.

@TWKND 

Remove the ?

Remove the =

One urgent comment before the rest of my response: please edit your post to remove the image including your API Key / Purple Dot Access Token. Anybody on the internet can use that token to view (and change) your private order details. Troy and I are good actors, but bad actors can scrape this forum for your key and cause harm.

With that out of the way...

@Troy Tessalone’s Create Multiple Spreadsheet Rows suggestion is the way to go.

But there are a few other things I would adjust as well:

Add a Date/Time Formatter after your trigger

Currently, your webhook is checking for orders made since the Zap began running, which is probably always going to be 0. Instead, you want to use the start of the zap run minus 1 day, formatted as seen below. Then, be sure this is the value being fed into your Webhook Query String Param.

Update Your Webhook Query Params OR (even better) Replace Your Webhook Step with a JavaScript Code Step for Pagination

Your Webhook only returns the first page of results in your search, which is 10 by default. If you have more orders than that in a day, they won’t make it into your Google Sheet.

There are two ways around this. If you know you’ll never have more than 200 orders in a day, you can simply update your Webhook query string params to include limit=200, as in the screenshot below:

But an even better option (and not limited to 200 results) is to use a code step. Simply copy and paste the following, being sure to replace YOUR_API_KEY_HERE with your actual Purple Dot access token (be sure to leave the quote marks). This will loop through all pages and grab every order, no matter how many there are.

Note that this code only grabs the order number and total price, but you can add more to it, if you’d like.

output = {orders: []};

const appKey = "YOUR_API_KEY_HERE";
let appData = "";
let has_more = false;
let starting_after = "";
let starting_after_param = "";

do {
  try {
    if (starting_after) {
      starting_after_param = `&starting_after=${starting_after}`;
    }
    const appResp = await fetch(
      `https://www.purpledotprice.com/admin/api/v1/pre-orders?limit=200&created_at_min=${inputData.created_at_min}${starting_after_param}`,
      {
        method: 'GET',
        headers: {
          'Content-Type': 'application/json',
          'X-Purple-Dot-Access-Token': appKey
        }
      }
    );

    appData = await appResp.text();
    appData = JSON.parse(appData);
    let orders = appData.data.orders;
    for (let i = 0; i < orders.length; i++) {
      output.orders.push({
        "Order Number": orders[i].order_number,
        "Total Price": orders[i].total_price
      });
    }
    has_more = appData.data.has_more;
    starting_after = appData.data.starting_after;
  } catch (e) {
    console.log(e.message);
  }
} while (has_more);

Finally, you’ll want to be sure to include the “created_at_min” as input data, as shown here:

@TWKND Yay! Glad you got it working!

To edit your posts:

Hover over the three dots in the lower right corner of the comment you would like to edit (in this case, you should be doing the one with the three image attachments) and then click “Edit”.

For adding up the data:

Assuming you used the code method I mentioned above, this would be an easy edit to that code (see below). This will return a list of orders (including order number and total_price) along with the overall total of all orders combined.

output = {orders: [], orderTotal: 0};

const appKey = "YOUR_API_KEY_HERE";
let appData = "";
let has_more = false;
let starting_after = "";
let starting_after_param = "";

do {
  try {
    if (starting_after) {
      starting_after_param = `&starting_after=${starting_after}`;
    }
    const appResp = await fetch(
      `https://www.purpledotprice.com/admin/api/v1/pre-orders?limit=200&created_at_min=${inputData.created_at_min}${starting_after_param}`,
      {
        method: 'GET',
        headers: {
          'Content-Type': 'application/json',
          'X-Purple-Dot-Access-Token': appKey
        }
      }
    );

    appData = await appResp.text();
    appData = JSON.parse(appData);
    let orders = appData.data.orders;
    for (let i = 0; i < orders.length; i++) {
      output.orders.push({
        "Order Number": orders[i].order_number,
        "Total Price": orders[i].total_price
      });
      output.orderTotal += orders[i].total_price;
    }
    has_more = appData.data.has_more;
    starting_after = appData.data.starting_after;
  } catch (e) {
    console.log(e.message);
  }
} while (has_more);

@TWKND How odd about the edit link. Are you sure you were logged in when you hovered over the three dots? As long as it’s your post, the edit link should be there… If it’s not, you may want to reach out to Zapier support to let them know and/or see if a moderator can remove it for you.

For adding up the data and sending it to the spreadsheet, can you confirm that the output of the code step and input for the Google Sheets step look like the following (including the “Order Total” from the code step? It’s working as expected for me, so without a closer look into your Zap, it’s difficult to identify the problem.