Custom functions workaround for Google Workspace add-ons-CodePudding

I'm looking for a workaround to the limitation that custom functions are not available in Google Workspace Add-ons. As proven by @Rubén in this SO answer.

I am building a Google Workspace Add-on. I want to get data from a specified range in the Spreadsheet, then run a function in the code, then output the data back to the user in the Spreadsheet.

I'm guessing I might use SpreadsheetApp.getActiveRange() to facilitate the spreadsheet data interactions. Something similar to the following pseudocode.

Pseudocode

const sourceRange = SpreadsheetApp.getActiveRange();
// do stuff
const destinationRange = foo;
destinationRange.setValues( bar );

Is this a viable path forward?

CodePudding user response：

Yes, you might use code like the shown in the question in Workspace add-ons for Google Sheets to make that your add-on UI interacts with the active spreadsheet.

The below code snippet is a complete helper function used in sample Workspace Add-on provided by Google Developers. It was taken from Translate text from Google Docs, Sheets, and Slides.

Breadcrumb: Samples by project type > Workspace Add-ons > Translate text Specific URL: https://developers.google.com/apps-script/add-ons/translate-addon-sample#code.gs.

Please note that it uses

var ranges = SpreadsheetApp.getActive().getSelection().getActiveRangeList().getRanges();

/**
 * Helper function to get the text of the selected cells.
 * @return {CardService.Card} The selected text.
 */
function getSheetsSelection(e) {
  var text = '';
  var ranges = SpreadsheetApp.getActive().getSelection().getActiveRangeList().getRanges();
  for (var i = 0; i < ranges.length; i  ) {
    const range = ranges[i];
    const numRows = range.getNumRows();
    const numCols = range.getNumColumns();
    for (let i = 1; i <= numCols; i  ) {
      for (let j = 1; j <= numRows; j  ) {
        const cell = range.getCell(j, i);
        if (cell.getValue()) {
          text  = cell.getValue()   '\n';
        }
      }
    }
  }
  if (text !== '') {
    var originLanguage = e.formInput.origin;
    var destinationLanguage = e.formInput.destination;
    var translation = LanguageApp.translate(text, e.formInput.origin, e.formInput.destination);
    return createSelectionCard(e, originLanguage, destinationLanguage, text, translation);
  }
}