formula to add new 100 rows from index 101 to 200

In google sheet, i want to add new 100 rows from 101 to 200 in column B (Index)

Example:

No | Index| Content
1 |101|
2|102|
..
100|200|

How can i do it?

Thank you

I believe your goal is as follows.

• You want to insert 100 rows from row 2 of column "B".

In this case, how about the following sample script of Google Apps Script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet.

In this sample, Sheets API is used for inserting the rows into the specific column. So, please enable Sheets API at Advanced Google services. And, please set your sheet name in the script, and save the script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const startRow = 2;
  const numRows = 100;
  const insertColumn = 2;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const requests = [{ insertRange: { range: { sheetId: sheet.getSheetId(), startRowIndex: startRow - 1, endRowIndex: startRow + numRows - 1, startColumnIndex: insertColumn - 1, endColumnIndex: insertColumn }, shiftDimension: "ROWS" } }];
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
  
  sheet.getRange(startRow, insertColumn, numRows).setValues([...Array(numRows)].map((_, i) => [i + 101]));
}

• When this script is run, 100 rows are inserted from row 2 of column "B". And, the numbers of 101 to 200 are inserted to the inserted rows.

• If you want to modify the insert column and the number of rows, please modify startRow, numRows, and insertColumn.

• If you want to insert 100 rows from row 2 of all columns, please use the following sample script. In this case, Sheets API is not used.

  function myFunction2() {
    const sheetName = "Sheet1"; // Please set your sheet name.
    const startRow = 2;
    const numRows = 100;
  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(sheetName);
    sheet.insertRowsBefore(startRow, numRows);
  
    sheet.getRange(startRow, 2, numRows).setValues([...Array(numRows)].map((_, i) => [i + 101]));
  }
  

• As another approach, when only the values from 101 to 200 are inserted from row 2 of column "B", I think that tha following sample script might be able to be used. In this case, Sheets API is not used.

  function myFunction3() {
    const sheetName = "Sheet1"; // Please set your sheet name.
    const startRow = 2;
    const numRows = 10;
    const insertColumn = 2;
  
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(sheetName);
    const range = sheet.getRange(startRow, insertColumn, sheet.getLastRow() - startRow + 1);
    const values = range.getValues();
    values.splice(0, 0, ...[...Array(numRows)].map((_, i) => [i + 101]));
    range.offset(0, 0, values.length, 1).setValues(values);
  }
  

References:

• Method: spreadsheets.batchUpdate
• InsertRangeRequest
• insertRowsBefore(beforePosition, howMany)

Added:

From your following reply,

> I just want to put 1 count from 101 to 200 for lines B2 to B20. I still don't know how to fill in the ... in the command. So the result is an error as shown. i.imgur.com/YcjWyF9.png and i.imgur.com/yC2G71X.png If possible, can you help me write an example in the ".splice(0, 0, ...[...Array(numRows)]" so that I can understand better?

From your question, I couldn't notice that you wanted to simply put the numbers from 101 to 200 using a custom function. From In google sheet, i want to add new 100 rows from 101 to 200 in column B (Index), I thought that you wanted to add new rows and put the numbers to the added new rows.

But, about I just want to put 1 count from 101 to 200 for lines B2 to B20., in this case, do you want to put the values from 101 to 119? Because I think that 200 number values cannot be put into 19 cells.

If my understanding is correct, in this case, how about the following 2 patterns?

Pattern 1:

Please put this formula to the cell "B2". This is from Daniel's comment. Ref

=ARRAYFORMULA(SEQUENCE(100)+100)

Pattern 2:

If you use a script, please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE(101,100) to a cell "B2". By this, the number values from 101 to 200 are returned to the column.

const SAMPLE = (firstNumber, numberOfValues) => [...Array(numberOfValues)].map((_, i) => [i + firstNumber]);