0

Here is a copy of the document I'm working on: https://docs.google.com/spreadsheets/d/1amIjjQsA8sNKf7mZvE1GjsF-377YOWxVIRk6HRgQjfA/edit?usp=sharing

I'd like the drop down menu (in purple--D3-L3) to modify a range of cells depending on the selection. Here is a chart of my ideal results (data validation selection is in bold followed by intended results):

2hr Delay clear cell values and grey out-- rows 4-18--no data collected.

Absent clear cell values and grey out-- rows 4-18--no data collected.

Activity auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Appointment clear cell values and grey out-- rows 4-18--no data collected Counseling Room autopopulate green cells (rows 4-8) with 0 preserving the "sum" function.

Early Dismissal clear cell values and grey out-- rows 4-18--no data collected.

Field Trip auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Equine Asst. Learning auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

In Class auto populate green cells (rows 4-8) to blank preserving the "sum" function.

In-School Intervention autopopulate green cells (rows 4-8) with 1 preserving the "sum" function.

Meeting auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Therapy auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

I've gotten this far on my own. At best I could get it to grey out the cells, but it would also clear the formatting. If a drop down selection was reverted back to a different selection, the formatting did not return.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="D3"){
  var stat=e.range.getValue()
    if(stat=="2hr Delay", 'Absent', 'Appointment', 'Early Dismissal'){
      s.getRange("D4:D18").clearContent(null)
      s.getRange("D4:D18").clearContent("D4:D18");
  }
      else{
        s.getRange("D4:D18").clearContent("D4:D18");      
        var cell = s.getRange("D4:D18");
   }}}
  • The spreadsheet is shared with anyone with the link can edit. I suggest you to change this to "can view" instead otherwise the risk that your spreadsheet gets unwanted changes is very high. By the other hand your spreadsheet has two sheets but you didn't mention which one you are talking about. – Rubén Mar 9 '18 at 3:32
  • The sheet I want to edit with this function is the template sheet. – Amanda Dawn Fischetti Mar 9 '18 at 6:35
1

The bounded script includes two onEdit functions this is not a good practice by one hand because this could lead to confusions and by the other only one will be executed usually the one on the last gs file.

Another observation, in this case regarding the code in the question, clearContent syntax doesn't include an argument. In other words, instead of

s.getRange("D4:D18").clearContent(null)

the right syntax is

s.getRange("D4:D18").clearContent();

and instead of

s.getRange("D4:D18").clearContent("D4:D18");

the right syntax is

s.getRange("D4:D18").clearContent();

NOTE: When changing Template!D3 to 2hr Delay the execution transcript shows the following error

Execution failed: Cannot find method clearContent(null). (line 8, file "WIP")

  • 1
    To add to this, @Amanda I believe your IF statement is also wrong. Do review: || operator here and else here – TheMaster Mar 9 '18 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.