google script on android to activate (select) a cell

2
votes

having spent half of the day searching for a solution and knowing that 99.9% of problems with any language have been asked already I finally came to conclusion that this is something new...

I am trying to make simple onEdit trigger to run on google sheets on android phone. Once cell has been changed the script should activate cell below (or next n-th cell below). This solution would help anyone who is trying to quickly fill google sheet from the phone, since often it is difficult to click on cell in the spreadsheet using someones big fingers...

all solutions work on my Mac, but none on Samsung phone. First event trigger for changes in range 'B15' actually works but it does not select another cell in the spreadsheet.

Any ideas? thanks! 

function onEdit(e) {

  var range  = e.range

  // THIS WORKS, but it does not use activate method 
  //(it is actually piece of code I took from stackoverflow...
  if (e.range.getA1Notation() == 'B15') {
    if (/^\w+$/.test(e.value)) {        
      eval(e.value)();
      e.range.clearContent();

    }
  }
 // The following code does not work
  if (e.range.getA1Notation() == 'B3'){
    eval('movedown')();
  }
  if (e.range.getA1Notation() == 'B4'){
    var tR = e.range.getRow()
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form').getRange(tR+1,2).activate()
  }
  if (e.range.getA1Notation() == 'B5'){
    range.getActiveCell().offset(1,0).activate();
  }
  if (e.range.getA1Notation() == 'B6'){range.offset(1,0).activate();}
  if (e.range.getA1Notation() == 'B7'){range.offset(5,0).activate();}
  if (e.range.getA1Notation() == 'B12'){range.offset(1,0).activate();}
  if (e.range.getA1Notation() == 'B13'){range.offset(2,0).activate();}
}

function movedown(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form'); 
 var cs = sh.getActiveCell().getRow()
 sh.getRange(cs+1, 2).activate()
}
2
google-apps-scriptgoogle-sheets
I'm surprised your code managed to run at all on a mobile device. As I understand it Apps Script does not run on the mobile versions of any GSuite application (google sheets or otherwise). Were you trying to run it from a mobile browser or a native app? – TheAddonDepot
@Brian, this is not entirely true. I managed to run a piece of code (function that I did not publish here) that picks up values from range and adds it to a table on another tab. – user1847907
@Dimu, it only works from native app. see my comment to Brian with the details of what I managed to make work. – user1847907
just tried on my iphone using google sheets app. updating entries from tab to another works. cell selection doesn't... – user1847907
@anonymousrabbit I know, I have tried that. However current goole forms do not support autocomplete and in-form calculations (even simple multiplication e.g. price*quantity). thanks for you suggestion – user1847907

2 Answers

2
votes

As of Nov 2017, it appears to be you cannot change active cell position when running google scripts on mobile device (we tried Samsung phone and iphone 6&7). And yes, you can run google scripts using trigger functions, e.g. onEdit(e) in order to manipulate your data by reference

1
votes

Revisiting this Answer

I did this on an Iphone7:

function onEdit(e) 
{
  if(e.range.getA1Notation()!='A1')
  {  
    var rg=e.source.getSheetByName('Form').getRange(1,1).setValue(Utilities.formatString('Row:%s,Col:%s,Value:%s',e.range.getRow(),e.range.getColumn(),e.value));
    e.range.offset(1,0).activate();
  }
}

The onEdit(e) does work on my Iphone7 but it does not move to the cell below the recently edited cell. In other words, e.range.offset(1,0).activate(); does not appear to work on my IPhone7.

Evidently the default behavior for the Iphone7 is that when you hit return it enters the data and moves down one space. So my Iphone7 will provide the questioners desired action out of the box. The onEdit works in so far as entering the event block information into cell A1 but as pointed out above does not affect the movement of the activated cell.

enter image description here

I forgot to mention that this onEdit(e) does work as expected on my laptop in Google Chrome.