Google Sheets Script: Global Triggers for onOpen and onEdit

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
Hi,

I am trying to accomplish two goals and would appreciate any advice:

Goal 1
When my Google sheet is opened, and every time it is opened, I would like the contents of Cell A2 to be cleared -- so that it has no text in it left over from the last time the sheet was used.
I also have several Data Validation dropdown menus located in Cells A10, A15, and A20, and I'd like their values to be set to "Select" -- which is a word in the validation range, along with 2 other text values.​

Goal 2
If possible, when the value of a specific cell in my Google Sheet is changed (Cell A2), I once again would like the value of the Data Validation dropdown menus in Cells A10, A15, and A20 to be set to "Select".​

I am a complete novice in terms of scripting, but I am hoping this may serve to convey what I am trying to do.

I've been told this needs to be declared at the global scope.

I would be grateful for any help with this.

Thank you.


Code:
//When the sheet is opened, the contents of Cell A2 are cleared and the values in the Data Validation dropdown menus in Cells A10, A15, and A20 are set to the default "Select"
 
  function onOpen() {
    SpreadsheetApp.getActiveSheet().getRange('A2').clearContent();  
    SpreadsheetApp.getActiveSheet().getRange('A10').setValue('Select');
    SpreadsheetApp.getActiveSheet().getRange('A15').setValue('Select');
    SpreadsheetApp.getActiveSheet().getRange('A20').setValue('Select');
}
 
//When the contents of Cell A2 is edited (changed), the values in the Data Validation dropdown menus in Cells A10, A15, and A20 are set to the default "Select"
 
function onEdit(e) {
  var ss = SpreadsheetApp.getActive()
  var sheet = SpreadsheetApp.getActiveSheet()
  var cell = sheet.getRange('A2')
  var cellContent = cell.getValue()
 
  if(cellContent === (edit) {
    SpreadsheetApp.getActiveSheet().getRange('A10').setValue('Select');
    SpreadsheetApp.getActiveSheet().getRange('A15').setValue('Select');
    SpreadsheetApp.getActiveSheet().getRange('A20').setValue('Select');
 
 
  }
}
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

blafarm

Board Regular
Joined
Oct 14, 2018
Messages
59
Re: Google Sheets Script: Please Help with Global Triggers for onOpen and onEdit

SOLVED

This code solved the problem.

A bit sluggish -- but it does the job.



Code:
function onOpen() {
    SpreadsheetApp.getActiveSheet().getRange('A2').clearContent();   
    SpreadsheetApp.getActiveSheet().getRange('DQ32').setValue('Select Option');
}




function onEdit(e){


  var app = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = app.getActiveSheet();
  var A2 = sheet.getRange("A2:A2");


  console.log(e.range.getA1Notation());
  if (e.range.getA1Notation() === "A2"){


    console.log("A2 Updated");
    SpreadsheetApp.getActiveSheet().getRange('DQ32').setValue('Select Option');


  } 
}
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,786
Messages
5,470,752
Members
406,720
Latest member
tylergaps

This Week's Hot Topics

Top