javascript - Using methods for the menu item callbacks in Google Apps Script

问题描述

So, I am doing a small project in Google Apps Script, to make adding/exporting leads from it...less painful.

How do I plan to do this?

I plan on doing this via adding some Actions menu, for the importing and exporting of leads. The imported sheet will, for now, be assumed to be of the same columns as the Google Sheet this script is bound to. (We can support some sheet column conversion features later, but it's probably a YAGNI for my use case.) The exported sheet will be converted from the columns of this sheet, to some simplified, ready-to-send-to-the-mailers columns.

How do I plan to code this (or how am I coding this)?

I am using MVVM design pattern, and have spent last night plugging away at writing MVVM wrappers for everything that I need to (keeping KISS in mind).

The MenuItemViewModels have some name,functionName that the Google Apps Script seem to be looking for. I note that there is some major pain-in-the-ass limitation, though: Google Apps Script wants function NAME and it cannot be method!

OK, show me some code or gtfo

I have some SpreadsheetPageViewModel that look like this:

class SpreadsheetPageViewModel extends BaseViewModel {
  init() { 

    
    this.exportVM = new ExportSpreadsheetEditViewModel();
    this.importVM = new ImportSpreadsheetEditViewModel();

    this.menuVM = new MenuViewModel(new MenuModel(),
      [
        'exportLeads', // this is utility function. I want/need to use openExport method
        'importLeads', // this is utility function. I want/need to use openImport method
      ]);


    this.childEditVM = null;
  } 

  openExport() { 
    this.childEditVM = this.exportVM;
    this.childEditVM.view.doShow();
  }

  openImport() { 
    this.childEditVM = this.importVM;
    this.childEditVM.view.doShow();
  }
}

The business logic for the modals that spawn on menu item click, will live in the child view models to this: the ExportSpreadsheetEditViewModel and ImportSpreadsheetEditViewModel.

I was trying to get around the limitation via this hack:

changing

function onOpen(event) { 
  // show the menu here....
  new SpreadsheetPageView().doShow();
}

to something like:

var mainView;

function onOpen(event) { 
  mainView = new SpreadsheetPageView();
  // show the menu here....
  mainView.doShow();
}

and then, in the MenuActionUtils.gs, crawling down that mainView like:

function exportLeads() { 
  mainView.viewModel.showExport();
}

function importLeads() { 
  mainView.viewModel.showImport();
}

What was the result of that hack?

It didn't work. Why? Because when Google Apps Script fired that exportLeads (or importLeads), mainView was no longer defined!!

Does this mean I have to give up my approach? How can I use the main view/view model in the onClick of the menu items? Failing all that, is there a way to create our menu, using this MVVM design pattern (and some HTML/React/....), and inject it in?

标签： javascriptgoogle-apps-scriptgoogle-sheetsdesign-patternsmvvm

By using Google Apps Script it's not possible to modify the look and feel of a Google Workspace editor (Docs, Forms, Sheets, Slides) custom menu, in other words, it's not possible to use HTML/React for this but you might use them in dialogs/sidebars.

Regarding using a design pattern, you might use any design pattern that you want but you should have in mind that every time that a Google Apps Script is triggered by an event the whole project is loaded, so if you need that some objects persist between events then you should find a place to save those objects.

To store an object you might use the Google Apps Script Properties Service and/or the Cache Service, just bear in mind that you should convert it to JSON before saving it. Also you might use a Google spreadsheet but this has several limitations or you might use an external service, i.e. nosql database, by using Google Apps Script URL Fetch service.