Custom Dialog Box for Google Sheets (using Apps Script)

I share a lot of spreadsheets. Most have a limited lifespan, and it’s not easy to ensure everyone has the latest version. Whenever a document is deprecated, I say so in the title but it could easily be missed. Making the doc unavailable is another alternative but leaves the end-user with no real solution.

In Google Sheets, the best way to manage your outdated sheet and re-route the user to the most up-to-date one is a dialog box.

Not to be confused with the MsgBox, which is also useful and really easy to implement, it unfortunately doesn’t provide the option of adding a link.

See also: MsgBox (Google Sheets documentation)

An HTML message can be created using showModalDialog

Here’s the App Script for the example shown above:

function onOpen(e) {
  myAlert()
}

function onSelectionChange(e) {
  myAlert()
}

function myAlert(){
var htmlOutput = HtmlService
    .createHtmlOutput('<p>The new version is available <a href="{URL}" target="_blank">here</a></p>')
    .setWidth(250)
    .setHeight(200);
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'This version of the document is deprecated');
}

It triggers the dialog box when the page loads, and again if you’re trying to modify it (in case it was already opened).

You should reload the tab after you save the script. Testing it without reloading might throw you an “unknown error” message.

Was this helpful?

YesNo

0 / 0