Problem with action function in commands.js from Excel add-in tutorial

Question

I have been trying to learn the basics for developing an Office add-in for Excel using the tutorial Microsoft provides: Excel Add-in Tutorial. My primary goal was to learn how to execute functions directly from a button on the ribbon. So, I skipped down to the Protect a worksheet sample in the tutorial.

I'm fairly confident I followed the instructions exactly (and even directly pasted the provided code later), but I could not invoke the toggleProtection function in commands.js based off the provided instructions. I spent countless hours debugging and researching the problem. Finally, I overcame the issue by moving the function and accompanying Office.actions.associate() line above the action function in commands.js. The action function is autogenerated when using Yeoman to create the Excel add-in, and the tutorial explicitly states, "Add the following function immediately after the action function."

I noticed the action function uses Office.MailboxEnums, which seems to be specific to the outlook package. So, is the action function supposed to exist for an Excel add-in? If the action function is intended and functional, does anyone know why there would be an issue with the toggleProtection function existing below the action function? Any clarification about this problem would be greatly appreciated. I've pasted the commands.js code below. Thanks!

/*
 * Copyright (c) Microsoft Corporation. All rights reserved. Licensed under the MIT license.
 * See LICENSE in the project root for license information.
 */

/* global global, console, Excel, Office, OfficeExtension, self, window */

Office.onReady(() => {
  // If needed, Office.js is ready to be called
  console.log("We're in commands.js!!!");
});

async function toggleProtection(args) {
  await Excel.run(async (context) => {
    console.log("we've entered toggleProtection!!!");
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    sheet.load("protection/protected");

    await context.sync();

    if (sheet.protection.protected) {
      sheet.protection.unprotect();
    } else {
      sheet.protection.protect();
    }

    await context.sync();
  }).catch(function (error) {
    console.log("Error: " + error);
    if (error instanceof OfficeExtension.Error) {
      console.log("Debug info: " + JSON.stringify(error.debugInfo));
    }
  });
  args.completed();
}

Office.actions.associate("toggleProtection", toggleProtection);

/**
 * Shows a notification when the add-in command is executed.
 * @param event {Office.AddinCommands.Event}
 */
function action(event) {
  const message = {
    type: Office.MailboxEnums.ItemNotificationMessageType.InformationalMessage,
    message: "Performed action.",
    icon: "Icon.80x80",
    persistent: true,
  };

  // Show a notification message
  Office.context.mailbox.item.notificationMessages.replaceAsync("action", message);

  // Be sure to indicate when the add-in command function is complete
  event.completed();
}

function getGlobal() {
  return typeof self !== "undefined"
    ? self
    : typeof window !== "undefined"
    ? window
    : typeof global !== "undefined"
    ? global
    : undefined;
}

const g = getGlobal();

// The add-in command functions need to be available in global scope
g.action = action;

Since you got it to work, I think is really an issue with the documentation (and a problem with Outlook code being included in an Excel project created with the Yeoman generator). I recommend that you create an issue in the repo for the documentation: https://github.com/OfficeDev/office-js-docs-pr/issues/new — Rick Kirkham, Aug 09 '22 at 21:17

score 0 · Answer 1 · answered Aug 12 '22 at 17:11

If that helps, here's how an add-in I developed handles protecting a column:

function templateLoader() {
    return Excel.run(function (context) {
        // store the sheet and worksheet list for use later
        sheet = context.workbook.worksheets.getActiveWorksheet();

        //reset the hidden status of the spreadsheet
        var range = sheet.getRangeByIndexes(0, 0, 1, 1000);
        range.columnHidden = false;

        var worksheets = context.workbook.worksheets;
        worksheets.load('items');

        return context.sync()
            .then(function () {

                // now we can add data validation
                // the easiest hack way to not allow any entry into the cell is to make sure its text length can only be zero
                range.dataValidation.clear();
                var textLengthZero = {
                    textLength: {
                        formula1: 0,
                        operator: Excel.DataValidationOperator.equalTo
                    }
                };
                range.dataValidation.rule = textLengthZero;

                range.dataValidation.prompt = {
                    message: "This is a protected field and not user editable.",
                    showPrompt: true,
                    title: "No entry allowed."
                };
                range.dataValidation.errorAlert = {
                    message: "Sorry, this is a protected field",
                    showAlert: true,
                    style: "Stop",
                    title: "No entry allowed"
                };

            })
            .catch(/*fail quietly*/);
    })
}

It basically prevents users from adding any text to it.

Problem with action function in commands.js from Excel add-in tutorial

1 Answers1