Background
Imagine you have a developer portal based on the Apigee Integrated Portal, and you have your APIs published there for integration. However some of your customers are not ready to integrate with APIs, and it would be helpful for them to be able to upload an Excel file of their data into the portal, and use their credentials to convert and pass the data from the Excel sheet to the API. This article describes how to realize this requirement using the Apigee integrated portal.
Portal configuration
To enable this feature, we will need to add a new Page in the integrated portal with the instructions and button to upload the Excel file.
Here is the content for the Upload page, including instructions, a file input, a table element to display the uploaded data, and a submit button.
**Upload File**
Steps to upload your data as Excel File to the stock API.
1. The first time you would like to upload, create an app subscription to the product "Stock Upload" under Apps. This only has to be done once.
2. Now you can upload your excel file here and the contents will be displayed below.
3. If the contents are correct then press "Submit."
<input type="file" id="file-input" />
<table>
<thead><tr><th>Part number</th><th>Stock</th><th>Comment</th></tr></thead>
<tbody id="tbody"></tbody>
</table>
<button mat-raised-button id="submitButton">
Submit
</button>
How we will also need to add a script to the integrated portal to do the upload, conversion and fetch to our API.
Here is the full script code. It uses the amazing SheetJS library to do the Excel to JSON conversion. It also fetches the user’s apps to get the credential (API key) that should be used with an appropriate product to submit the data.
<script src="https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js"></script>
<script>
var productName = "deepmind_alphafold_1e3h";
var apiKey = "";
var objectData = [];
var apiEndpoint = "https://34-8-196-4.nip.io/v1/stock";
window.portal = {};
window.portal.pageEventListeners = {
onLoad: (path) => {
if (path === '/upload') {
document.getElementById('file-input').addEventListener('change', readSingleFile, false);
var tableBody = document.getElementById("tbody");
var submitButton = document.getElementById("submitButton");
submitButton.onclick = submitData;
fetch("/consumers/api/apps").then((response) => {
if (response.status == 200) return response.json();
}).then((data) => {
console.log(data);
if (data && data.data) {
for (let app of data.data) {
for (let product of app.apiProducts) {
if (product.apiproduct == productName) {
if (app.credentials && app.credentials.length > 0) {
apiKey = app.credentials[0].consumerKey;
console.log(apiKey);
break;
}
}
}
if (apiKey) break;
}
}
});
}
return undefined;
},
onUnload: (path, contextReturnedFromOnLoad) => {
},
};
function readSingleFile(e) {
var file = e.target.files[0];
if (!file) {
return;
}
var reader = new FileReader();
reader.onload = function(e) {
var contents = e.target.result;
const workbook = XLSX.read(contents);
if (workbook.SheetNames && workbook.SheetNames.length > 0) {
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
const raw_data = XLSX.utils.sheet_to_json(worksheet, {header: 1});
console.log(raw_data);
if (raw_data && raw_data.length > 1) {
for (var i=1; i < raw_data.length; i++) {
var newRow = {};
const row = document.createElement("TR");
for (var rowIndex = 0; rowIndex < raw_data[0].length; rowIndex++) {
newRow[raw_data[0][rowIndex]] = raw_data[i][rowIndex];
row.innerHTML += `<td>${raw_data[i][rowIndex]}</td>`;
}
objectData.push(newRow);
tbody.appendChild(row);
}
}
console.log(objectData);
} else {
console.log("No data found in uploaded file");
}
};
reader.readAsArrayBuffer(file);
}
function submitData() {
fetch(apiEndpoint, {
method: "POST",
headers: {
"x-api-key": apiKey
},
body: JSON.stringify(objectData)
}).then((response) => {
if (response.status == 200) {
window.location.href = "/";
} else {
alert("The data could not be submitted. Error code: " + response.status + ". Please contact support.");
}
});
}
</script>
API configuration
Now that we have the portal ready, let’s configure an API proxy to receive the data. It will do API key verification based on the API key that the portal used to submit the data, as well as CORS validation to allow for the browser fetch call.
Here is the Apigee proxy in YAML format using aft.
name: stock
displayName: ""
type: proxy
categories: []
description: stock
parameters: []
endpoints:
- name: default
basePath: /v1/stock
routes:
- name: default
flows:
- name: PreFlow
mode: Request
steps:
- name: CORS-SetCors
- name: VA-VerifyKey
faultRules: []
targets: []
policies:
- name: CORS-SetCors
type: CORS
content:
CORS:
_attributes:
continueOnError: "false"
enabled: "true"
name: CORS-SetCors
DisplayName:
_text: CORS-SetCors
AllowOrigins:
_text: "{request.header.origin}"
AllowMethods:
_text: GET, PUT, POST, DELETE
AllowHeaders:
_text: "*"
ExposeHeaders:
_text: "*"
MaxAge:
_text: "3628800"
AllowCredentials:
_text: "false"
GeneratePreflightResponse:
_text: "true"
IgnoreUnresolvedVariables:
_text: "true"
- name: VA-VerifyKey
type: VerifyAPIKey
content:
VerifyAPIKey:
_attributes:
continueOnError: "false"
enabled: "true"
name: VA-VerifyKey
DisplayName:
_text: VA-VerifyKey
Properties: {}
APIKey:
_attributes:
ref: request.header.x-api-key
resources: []
tests: []
And of course we have an API product and portal catalog entry for the Stock API as well, so that the user can subscribe and get a key.
Conclusion
Now that we have everything configured, we have this file upload option in our integrated portal.
Here is my test Excel file with some stock data.
Now after uploading it, it is converted to a JSON object and added to the table in the page.
Then the user can submit the data. If the user has a subscription to the API, and the submission is successful (200), the user is redirected to the portal home page.
It’s amazing what’s possible with small extensions to the Apigee integrated portal, feel free to reach out in the comments with any questions.