CODEWITHSUNDEEP
functiongoogle-apps-scriptgoogle-sheets
Amir Flax
Amir Flax

Reputation: 3

appendRow to specific location

I have the following function:

function addrow() {  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Sheet1");
  ws.appendRow(["apple", "orange", "banana"]);
 }

appendRow just adds the items in the array on the last available row on the spreadsheet.

how do i make it start from a designated row (say row #10) and then just keep adding rows, "pushing" all the other rows down? so if i set the script to start on row 10, when i execute, items will go in row 10, when i execute again, items will go in row 10 again, and the items from previous execution will now be in row 11 etc..

Upvotes: 0

Views: 1710

Answers (1)

NightEye
NightEye

Reputation: 11214

appendRow always appends the data onto the last row, there is nothing we can do about it.

There are many possible ways to append in the middle of the sheet, but the easiest way is to use insertRows() or other insertRow functions:

insertRow functions:

functions

Feel free to test them one by one and see if they are applicable to your issue. They are generally similar, but differs on how many and where they insert the row/s.

Code:

function addrow() {  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Sheet1");
  var data = ["apple", "orange", "banana"];
  // shift all rows down by one from row 10 
  ws.insertRows(10, 1);
  // then insert data into blank row (10)
  ws.getRange(10, 1, 1, data.length).setValues([data]);
}

Sample Data:

sample data

Sample Output:

sample output

Note:

If you are eager to use only the appendRow to append in the middle of the sheet, then there is a way but is not an optimal solution and just a hack. Here are the steps:

  1. Copy and store all the data from 10th-last row into a variable
  2. Delete the data on 10th-last row in the sheet
  3. Use appendRow to append your data (The data now should be pasted on the 10th since 10th-last row are already deleted)
  4. Use setValues to paste the original value of 10th-last into 11th-last based on the variable's length.

Upvotes: 1

Related Questions