How to 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 Build to fill the contents of a dropdown on a form.

Create the Sheet

• Create a new Google Sheet.

• The sheet needs to have two columns.

• The first column needs to be a unique ID column

• The second column is the display values for each id

• The first row in the sheet should contain the names for each column

• Update permissions to allow anyone with the link to have access (both Viewer and Editor access will work). 

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 ​/spreadsheets/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 spreadsheet 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 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 ​Get List of Data Items​.
5. Set HTTP Method​ to ​GET.​
6. Insert the URL from current web app URL into​ Integration URL.​
7. Set A​uthentication Type​ to N​o authentication
8. Set P​ath to Results Array​ to ​results
9. Set I​D Key​ to ​id (or the name you gave the first column in your sheet)
10. Set L​abel Key​ to ​building​ (or the name you gave the second column in your sheet.
11. Click Run Test​. Once the test runs successfully with a Status 200, click Save.

You can now use the External Data field in the Form Builder and you can set the Source of data to the API Integration you just created. And the DataSet should start displaying the content from the sheet.

Code Text for Code.gs

*PLEASE NOTE on the below code.gs*

• The var sheet code below '1zW_M0YTmqi-QF1H2zA4JPZKip8szwYcMEl4OSBoAqTQ' needs to be replaced with your own unique spreadsheet ID as mentioned above.
• 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 doGet(req) {
var results = {}
var sheet = '1XnSk1PU5mrbrAv7alZ88Lxi4lTOR8iG-a_AvD9OJkXQ'
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;
}