0

How to Dynamically Update a Sheets Custom Menu with Typescript

I have implemented the following: Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script a sample script for dynamically updating the custom menu of Google Spreadsheet using Google Apps Script.

but in Typescript there is an exception when clicking the menu item: Script function not found: Col1

Is there something else that is required with typescript?

Main.ts

import { CustomMenu } from './ui/CustomMenu'

function onOpen() {
  CustomMenu.createMenu()
}

Main.gs

// Compiled using ts2gas 3.6.5 (TypeScript 4.3.2)
var exports = exports || {};
var module = module || { exports: exports };
//import { CustomMenu } from './ui/CustomMenu'
function onOpen() {
    //var menu = Menu.createMenu('Tracker')
    var menu = CustomMenu.createMenu();
}

CustomMenu.ts

export module CustomMenu {

  export function createMenu() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
    var ui = SpreadsheetApp.getUi();
    var menu = ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator();
    var subMenu = ui.createMenu('Sub-menu');
    for (var i = 0; i < headers.length; i++) {
      var dynamicMenu = headers[i];
      this[dynamicMenu] = dynamicItem(i);
      subMenu.addItem(dynamicMenu, dynamicMenu);
    }
    menu.addSubMenu(subMenu).addToUi();
  }
}

function dynamicItem(i) {
  return function() {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(2, i + 1, sheet.getLastRow() - 1, 1).activate();
  }
}

CustomMenu.gs

// Compiled using ts2gas 3.6.5 (TypeScript 4.3.2)
var exports = exports || {};
var module = module || { exports: exports };
exports.CustomMenu = void 0;
var CustomMenu;
(function (CustomMenu) {
    function createMenu() {
        var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
        var ui = SpreadsheetApp.getUi();
        var menu = ui.createMenu('Custom Menu')
            .addItem('First item', 'menuItem1')
            .addSeparator();
        var subMenu = ui.createMenu('Sub-menu');
        for (var i = 0; i < headers.length; i++) {
            var dynamicMenu = headers[i];
            this[dynamicMenu] = dynamicItem(i);
            subMenu.addItem(dynamicMenu, dynamicMenu);
        }
        menu.addSubMenu(subMenu).addToUi();
    }
    CustomMenu.createMenu = createMenu;
})(CustomMenu = CustomMenu || (CustomMenu = {}));
function dynamicItem(i) {
    return function () {
        var sheet = SpreadsheetApp.getActiveSheet();
        sheet.getRange(2, i + 1, sheet.getLastRow() - 1, 1).activate();
    };
}
Improve this question

1 Answer 1

Reset to default
1

Modification points:

  • I thought that in your script, when the menu of Col1 is run, the function is not created.
  • Also, I thought that the reason of your issue is due to this[dynamicMenu] = dynamicItem(i);.
    • I think that in your situation, this in the function cannot be used for installing the functions.

By above situation, the error of Script function not found: Col1 occurs. So, in order to avoid this issue for your current script, how about the following modification?

Modified script:

In this answer, I modified your script as follows.

Main.ts:

import { CustomMenu } from './ui/CustomMenu'

function onOpen() {
  CustomMenu.createMenu(this)
}

CustomMenu.createMenu(this)
  • CustomMenu.createMenu(this) out of the function onOpen is run, when the function is run from the custom menu. By this, the function is dynamically created.

CustomMenu.ts:

export module CustomMenu {

  export function createMenu(obj) {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
    var ui = SpreadsheetApp.getUi();
    var menu = ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator();
    var subMenu = ui.createMenu('Sub-menu');
    for (var i = 0; i < headers.length; i++) {
      var dynamicMenu = headers[i];
      obj[dynamicMenu] = dynamicItem(i);
      subMenu.addItem(dynamicMenu, dynamicMenu);
    }
    menu.addSubMenu(subMenu).addToUi();
  }
}

function dynamicItem(i) {
  return function() {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(2, i + 1, sheet.getLastRow() - 1, 1).activate();
  }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Adding the 2nd CustomMenu.createMenu(this) generates run time error: Google Apps Script: ReferenceError: CustomMenu is not defined. Custom Menu is not displayed.
@dak Thank you for replying. I apologize for the inconvenience. Unfortunately, I cannot replicate your situation. When I tested the modified typescript, no error occurs. I apologize for this. So in order to correctly understand about your situation, can you provide the compiled Google Apps Script for replicating the issue of Google Apps Script: ReferenceError: CustomMenu is not defined. in your question? By this, I would like to confirm it.
I'm not sure how to compile the Google Apps Script. Could you please provide the steps required or reference to compile the Google Apps Script. Thanks.
@dak Thank you for replying. I have to apologize for my poor English skill. In that case, please provide Main.gs and CustomMenu.gs, reflected my proposed script, on Google Apps Script project instead of Main.ts and CustomMenu.ts. Unfortunately, when I tested my modified script, no error occurs. When "Col1" is run, the rows of "Col1" are activated. So I cannot still replicate your situation of Google Apps Script: ReferenceError: CustomMenu is not defined.. This is due to my poor skill. I deeply apologize for this situation.
I found the issue. This is my project structure with sub folders: src/main.gs and src/ui/CustomMenu.gs. With a flat structure, i.e. moving main.gs and CustomMenu.gs to root) the script worked. How to make the script work with the original project structure src/ui/CustomMenu.gs?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.