CODEWITHSUNDEEP
google-apps-script
RandomNumberFun
RandomNumberFun

Reputation: 642

how to get a range from mouse selection in google sheets and pass it as a range to a app script function?

Ideally, nothing is hardcoded outside of the spreadsheet itself, by URL. In the spirit of making things work.

I would like to take a gui selection in google sheets, get its range, pass the range to a function to "do things"

Here is what I have so far...

A gui button to call my script.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Run Script Menu')
      .addItem('Run Test function', 'run')
      .addToUi();
}

The function that is meant to capture the range and pass the range to another function.

Here is a "working" hardcoded version of the function e.g the function rowData returns as expected.

function run(){

    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/myfullurl");
    var sheet = ss.getSheetByName("some sheet name")
    var range = sheet.getRange("A3:M3");
    var ar = sheet.setActiveRange(range);
    var rowData = getRowsData(sheet,ar,1);

 }

Here is my attempts to not hard code just the range I am attempting to pass to the function.

function run(){

    var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/myfullurl");
    var sheet = ss.getSheetByName("some sheet name")
    var range = sheet.getSelection().getActiveRange();
    var ar = sheet.setActiveRange(range);
    var rowData = getRowsData(sheet,ar,1);

 }

Then I get the error

Specified range must be part of the sheet

Not sure if it useful but the getRowsData is this function https://gist.github.com/mhawksey/51a1501493787bc5b7f1

Upvotes: 0

Views: 1076

Answers (1)

Cooper
Cooper

Reputation: 64062

Both of these function are google script. Copy them into a .gs file in the script editor and run getSelectRange(). It will launch a modeless dialog. Make a range selection and click the button and the range will appear in the text box.

function getSelectRange() {
  var html='<input type="text" id="selrg" /><br /><input type="button" value="Get Selected Range" onClick="getSelectedRange();" />';
  html+='<script>function getSelectedRange(){google.script.run.withSuccessHandler(function(rg){document.getElementById("selrg").value=rg;}).getSelRange();}</script>';
  var userInterface=HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Get Select Range');
}

function getSelRange() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getActiveRange();
  return rg.getA1Notation();
}

Upvotes: 2

Related Questions