Passing cell references to spreadsheet functions

by

(From my answer on Web Apps.) One can get a reference to the passed range by parsing the formula in the active cell, which is the cell containing the formula. This makes the assumption that the custom function is used on its own, and not as a part of a more complex expression: e.g., =myfunction(A1:C3), not =sqrt(4+myfunction(A1:C3)).

The method also supports references to other sheets, such as
=myfunction(Sheet2!A3:B5) or =myfunction('Another Sheet'!B3:G7).

As a demo, this function returns the first column index of the passed range. This bit is at the very end of the function; most of it deals with range extraction.

function myfunction(reference) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var formula = SpreadsheetApp.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  try {
    if (args.length == 1) {
      var range = sheet.getRange(args[0]);
    }
    else {
      sheet = ss.getSheetByName(args[0].replace(/'/g, ''));
      range = sheet.getRange(args[1]);
    }
  }
  catch(e) {
    throw new Error(args.join('!') + ' is not a valid range');
  }

  // everything so far was only range extraction
  // the specific logic of the function begins here

  var firstColumn = range.getColumn();  // or whatever you want to do with the range
  return firstColumn;
}

More Related Contents:

  1. Refresh data retrieved by a custom function in Google Sheet
  2. Custom functions intermittently hang with “Error – Loading Data”
  3. Selecting the last value of a column
  4. Custom function throws a “You do not have the permission required to setValue” error
  5. Refresh data retrieved by a custom function in Google Sheet
  6. How to evaluate a spreadsheet formula within a custom function?
  7. Custom Functions and Recalculation
  8. How to protect the Apps Script code in a Google spreadsheet?
  9. Is there a way to evaluate a formula that is stored in a cell?
  10. Automatic timestamp when a cell is filled out
  11. Apps Script: how to get hyperlink from a cell where there is no formula
  12. How to auto select range from a filter without having to manually enter it?
  13. Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?
  14. You do not have permission to call openById
  15. Google Apps Script, copy one spreadsheet to another spreadsheet with formatting
  16. Single Google Form for multiple Sheets [duplicate]
  17. How do I communicate a page(modal dialog) with its sibling (sidebar)?
  18. How to replace text in Google Spreadsheet using App Scripts?
  19. Google App Scripts cannot be given Authorization or Permission
  20. Google Apps Script: Dynamically creating spreadsheet menu items
  21. Increment Cell value by one by clicking
  22. email notification if cell is changed
  23. Apply multiple font colors to the text in a single Google Sheets cell
  24. To exceed the ImportXML limit on Google Spreadsheet
  25. Google Sheet use Importxml error could not fetch url [duplicate]
  26. On Edit doesnt work if cell auto updates – Need a workaround Google Sheets
  27. Adding a property to an Html template gives error “Object does not allow properties to be added or changed”
  28. Use script in all spreadsheets
  29. We’re sorry, a server error occurred while reading from storage. Error code PERMISSION_DENIED
  30. google script openById : You do not have permission to perform that action

Leave a Comment