Script for add new rows in all sheets

Question

I would be most grateful for your help! I have a spreadsheet with 4 same sheets, which have same data template (all tables in this sheets are the same (size, names, ets), only data in sheets are unique and sheet names also others) Also 1 additional sheet with list of names this 4 sheets. Its look like this: https://docs.google.com/spreadsheets/d/1Xdws6Vr6YVXuj19mvpOvkpJokOfY2T7Sh1aMpous_Xw/edit#gid=0

What i want to do: In all 4 sheets add a same numbers of rows in the same place. For example in sheet "1. Win" after row A11 add 10 rows and insert the next month date (in example its must be 01.07.2014 and so on). Repeat this operations on the remaining sheets.

I have some popular script, that create pop-up window, where i can type a numbers of rows, and after that it add rows in active cell.

    function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add rows", functionName: "doGet"}];
  ss.addMenu("Bonus script", menuEntries);
}

function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var app =UiApp.createApplication().setTitle('Insert Rows').setHeight(75).setWidth(225);
  // Create a grid with 1 text box and corresponding label. 
  // Test entered into the text box is passed in to numRows.
  // The setName extension will make the widget available by the given name to the server handlers later.
  var grid = app.createGrid(1, 2);
  grid.setWidget(0, 0, app.createLabel('Number of Rows to Insert:'));
  grid.setWidget(0, 1, app.createTextBox().setName('numRows').setWidth(50));
  // Create a Vertical Panel and add the Grid to the Panel.
  var panel = app.createVerticalPanel();
  panel.add(grid);
  // Create a button and Click Handler.
  // Pass in the Grid Object as a callback element and the handler as a click handler.
  // Identify the function insertRows as the server click handler.
  var button = app.createButton('Submit');
  var handler = app.createServerHandler('insertRows');
  handler.addCallbackElement(grid);
  button.addClickHandler(handler);
  // Add the button to the Panel, add Panel to the App, launch the App
  panel.add(button);
  app.add(panel);
  ss.show(app);
}

function insertRows(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cursPos = sheet.getActiveCell().getRow();
  var valueRows = e.parameter.numRows;
  sheet.insertRowsAfter(cursPos, valueRows);
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

But i dont understand how to remake it for my needs.. I have some idea, dont use pop-up window, and type number of rows in some cell (for ex A1 in sheet "All_base"), which be used in valueRows.

Ihor Husar Ihor Husar · Accepted Answer · 2015-07-14T16:21:54

The ugly part of it is finding a row after which you should insert the new rows. I do it by starting from the 6th row and then looking for the cell that says "Picture" since you have it in every sheet. Then, insert the rows 2 positions above it.

It can be done by finding the position of the last Date type, however I was not sure how will your sheets look when they are empty, since there are numerous empty rows in them.

add 10 rows and insert the next month date

Does this mean that you want to increase month by 1 for every row that you add? If not, then change newDate.setMonth(newDate.getMonth()+1+i); to be newDate.setMonth(newDate.getMonth()+1);.

function loop(howMany){
  if(!howMany){howMany = 2;};
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();//returns an array of all sheets
  //this is just a different way of looping through the sheets
  for (var sheetId = 0; sheetId<sheets.length; sheetId++){
    if(sheets[sheetId].getSheetName() != "All_base"){
      var data = sheets[sheetId].getDataRange().getValues();
      var insertAfter = 6;
      for(var i = 5; i<data.length; i++){
        if(data[i][0] == "Picture"){
          insertAfter = i-1; //it is actually -2, but since i starts from 0, it is already smaller by 1;
          //actual row of Picture cell is i+1, and 2 cells up is i+1-2 = i-1
        };
      };
      sheets[sheetId].insertRowsAfter(insertAfter, howMany);
      //At this point we inserted rows
      var lastDate = sheets[sheetId].getRange(insertAfter,1).getCell(1,1).getValue();//the last specified date as a Date object
      for(var i=0; i<howMany; i++){
        var newDate = new Date(lastDate.getTime());
        newDate.setMonth(newDate.getMonth()+1+i);//automatically will increase year if the next month is in the next year.
        sheets[sheetId].getRange(insertAfter+1+i,1).getCell(1,1).setValue(newDate);
      };
    };
  }; 
}

If you want to add SEO and Direct values in the loop you can do that by adding

sheets[sheetId].getRange(insertAfter+1+i,2).getCell(1,1).setValue(seo);
sheets[sheetId].getRange(insertAfter+1+i,3).getCell(1,1).setValue(direct);

to the loop, where seo and direct are the values that you want to add, or by adding

sheets[sheetId].getRange(insertAfter+1+i,1,1,3).setValues([[newDate,seo,direct]]);

Note: This will create rows that are not styled.

Script for add new rows in all sheets

1 Answers