How to Automatically Update a Custom Menu in Google Sheets using Typescript
I have successfully set up the following: Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script, a demonstration script for dynamically updating the custom menu of Google Spreadsheet using Google Apps Script.
In Typescript, I encountered an issue when clicking the menu item: Script function not found: Col1
Is there something missing while working 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();
};
}