Trouble with Google Sheets script for opening URL in overlay

Other Questions Google Sheets
1

I’m having issues with a script in my Google Sheets. I want to show a URL in an overlay when I click a button. The script runs but doesn’t open the overlay window. Here’s what I’m working with:

function openTaskForm() {
  var btn = CardService.newTextButton()
    .setText('Open Task Form')
    .setOpenLink(CardService.newOpenLink()
      .setUrl('https://example.com/task-form')
      .setOpenAs(CardService.OpenAs.OVERLAY)
      .setOnClose(CardService.OnClose.RELOAD_ADD_ON));
}

My manifest file looks like this:

{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "urlFetchWhitelist": ["https://example.com/task-form"],
  "openLinkUrlPrefixes": ["https://example.com/task-form"]
}

Can anyone spot what’s wrong or suggest a fix? Thanks!

2

I encountered a similar problem when working on a project for my company. The issue you’re facing is likely related to how the function is being called and integrated into your Sheets environment.

From what I can see, you’ve defined the function correctly, but it’s not clear how you’re triggering it. Make sure you’re connecting this function to a UI element like a custom menu item or a drawing shape.

Here’s a quick example of how I set up a custom menu to call a similar function:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Custom Menu')
    .addItem('Open Task Form', 'openTaskForm')
    .addToUi();
}

This creates a menu item that, when clicked, will call your openTaskForm function. Also, double-check that your script is deployed as an Add-on and not just a bound script.

If you’re still having trouble after trying these suggestions, you might want to look into using the HTML Service instead of Card Service for more flexibility in creating custom interfaces.

3

I’ve dealt with similar overlay issues in Google Sheets scripts. One thing that stands out is that you’re creating a button, but not actually returning or using it anywhere. Try modifying your function to return a card with the button:

function openTaskForm() {
  var card = CardService.newCardBuilder();
  var btn = CardService.newTextButton()
    .setText('Open Task Form')
    .setOpenLink(CardService.newOpenLink()
      .setUrl('https://example.com/task-form')
      .setOpenAs(CardService.OpenAs.OVERLAY)
      .setOnClose(CardService.OnClose.RELOAD_ADD_ON));
  card.addSection(CardService.newCardSection().addWidget(btn));
  return card.build();
}

This should create a card with your button that can be displayed in the add-on. Make sure you’re also calling this function at the right time, like in response to a user action or menu selection.

4

hey there amelial, i’ve run into similar issues before. looks like ur missing the return statement for the button in ur function. try adding ‘return btn;’ at the end of openTaskForm(). that should do the trick! let me know if it works for ya