CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
rant
rant

Reputation: 19

Google Sheets/Script OnEdit to Change Two Sheets

I've been trying to make a script for a workout calendar. The idea is to have two versions on separate tabs; one is a list of the days/dates and the workout, and the second is a traditional calendar view.

The challenge I'm running into is the option to extend the calendar, un-hiding hidden rows if a checkbox is selected. For the "schedule" view, I've been able to get it to work, but I can't get it to work on the separate sheet for the calendar view. I'd like the one onEdit action to change both sheets, if possible.

This is where I am currently: The checkbox in H2 on the Schedule Tab is the trigger. If unchecked, rows 97-124 are hidden, and shown if the box is checked. The successful code is below, based on what I've modified from this stackoverflow question.

function onEdit(e) {
  var sheet = e.range.getSheet();
  if (sheet.getName() !== 'Schedule') return; 
  if (e.range.getColumn() !== 8) return;
  if (e.value == 'TRUE') {
    switch (e.range.getRow()) {
      case 2:
        sheet.showRows(97,28);
        break;
    }
  } else {
    switch (e.range.getRow()) {
      case 2:
        sheet.hideRows(97,28);
        break;
    }
  }
};

But I can't get it to affect the second Calendar sheet. The default there would be having three months visible, then showing hidden rows for an additional 1-2 months based on how the dates fall. There's a formula in cell C1 that reflects the number of months (Row 1 would ultimately be hidden), but I haven't been able to find a way to get the code to trigger on the result of the formula. Using code from this link, I've been able to get the desired result by manually entering the number of months I want shown, but that's the extra step I'm trying to avoid. (I know I could make this a little easier to manage by moving the hidden rows calculated dates to the top of the sheet, for a more concise showRows/hideRows; the layout was done before I decided to try this automating the sheet).

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  if( activeSheet.getName() == 'Calendar' ) {
    var activeRange = ss.getActiveRange();
    var activeRow = activeRange.getRow();
    var activeColumn = activeRange.getColumn()
    if( activeColumn == 3 && activeRow == 1 ) {
      if ( activeRange.getValue() == '3' )
        activeSheet.hideRows(65,42);
      if ( activeRange.getValue() == '4' )
        activeSheet.showRows(65,21);
        activeSheet.hideRows(66,1);
        activeSheet.hideRows(69,1);
        activeSheet.hideRows(72,1);
        activeSheet.hideRows(75,1);
        activeSheet.hideRows(78,1);
        activeSheet.hideRows(81,1);
        activeSheet.hideRows(84,1);
        activeSheet.hideRows(86,21);
      if ( activeRange.getValue() == '5' )
        activeSheet.showRows(65,42);
        activeSheet.hideRows(66,1);
        activeSheet.hideRows(69,1);
        activeSheet.hideRows(72,1);
        activeSheet.hideRows(75,1);
        activeSheet.hideRows(78,1);
        activeSheet.hideRows(81,1);
        activeSheet.hideRows(84,1);
        activeSheet.hideRows(87,1);
        activeSheet.hideRows(90,1);
        activeSheet.hideRows(93,1);
        activeSheet.hideRows(96,1);
        activeSheet.hideRows(99,1);
        activeSheet.hideRows(102,1);
        activeSheet.hideRows(105,1);
    }
  }
};

Any help, even confirmation that an onEdit can't affect multiple sheets, would be appreciated!

Upvotes: 0

Views: 475

Answers (1)

Wicket
Wicket

Reputation: 38435

onEdit simple trigger can affect multiple sheets it has several limitations including an execution time limit of 30 secs. Ref. https://developers.google.com/apps-script/guides/triggers

As hideRows is a slow operation and you script have a several of them perhaps the solution is to used a installable trigger instead.

Upvotes: 1

Related Questions