add rows when running out of white rows

Dandal 0
Viewed 38

I need to add rows after the absolute last row, if the difference between the absolute last row and the last written row is less than or equal to 20. Only if the edited sheet is one or more predefined sheets. The automatic execution was enabled when the sheets were modified.

The problem is that the code is not executed when making the modification, and if I execute it directly it only modifies the first sheet, without respecting the names of the sheets defined to be executed or the active sheet.

function test3() {
  sheet = SpreadsheetApp.getActiveSpreadsheet();
  if( sheet.getActiveSheet().getName() == "test" ) return;

  var dataRange = sheet.getDataRange();
  var lastRow = dataRange.getLastRow();
  var rawlengthsheet = sheet.getRange("B1:B").getValues();
  var lengthsheet = rawlengthsheet.length;
  Logger.log(lastRow)

  if ((lengthsheet -20)<lastRow){
      sheet.insertRowsAfter(lengthsheet, 20);
      sheet.appendRow(['']);
  }
}
google-sheets google-apps-script
1 Answers

The sheet modification trigger works with the help of @tanaike, but the editing restriction of specific sheets still does not work

function test3() {
  sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  if( !(sheet.getSheetName() == "test") && !(sheet.getSheetName() == "test2") ) return;
  var lengthsheet = sheet.getRange("B1:B").getLastRow()
  var testlengthcolumnB = sheet.getRange("B"+(lengthsheet-20)).getValue()
  Logger.log(testlengthcolumnB)
  
  if (testlengthcolumnB != ""){
      sheet.insertRowsAfter(lengthsheet, 20);
      sheet.appendRow(['']);
    }

}
Dandal0
Related