How to Create a POST API Integration with a Google Sheet

You can use Google Scripting to post information from a Kuali Build form to another source via API (i.e. Google Sheet, Google Doc, etc) as an integration step in workflow.  Below outlines the steps to take to create a basic POST integration from Kuali Build to a GoogleSheet.  More information on app scripts can be found here.

Create the Google Sheet

• Create a new Google Sheet

• Update permissions to allow anyone with the link to have access

Then you’ll need to note the unique ID for the sheet itself. You’ll need this later when we setup a google script to expose the rest api. If you look at the url of the sheet, you’ll see the ID comes at the end of the ​/spreadsheet/d/<id>/edit

(Note: when you copy and paste this id, it should NOT include the “/edit#..” part.)

Create the Script

1. Go to​ https://script.google.com
2. Click on N​ew Project
3. Replace the contents of the C​ode.gs​ file with the code text at the bottom of this document.
4. Replace the spreadsheet id​ in line 2 with the id of your spreadsheet. (Note: Don't delete the apostrophes to each side of the id number.)
5. Click on Deploy, then New Deployment
6. In the New deployment dialog, add Description, select Type as Web app. In the Configuration screen, set Execute as to Me (your email address) and set ​Who has access​ t​o Anyone.
7. Click on D​one - You’ll be prompted to Authorize access to allow your project access to your Google Account. This is what allows the script to access your sheet securely.
8. In the final New Deployment dialog, copy the Web app​ URL.​ This is the URL you need to configure the API Integration in Kuali Build.
9. Click on Done.

Create the field in the Build Form (that you want to post)

Within your form you will need to add the field to the form that you want to then send externally via API integration.  For this example, I am using a Short Text field gadget.  Please be aware that Checkboxes, Radios, and Dropdowns cannot currently be used for Build integrations.

1. Within the form I've added the below Short Text field of 'Type of Fruit TEST" - please note that I have added a custom JSON key (fruittest) for easier selection/use later in the API Integration:
   
2. Save the field to the form template - you can wait to publish until you add the workflow step.

Create the API Integration in Build

1. From your Kuali Build Dashboard click on the Kuali Build dropdown menu in the upper left corner. Click System API Integrations.
   
   
2. You will find yourself on the API Integration page. Click on Add API Integration. 
   
3. Enter a n​ame​ for the integration
   
4. Set Type of Integration​ to ​Use in Workflow​.
5. Set HTTP Method​ to ​POST.​
6. Insert the URL from current web app URL into​ Integration URL.​
7. Set A​uthentication Type​ to N​o authentication
8. In the Configure Request Body is where you can set what information is sent.  For this example,  I specified two fields to send - the specific Fruit field and then the Created By User info.  The Label is what you want to call the field in the Body and then the Data Path is the JSON to retrieve the field (i.e. fruittest from the field we created in the prior step).  The Gadget Type is then selected to match.  If desired you can send additional information via the checkboxes for applicationID, documentId, formId, meta.
   
   
9. Within the Header information put the Key as 'Content-Type' and Value of 'application/json' as below:
   
10. Click Run Test. Once the test runs successfully with a Status 200, click Save.

You can now use this integration as an Integration step in the Form workflow in the next step.

Add the Integration Workflow Step

Within the Form Workflow you can then add an Integration step in the desired place to trigger this POST action to send the configured information.  Within the integration you point to the newly created integration and then specify or connect the fields configured in the Request Body of the integration (which we added fruit and created by fields specifically):

 

Once added you can then publish the form with all the changes.  Then create a document and submit to the integration step to confirm it's sending the information to the Google Sheet.  

Code Text for Code.gs (to insert in the script)

*PLEASE NOTE on the below code.gs*

• The var sheet code below 'SHEET ID GOES HERE' needs to be replaced with your own unique sheet ID as mentioned above.
• Next, you will also need to replace the dataForRow fields to match the JSON keys you are sending from your form. For example, if you were hoping to add a field with a JSON key called "title", you would replace "field1" with the word "title" (so it would read data.title). You need to comma separate as many fields as you wish to add to your row (and in the column order you wish to add them) within that dataForRow field.
• This is only a suggested code.gs - you can modify as needed and script.google could modify requirements that could make this sample no longer work as expected.

function doPost(e) {
Logger.log(e)
const sheetId = "SHEET ID GOES HERE";
const data = JSON.parse(e.postData.contents)
const dataForRow = [data.field1, data.field2, data.field3, data.field4]
Logger.log(data)
var sheet = SpreadsheetApp.openById(sheetId);
sheet.getActiveSheet().appendRow(dataForRow);
return;
}