0

i need a script that can run only if cell c2 in sheet "Daily #s WK1" is changed this is the code i have it open and closes cells

 function onChange() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Daily #s WK1");
  var currentRange = ss.getRangeByName("NamedRange1");
  var rangeStart = currentRange.getRow();
  var values = currentRange.getValues();
var index = 0, rows = 1;
  var show = !(values[0][40] == "" );
for (var i = 1, length = values.length; i < length; i++) {
if (values[i][0] == 1 ) {
if (show) {
 sheet.showRows(rangeStart + index, rows);
 show = false;
index = i;
rows = 1;
} else
rows++;
} else {
if (show)
 rows++;
else {
sheet.hideRows(rangeStart + index, rows);
show = true;
index = i;
rows = 1;
}
jeremy lessig
  • 1
  • 1
  • its for google sheets – jeremy lessig Sep 12 '20 at 06:23
  • it runs when ever anything is edited i need it to run only when c2 changes – jeremy lessig Sep 12 '20 at 06:26
  • I do not work with js, check the FAQs if there is worksheet change event. In VBA we use `Worksheet_Change` event for trapping such changes. Here is a [VBA example](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Sep 12 '20 at 06:28
  • @jeremylessig how is C2 changed ? Do you edit it manually or it is changed via a function for example ? – Marios Sep 12 '20 at 08:12
  • It is a data validation cell I click and change. I would use simple on edit or on change trigger but that fired all the time I need it to only fire when that cell is changed. – jeremy lessig Sep 12 '20 at 13:37

1 Answers1

0

Answer

You have to make use of the event handled by your function in order to check the current cell edited. As per the Apps Script Documentation says:

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet. Most onEdit(e) triggers use the information in the event object to respond appropriately.

I suggest you to use the following code.

Bear in mind:

Code

function onEdit(e) {

  if (e.range.getA1Notation() === "C2" &&  e.source.getActiveSheet().getName() === "Daily #s WK1") {
    // Your code
  }
}

Reference

Apps Script: Simple Triggers

Jose Vasquez
  • 1,678
  • 1
  • 6
  • 14