Connecting KiSSFLOW to G Suite with Google Apps Script

This documentation is for KiSSFLOW, if you're looking for Kissflow Digital Workplace documentation, visit docs.kissflow.com

Google Apps Scripts are easy to set up in KiSSFLOW. Let’s look at an example where you want to pull data from a PO request and put it in a Google Sheet.

The first thing to do is to go to your Google Drive account, click on New, then More, then Google Apps Script. If you don’t see this option, click, Connect more apps. Search for Google Apps Script and connect it.

Now you are in the Script editor. Give your project a name. I’ll call this one PO Items to Spreadsheet.

Copy the script from the article below.

Note: “//” - Comments for the script

function doPost(e){
    try{
    	var jsonString = e.postData.getDataAsString();
    	setLog(jsonString);
    	var jsonData = JSON.parse(jsonString);
    	/* 
          Sample jsonData from KiSSFLOW
          {
             "Process Name": "Purchase Order - Wayne Enterprise",
             "Action Performed EmailId": "[email protected]",
              "Action Name": "Submit",
              "Process Step": "Start",
              "PONO": "435435",
              "Action Performed DateTime": "2015-08-11 07:30:47",
              "Initiated By": "[email protected]",
              "Id": "She063cfd4_3ffa_11e5_85e0_238d35503ab3",
              "Subject": "Request for 435435"
           }
        */
    	//Assigning id from JSON to a variable id  
    	var id = jsonData["Id"];
    	//Assigning Process Step from JSON to a variable processStep 
    	var processStep = jsonData["Process Step"];    
    	// Prints the current Step name
    	setLog(processStep)
    	// Calling Parent function
    	updateSheetParent(jsonData);
    
    	// Calling LineItems function 
    	updateSheetLineItems(jsonData);  
    }catch(e){   	 	
    	setLog("Exception occured="+JSON.stringify(e));  
    }
} 
// To update Parent data
function updateSheetParent(jsonData) {  
    // Create a Spreadsheet and replace with your Sheet URL
    var sheetUrl = "<enter Sheet URL>";
    // Sheet name of the spreadsheet. To update the parent data in Sheet 1  
    var sheetName = "Sheet1";  
    var doc = SpreadsheetApp.openByUrl(sheetUrl);  
    var sheet = doc.getSheetByName(sheetName);  
    var item = jsonData["FieldID1"];  
    var rowList = [];  
    rowList.push(jsonData["FieldID1"])  
    sheet.appendRow(rowList);
}
//To update line items from a table function 
function updateSheetLineItems(jsonData){ 	
    // Replace with the same Sheet URL  
    var sheetUrl = "<enter the same Sheet URL as above>";
    // Sheet name of the spreadsheet. To update the Line items in Sheet2  
    var sheetName = "Sheet2";  
    var doc = SpreadsheetApp.openByUrl(sheetUrl);  
    var sheet = doc.getSheetByName(sheetName);    
    var itemList = jsonData["<enter name of table>"];    

    for (var i in itemList){    
    	var item = itemList[i];    
    	var rowList = [];    
    	rowList.push(jsonData["FieldID1"],item["FieldID2"],item["FieldID3"])    
    	sheet.appendRow(rowList);  
    }
}

function setLog(message){
    // Create a Google document and give the document URL below to print the error logs of the script if you want or you can use Logger.log(“Print log”+e)
    var doc = DocumentApp.openByUrl('<enter URL for Google Doc for logs>');  
    doc.getBody().appendParagraph(message);
}

This top section pulls the data from a KiSSFLOW item. You don’t need to edit any of the information in this section.

Starting at line 24, this is the function that will take data from your parent form and place it in the spreadsheet. In line 26, enter the URL of the Google Sheet where you want the information to go to. You can also enter the name of the sheet here.

In line 31 and 33, you need to enter the Field ID of the field that you want to pull the data from. You can find this inside the App Builder. For example, I want to get the PO number. This is a auto-generated sequence number. The Field ID is PO_Number. I’ll enter that into the Script.

The next section shows you how to pull information from a table. Similarly, you are going to first put the URL of the Google Sheet and specify the sheet name.

Then, you need to enter the name of the Table. Check your App Builder to make sure you have the name right. Use the underscore sign for any spaces.

Below that, there is a place to enter all the Field IDs you want to display. You can enter IDs from the parent form, such as the PO Number again, or you can get data from inside the table. Just copy the Field IDs and enter them here.

Finally, you need to create a Google Doc that will capture all of your logs. Simply create a new Google Doc, give it a title, and grab the URL. Paste the URL here at the bottom of the Script.

Now you need to publish your Script. Go to the menu and under Publish, click Deploy as web app. When the settings display, change the one that says “Who has access to the app”. Change it to Anyone, even anonymous. This will make sure that the script will fire no matter who uses the app in your KiSSFLOW account.

Allow the Script permission to access your documents and spreadsheets when it asks.

Then, it will give you a URL to copy.

Back in KiSSFLOW, open the app you want, and go to the workflow. Click on (+) and then click Add Action. Select Send Data to a Webhook and then enter the URL that you copied. Click Send Test Data to make sure that everything is working. You can see in your logs and in your spreadsheet that the sample data went through.

Now let’s test it. Here’s a new form that I’m about to submit. You can see that the data is automatically pushed to my spreadsheet. The first sheet grabs the PO Number and the second sheet is set to grab table data.