Create a GET API Integration with a Google Sheet

You can use Google Scripting to expose the content of a Google Sheet via REST API. That REST API can then be used in Kuali to fill the contents of a dropdown on a form.

• Create the Sheet
• Create the Script
• Create the API Integration in Kuali
• Code for Establishing a Google Script

Create the Sheet

• Create a new Google Sheet.
• The sheet needs to have at least two columns.
• The first column needs to contain a unite attribute so that it can function as an ID.
• The second column will house the values associated with each unique ID.
• The first row in the sheet should contain the names for each column.
• 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 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 Get List of Data Items.
• Set HTTP Method to GET.
• Paste the URL from the Web App URL of your Google Script into the Integration URL field.
• Set Authentication Type to No authentication.
• Enter results into the Path to Results Array field.
• Enter the name of the first column within your sheet in the ID Key field. For example, id.
• Enter the name of the second column in your sheet in the Set Label Key field. For example, building.
• Scroll down to the bottom and click Run Test.
• In the Form Preview, confirm that the status returned is 200.

• Once the test has run and successfully returns a Status 200, click Save.

You can now use Advanced Gadgets (List or Multiselect Lookups) on your form that reference External Data and you can set the Source of data to the API Integration you've just created and see the data from your Google Sheet referenced in the form.

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.
• 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 doGet(req) {
var results = {}
var sheet = '{{sheetID}}'
var ss = SpreadsheetApp.openById(sheet)
//optionally include a parameter for sheet; so you can specify which sheet on a spreadsheet to pull from; otherwise it will pull from the first sheet
if (req.parameter['sheet']) {
// sheet = ss.getSheetByName(req.parameter['sheet'])
} else {
sheet = ss.getSheets()[0]
}
var data = dataToJson(sheet)
var headers = getHeaders(sheet)

//filtering based on header from spreadsheet matching a request parameter
for (var i = 0; i < headers.length; i++ ) {
var header = headers[i]
if (req.parameter[header]) {
data = data.filter(function (elem) {
return elem[header] == req.parameter[header]
})
}
}
Logger.log(results)
results['results'] = data
return ContentService.createTextOutput(JSON.stringify(results)).setMimeType(ContentService.MimeType.JSON)
// var res = ContentService.createTextOutput(JSON.stringify({1: 'casey', 2: 'coolperson2'}))
// res.setMimeType(ContentService.MimeType.JSON);
return res;
}

function dataToJson(sheet) {
var properties = getHeaders(sheet);
if (properties){
// properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
}

var rows = getDataRows(sheet);
var data = [];
for (var r = 0; r < rows.length; r++) {
var row = rows[r];
var record = {};
for (var p in properties) {
if (row[p]) {
record[properties[p]] = convert_(row[p]);
}
}
data.push(record);
}
return data;
}

//headers
function getHeaders(sheet) {
return sheet.getRange(1,1,1,sheet.getDataRange().getLastColumn()).getValues()[0];
}

//data as rows
function getDataRows(sheet) {
return sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
}

function convert_(value) {
if (value === "true") return true;
if (value === "false") return false;
return value;
}