Google apps script in sheets. Progressively hide rows each week-CodePudding

I have a

Tried single instance of for and it hides 23 rows only.

for(n = 1; n < 23; n  ) {
    sheet.hideRows(1, n)
}

It would have been easier to just delete the rows, since then the following rows would be just recounted from 1, but I need the rows hidden in case I may need look back and unhide them addressing uncompleted or WIP things.

CodePudding user response：

Basic code that does the work is here:

function hide_23_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Nedēļas_plānotājs');
  var last_row = sheet.getLastRow();

  // get the last unhidden row
  for (var row = 1; row < last_row; row  = 23) {
    if (!sheet.isRowHiddenByUser(row)) break;
  }

  // hide next 23 rows after first unhidden row
  sheet.hideRows(row, 23);
}

Probably it makes sense to make the function that unhides rows of last week. And to add these two functions into a custom menu. This way you can hide and show previous week manually any time. Let me know if you need it.

Update

Here is the extended variant of the code:

function hide_23_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Nedēļas_plānotājs');
  var last_row = sheet.getLastRow();

  // get last unhidden row
  for (var row = 1; row < last_row; row  = 23) if (!sheet.isRowHiddenByUser(row)) break;

  // hide next 23 rows
  sheet.hideRows(row, 23);
}

function show_23_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Nedēļas_plānotājs');
  var last_row = sheet.getLastRow();

  // get last unhidden row
  for (var row = 1; row < last_row; row  = 23) if (!sheet.isRowHiddenByUser(row)) break;

  if (row < 24) return; // no hidden rows on the sheet

  // show previous 23 rows
  sheet.showRows(row-23, 23);
}


// -----------------------------------------------

// custom menu
function onOpen() {
  SpreadsheetApp.getUi().createMenu('