Create a POST API Integration with a Google Sheet

You can use Google Scripting to post information from a Kuali form to another source via API (i.e. Google Sheet, Google Doc, etc) as an integration step in workflow.  The steps create a basic POST integration from Kuali to a Google Sheet are outlined below, and more information on Web App scripts can be found here.

• Create the Sheet
• Create the Script
• Create the field in the Kuali Form
• Create the API Integration in Kuali
• Add the Integration Workflow Step
• Code for Establishing a Google Script

Create the Sheet

• Create a new Google Sheet.
• Ensure that the sharing permissions for the Google Sheet are set to allow anyone with the link to have access. Viewer and Editor access will allow work with this integration, but it must not require a user authorization in order to access it.
• This can be done clicking on the Share option, and then under General Access select the Anyone with the link option.

Once the Google Sheet has been created, you’ll need to note the unique ID for the sheet itself. This will be required later when setting up a Google script to expose the rest API.

To find the ID for the sheet - look at the URL. The ID will be displayed at the end of the of the URL, like this:

{{ID}}/edit?usp=sharing

Note: When you copy the URL to capture the ID, it will only include the string of text/numbers between the slashes.

Create the Script

• Navigate to https://script.google.com.
• Click on New Project.
• This will open a new, untitled project. In the right panel, you'll see a small section of code that displays:

  function myFunction() {
              }

  This is the default script, labeled Code.gs.

• Replace the contents with the script shown here, at the bottom of this article.
• On line two of the script, replace the {{SheetID}} with the ID from the URL of your sheet, mentioned previously.

Note: Be sure that you do not delete the apostrophes that appear on each side of the {{SheetID}}. The script will not work if the apostrophes are removed.

• After adding the script, click on Deploy in the top right, then select New Deployment.

• In the New deployment dialog window, ensure that under Select Type, that the option Web App is listed.
• On the right side of the window, under the Configuration heading, enter a Description. (This is not required, but is helpful to note as a reminder for future use.)
• Under Web app, make sure the Execute as option is set to Me (Your Email Address).
• Under Who has access, make sure this is set to Anyone.

• Click the Deploy button.
• In the dialog window, copy the Web app URL. This is the URL needed to configure the API Integration in Kuali.
• Click on the Done button.

Create the field in the Kuali Form

Within your form, a field needs to be added that will be used to send data through the API to the external source. For this example, a Short Text field gadget was used.

Note: Checkboxes, Radios, and Dropdown gadgets cannot currently be used within Kuali integrations.

• Add a field to your form to house the data you want to send.
• For this example, we've added a short text field titled Type of Fruit TEST, and provided a custom JSON key (fruittest) for easy reference.

• You can leave the form in the draft state at this time; the publish step will come at the end after the workflow has been updated.

Create the API Integration in Kuali

• Click on the Suite Menu icon (), then navigate to System Settings. (Spaces & Settings for Enterprise customers).

• Non-Enterprise customers will be taken directly to the Integrations tab.

• For Enterprise customers, select the Integrations tab within the appropriate space.

• Click on the + Add API Integration button.
• The +Add Advanced option is only available for Enterprise customers.
• The New Integration modal window will appear. Provide a name for the integration, and if desired, a short description that will help you or other Admins distinguish between sources. This is the name and description that will show up in your list of API Integrations.

• Set Type of Integration to Use in Workflow.
• Set HTTP Method to Post.
• Paste the URL from the Web App URL of your Google Script into the Integration URL field.
• Set Authentication Type to No authentication.
• Under Configure Request Body, check the option for Specify fields sent in the request body to this integration. This section is where you will specify what information is sent.
  • For this example, Two fields have been specified to send - the Fruit field and the Created By User details.
• Under Request Body Inputs**, a Label, Data Path, and Gadget will need to be specified.
  • Label will be the the name of the field.
  • Data Path will be the unique JSON key used to retrieve the data. For example, fruittest.
  • Gadget indicates the type of data, and should match the gadget used on the form. For example, if the data contains a string of text, then Text should be used. If the data were to house a number, then Number would be used instead.

• Under Header, set the Key to Content-Type and set Value to application/json.

• Click Run Test. Once the test runs successfully with a Status 200, click Save.
• Scroll down to the bottom and click Run Test.
• In the Form Preview, confirm that the status returned is200.

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

Add the Integration Workflow Step

Within the Workflow, you can add an Integration step to trigger this POST action to send the configured information.

• Once the step has been added, you will need to select the Integration that you've just created to use.
• After specifying the integration, select the appropriate field from the form to match the field in the spreadsheet to line up the data. For example, match 'Fruit' to 'Fruit' in your Google Sheet.

• With the steps and form in place, you can now test the ingetration step with the Workflow Simulator.
• Click on the toggle at the top of the page to change it from Design to Test. In the new panel, click Run Test.
• In the simulator, all required fields must be filled in to progress in the test.
  • Any fields noted in yellow are needed for the workflow, but may not necessarily be required in the form.
  • Any branch steps that include parallel steps will show a message indicating they are happening simultaneously.
  • Integration steps will allow you to preview the JSON that would be sent within the integration.
  • Previews will be shown for all email notifications, as well as any tasks or approval requests
• After reviewing, the final step will be to Publish the form by going to the top right and clicking Publish. This will show a review of items that have been changed, and another Publish item button at the bottom. Once published, the form will be available for users to fill in, submit documents, and send information to your Google Sheet.

Code for Establishing a Google Script

Note: When using the code below, please keep the following in mind:

• On the third line of the script, where it says var sheet = '{{sheetID}}', the bolded  text {{sheetID}} needs to be replaced with the unique spreadsheet ID you have created.
• Do not remove the apostrophes around the ID. This is required for the script to function.
• On the fifth line of the script, where it says [data.field1, data.field2, data.field3, data.field4], the bold text needs to be replaced with the JSON keys you are sending to your form. For example, if you are sending the field from our example above, fruittest, you would replace field1 with the key fruittest, so that it now reads data.fruittest. Each field added must be seperated by a comma.
• This is only a suggested script for use in the code.gs - you can modify the script as needed and, or Google Scripts could modify requirements that may result in this sample no longer functioning as expected.

function doPost(e) {
Logger.log(e)
const sheetId = "{{sheetID}}";
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;
}