How can I get active range/selection in Google Spreadsheets when a script is deployed as a Web App?

Question

I found that functions like getActiveRange(), getActiveSelection() work only when a script runs as a Custom Function in Spreadsheets or a Container Extension (see Execution Methods for Scripts). Is my observation correct? Is there any way to deploy script as a Web App, open a spreadsheet using SpreadsheetApp.openById() and get active range/selection, assuming the owner of the spreadsheet has it opened in Drive?

The matter is I'm making an extension for Google Spreadsheets, but for building my custom UI I don't want to use Html Service or UI Service, I want traditional plain HTML/JS. So, I render my UI on a page, I open a spreadsheet in iframe and then my UI calls Google Scripts as a service, meaning I deploy scripts as Web Apps (which automate different spreadsheet tasks) and call them with URL parameters. It works fine except I can't get active range/selection which is a deal breaker for my application.

Serge insas Serge insas · Accepted Answer · 2013-01-03T18:43:49

Well I'm afraid you can't since your observation is correct.

But there is a possible workaround if you use a second sheet in your main spreadsheet and an onEdit() trigger that monitors the active cell in the main sheet. Then an external webApp could poll the value on this second sheet quite simply.

Here is the (simple) code in the spreadsheet:

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
 // if(sh.getName()!='main sheet'){return};
  ss.toast('hi = '+r.getA1Notation()+'/'+sh.getName()+'   value='+r.getValue());
  var tracker = ss.getSheetByName('tracker sheet');
  tracker.getRange(tracker.getLastRow()+1,1).setValue(r.getA1Notation());
  Browser.msgBox('new')
}

And a simple webApp that shows the result like this :

function doGet(){
  var targetId = '0AnqSFd3iikE3dFRqUFVMSjdiSU9EV1pGcG1hQ3FlT1E'
  var sh = SpreadsheetApp.openById(targetId).getSheetByName('tracker sheet');
  var cell = sh.getRange(sh.getLastRow(),1).getValue();
  var html ="<!DOCTYPE html><body>Active Cell in your sheet is "+cell+"</body></html>";
  return HtmlService.createHtmlOutput(html).setTitle('onEdit result')
}

A test sheet is here (with a "main sheet" on which you can write and a "tracker sheet" that holds the A1 notation) and the webapp attached to it is here

I added a "toast" to monitor the onEdit function for test purpose only

How can I get active range/selection in Google Spreadsheets when a script is deployed as a Web App?

1 Answers