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');
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
.
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.
Comments