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 · Accepted Answer · 2015-09-14 22:16:48Z

1

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.

answered Sep 14, 2015 at 22:16

Gerardo

3,8951 gold badge20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Hey Gerardo, thank you so much! Where you have 'range_you_need' wouldn't this just be where I put my mouse and right clicked to add a row? For example, if I am on row 67 and I click add 1 row above, how would I code in "get active row"?

Gerardo Over a year ago

Yes, in the example I gave if you add a row before row 2, the new row will be the row 2. But, if you use addRowAfter, then the new row will be row 3. As I didn't know which function you were going to use (addRowBefore, addRowAfter), that's why I mentioned 'range_you_need'. With getActiveRow you will get the row that is selected in the UI. You can use setActiveRow() to programmatically set this value.

Collectives™ on Stack Overflow

Default cell values when Inserting new row in Google Sheets

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related