Default cell values when Inserting new row in Google Sheets

Question

In Google Sheets, I would like to "Insert 1 row above/below" and have certain cells in this new row to have default values.

For example, when a new row is added, the cell in column C for this row will have the letter "N", column G will have a "Y", and column T will have a formula of "=sum(A2+B2)" - of course the A2 and B2 will need to reflect the number of this new row.

What I have so far:

function addRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var range = source.getRange("'SAMPLE ROW'!A2:AJ2");
  range.copyValuesToRange(0, 4, 6, 4, 6);
}

The source.getRange is grabbing data from a hidden tab I am using.

How do I use the current Row in the copyValuestoRange section?

Gerardo Gerardo · Accepted Answer · 2015-09-14T22:16:48

To insert a new row you can use the function insertRowBefore or insertRowAfter, you just have to indicate the index (integer) of reference where the new row will be inserted.

sheet.insertRowBefore(2); //Here a new row will be inserted before the second row.

To insert values to the cells in the new row you can use the function setValue() from range.

sheet.getRange(2,3,1,1).setValue('N'); // this will insert the value N in the column C.

You can also use the a1Notaion to select a range. e.g. getRange('C2'). Here is the documentation about getRange check it for more details.

I didn't understand your question "How do I use the current Row in the copyValuestoRange section?".

to get the values from a range, you have to call the range function getValues(). It will return a two dimensional array. then with the range function setValues() you can use that array to insert those values in the new range.

var values = source.getRange("'SAMPLE ROW'!A2:AJ2").getValues();
sheet.getRange('range_you_need').setValues(values);

Hope this helps.

Default cell values when Inserting new row in Google Sheets

1 Answers