I'm just getting into Office.JS and I'm trying to perform a basic action of clicking a button in the Ribbon UI and doing something on the spreadsheet. All the examples I found related to clicking a cell and using the =CONTOSO... to perform functions that return variables. Not a single one of my macro's works in this fashion, they either have a UserForm for input, or just "do" something.
I've managed to create a new tab in the ribbon, with a button that should call a function getdata which really isn't a function, but more a module/sub that I simply want to enter FOO in cell A1.
Here are my changes to the default setup that I got running yo office.
manifest.xml:
<ExtensionPoint xsi:type="PrimaryCommandSurface">
<CustomTab id="TabCustom1">
<!-- <OfficeTab id="TabData"> -->
<Label resid="TabGroup.Label"/>
<Group id="CommandsGroup">
<Label resid="CommandsGroup.Label"/>
<!-- Can only use 1, or default = Far Right
<InsertAfter>TabReview</InsertAfter>
<InsertBefore>TabReview</InsertBefore>
-->
<Icon>
<bt:Image size="16" resid="Icon.16x16"/>
<bt:Image size="32" resid="Icon.32x32"/>
<bt:Image size="80" resid="Icon.80x80"/>
</Icon>
<Control xsi:type="Button" id="TaskpaneButton">
<Label resid="TaskpaneButton.Label"/>
<Supertip>
<Title resid="TaskpaneButton.Label"/>
<Description resid="TaskpaneButton.Tooltip"/>
</Supertip>
<Icon>
<bt:Image size="16" resid="Icon.16x16"/>
<bt:Image size="32" resid="Icon.32x32"/>
<bt:Image size="80" resid="Icon.80x80"/>
</Icon>
<Action xsi:type="ShowTaskpane">
<TaskpaneId>ButtonId1</TaskpaneId>
<SourceLocation resid="Taskpane.Url"/>
</Action>
</Control>
<Control xsi:type="Button" id="DoButton">
<Label resid="DoButton.Label"/>
<Supertip>
<Title resid="DoButton.Label"/>
<Description resid="DoButton.Tooltip"/>
</Supertip>
<Icon>
<bt:Image size="16" resid="Icon.16x16"/>
<bt:Image size="32" resid="Icon.32x32"/>
<bt:Image size="80" resid="Icon.80x80"/>
</Icon>
<Action xsi:type="ExecuteFunction">
<FunctionName>getData</FunctionName>
</Action>
</Control>
</Group>
<!-- </OfficeTab> -->
</CustomTab>
</ExtensionPoint>
...
<bt:ShortStrings>
<bt:String id="Functions.Namespace" DefaultValue="CONTOSO"/>
<bt:String id="CommandsGroup.Label" DefaultValue="Group Label"/>
<bt:String id="GetStarted.Title" DefaultValue="Get started with your sample add-in!"/>
<bt:String id="TaskpaneButton.Label" DefaultValue="TaskPane Button label"/>
<bt:String id="DoButton.Label" DefaultValue="Do Button label"/>
<bt:String id="TabGroup.Label" DefaultValue="Custom Tab"/>
</bt:ShortStrings>
<bt:LongStrings>
<bt:String id="GetStarted.Description" DefaultValue="Your sample add-in loaded succesfully. Go to the Custom Tab and click the 'Button label' button to get started."/>
<bt:String id="TaskpaneButton.Tooltip" DefaultValue="Click to Show a Taskpane"/>
<bt:String id="DoButton.Tooltip" DefaultValue="Click to Run A Function"/>
</bt:LongStrings>
I'm unsure where to even add this function, I messed around in functions.js but again, this all seems to be geared towards entering =FUNCNAME in a cell. Can anyone point me in the right direction?
Thanks
CodePudding user response：
Finally got it! Only took hours of reading, I can't figure why they don't start with something simple like this, but the doc's start w/ making a table via a taskpane, filtering table, freezing header row and then finally at the bottom I found a portion called 
Here is my basic instructions for inserting "hello world" into cell A1 via clicking a button in the ribbon via Office.JS.
yo office
Choose a project type: Office Add-in Task Pane project
Choose a script type: JavaScript
What do you want to name your add-in? My Office Add-in
Which Office client application would you like to support? Excel
manifest.xml
Insert After end of existing <Control></Control>
<Control xsi:type="Button" id="HelloWorldButton">
<Label resid="HelloWorld.Label"/>
<Supertip>
<Title resid="HelloWorld.Label"/>
<Description resid="HelloWorld.Tooltip"/>
</Supertip>
<Icon>
<bt:Image size="16" resid="Icon.16x16"/>
<bt:Image size="32" resid="Icon.32x32"/>
<bt:Image size="80" resid="Icon.80x80"/>
</Icon>
<Action xsi:type="ExecuteFunction">
<FunctionName>helloworld</FunctionName>
</Action>
</Control>
....
<bt:String id="HelloWorld.Label" DefaultValue="Ribbon helloworld Func" />
...
<bt:String id="HelloWorld.Tooltip" DefaultValue="Click to run helloworld func" />
commands.js
Insert After function action(event) closing }
function helloworld(args) {
Excel.run(function (context) {
var ws = context.workbook.worksheets.getActiveWorksheet();
var range = ws.getRange("A1");
range.values = "Hello World!";
range.select();
return context.sync();
}).catch(function (error) {
console.log("Error: " error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " JSON.stringify(error.debugInfo));
}
});
args.completed();
}
at bottom of doc:
g.helloworld = helloworld;
Test:
npm start
CodePudding user response：
The FunctionFile element specifies a file that contains JavaScript code to run when an add-in command uses the ExecuteFunction action. The FunctionFile element's resid attribute is set to a HTML file that includes all the JavaScript files your add-in commands require. You can't link directly to a JavaScript file. You can only link to an HTML file. The file name is specified as a Url element in the Resources element. For example:
<DesktopFormFactor>
<FunctionFile resid="residDesktopFuncUrl" />
<ExtensionPoint xsi:type="PrimaryCommandSurface">
<!-- information about this extension point -->
</ExtensionPoint>
<!-- You can define more than one ExtensionPoint element as needed -->
</DesktopFormFactor>
If your function is still not available when you click on a ribbon button, most probably you need to make it visible for external callers in the following way (how I do that for Outlook add-ins):
function getGlobal() {
return typeof self !== "undefined"
? self
: typeof window !== "undefined"
? window
: typeof global !== "undefined"
? global
: undefined;
}
const g = getGlobal() as any;
// The add-in command functions need to be available in global scope
g.action = action;