Google-sheets – Script that adds new row to multiple sheets in Google Spreadsheets

google sheetsgoogle-apps-script

I want to add a script in Google Spreadsheets that adds new row on the second line of every sheet within a Google Spreadsheet that contains multiple spreadsheets.

This will need to happen every day.

Best Answer

Try this code:

function AddRow() {
  //gets the spreadsheet which it's bound to
  var ssheet = SpreadsheetApp.getActiveSpreadsheet();  

  //gets all sheets in this spreadsheet
  var sheet = ssheet.getSheets();                      

  //browses all the sheets, until "i" is larger than the number of sheets
  for (var i = 0; i < sheet.length; i++)               
  {
    //adds to the actual sheet a row AFTER the first row
    sheet[i].insertRowAfter(1);                        
  }
}

and trigger it on a daily basis (like on 1 a.m.)

Related Solutions

Google-sheets – How to record the date/time when a new row is added

In the question you are linking to, the date stamping happens when a new line is edited. If you want the date to be inserted immediately when a new line is added, simply replace EDIT with INSERT_ROW:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "INSERT_ROW") { // <-- This is true when a new row is inserted
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

The script must be installed as an event handler: Click Resources -> Triggers -> Add new trigger, select the onModification method and select On change from the right-most dropdown menu.

NOTE: This does not seem to work yet if you have opted in to the new Google Spreadsheets.

Google-sheets – Fetch cell row and column indices in Google Sheets

With the following little piece of code you can accomplish that.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index