Apps Script linked to button

Question

I want to link an existing code in Script editor to an image (which is going to be a button) in my spreadhseet.

I have looked at other articles and some recommend a code like onClickImage{....} but this doesn't work.

The code the button will run is as follows:

// The numeric index of the column you wish to keep auto-sorted. A = 1, B = 2,
// and so on.
var SORT_COLUMN_INDEX = 2;
// Whether to sort the data in ascending or descending order.
var ASCENDING = true;
// If you have header rows in your sheet, specify how many to exclude them from
// the sort.
var NUMBER_OF_HEADER_ROWS = 1;

// No need to edit anything below this line for general use.
// Make an improvement? Ping me on GitHub and let me know!

// Keep track of the active sheet.
var activeSheet;

/**
 * Automatically sorts on the pre-defined column.
 *
 * @param {Sheet} sheet The sheet to sort.
 */
function autoSort(sheet) {
  // Get the entire set of data for this sheet.
  var range = sheet.getDataRange();

  // Then, if there are any header rows, offset our range to remove them from
  // it; otherwise, they will end up being sorted as well.
  if (NUMBER_OF_HEADER_ROWS > 0) {
    // Setting the second parameter of offset() to 0 to prevents it from
    // shifting any columns. Note that row headers wouldn't make much
    // sense here, but this is where you would modify it if you
    // wanted support for those as well.
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
  }

  // Perform the actual sort.
  range.sort( {
    column: SORT_COLUMN_INDEX,
    ascending: ASCENDING
  } );
}

/**
 * Triggers when a sheet is edited, and calls the auto sort function if the
 * edited cell is in the column we're looking to sort.
 *
 * @param {Object} event The triggering event.
 */
function onEdit(event) {
  var editedCell;

  // Update the active sheet in case it changed.
  activeSheet = SpreadsheetApp.getActiveSheet();
  // Get the cell that was just modified.
  editedCell = activeSheet.getActiveCell();

  // Only trigger a re-sort if the user edited data in the column they're
  // sorting by; otherwise, we perform unnecessary additional sorts if
  // the targeted sort column's data didn't change.
  if (editedCell.getColumn() == SORT_COLUMN_INDEX) {
    autoSort(activeSheet);
  }
}

/**
 * Runs when the sheet is opened.
 *
 * @param {Object} event The triggering event.
 */
function onOpen(event) {
  activeSheet = SpreadsheetApp.getActiveSheet();
  autoSort(activeSheet);
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure any initializion
 * work is done immediately.
 *
 * @param {Object} event The triggering event.
 */
function onInstall(event) {
  onOpen(event);
}

So far I haven't had anything work so I would be grateful of any help on this please?

"So far I haven't had anything work", Please be more specific - which script are you trying to attach to the button? Do you mean that you can't create the button, can't assign the script to the button, press the button and it doesn't work, press the button and it works but the script fails. If an error message is being generated, please provide the full and complete message. — Tedinoz
– Tedinoz, Commented Nov 4, 2019 at 21:07
Hello @Gav, please let me know if your answer works for you - and if it doesn't, at which step do you encounter any issues. Thanks! — carlesgg97
– carlesgg97, Commented Nov 5, 2019 at 10:14

carlesgg97 · Accepted Answer · 2019-11-05 10:13:30Z

1

In order to create a button with a script on it you have to:

Create the function to be run upon clicking the button. For that go to the script editor, and append the following code (you may modify it as you please):
```
function onClickButton() {
  var sheet = SpreadsheetApp.getActiveSheet();
  autoSort(sheet);
}
```
Insert any image into your sheets document. To do so, go to Insert>Image>Image over cells.
Select your new image, and click on the top-right button with three dots that appears. It should look like the following:

Click "Assign script", and in the textbox type onClickButton.

answered Nov 5, 2019 at 10:13

carlesgg97

4,4961 gold badge11 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cooper · Accepted Answer · 2019-11-04 21:25:10Z

0

function sortActiveSheet() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  rg.sort({column:1,ascending:true}); 
}

A bit more complex:

function sortActiveSheet() {
  var hdrs=1;
  var sortcol=1;
  var asc=true;
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(hdrs+1,1,sh.getLastRow()-hdrs,sh.getLastColumn());
  rg.sort({column:sortcol,ascending:asc}); 
}

answered Nov 4, 2019 at 21:25

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

Apps Script linked to button

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related