Video not working? Try this link.


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

//var jsonData = JSON.parse('{"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 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);

}

//---------------------------------------------> Script End←--------------------------------------------------------

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, App Administrators have access to Web Services. Click Manage. Choose the process name that you want to link this Script to. Then enter the URL that you copied. Send some 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. 

That’s how to set up a Google Apps Script for KiSSFLOW. 

Once you are done with all the changes, click on Deploy as web app under the Publish menu. Execute the app as yourself, but change the access to the app to Anyone, even anonymous.

Click on Review Permissions and click on Allow

Copy the current web app URL and click on OK.

Then go back to your KiSSFLOW, under the Admin tab, click on Manage under Web Services. Add a new webhook by selecting the name of the Process and then entering the URL that you copied from your Google Apps Script.  

When you initiate the App and submit your information, you can see that the data is pushed automatically from KiSSFLOW to your Google Sheet. In this example, Sheet 1 just has the PO number while Sheet 2 has more details. 

Did this answer your question?