CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Anthony Madle
Anthony Madle

Reputation: 393

Sheets is not defined Issue?

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);
   }
}

Upvotes: 0

Views: 267

Answers (2)

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 2

Cooper
Cooper

Reputation: 64032

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));
}

Upvotes: 1

Related Questions