CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Gabe
Gabe

Reputation: 41

onSelectionChange for a specific Google Sheets tab

Here is my test sheet.

Goal: whenever I click on cells A5:A10 in 'Sheet 1', I want the value of A1 to change to B5:B10. For example: if I click A7, A1 = B7.

Note: I don't want this script to run for any other sheet or document.

Can you please help me create a script to run automatically for this purpose?

Upvotes: 0

Views: 1828

Answers (2)

0Valt
0Valt

Reputation: 10345

As an alternative to what Marios suggests, I prefer exiting as early as possible (since the onSelectionChange can fire very rapidly, I find it somewhat more performant). So, you can move your check to the top of the function (the rest still apply):

function onSelectionChange({ range }) {
  const sh = range.getSheet();
  const shname = sh.getSheetName();

  if( shname !== "<sheet name here>" ) { return; }

  //continue if ok
}
  1. Note that usually, it is better to put the sheet name in a configuration object (or, even better, in a function that returns a configuration object) for easy maintenance.
  2. Also, since each sheet has a unique Id (you can visually find it in the gid anchor of the open spreadsheet URL or programmatically with the method mentioned below), you could save you some trouble if the sheet gets renamed and check for id match instead with getSheetId:
function onSelectionChange({ range }) {
  const sh = range.getSheet();
  const id = sh.getSheetId();

  if( id !== 123456789 ) { return; }

  //continue if ok
}

Upvotes: 3

Marios
Marios

Reputation: 27350

Explanation:

  • Indeed, the onSelectionChange(e) trigger is what you are looking for.

  • You just need to take advantage of the event object to capture information of the selected cell.

  • When you click on a particular cell in range A5:A10 of Sheet1 the following script will update the value of cell A1 to the corresponding value of B5:B10.

  • What is important here is to understand the if condition I used:

    if (as.getName() == 'Sheet1' && row>4 && row<11 && col==1)

    Essentially, I am asking for selections only in Sheet1, after row 4 and before row 11 and column 1. That is basically the range A5:A10.

Solution:

function onSelectionChange(e) {
  const as = e.source.getActiveSheet();
  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (as.getName() == 'Sheet1' && row>4 && row<11 && col==1){ 
        as.getRange('A1').setValue(as.getRange(row,2).getValue());
  }
}

You could also use offset to get the value of the next column instead of hardcopying the number 2.

Replace:

as.getRange('A1').setValue(as.getRange(row,2).getValue());

with:

as.getRange('A1').setValue(e.range.offset(0,1).getValue());

but both approaches work just as fine.

Upvotes: 3

Related Questions