Google Sheets – Move Sheet to Excel with Script

There are a couple of ways to do that. First, you need to declare the spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();  

Secondly, declare the first sheet like this:

var sh0 = ss.getSheets()[0];

or like this:

var sh0 = ss.getSheetByName("Robin");

Then set the active cell and retrieve the column index:

var editedCell = sh0.getActiveRange().getColumnIndex();

And do the logic for the sort:

var columnToSortBy = 1;
var tableRange = "a2:I30";

if(editedCell == columnToSortBy){   
  var range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy } );
}

Complete code, a bit shortened:

function onEdit(){
  var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var editedCell = sh0.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh0.getRange("A2:I30");
    range.sort({column: 1});
  }
}

It seems that neither images nor custom menu items work in Sheets Android app at present. I suggest creating a "function menu" within the spreadsheet. For example:

1. Cell A1 says "Select a function"
2. Cell B1 has a data validation rule that restricts the content to the names of functions you have. In my example, they are "insertSomething" and "convertSomething". (Don't check "show help" in data validation dialog, the "help" popup is an annoyance on mobile.)
3. A script function onEdit (simple trigger), running on every edit, checks whether the content of B1 has changed. If so, it runs the appropriate function.

Here is my code, with two functions included for demonstration purpose:

function onEdit(e) {
  if (e.range.getA1Notation() == 'B1') {
    if (/^\w+$/.test(e.value)) {        
      this[e.value]();
      e.range.clear();
    }
  }
}

function insertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2,3).setValue('inserted something');
}  

function convertSomething() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(3,3).setValue('converted something');
}  

The condition /^\w+$/.test(e.value) is just to check we have a nonempty string and are not executing malicious code that someone somehow put in cell B1. After the function is envoked with this[e.value]();(this refers to the global object and contains function names) the content of B1 is cleared. One can choose to run the same function again, or another one.

As a proof of concept, here are screenshots from the app. Screenshot 1: selection a function

Screenshot 2: after the function has run

References

• https://developers.google.com/apps-script/guides/sheets
• https://developers.google.com/apps-script/guides/triggers