Injecting Data – Importing knowledge from google spreadsheet

Injecting knowledge from Google Sheets

Knowledge maps do not require an end user to directly input data to run a query. Large amounts of data can be injected into from Google Sheets into a knowledge map, allowing Rainbird to run multiple queries simultaneously.

Note: The Evidence Security needs to be switched off (to the left) to allow viewing the Evidence Tree in a Google Spreadsheet cell.

To demonstrate how Rainbird can use data from a Google Sheet to complete a query, this article will explain how to build a “Hello World” map that uses data injected from a Google Sheet. The “Hello World” map will make a decision on what language a person may speak, based on where they were born, and the country they currently live in.

Building a “Hello World” map with a Google Sheets injection

If we were to run the query ‘PERSON speaks LANGUAGE’ in a Rainbird agent, Rainbird would ask the end-user:

1 – What is your name?

2 – What Country were you born in?

3 – What Country do you live in?

The answers to these questions are what needs to be injected into the Knowledge Map.

The Data may look something like the example below:

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. 

Log into or create an account: https://www.google.co.uk/sheets/about/

Open a ‘blank’ sheet and populate data as required (in our example ‘Hello World’).

Select Extensions => Apps Script.

There are 2 script files that are required, one that is generic to all google sheet integrations and one that is tailored to each.

Select the ‘+’ symbol on the top left side and select ‘Script’. Call this script file ‘Rainbird’. In this file copy and paste the following code:

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:

Click on the ‘Export.rbird’ button to download the ‘Rule Types’ map used in this example. The knowledge map can then be imported into your Rainbird Studio.

Query and results

The non-specific rule sits on the ‘is eligible for’ relationship.

The object-specific rule also sits on the ‘is eligible for’ relationship.

For an example of a query using facts, run the ‘is eligible for query’and enter ‘Pete’ as your subject.

Article Feedback form
Did you find this article useful?

Version 1.02 – Last Update: 31/05/2022