添加新的100行,从索引101到200。

huangapple go评论76阅读模式
英文:

formula to add new 100 rows from index 101 to 200

问题

在Google表格中,我想在B列(索引)中添加从101到200的新的100行。

示例:

序号 | 索引 | 内容
1 | 101 | 
2 | 102 | 
..
100 | 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

答案1

得分: 2

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 the 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:

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]);
英文:

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:

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]);

huangapple
  • 本文由 发表于 2023年3月1日 16:47:27
  • 转载请务必保留本文链接:https://go.coder-hub.com/75601374.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定