Embedded Files
BlipAI's API allows programmatic submission` of shipment information to calculate the optimal staging lane in your warehouse. API's allow automation of many of these processes that otherwise require user intraction with a web app. Google's AppScript provides the tools to enable the design of powerful workflows directly within your spreadsheet.
Google Sheet Setup
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.
Important Considerations When Calling APIs
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
Sample Code
To view the javascript interface, on the Google Sheet menu, go to Extensions > AppScript
The sample code demonstrates a 3-step automated process that can be achieved with the API. Your API specification may vary
/*
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
}
Debugging And Execution Logs
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.
Google Sites
Report abuse