Search code examples
google-apps-scriptgoogle-sheetsidegoogle-workspaceadd-on

Creating and testing an add-on for Google Sheets in the new Script Editor

I've created several internal add-ons to automate a few workflows within our company using the "old" Apps Script IDE. Now I'd like to create a new one using the all new Apps Script IDE which was released last december. All looks good and works great, however there's a few things I can't figure out.

First off: using deployments the Google Workspace Marketplace IDE states that "Your deployment ID will identify all the Google Workspace services that your app supports." when using a deploymeny ID. So, I've pasted my deployment ID in there. Just a simple script creating an add-on menu and running a function resulting in an alert:

function onInstall () {
  onOpen()
}

function onOpen () {
  var ui = SpreadsheetApp.getUi()
  var menu = ui.createAddonMenu()

  // Create menu structure
  menu
    .addItem('Create proposal', 'proposalShowPopup')

  // Add menu to UI
  menu.addToUi()
}

function proposalShowPopup() {
  Browser.msgBox('Test')
}

However, when I deploy this, I can see the listing in the marketplace, but I can't install it in the sheets where I need it.

Furthermore, probably because I haven't declared this add-on to be a Sheets add-on, I can't install the test-deployment as mentioned in this article: Testing Google Workspace add-ons by Google. The Install is missing from the Apps Script's Deployments dialog.

I read a lot about the appsscript.json file but can't find any information on what this file should contain other than the following lines of code, applicable to my script:

{
  "timeZone": "Europe/Paris",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

Any ideas on how to create an Sheets only add-on using the new IDE (which works great as mentioned before)?

Any help or nudges in the right direction would be appreciated.

Solution

  • The reason you cannot install the add-on in Sheets is essentially because you are not declaring in your manifest anything about the script being an add-on.

    Since you want it to be specifically used in Sheets, the manifest appscript.json file should look something similar to this:

    {
  "timeZone": "Europe/Paris",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Sheets",
        "version": "v4",
        "serviceId": "sheets"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "addOns": {
    "sheets": {
      "homepageTrigger": {
        "runFunction": "proposalShowPopup"
      }
    }
  }
}

    Notice that the addOns parameter has been added as well as with the sheets paramater.

    The addOns parameter has the following structure and it represents the configuration that is used to define the add-on's content and behavior.

    {
  "common": {
    object (Common)
  },
  "calendar": {
    object (Calendar)
  },
  "drive": {
    object (Drive)
  },
  "gmail": {
    object (Gmail)
  },
  "docs": {
    object (Docs)
  },
  "sheets": {
    object (Sheets)
  },
  "slides": {
    object (Slides)
  }
}

    As for the sheets parameter it has the below structure - which is similar to the one most of the parameters from the addOns have; however, since you mention it as being sheets this will clearly indicate for which of these add-on types is meant for.

    {
  "homepageTrigger": {
    object (HomepageTrigger)
  },
  "onFileScopeGrantedTrigger": {
    object (OnFileScopeGrantedTrigger)
  }
}

    After setting your manifest file accordingly, you should be able to see the Install button pop up when going to Test Deployments:

    add on install

    Reference