Question

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


Hi All! Hoping I can get some help here.

Skill Level: Beginner

Goal: Collect daily revenue from preorder from Purple Dot and send to a new row in a google spreadsheet daily

What I have so far: I can pull the price of preorders from Purple Dot to a google spreadsheet, however, it sends ALL order price information in a single cell (separated by commas)when I am wanting to collect the information daily, preferably separated by row instead of all in the same cell.

I have been following the API article from Purple Dot Here. However, I’m at a loss for how I can accomplish this goal.


10 replies

Userlevel 7
Badge +14

Hi @TWKND 

For us to have more context, post screenshots with how your Zap steps are outlined and configured in EDIT mode.

Userlevel 6
Badge +8

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.

Hi @Troy Tessalone  and @Todd Harper. Thank you for your replies! I'll attach screenshots of the zap I have so far.
 

Pulling the preorders for the past 24 hours is something I am having trouble with as I just get all of the preorder information sent through at once.

 

Confirmed that I am pulling the data from data > orders > total_price

On your first consideration, you would recommend not scheduling a time to trigger the pull? I am not 100% sure of the best way to go about that. Could you provide some suggestions I can try?

Userlevel 7
Badge +14

@TWKND 

Remove the ?

Remove the =

 

Userlevel 7
Badge +14

@TWKND 

Use this Zap action: GSheets - Create Rows

This handles line items to create 1 row per line item.

 

Userlevel 6
Badge +8

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:

 

Thank you so much for all your help @Todd Harper & @Troy Tessalone. I’m pulling that data correctly now!

Forgive my ignorance, but I’m not seeing where I can edit my posts here? Could you point me in the right direction?

Finally, I have one more question regarding pulling the data. If I wanted to add up all the order price data for a single day and pass that total to a new row each day on a separate google spreadsheet, what would be your suggestions on adding more steps to the zap I already have?

Userlevel 6
Badge +8

@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);

 

Hi @Troy Tessalone. It looks like I don't have the option to edit on my side? Am I doing something incorrectly? (attached screenshot)

Thank you again with the help on the javascript code! Unfortunately, when trying to send the added up data to a spreadsheet I get an error saying “No data to show for this record”.

Userlevel 6
Badge +8

@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.

Output from Code by Zapier step (starting at 163rd item in list of orders)
Input for Google Sheets multiple spreadsheet rows step.

 

Reply