BlipAI's web based apps require the user to learn the user interface that is designed based on original requirements. Despite patches and add-ons they can quickly become obsolete in a organization that uses spreadsheets that change often based on the structure of data and operational needs.
Traditional spreadsheets are not powerful enough to integrate data directly with backend data sources or make heavy computations.
With Google Sheets Appscript extensions and BlipAI's supply chain APIs, large enterprises can now have intelligent and aware spreadsheets that can directly communicate with cloud resources and provide features similar to custom built webapps. This helps enterprises avoid commiting to monolithic apps that are usually upgraded at a very slow pace.
Scripting with Google Sheets does require some fluency with Javascript programming. BlipAI's APIs are designed to be language agnostic and follow the REST principles of API design.
/* Example call from Appscript (Google Sheets) */
const door = blip_find_best_door(order_number);
console.log("Best door found", door);
Requests are secure and authenticated using an API key and a facility ID
var wh_id = 'test_proctergamble_houston'
var api_key = 'replace-with-api-key-provided-by-blip'
var url = 'https://api.blipai.com/api_v1/sheets'
var options = {
'method': 'GET',
"contentType": "application/json",
'headers': {
'authorization': "Bearer " + api_key,
'x-blip-wh-id': wh_id
}
};
var ret
try {
var endpoint = `${url}/get_req_door/${reqno}`
var response = UrlFetchApp.fetch(endpoint, options);
var json = JSON.parse(response.getContentText())
ret = json.assigned_door
if (!ret) ret = 'Assign Door at app.blipai.com'
} catch (e) {
console.log(e)
ret = 'NOT RUN' + JSON.stringify(e)
}
return ret
Triggers > Add Trigger
Avoid creating triggers directly to the API functions. Instead route all call logic through a custom function. In the following example whenEdit() is the custom handler for all cell edit events. The event object is passed to the function on each trigger.
Google may prompt you to grants permissions to run external http commands.
There are several disadvantages to wrap APIs into gsheet formula
A call is made every time the spreadsheet refreshes. This means a column of 100 rows can quickly become 1000s of calls depending on the activity
Data may be archived / removed from the backend database in which case data will not available to the ghseet for future reference or reporting
Heavy computation in scripts could cause the sheet to become less responsive
Each call to whenEdit is logged under Executions section. Messages logged with console.log may take some time to appear after each event is created. Try the Refresh link until the logs are visible.
/*
BlipAI API endpoints
*/
var wh_id = 'test_proctergamble_houston'
var api_key = 'replace-with-your-api-key'
var url = 'https://us-central1-blipai.cloudfunctions.net/api_v1/sheets'
/**
* Submit mbol to be optimized
* POST
*/
function blip_submit_mbol(mbol) {
if (!mbol || mbol.length <= 0) return
var options = {
'method': 'post',
'contentType': "application/json",
'headers': {
'authorization': "Bearer " + api_key,
'x-blip-wh-id': wh_id
},
'payload': JSON.stringify({ mbol: mbol })
};
try {
var endpoint = `${url}/submit_mbol`
var response = UrlFetchApp.fetch(endpoint, options);
} catch (e) {
return 'error ' + e.message
}
return 'Sent to Blip'
}
/**
* Get staging lane assigned to reqno
* GET
*/
function blip_get_req_door(reqno) {
var options = {
'method': 'get',
"contentType": "application/json",
'headers': {
'authorization': "Bearer " + api_key,
'x-blip-wh-id': wh_id
}
};
var ret
try {
var endpoint = `${url}/get_req_door/${reqno}`
var response = UrlFetchApp.fetch(endpoint, options);
var json = JSON.parse(response.getContentText())
ret = json.assigned_door
if (!ret) ret = 'Assign Door at app.blipai.com'
} catch (e) {
console.log(e)
ret = 'NOT RUN' + JSON.stringify(e)
}
return ret
}
/**
* Release a staging lane
* POST
*/
function blip_unblock_lane(stage) {
var options = {
'method': 'post',
"contentType": "application/json",
'headers': {
'authorization': "Bearer " + api_key,
'x-blip-wh-id': wh_id
},
'payload': JSON.stringify({ stage: stage })
};
try {
var endpoint = `${url}/unblock_lane`
var response = UrlFetchApp.fetch(endpoint, options);
} catch (e) {
return 'error ' + e.message
}
return 'Closed'
}
/**
* Sample code to handle edits on the spreadsheet.
*
* WARNING: All events that submit data to Blip (HTTP POST requests) *must* use onEdit.
* Triggering an API call from a formula in a cell will cause POST requests to be
* duplicated any time the sheet refreshes.
*
* This method also ensures integrity and permanence of the data that is not
* dependent upon a formula. Remove the dependency on the server where not needed
* such that it doesn't affect your gsheet when data is removed from the backend
*
*/
function whenEdit(e) {
var range = e.range
var col_edited = range.getColumn()
var row_edited = range.getRow()
var cell_value = range.getValue()
// cells to read
var mbol = SpreadsheetApp.getActiveSheet().getRange(row_edited, 7).getValue() // Col G
var req_no = SpreadsheetApp.getActiveSheet().getRange(row_edited, 5).getValue() // Col E
var stage = SpreadsheetApp.getActiveSheet().getRange(row_edited, 17).getValue() // Col Q
// cells to update
const B = SpreadsheetApp.getActiveSheet().getRange(row_edited, 2) // progress column
const C = SpreadsheetApp.getActiveSheet().getRange(row_edited, 3) // status column C
const Q = SpreadsheetApp.getActiveSheet().getRange(row_edited, 17) // staging lane
console.log('edited', 'row:', row_edited, 'column:', col_edited, 'mbol:', mbol, 'req:', req_no)
var response
switch (col_edited) {
case 3: // Column C
B.setValue('Working...')
switch (cell_value) {
case 'A':
console.log('submitting mbol', mbol)
B.setValue(blip_submit_mbol(mbol))
break
case 'C':
console.log('unblocking lane', stage)
response = blip_unblock_lane(stage)
console.log('unblock resp', response)
B.setValue(response)
break
case 'N':
B.clearContent()
break
}
break
case 14: // Column N
Q.setValue('Checking..')
if (C.getValue() == 'A' && cell_value == true) {
console.log('getting lane for req#', req_no)
response = blip_get_req_door(req_no)
console.log('got lane', response)
Q.setValue(response)
} else if (C.getValue() == 'N' && cell_value == true) {
Q.setValue('Allocate MBOL')
} else {
Q.clearContent()
}
break
}
return
}
Have specific API requirements that you would like to use in yor organisation? Email us support@blipai.com