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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,128,132
Messages
5,628,891
Members
416,349
Latest member
salmanb74

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top