Manipulate Worksheet from Office Add-In Button

Question

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

Answer 1

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;

Answer 2

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 "protect a worksheet" that wasn't related to running via "taskpane" but simply clicking the button.

An additional couple issues I debugged were that I couldn't get this to work if I picked Excel Custom Functions Add-in project (This is where =CONTOSO namespace comes from that I don't need anyway) it had to be Office Add-in Task Pane project. I also learned to use npm start vs npm run start:desktop as I noticed sometimes it would use production vs development.

Finally, learning how to clear everything out is important, such as going into "trust center" and clicking "next time office starts clear cache" and also deleting everything under --> %LOCALAPPDATA%\Microsoft\Office\16.0\Wef\

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