How to Automate Reports with BuiltOn Webhooks; Export Orders to Google Spreadsheet in Real Time

Tigran Bagdasaryan, Frontend Developer @ BuiltOn.dev on .

Share with Facebook
Share with Twitter
Share with LinkedIn

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.

Export orders faster with BuiltOn Webhooks

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.

Spready with column headings

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:

function doGet(e) {
  return HtmlService.createHtmlOutput("doGet request");
}

function doPost(e) {
  var columns = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
  
  try {
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = activeSpreadsheet.getActiveSheet();
    // BuiltOn Webhook Type
    var type = JSON.parse(e.postData.contents).kind;
    // Webhook data
    var data = JSON.parse(e.postData.contents).object;
    
    // Add product id's
    var items = [];
    
    for (var i = 0; i < data.items.length; i += 1) {
      if (data.items[i].name) {
        items.push(data.items[i].product);
      }
    }
    
    // Row data to insert/update
    var rowData = [
        data.human_id,
        new Date(data.created),
        data.order_status,
        data.user,
        data.delivery_address.street_name + ', ' + data.delivery_address.city + ', ' + data.delivery_address.country,
        items.join(', '),
        data.total_amount + ' ' + data.currency
      ];
    
    if (type === 'order.created') {
      // Create new row with the data
      sheet.appendRow(rowData);
    } else {
      // Order updated
      var dataSet = sheet.getDataRange().getValues();
      var orderHumanId = data.human_id;
      // we iterate through the dataset to get the row by its human id
      for (var i = 0; i < dataSet.length; i++) {
        for (var j = 0; j < dataSet[i].length; j++) {     
          if (dataSet[i][j] === orderHumanId) {
            // get the Row number and the range
            var rowNumber = i + 1;
            var range = 'A' + rowNumber + ':' + columns[rowData.length - 1] + rowNumber;
            
            // update the row
            sheet.getRange(range).setValues([rowData]);
            break;
          }
        }
      }
    }
    
  } catch (error) {
    Logger.log(error);
  }
  return HtmlService.createHtmlOutput("doPost received");
}

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.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>Hello,</p>
    <p>Below you can find an attachment with your orders for the month of <?= month ?></p>
    
    <p>Thanks<br/></p>
    Builton.dev
  </body>
</html>

Once you have our email template, you can create your attachment. A full example can be found below:

function getSpreadsheetName() {
   var months = [
    'January',
    'February',
    'March',
    'April',
    'May',
    'June',
    'July',
    'August',
    'September',
    'October',
    'November',
    'December'
  ];
  
  var monthIndex = new Date().getMonth();
  var year = new Date().getFullYear();
  
  // This is because we set the trigger to the 1st of next month, between 0 and 1AM
  // That means that we are actually sending data for the previous month
  var date = months[monthIndex === 0 ? 11 : monthIndex - 1] + ' ' + year;
  
  return date;
}

// original author of this function: https://gist.github.com/mderazon/9655893
function convertRangeToCsvFile(sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  var data = activeRange.getValues();
  var csvFile = undefined;
  
  if (data.length > 1) {
    var csv = "";
    for (var row = 0; row < data.length; row++) {
      for (var col = 0; col < data[row].length; col++) {
        if (data[row][col].toString().indexOf(",") != -1) {
          data[row][col] = "\"" + data[row][col] + "\"";
        }
      }
      
      // join each row's columns
      // add a carriage return to end of each row, except for the last one
      if (row < data.length-1) {
        csv += data[row].join(",") + "\r\n";
      }
      else {
        csv += data[row];
      }
    }
    csvFile = csv;
  }
  return csvFile;
}


function sendEmailWithAttachment() {
  // Row columns
  var rowColumns = [
    'Human ID',
    'Created',
    'Order Status',
    'User',
    'Delivery Address',
    'Items',
    'Total Amount'
  ];
  
  try {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    // Get the template from the html template file
    // We are going to use it for email body
    var template = HtmlService.createTemplateFromFile('email-template');
    // replace the month in the template with the current month
    template.month = getSpreadsheetName();
    var message = template.evaluate().getContent();
    
    // Convert the sheet to string
    var csvString = convertRangeToCsvFile(sheet);
    // Create new CSV file
    var csvFile = Utilities.newBlob(csvString, 'text/csv', getSpreadsheetName() + '.csv');
    
    // Send the mail
    MailApp.sendEmail({
      to: '<RECIPIENT_EMAIL>',
      subject: "Demo Store orders for " + getSpreadsheetName(),
      htmlBody: message,
      attachments: [csvFile]
    });
    
    // Make an exact copy of the current file for future references, set the name to the corresponding month
    DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).makeCopy(getSpreadsheetName());
    // clear the current sheet
    sheet.clearContents();
    // set the heading
    sheet.appendRow(rowColumns);
  } catch(err) {
    // In case something wrong happens, we are going to mail us
    Logger.log(err);
    var errorTemplate = HtmlService.createTemplateFromFile('error-template');
    errorTemplate.month = getSpreadsheetName();
    var errorMessage = template.evaluate().getContent();
     
    MailApp.sendEmail({
      to: '<RECIPIENT_EMAIL>',
      subject: "Failed to send email to <RECIPIENT> for month " + getSpreadsheetName(),
      htmlBody: errorMessage,
    });
  }
}

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.

Don’t @ me, please. 😉

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

Copy to clipboard

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.

Triggger for the e-mails

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

BuiltOn Webhook Creation

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.

See - it works

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.

An e-mail to my inbox?

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.

loading...