Results 1 to 2 of 2

Thread: Google Sheets Script: Global Triggers for onOpen and onEdit
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Google Sheets Script: Global Triggers for onOpen and onEdit

    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');
     
     
      }
    }

  2. #2
    Board Regular
    Join Date
    Oct 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 by blafarm; Jun 24th, 2019 at 09:49 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •