For example we know that Tom was born in England and works in England. To inject this information into Rainbird we need to add a script to the Google Sheet. 

function Rainbird(opts) {
this.apiKey = opts.apiKey
this.authHeader = 'Basic ' + Utilities.base64Encode(this.apiKey + ':')
this.sessionId = null
this.kmId = null
this.apiDomain = opts.apiDomain || 'https://api.rainbird.ai'
this.useDraft = typeof opts.useDraft === 'undefined'
? false
: opts.useDraft
}

Rainbird.prototype.start = function(kmId) {
this.kmId = kmId
var startUrl = this.apiDomain + '/start/' + kmId + '?useDraft=' + this.useDraft
var options = {
headers: { Authorization: this.authHeader }
}

var response = JSON.parse(
UrlFetchApp.fetch(startUrl, options).getContentText()
)

this.sessionId = response.id
return response
}

Rainbird.prototype.query = function(queryOpts) {
var queryUrl = this.apiDomain + '/' + this.sessionId + '/query'
var options = {
headers: { Authorization: this.authHeader },
method: 'post',
payload: JSON.stringify(queryOpts),
contentType: 'application/json'
}

var response = JSON.parse(
UrlFetchApp.fetch(queryUrl, options).getContentText()
)

return response
}

Rainbird.prototype.inject = function(facts) {
var injectUrl = this.apiDomain + '/' + this.sessionId + '/inject'
var options = {
headers: { Authorization: this.authHeader },
method: 'post',
payload: JSON.stringify(facts),
contentType: 'application/json'
}

var response = JSON.parse(
UrlFetchApp.fetch(injectUrl, options).getContentText()
)

return response
}

The above code is what is used to connect to the Rainbird API and run the session and does not need to be altered.

In the other Code Script file named Code Paste the following code.

//The following variables are required to identify the correct Knowledge Map
var RAINBIRD_API_KEY = 'cfd1aa57-9b96-4386-96bf-0df01c97bfbf'; //Replace ***YOUR_API_KEY_HERE*** with your API key
var KMID = '24f102e4-4733-4ba2-afd9-9a1d62f46c3f'; //Replace ***YOUR_KNOWLEDGE_MAP_ID_HERE*** with your Knowledge Map ID
var RAINBIRD_OPTIONS = { apiKey: RAINBIRD_API_KEY, useDraft: false };

//The following variable and function will add a button to allow the script to be run

var ui = SpreadsheetApp.getUi();
function onOpen() {
  ui.createMenu('Run Me')
    .addItem('Inject Data in Rainbird', 'main')
    .addToUi();
}

//following is the main function that reads the data in the spreadsheet and injects it as facts

function main() {
  var sheet = SpreadsheetApp.getActiveSheet(); //Reads the current sheet
  var data = sheet.getDataRange().getValues(); //Finds all the data in the spreadsheet, will stop once there is a blank row

//following loop runs through the rows that contain data and assigns variables person, bornIn and livesIn to the data in the relevant cells 
//data[X][Y] is the data in call in the X row and the Y column (starting at 0). i is a variable that increases by one each time 

  for (var i = 1; i < data.length; i++) {
      var facts = [];
      var person = data[i][0];
      var bornIn = data[i][1];
      var livesIn = data[i][2];

//following lines create the facts in Rainbird, using the variable person from the first column, the relationship which is free text
//and the object bornIn from the second column. Note that the text for relationship needs to match exactly the relationship name in the map.

      facts.push({
        subject: person,
        relationship: 'born in',
        object: bornIn
      });

      facts.push({
        subject: person,
        relationship: 'lives in',
        object: livesIn
      });

//following is the query that is run in the Knowledge map. The person is read from the first column, the relationship is the
//name of the relationship of the query. The object is null as it has not yet been created. This is the decision the map is making    

      var query = {
        subject: person,
        relationship: 'speaks language',
        object: null
      };

//following code will use start a new session in the knowledge map, inject the facts that have created above then run the query.    

      var rbClient = new Rainbird(RAINBIRD_OPTIONS);
      var sid = rbClient.start(KMID);
      rbClient.inject(facts);

//following variable is the result of the query that has been ran in Rainbird    

      var queryResult = rbClient.query(query);

//following variables set the columns to be the results from Rainbird. These include the Result, the certainty factor and a link
//to the evidence tree

      var speaksCell = sheet.getRange(i+1, 4);
      speaksCell.setValue(queryResult.result[0].object);

      var cfCell = sheet.getRange(i+1, 5);
      cfCell.setValue(queryResult.result[0].certainty);

      var evidence = createEvidenceTreeUrl(queryResult.result[0].factID, sid.id);
      var evidenceCell = sheet.getRange(i+1, 6);
      evidenceCell.setValue('=HYPERLINK("' + evidence + '", "Evidence")');
  }
}


//following function creates a link to the evidence tree for each query that has been run.

function createEvidenceTreeUrl (fact, sid) {
  return 'https://app.rainbird.ai/applications/components/rainbird-analysis-ui/whyAnalysis.html?id='
    + fact
    + '&api=https://api.rainbird.ai&engine=v2.0&sid='
    + sid
}

When saving the script with a syntax error in line 3, please replace the ‘ – as the fond might not be accepted from the copy/paste action.

The script will need to be altered so that the correct knowledge map is accessed. The information that needs to be changed has been commented in the code above.

It is important to understand that the locations of cells in the spreadsheet have a column and row value. In any value that is contained within square brackets [ ], the numbers start at zero rather than one. However values inside rounded brackets ( ) start at one.

For example, consider the following line of code;

var person = data[i][0];

This is creating a variable called person. It is reading the data in column [i], where ‘i’ is a number that represents the row number. The second set of square brackets contains the column number, note that this is always going to be zero as the column will always stay the same. In this case zero represents the first column.

Therefore if we are looking at i being equal to 1, the variable person would be equal to the information in cell row number 2, column 1. Highlighted in below image.

For the integration to work with the Knowledge Map, the concept instances that are injected from the script will have to be assigned to the correct concepts in the Map. In the Hello World Example there are three concepts; Person, Country and Language. We are going to determine the language that the person speaks so we don’t need to create a variable for Language. However we do need variables for Person and Country as we are going to inject the instances of person as well as the instances of the country they were born in and where they currently live.

for (var i = 1; i < data.length; i++) {
      var facts = []
      var person = data[i][0];
      var bornIn = data[i][1];
      var livesIn = data[i][2];

The variable ‘person’ is populated from cells in the first column, bornIn from the second and livesIn from the third.

for (var i = 1; i < data.length; i++) {

The above line of code will start a ‘for-loop’. This will loop through the spreadsheet, starting at the second row which has a number value of 1 (as the first row contains the headers). This will then run for as long as there is information in the cells, collecting the relevant information and assigning it to the facts, then running the relevant query. Once this is done it will increase the number of ‘i’ which will move onto the next row in the spreadsheet and repeat the process once again.

For the integration to work with the Knowledge Map, the facts that are injected from the script will have to match the facts in the Map.

For example, consider the following code;

facts.push ({
 subject: person,
 relationship: 'born in',
 object:bornIn
});

Each relationship that will have facts created in the Knowledge map will need to be created. In this example there are two facts that need to be created;

Person => born in => Country

Person => lives in => Country

The subject of the fact is the variable person, the relationship is text (inside single quote marks) ‘born in’ and the object is the variable bornIn.

This is repeated for each fact that is created in the Knowledge Map.

Similar to the facts that are created, we need to specify which queries to run in the knowledge map. This is done in the same manner as the facts above, however the object is set to null as this is the decision being made and so will be blank until after the query is run.

 var query = {
  subject: person,
  relationship: 'speaks language',
  object: null
};

The following code is what is used to populate the spreadsheet with the results from Rainbird. In this example we are populating the columns with the result from the query that was run above. These results are the Language that Rainbird thinks the person speaks, the certainty of that decision and a link to the evidence tree.

      var speaksCell = sheet.getRange(i+1, 4);
      speaksCell.setValue(queryResult.result[0].object);

      var cfCell = sheet.getRange(i+1, 5);
      cfCell.setValue(queryResult.result[0].certainty);

      var evidence = createEvidenceTreeUrl(queryResult.result[0].factID, sid.id);
      var evidenceCell = sheet.getRange(i+1, 6);
      evidenceCell.setValue('=HYPERLINK("' + evidence + '", "Evidence")');
  }
}

Finally the last section creates the evidence tree link. This section does not require any changes:

function createEvidenceTreeUrl (fact, sid) {
  return 'https://app.rainbird.ai/applications/components/rainbird-analysis-ui/whyAnalysis.html?id='
    + fact
    + '&api=https://api.rainbird.ai&engine=v2.0&sid='
    + sid
}

To find the knowledge map ID, click the dropdown arrow beside the map name and select ‘View Knowledge Map ID” from the options. The KMID will be displayed on screen and can be copied.

To find your Rainbird API key, select Account on the bottom of the left hand side bar of the Rainbird Studio. Here you will see your personal API key, this is unique to you rather than the knowledge map.

Before running the excel sheet the file must be saved (or reloaded).

A ‘Run me’ button will appear in the top row.

To run the script and inject the data from the spreadsheet the user is required to connect the sheet to their google account.

In the above example Certainty would be shown as a number.

If we want to include a percentage symbol, Google Sheets would automatically change our outcome. To keep the output from Rainbird and show the percentage, we need to format the cell.

To do so open ‘Format’, hover over ‘Number’, then ‘More Formats’ and ‘Custom Number Format’.

For the first time, enter the following: ##\%

For further changes in future spreadsheets, the option will be shown in the list: