I am trying to Get the Value of the Active cell in Excel
I was able to get the Value by writing a simple Excel Add-in.
I was also able to do the same using Office-Scripts
Office-Script:
function main(workbook: ExcelScript.Workbook) {
// Get the current active cell in the workbook.
let cell = workbook.getActiveCell();
// Log that cell's value.
console.log(`The current cell's value is ${cell.getValue()}`);
}
Similar code was used for Add-In: Used stock code (example code) which did the same job
Home.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<title></title>
<script src="../Scripts/jquery-3.5.0.js" type="text/javascript"></script>
<script src="../Scripts/MessageBanner.js" type="text/javascript"></script>
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
<!-- To enable offline debugging using a local reference to Office.js, use: -->
<!-- <script src="../Scripts/Office/MicrosoftAjax.js" type="text/javascript"></script> -->
<!-- <script src="../Scripts/Office/1/office.js" type="text/javascript"></script> -->
<script src="Home.js" type="text/javascript"></script>
<link href="Home.css" rel="stylesheet" type="text/css" />
<link href="../Content/Button.css" rel="stylesheet" type="text/css" />
<link href="../Content/MessageBanner.css" rel="stylesheet" type="text/css" />
<!-- For the Office UI Fabric Core, go to https://aka.ms/office-ui-fabric to learn more. -->
<link rel="stylesheet" href="https://static2.sharepointonline.com/files/fabric/office-ui-fabric-core/9.6.0/css/fabric.min.css">
<!-- To enable the offline use of Office UI Fabric Core, use: -->
<!-- link rel="stylesheet" href="../Content/fabric.min.css" -->
<!-- Office UI Fabric JS and it's components are no longer actively supported. Please see https://aka.ms/PnP-OfficeFabricReact for recommended Patterns and Practices -->
<!-- <link rel="stylesheet" href="https://appsforoffice.microsoft.com/fabric/2.1.0/fabric.components.min.css"> -->
</head>
<body class="ms-Fabric" dir="ltr">
<div id="content-main">
<div class="padding">
<br />
<div class="ms-font-xxl ms-fontColor-themeDarkAlt ms-fontWeight-semilight">Welcome</div>
<p class="ms-font-m-plus">This example will read the current document selection.</p>
<button class="Button Button--primary" id="get-data-from-selection">
<span class="Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span>
<span class="Button-label">Get data from selection</span>
<span class="Button-description">Gets the selected data and displays it.</span>
</button>
<br /><br /><br />
<div class="ms-font-m"><a target="_blank" class="ms-Link ms-Link--hero" href="https://go.microsoft.com/fwlink/?LinkId=276813">Find more samples online...</a></div>
</div>
</div>
<!-- Component used for displaying notifications -->
<div class="MessageBanner" style="position:absolute;bottom: 0;">
<div class="MessageBanner-content">
<div class="MessageBanner-text">
<div class="MessageBanner-clipper">
<div class="ms-font-m-plus ms-fontWeight-semibold" id="notificationHeader"></div>
<div class="ms-font-m ms-fontWeight-semilight" id="notificationBody"></div>
</div>
</div>
<button class="MessageBanner-expand" style="display:none"><i class="ms-Icon ms-Icon--chevronsDown"></i> </button>
<div class="MessageBanner-action"></div>
</div>
<button class="MessageBanner-close"> <i class="ms-Icon ms-Icon--ChromeClose"></i> </button>
</div>
</body>
</html>
Home.js
(function () {
"use strict";
var messageBanner;
// The initialize function must be run each time a new page is loaded
Office.initialize = function (reason) {
console.log("Inside Initialization")
$(document).ready(function () {
var element = document.querySelector('.MessageBanner');
messageBanner = new components.MessageBanner(element);
messageBanner.hideBanner();
$('#get-data-from-selection').click(getDataFromSelection);
});
console.log("done Initialization")
};
// Reads data from current document selection and displays a notification
function getDataFromSelection() {
if (Office.context.document.getSelectedDataAsync) {
Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
function (result) {
if (result.status === Office.AsyncResultStatus.Succeeded) {
showNotification('The selected text is:', '"' + result.value + '"');
} else {
showNotification('Error:', result.error.message);
}
}
);
} else {
app.showNotification('Error:', 'Reading selection data is not supported by this host application.');
}
}
// Helper function for displaying notifications
function showNotification(header, content) {
$("#notificationHeader").text(header);
$("#notificationBody").text(content);
messageBanner.showBanner();
messageBanner.toggleExpansion();
}
})();
Now, I am trying to run this script from outside of Excel, similar to VBScript where I can run the script independently outside Excel and get the active workbook, active cell and corresponding value.
I tried running the JS code from outside of excel I am getting this error:
Warning: Office.js is loaded outside of Office client
office.js:46 The add-in is not hosted in plain browser top window.
Ewa.js:1 SendShuttingDownRequest: https://excel.officeapps.live.com/x/RemoteUls.ashx?officeserverversion=16.0.14206.35904&waccluster=GSH7; requestApi: f
Office.js loaded outside of Office Client
Here I'm using Excel in Sharepoint on a browser
I am injecting the JS code to the webpage (Excel Sharepoint page),
JS code calls Office.initialize and getDataFromSelection()(mentioned above) also loaded "https://appsforoffice.microsoft.com/lib/1/hosted/office.js" and tried to get the active cell value of Excel.
But as soon I call Initialize, Excel Sharepoint page will go blank cause of the shutdown (probably cause of this SendShuttingDownRequest).
In my current use case, I would have to use an external script to get data from Excel Sharepoint page, and also I will not be able to use Power Automate to run the scripts.
- Am I doing something wrong in the way of using the JS API?
- Is there a way to use JS APIs to get data out of Excel similar to VB Script?
You cannot use Office.js outside of the context of an add-in. It needs to get information from the Excel host in order to initialize.