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.
Hi
For us to have more context, post screenshots with how your Zap steps are outlined and configured in EDIT mode.
Hi
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:
- 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
- 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
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?
Remove the ?
Remove the =
Use this Zap action: GSheets - Create Rows
This handles line items to create 1 row per line item.
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...
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: u]};
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": ordersui].order_number,
"Total Price": ordersPi].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
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?
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: e], 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": ordersri].order_number,
"Total Price": ordersri].total_price
});
output.orderTotal += ordersri].total_price;
}
has_more = appData.data.has_more;
starting_after = appData.data.starting_after;
} catch (e) {
console.log(e.message);
}
} while (has_more);
Hi
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”.
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.