google-apps-script google-sheets google-workspace-add-ons

Hello world menu and sidebar for Google Workspace Add-On for Google Sheets

I am trying to get started with a "Hello World" implementation of a menu that loads a sidebar for a Google Workspace Add-On for Google Sheets.

When I open a new Google Sheet, I expect to see a menu appear under Extensions > MyApp > Start. And when I click the item labeled "Start" in the dropdown menu, I expect to see a sidebar open on the right that reads "Hello World."

Instead of that, what I actually see is when I open a new Google Sheet, I see no MyApp item in the Extensions dropdown menu. It's as if my test deployment was never loaded.

The below code is contained in a standalone script file (as required to make a Workspace Add-on).

In addition to the code I shared below, I also followed the documentation at this link and did the following steps necessary to create a test deployment.

What am I missing or doing wrong?

https://developers.google.com/apps-script/add-ons/cats-quickstart#drive.gs

Create the Apps Script project

To create a new Apps Script project, go to script.new. Click Untitled project.
Rename the Apps Script project Cats and click Rename.
Next to the Code.gs file, click More more_vert > Rename.
Name the file Common. Click Add a file add > Script.
Name the file Gmail.
Repeat step 5 to create 2 more script files named Calendar and Drive. When you're done you should have 4 separate script files.
Click Project Settings The icon for project settings.
Check the Show "appsscript.json" manifest file in editor box.
Click Editor code. 10.Open the appsscript.json file and replace the contents with the following code, then click Save Save icon.

Copy the Cloud project number

Go to your Cloud project in the Google Cloud console.
Click Settings and Utilities more_vert > Project settings.
Copy the Project number.

Set the Apps Script project's Cloud project

In your Apps Script project, click Project Settings The icon for project settings.
Under Google Cloud Platform (GCP) Project, click Change project.
In GCP project number, paste the Google Cloud project number.
Click Set project.

Install a test deployment

In your Apps Script project, click Editor code.
Open the Common.gs file and click Run. When prompted, authorize the script.
Click Deploy > Test deployments.
Click Install > Done.

Code.js

const ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen( e ) {
  ui.createAddonMenu()
    .addItem( 'Start', 'showSidebar', )
    .addToUi();
  
  const menuItems = [];
  menuItems.push({ name: 'Start' , functionName: 'showSidebar' });
  ss.addMenu( 'MyApp', menuItems, );
}

function showSidebar() {
  // code TBD
  // right now, I'm just trying to get the menu to appear
}

Sidebar.html

<html>
  <body>
    <div>Hello World</div>
  </body>
</html>

appsscript.json


{
  "timeZone":"America/Los_Angeles",
  "dependencies":{
    
  },
  "exceptionLogging":"STACKDRIVER",
  "runtimeVersion":"V8",
  "addOns":{
    "calendar":{
      "createSettingsUrlFunction":"getConferenceSettingsPageUrl",
      "conferenceSolution":[
        {
          "id":"my-video-conf",
          "logoUrl":"https://lh3.googleusercontent.com/...",
          "name":"My Video Conference",
          "onCreateFunction":"onCreateMyVideoConference"
        },
        {
          "id":"my-streamed-conf",
          "logoUrl":"https://lh3.googleusercontent.com/...",
          "name":"My Streamed Conference",
          "onCreateFunction":"onCreateMyStreamedConference"
        }
      ],
      "currentEventAccess":"READ_WRITE",
      "eventOpenTrigger":{
        "runFunction":"onCalendarEventOpen"
      },
      "eventUpdateTrigger":{
        "runFunction":"onCalendarEventUpdate"
      },
      "eventAttachmentTrigger":{
        "label":"My Event Attachment",
        "runFunction":"onCalendarEventAddAttachment"
      },
      "homepageTrigger":{
        "runFunction":"onCalendarHomePageOpen",
        "enabled":true
      }
    },
    "common":{
      "homepageTrigger":{
        "runFunction":"onDefaultHomePageOpen",
        "enabled":true
      },
      "layoutProperties":{
        "primaryColor":"#ff392b",
        "secondaryColor":"#d68617"
      },
      "logoUrl":"https://ssl.gstatic.com/docs/script/images/logo/script-64.png",
      "name":"Demo Google Workspace Add-on",
      "openLinkUrlPrefixes":[
        "https://mail.google.com/",
        "https://script.google.com/a/google.com/d/",
        "https://drive.google.com/a/google.com/file/d/",
        "https://en.wikipedia.org/wiki/",
        "https://www.example.com/"
      ],
      "universalActions":[
        {
          "label":"Open settings",
          "runFunction":"getSettingsCard"
        },
        {
          "label":"Open Help URL",
          "openLink":"https://www.example.com/help"
        }
      ],
      "useLocaleFromApp":true
    },
    "drive":{
      "homepageTrigger":{
        "runFunction":"onDriveHomePageOpen",
        "enabled":true
      },
      "onItemsSelectedTrigger":{
        "runFunction":"onDriveItemsSelected"
      }
    },
    "gmail":{
      "composeTrigger":{
        "selectActions":[
          {
            "text":"Add images to email",
            "runFunction":"getInsertImageComposeCards"
          }
        ],
        "draftAccess":"METADATA"
      },
      "contextualTriggers":[
        {
          "unconditional":{
            
          },
          "onTriggerFunction":"onGmailMessageOpen"
        }
      ]
    },
    "docs":{
      "homepageTrigger":{
        "runFunction":"onEditorsHomepage"
      },
      "onFileScopeGrantedTrigger":{
        "runFunction":"onFileScopeGrantedEditors"
      }
    },
    "sheets":{
      "homepageTrigger":{
        "runFunction":"onOpen"
      },
      "onFileScopeGrantedTrigger":{
        "runFunction":"onFileScopeGrantedEditors"
      }
    },
    "slides":{
      "homepageTrigger":{
        "runFunction":"onEditorsHomepage"
      },
      "onFileScopeGrantedTrigger":{
        "runFunction":"onFileScopeGrantedEditors"
      }
    }
  }
}

Solution

From the question:

When I open a new Google Sheet, I expect to see a menu appear under Extensions > MyApp > Start.

Strictly speaking, this is not possible with Google Workspace Add-ons. To do this, you should create an Editor Add-on.

From Add-on Types

Workspace Add-ons

Google Workspace Add-ons are the latest generation of add-ons, and provide many capabilities, including: ...

Use standardized interfaces: Construct user interfaces from built-in widget elements provided by the Apps Script Card service. You don't need any expertise with HTML or CSS to define these interfaces.

Editor Add-ons

Editor Add-ons behave differently from Google Workspace Add-ons in the following ways:
...

Editor Add-ons can create interfaces consisting of menu items, dialogs, and sidebars. Add-ons dialogs and sidebars are defined using standard HTML and CSS.

It's worth noting that you could have both types of add-ons in the same Apps Script project. The way to test each of them varies a bit.

When publishing the add-on, you might use a single Cloud Platform project and have a single Workspace Marketplace listing.

However, the custom menu can't be included if you switch to another runtime. The Google Workspace Add-on API could get a JSON with card design but not for the custom menus. The Google Sheets API, as well as the other editors' API, doesn't include methods to create user interfaces like a custom menu.

Resources