How to Use onOpen Trigger Script to Change Google Sheet Behavior?

blafarm

On the advice of someone below, I am editing this post:

My initial goal is that when my Google sheet is opened, and every time it is opened, I would like the values of several Data Validation dropdown menus, currently located in Cells A10, A15, and A20, to be set to the option of "Select" -- which is a word in the validation range, along with 2 other text values.

I have been informed that this needs to be declared at a global scope -- but I am a complete script novice and, frankly, have no idea as to how to make this work.

Any advice will be greatly appreciated.

function onOpen() {
    SpreadsheetApp.getActiveSheet().getRange('A10').setValue('Select');
    SpreadsheetApp.getActiveSheet().getRange('A15').setValue('Select');
    SpreadsheetApp.getActiveSheet().getRange('A20').setValue('Select');
Rubén

Note for new readers:

The original code on the question was this

 //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 myFunction() {
  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 are 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');


  }
}

Simple triggers should not be declared as local functions of another function, they should be declared at the global scope.

In other words, don't put onOpen and onEdit inside of myFunction.


A function on the global scope in a Google Apps Script script looks like this:

NOTE: Only one code line is included inside onOpen code block {} for simplicity. It could have any number of code lines that takes no more than 30 seconds to execute.


By the other hand simple triggers has several limitations so maybe instead of simple triggers you should consider to use installable triggers. To learn about Google Apps Script triggers please read https://developers.google.com/apps-script/guides/triggers

Also, you should bear in mind the real-time collaboration features of Google Sheets. If one user has opened the spreadsheet and another user open the same spreadsheet, the onOpen, simple and installable triggers, will be triggered and could change what the first user already edited.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to add the onOpen trigger to a Google Sheet with Google Apps Script?

How many times an editor on a google sheet can consume an onOpen() trigger?

How to not install trigger more than once with onOpen in google script?

How to fix the script to work on certain sheet only in the onOpen function?

My onOpen trigger is running only half of the script

How to extract Google sheet formulas in sheet with a script?

How can I make a 'google sheet script' or formula to change values in different cells based on a checkbox in the sheet

use onedit() trigger on a specific sheet within google scripts for google sheets

Google Email: Button/Link which trigger a script for sheet

Create trigger function(e) programmatically Google Script (one single Sheet )

Add a script trigger to Google Sheet that will work in Android mobile app: revisited

Trigger an onChange event programmatically in Google Sheet Apps Script

Avoid Google Script trigger each time a sheet is edited

Change Google Sheet trigger frequency based on cell contents

How do I call a installed onOpen() trigger?

Google App Script to trigger on cell value change

How to use google Sheet as an database

Trigger a google Sheet

How to Preserve Sheet Formats and Translate? ( Google Apps Script, google sheet )

Google Apps Script (V8); why can't I use an object instance inside of onOpen?

Google Spreadsheet - app script - onOpen function not working

How to enable other (non-registered) editors to use the Google Apps Script attached to a Google Sheet?

How to use google apps script to duplicate a new google sheet while preserving the conditional formatting?

Google script change row color based on data change, sheet sorted

Change Google Sheet sharing settings using Google App Script

How to make a loop script for Google sheet?

How to refer to other sheet in google app script?

How to run a Google Apps Script on a hidden sheet

I want change cell values from some sheet in many sheet by google spread sheet script