Get Daily Reports from Google Forms or Sheets in JANDI

On Jun 2, 2020

Automatically get sales data and alerts in JANDI

Automating the reporting process and setting up JANDI to receive daily sales, new orders, and/or location enables the team to have visibility of business performance in real-time. In this example, sales promoters working in different retail outlets use a simple Google Form to select their store and input the total sales on that day. Once the form is submitted, a new record is automatically generated in the Google Sheet and in the JANDI “Sales Topic”. This easy two-step integration uses Google Forms and Script Editor to automate the reporting process.

<Sales Report Google Form & Data Sheet >

<Sales Report on JANDI >

Let’s see how to make a daily sales report bot on JANDI.

1. Create a JANDI Connect – Incoming Webhook

1-1) Click the [JANDI Connect] button at the upper right of the topic you want to connect the sales report Google Form bot and add an Incoming Webhook Integration.

1-2) Click the [Save Integration] button to complete the JANDI Connect Webhook creation.

Copy the Incoming Webhook URL by clicking the ‘Copy’ button.
Select a topic to receive the Google Form response notification. If you need to create a new topic, click the ‘New Topic’ button.
Set the message profile image and name.

2.Create Sales Report Google Form

As an example, here is a simple daily sales report Google Form where the salesperson can enter the information.

2-1) When the report form is ready, click the [Script Editor] menu.

2-2) Write the code below and save it.

Write the code as below. Webhook URL and each variable of the sheet should be modified according to your JANDI connect and google form.

Red URLs have to be replaced based on your JANDI Webhook and Google Form.
Blue codes can be modified as you want.

<Code.gs>

function myFunction() {
    var form = FormApp.getActiveForm();
    var lastLine = FormApp.getActiveForm().getResponses().length;
    // latest
    var name = form.getResponses()[lastLine - 1].getItemResponses()[0].getResponse(); // name
    var dateOfSale = form.getResponses()[lastLine - 1].getItemResponses()[1].getResponse(); // dateOfSale
    var revlonSale = form.getResponses()[lastLine - 1].getItemResponses()[2].getResponse(); // revlonSale
    var RealTechniquesSales = form.getResponses()[lastLine - 1].getItemResponses()[3].getResponse(); // RealTechniquesSales
    var Location = form.getResponses()[lastLine - 1].getItemResponses()[4].getResponse(); // Location
  
    var message = 'Name : ' + name + '\n' + 'Date of Sale : ' + dateOfSale;
    // jandi setting

    var jandi_incoming_url = "https://wh.jandi.com/connect-api/webhook/279/b0c394c6ee6629f625679739d7c123rdf"; //change webhook url  
    var jandi_headers = {
        "Accept": "application/vnd.tosslab.jandi-v2+json",
        "Content-type": "application/json",
    };
    var jandi_formData = {
        "body": message,
        "connectColor": "#FAC11B",
        "connectInfo": [{
          "title": 'Revlon Sale : ' + revlonSale + ", Real Techniques Sale : " + RealTechniquesSales,
          'description': "Location : " + Location + "\n[Go to GoogleSheet](https://docs.google.com/spreadsheets/d/18FrpilB815hdQCm1r9ZdCk5_HV53Cv0XaeCtyeoRCHE/edit#gid=1211612343)"
        }]
    };
    var jandi_options = {
        "method": "POST",
        "payload": JSON.stringify(jandi_formData),
        "headers": jandi_headers,
    };
    response = UrlFetchApp.fetch(jandi_incoming_url, jandi_options);
 
}