-1

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?

Let Me Tink About It
  • 15,156
  • 21
  • 98
  • 207
  • From the question: `I want to implement a custom function to interact with data in Google Sheets`. In Google Sheets + Google Apps Script context, custom function is the name of a specific feature see https://developers.google.com/apps-script/guides/sheets/functions. According to this official doc, custom functions can't use `SpreasheetApp.Range.setValues` or any other method that modifies anything other than the value that will be returned. It's not clear how a *custom function* relates to `Would it be wise to have a field that accepts a user input cell range? ...` – Rubén Jan 28 '23 at 21:38
  • Is really necessary to include in the question `I want to implement a custom function to interact with data in Google Sheets`? Have you considered to just drop out the "custom function" concept from your Workspace add-on? – Rubén Jan 28 '23 at 21:40
  • @Rubén I removed all references to a "custom function" in the question body. But I left it in the title because I'm seeking a workaround for that functionality. I tried to make the question more specific by eliminating that reference to custom functions as you suggested. Is that sufficient or should I remove referencing custom functions in the title to this question as well? – Let Me Tink About It Jan 29 '23 at 01:01

1 Answers1

1

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);
  }
}
Rubén
  • 34,714
  • 9
  • 70
  • 166