CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetstriggers
CuriousNewbie
CuriousNewbie

Reputation: 329

How to make trigger script work in google sheets?

My intention is to generate a random number on a specific cell everytime I select anything on my sheet. So what should I do in order to make this code below work?

/**
 * The event handler triggered when the selection changes in the spreadsheet.
 * @param {Event} e The onSelectionChange event.
 */
function onSelectionChange(e) {
  // Set background to red if a single empty cell is selected.
  var range = e.range;
  if(range.getNumRows() === 1 
      && range.getNumColumns() === 1 
      && range.getCell(1, 1).getValue() === "") {
      range.set(RAND());
  }
}

when I execute the code above I just got error like this TypeError: Cannot read property 'range' of undefined (line 7, file "triggers")

this is my first time utilizing script tool in sheets

Upvotes: 1

Views: 568

Answers (1)

Tanaike
Tanaike

Reputation: 201573

How about this answer?

Modification points:

  • onSelectionChange is the function for the simple trigger. And e of onSelectionChange(e) is the event trigger. When the event is fired, e has the value. So from your error message of Cannot read property 'range' of undefined, I thought that you might be directly run the function onSelectionChange at the script editor. In this case, the event object is not given. By this, such error occurs.
  • In your script, there is a modification point. I think that range.set(RAND()); might be range.setValue(RAND());.
  • I think that range.getCell(1, 1).getValue() === "" can be modified to range.isBlank().

When above points are reflected to your script, it becomes as follows.

Modified script:

/**
 * The event handler triggered when the selection changes in the spreadsheet.
 * @param {Event} e The onSelectionChange event.
 */
function onSelectionChange(e) {
  // Set background to red if a single empty cell is selected.
  var range = e.range;
  if(range.getNumRows() === 1 
      && range.getNumColumns() === 1 
      && range.isBlank()) {  // Modified
      range.setValue(RAND());  // Modified
  }
}
  • In order to run this function, please copy and paste above script to the script editor of Spreadsheet, and save the script. And, please select a cell on the Spreadsheet. By this, onSelectionChange is run by the simple trigger.
  • In this modification, it supposes that the function RAND() works.

References:

Added:

When you want to put the value of RAND() to the cell "A1" when the cell is selected, how about the following script?

Sample script:

function onSelectionChange(e) {
  e.range.getSheet().getRange("A1").setValue(RAND());
}

Sample situation:

As a sample situation, when this script is used, the following result can be seen.

function onSelectionChange(e) {
  e.range.getSheet().getRange("A1").setValue(Math.random());
}

enter image description here

Upvotes: 1

Related Questions