Read a standalone Google Apps Script in a specific spreadsheet

Question

I have a script that I am running in an Apps Script created from Google Drive as follows:

var ssid="14ESdH--------------------------------HOn8A";

SHEET_NAME = "Chilled Sheet";
SORT_DATA_RANGE = "A3:F";
SORT_ORDER = [
{column: 2, ascending: true}, // 1 = column number, sort by ascending order 
];

function autoSort(){
  var ss=SpreadsheetApp.openById(ssid);
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
}

When I run this script in the apps script the function works as it should. I would now like to run this function from a button within the sheet. Previously I have written a script that is like the below within the script situated within the spreadsheet itself:

function onClickButton() {
  var sheet = SpreadsheetApp.getActiveSheet();
  autoSort(sheet);
}

Unfortunately, using this and editing it several times various ways I can't get the button on my sheet to read script onClickButton. I deleted this out eventually and tried running the script autoSort with the button which also didint work.

Does anyone have any ideas on how I can run the sheet with a button that activates the autoSort script please?

Cooper Cooper · Accepted Answer · 2019-12-10T19:13:15

Here's a diagram you can following to create and connect a button to a script.

Now with reference to this script:

function onClickButton() {
  var sheet = SpreadsheetApp.getActiveSheet();
  autoSort(sheet);
}

You should note that you are including the parameter 'sheet'

But in this function definition there is no parameter.

function autoSort(){
  var ss=SpreadsheetApp.openById(ssid);
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
}

Personally I would always include all the values needed for the function to run within the function definition.

var ssid="14ESdH--------------------------------HOn8A";
SHEET_NAME = "Chilled Sheet";
SORT_DATA_RANGE = "A3:F";
SORT_ORDER = [
{column: 2, ascending: true}, // 1 = column number, sort by ascending order];

Perhaps you didn't notice that in the line immediately above the comment is placed inside of the string for SORT_ORDER so that's not going to work.

function autoSort(){
  var SHEET_NAME = "Chilled Sheet";
  var SORT_DATA_RANGE = "A3:F";
  var SORT_ORDER = [{column: 2, ascending: true}];
  var ss=SpreadsheetApp.openById(ssid);
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
}

and since they're hard wired any way I would just do it like this:

function autoSort(){
  var ssid="14ESdH--------------------------------HOn8A";
  var ss=SpreadsheetApp.openById(ssid);
  var sheet = ss.getSheetByName("Chilled Sheet");
  var range = sheet.getRange("A3:F");
  range.sort([{column: 2, ascending: true}]);
}

Read a standalone Google Apps Script in a specific spreadsheet

1 Answers