Sheets is not defined Issue?-CodePudding

Really confused here - this script works in one document, but not in another. I've tested out altering tab names, using an array vs. a bunch of if statements, really not sure where to go here.

Ultimately, all I want to do is add a row above row 30 on every tab in my document minus a few:

function insertRow() {
   
   // Retrieve the spreadsheet
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   var allsheets = ss.getSheets();
   
   var exclude = ["Sheet2", "Sheet5"];
   
   for(var s in allsheets){
      var sheet = allsheets[s];
    
      // Stop iteration execution if the condition is meet.
      if(exclude.indexOf(sheet.getName())==-1) continue;
    
      sheets[i].insertRowBefore(row);
   }
}

CodePudding user response：

Modification points:

In your script, sheets[i].insertRowBefore(row); is required to be modified. sheets, i, row are not declaread.
From Ultimately, all I want to do is add a row above row 30 on every tab in my document minus a few:, insertRowBefore might be insertRowsBefore.

When these points are reflected in your script, how about the following modification?

From:

sheets[i].insertRowBefore(row);

To:

sheet.insertRowsBefore(1, 30);

By this modification, 30 rows are inserted to the 1st row.
If you want to insert 30 rows after the last row, please use sheet.insertRowsAfter(sheet.getLastRow(), 30);.

Note:

From your showing script, I thought that you might want to use the specific sheets in var exclude = ["Sheet2", "Sheet5"];. And, from I jwant to add a SINGLE row above row #30.. I was doing "30,1" and it wasn't working there either, in this case, how about the following modification?
- From
```
  sheets[i].insertRowBefore(row);
```
- To
```
  if (sheet.getMaxRows() >= 30) {
    sheet.insertRowBefore(30);
  }
```
- When the number of rows is less than 30, an error occurs. Please be careful about this.
If you want to exclude the sheets var exclude = ["Sheet2", "Sheet5"];, please modify if(exclude.indexOf(sheet.getName())==-1) continue; to if (exclude.indexOf(sheet.getName()) != -1) continue;.

Reference:

insertRowsBefore(beforePosition, howMany)

CodePudding user response：

Insert a row above row 30

function insertRowBefore30() {
  const ss = SpreadsheetApp.getActive();
  const shts = ss.getSheets();
  var exclude = ["Sheet2", "Sheet5"];
  shts.filter(sh => !~exlude.indexOf(sh.getName())).forEach(sh => sh.insertRowBefore(30));
}