Blog

Tutorial: how to build a simple CRM with google apps script [part2]

Posted on by , 0 comment(s)
Type of post:
tutorial
Target Audience:
newbie programmer, small/local business owner
Requisites:
basic knowledge of a spreadsheet, basic knowledge of Google Drive, some experience in Javascript
Keywords:
customer relationship management, gogole drive, google spreadsheet

This is the second post of a series: for the first part see “How to build a simple CRM with Google Apps Script

// A little bit of automation: your very first report

Let’s say that you have to send to your manager (or to yourself) a short report about the purchase history of a certain customer. In addition, you also want to send to your customer a short version of this report. What we would like to achieve (our objective) is to:

  1. Click on the customer id on the “customers” Sheet
  2. Click on a button or a menu to send the email to the customer or the manager
  3. This click should trigger a function that will:
    • Take the id of the customer that we have selected
    • Collect the customer information
    • Go in the “orders” sheet, find all the orders with the same customer_id and collect them
    • Prepare the report
    • Send the report by email

All this can be easily be done with a simple script. Click on Tools->Script Editor->Blank Project, then, from the menu on the left, let’s change the name of the script by clicking on it to “PurchaseReport”. Create a new script file by clicking File->New->Script File and call it “ObjService”.

Open ObjService.txt and copy-paste it in the newly created ObjService.gs script file. This code is a library from the very useful ScriptsExamples website: thumbs up for these guys!

A utility class

First of all, let’s build a class that will open the database, initiate the Sheet etc… since we will use these operations in different functions. Put the following code at the end of the file. The different methods are quite straightforward so we will not explain them one by one (they open the Spreadsheet, returns Sheets and so on).


function Crm(customersSheetName, ordersSheetName, devMode){  
  this.managerEmail = null;
  this.customersSheetName = null;
  this.ordersSheetName = null; 
  this.devMode = false;
  this.ss = null;
  this.customersSheet = null;
  this.ordersSheet = null;

  this.init = function(){
    this.devMode = devMode;
    this.managerEmail = Session.getActiveUser().getEmail();
    this.customersSheetName = customersSheetName;
    this.ordersSheetName = ordersSheetName;
    this.ss = SpreadsheetApp.getActiveSpreadsheet();
  }
  
  this.getCustomersSheet = function(){
    this.customersSheet = this.ss.getSheetByName(this.customersSheetName);
    return this.customersSheet;
  }
  this.getOrdersSheet = function(){
    this.ordersSheet = this.ss.getSheetByName(this.ordersSheetName);
    return this.ordersSheet;
  }
  this.getManagerEmail = function(){
    return this.managerEmail;
  }
  this.setManagerEmail = function(email){
    this.managerEmail = email;
  }
  this.getSs = function(){
    if(this.ss !=null)
      return this.ss;
    else{
      this.throwError('No active Database Selected', (this.devMode)?'logger':'popup');
      return false;
    }
  }
  this.throwError = function(error, mode){
    if(typeof error == 'string' && typeof mode == 'boolean'){
      if(mode)
        Logger.log(error);
      else
        Browser.msgBox(error);
    }
    else
      throw "you must specify the error text and the mode";
  }
  //Initialization of the class
  this.init();
}

Functions to build

Since the level of abstraction is very low we will name our functions in a way that they can be used only for this tutorial: feel free to modify their name and adapt the code for your project. Having said that we will need only 3 functions to perform our tasks:

  1. getCustomer()
  2. getCustomerOrders(customerId)
  3. getMessage(customer, orders, type)

And 2 functions to actually send the report:

  1. sendReportToManager()
  2. sendReportToCustomer()
getCustomer()

With this function we get the value of the selected cell (remember our plan? We wanted to let the user click on the customer ID) and we check with if(currentSheetName != crm.customersSheetName) that we are in the right Sheet. Next, we get the customer data in the row where the selected cell is and we build and return an object of header:value with rangeToObjects(customerData).


function getCustomer(){
  var ss = crm.getSs(),
      sheet = crm.getCustomersSheet(),
      headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(),
      selectedCell = ss.getActiveCell(),
      currentSheetName = selectedCell.getSheet().getName();
  //Check that the current sheet is the customer's one
  if(currentSheetName != crm.customersSheetName){
    crm.throwError('Error! You must select the ID of a customer in the "'+crm.customersSheetName+'" sheet', crm.devMode);
    
    return false;
  }else{
    var selectedValue = selectedCell.getValue(),
        customerRow = sheet.getRange(selectedCell.getRow(), 1, 1, sheet.getLastColumn()).getValues(),
        customerData = new Array();
    customerData.push(headers[0]);
    customerData.push(customerRow[0]);
    
    return rangeToObjects(customerData);
  }
  
}

getCustomerOrders(customerId)

The task here is to take all the orders of a certain customer given the ID. To do that we open the Orders Sheet and we convert all the data in an object with key : value (as before, header : value) thanks to rangeToObjects(orders). We then loop through all the data and we push a row in a new Array customerOrders if the condition order.customerid == customerId is satisfied or, in other words, if the current customer_id is equal to the Id that we have passed to the function.


function getCustomerOrders(customerId){
  var orders = crm.getOrdersSheet().getDataRange().getValues(),
      orders = rangeToObjects(orders),
      countOrders = orders.length,
      customerOrders = new Array();
    
  if(crm.devMode)Logger.log(countOrders);
  
  if(countOrders == 0){
    crm.throwError('Error! There are not orders in the sheet', crm.devMode);
  }else{
    for (var i=0; i < orders.length; i++){
      var order = orders[i];
      if(order.customerid == customerId)
        customerOrders.push(order);
    }
    if(customerOrders.length == 0){
      crm.throwError('Error! No orders for customer '+customerId+' were found', crm.devMode);
      
      return false
    }
    else
      
      return customerOrders
  }
}

getMessage()

This function will construct the html message for the email given the customer data, his orders and the type (“manager” or “customer”).
We build a simple definition list for the customer details (to include only in the manager report) and a table with all the orders for both the customer and the manager report.


function getMessage(customer, orders, type){
  
  var ordersTable = '<table>';
  
  //Build headers
  ordersTable+='<tr>';
  for(var property in orders[0]){
    if (property!='rowNum')
      ordersTable+= '<th>'+property+'</th>';
  }
  ordersTable+='</tr>';
  
  var quantitySum = 0;
  var priceSum = 0;
  for (var i=0; i<orders.length;i++){
    var order = orders[i];
    ordersTable+='<tr>';
    for (var property in order){
      if(property == 'date'){
        var date=new Date(order[property]);
        order[property] = date.toString('dddd, MMMM ,yyyy');
      } 
      else if(property=='quantity') 
        quantitySum += order[property]
      else if(property=='price') 
        priceSum += order[property]
      else if (property=='rowNum')
        continue;
      ordersTable+='<td>'+order[property]+'</td>';
    }
    ordersTable+='</tr>';
  }
  ordersTable += '</table>';  
  
  var ordersSummary = 'Summary: </br><ul>'
      +'<li><strong>Total orders</strong>: '+i+'</li>'
      +'<li><strong>Items Shipped</strong>: '+quantitySum+'</li>'
      +'<li><strong>Turnover €</strong>: '+priceSum+'</li>'
      +'<li><strong>Avg.Ticket €</strong>: '+priceSum/quantitySum+'</li>'
      +'</ul>';
  
    if(type=='manager'){
      var customerDetails = '<ul>';
      for(var property in customer){
        if (property!='rowNum')
          customerDetails += '<li><strong>'+property+'</strong>: '+customer[property]+'</li>';
      }
      customerDetails += '</ul>';
      
      return "<html><body>"
        +"Hello,"
        +"<p> here you'll find the purchase report for the following customer: "
        + "<p>" + customerDetails
        +'<p>'+ordersSummary
        +'<p>'+ordersTable
        +"<hr />"
        +"<p> Have a nice day!"
        + "</html></body>";
    }else if (type=='customer'){
      return "<html><body>"
        +"Hello "+customer.firstname+","
        +"<p> here you'll find all your purchases: "
        +'<p>'+ordersSummary
        +'<p>'+ordersTable
        +"<hr />"
        +"<p> Have a nice day!"
        + "</html></body>";
    }
}

Put it all together: sendReportToManager() and sendReportToCustomer()

These 2 functions are quite identical and we could have built a third one to manage the common code but we wanted to keep things simple for the sake of the tutorial. First we take the customer calling getCustomer() (the [0] is because the function getValues returns an Array [[value, value, ...]] but we need one in the form of [value, value, ...]). Then we pass the customerId in the previous array (calling customer.id) to the getCustomerOrders() function.

function sendReportToManager(){
  var customer = getCustomer()[0],   
      orders = getCustomerOrders(customer.id);
  if(customer && orders){
    MailApp.sendEmail(crm.managerEmail, "Purchase Report for customer "+customer.id, "", {htmlBody: getMessage(customer, orders, 'manager')});
    Browser.msgBox('The report was sent through email to '+crm.managerEmail);
  }else{
    throw 'Error, customer or orders are not set';
  }
  
}
function sendReportToCustomer(){
  var customer = getCustomer()[0],   
      orders = getCustomerOrders(customer.id);
  if(customer && orders){
    MailApp.sendEmail(customer.email, "Your Purchase Report", "", {htmlBody: getMessage(customer, orders, 'customer')});
    Browser.msgBox('The report was sent through email to '+customer.email);
  }else{
    throw 'Error, customer or orders are not set';
  }
  
}

We now have the customer details and the orders (if there is any), so now we’re ready to send this data to getMessage() in order to have the html version of the email. We then prompt a popup in the Spreadsheet if everything went as planned (ando no error was found).

In sendReportToManager function we are sending the email to the active user using the Google Apps Script function Session.getActiveUser().getEmail(); if you want to send the email to someone other just modify the following line of code:

 

MailApp.sendEmail(crm.managerEmail, "Purchase Report for customer "+customer.id, "", {htmlBody: getMessage(customer, orders, 'manager')});

and replace crm.mangerEmail with another email address (like “test@test.com”)

Add the menu

One more thing: we need to initiate the class Crm() and to create a custom menu in the Spreadsheet. To do that, put at the beginning of your code the following function:

 
crm = new Crm('customers', 'orders', false);

function onOpen() {
  var menuEntries = [ {name: "Send Purchase Report to manager", functionName: "sendReportToManager"},
                      {name: "Send Purchase Report to customer", functionName: "sendReportToCustomer"} ];
  crm.getSs().addMenu("Daviom", menuEntries);
}

If you have named your Sheets with other than “customers” and “orders” you can set the titles in the Crm initialisation (ex: new Crm("lorem", "ipsum", false)).

// The final result

Save everything and reload your spreadsheet. After a couple of seconds you should see a new menu “Daviom”:

  1. Click on the Customer Id on the customer Menu:
  2. Click on Send Report To Manager
  3. If everything was set up correctly you should see a popup. Check your email.

If you’re having difficulties or you would like to adapt this code to your needs do not hesitate to Contact Us!


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> 

// Quick Links

// Stay In Touch

There are real people behind Daviom, so if you have a question or suggestion (no matter how small) please get in touch with us:

// Subscribe and get updates

Subscribe to our newsletter and get exclusive deals you wont find anywhere else straight to your inbox!