How to Automate Reports with BuiltOn Webhooks; Export Orders to Google Spreadsheet in Real Time
Automation of processes is a requirement we have all dealt with as developers. Whether it is a business requirement or just a cool feature to have, it’s nice knowing you’re saving your future self some time and trouble.
This article follows up on Unleash the Power of BuiltOn’s Webhooks!, which is worth going through before you dive into this one. Here, I will show you how you can extend your application by using BuiltOn webhooks to live export and update orders in a nicely structured Google spreadsheet. We are also going to create backups of those spreadsheets every month and send reports as CSV for audit, or whatever your business analyst says they need it for.
BuiltOn is a headless commerce software-as-a-service platform, with built in machine learning capabilities. It allows you to build reliable e-commerce solutions fast, and use ready made ML models like recommendations and complimentary items when you want them.
Note that this example uses our Orders module, but it can be adapted to all types of webhooks we support at BuiltOn (e.g. Users, Products, Subscriptions, etc.).
Prerequisites
Enough preamble, let’s do this! To begin with, you will have to set up a couple of things to get your application running and create orders that will get exported to your future spreadsheet.
- In the article How to Build a Smart Online Store in Less Than 21 Days!, I explain the core elements of how to build your own online store with BuiltOn APIs. I encourage you to check out the BuiltOn Demo Store, in case you need a more in-depth example on implementation.
- Once you have set up your own online store, you need to create a Google Sheet where you will put the orders. If you don’t have a google account, congrats, that’s almost impressive at this point. But for the rest of you, just visit your Google Drive and create a new Google Sheet. You can name it however you wish.
And now you’re ready! Let’s see how to use the webhooks to export your orders.
BuiltOn Order to Google Sheets?… easy
Now that you have set up your project and have orders in the system, you can export them to the spreadsheet. There are a couple of steps to do first:
- Create a spreadsheet where we are going to place our orders.
- Deploy a google script that will receive your webhook and then create or update an order in the spreadsheet. In addition, at the end of every month it will send an email containing a CSV file with all the orders for that month and duplicate the file as a backup.
- Create the webhooks in your BuiltOn Dashboard for your order events (create and update).
Prepare your spreadsheet to extract the webhook data nicely formatted into the document. Add column headings based on what data you want in the spreadsheet (see image below). Keep in mind that the limitation per document is 5 000 000 cells, so delete all the remaining columns that aren’t used to free up space for more orders.
Next, add some code that will accept the webhook, extract the order and insert it into the row in your spreadsheet. You can do that by navigating to Tools and clicking on Script Editor. This opens a new screen where you can write your code. You’ll want to handle both creating and updating an order. Creating an order will append a new row to the sheet and updating an order will replace the values in an existing row. So, let’s look at how to get the orders:
What happens is…
Begin with getting the sheet, the type (either creating an order or updating one), and the actual data. Create an array of the data that you are going to insert, each element in the array representing a cell in the row. Then, begin the data handling part, which is separated by a condition check to determine if you are creating an order or updating one.
When creating a new order, all you need to do is append a new row to the sheet.
Otherwise, if the order is being updated, iterate through all the cells in the rows to match the unique ID (human_id) and get the range (A1:H1 format) for the order details. All that is left is to replace those cell values with the updated ones.
What about our emails?
So far you have ensured that your orders will get appropriately handled in the spreadsheet. Now, set up the emails that are going to be sent every month containing a CSV document with the orders for that month.
To do so, go to your Google Scripts page and click on File and New, select HTML file and give the file a name. There you can create your template. Repeat for the error template.
Once you have our email template, you can create your attachment. A full example can be found below:
To recap:
- Use your predefined email templates and attach your CSV file to it, along with recipient and subject. The name of the CSV is set by following the pattern /Month_Name Full_Year/ for better readability. Finally, use the MailApp class to send the mail.
- For future audit purposes, and to overcome the 5 000 000 limitation in each document (still that limitation remains for each month), create backups and clear the current document.
- Setup a notification for yourself in case something goes wrong with the email.
Finally, let’s cast the spell
Once you have the script all set, give it a name and deploy as a web app by clicking on Publish and Deploy as a web app.
Note: access needs to be set to “Anyone, even anonymous” to allow the webhook to access the google scripts endpoint.
After deployment, if permissions are requested, grant them. You should see a pop-up with a URL which you are going to use for your webhooks, so copy it.
All that is left now is to set up your trigger, which will call your function to send the email every month. You can do that again from your Script Editor view by navigating to Edit and selecting Current Project Triggers. Since you don’t yet have one, create it by clicking Create Trigger. There you need to choose the function, in this example “sendEmailWithAttachment,” as well when it’s going to be called. And you can set your function to be triggered every 1st of the month between 0 and 1 AM, or whenever makes sense for your case.
Now, configure your webhooks, which can be easily done through your BuiltOn Dashboard. Log in, navigate to Webhooks and create new, which will open a pop-up where you need to set the endpoint to the URL you copied after deploying the script in Google Sheets, and the event type, which defines when you want the webhook to trigger. In this case, you’d want your webhook to trigger when an order is created, so select ‘order.created’.
You will need to create a similar webhook for ‘order.updated’ to get the updated orders as well. Then, whenever an order is being created in your store, the webhook is triggered and it is placed nicely in your spreadsheet. The same goes for orders that are updated for whatever reason. And voila, your orders start to appear in the spreadsheet.
And done!
Now, every time you get an order, or an order gets updated, it goes in your spreadsheet. Every 1st of the month, an email with the CSV audit doc with all the orders for the previous month will be sent to whoever you want, you create a backup spreadsheet for that month and you set up your main spreadsheet for future orders.
BuiltOn Webhooks let you do a lot with your data. Using them with Google Scripts is just one example of how they can be applied. BuiltOn Docs can give you more general information on the system implementation, and Google knows the rest.
