How to increase google sheets/excel cell count by one and then reorder rows-CodePudding

I am not familiar with google sheets or excel, so I'm not even sure if what I want to do is possible.

I have one thousand rows in a google sheets document. I need a column that has a number to represent the row. Starting at an arbitrary row (lets say row 5 in the document), That cell value needs to be 1. The next row needs that column value to be 2. Then 3, 4, 5, 6... all the way to 1000. It's not feasible to do that manually, so is there a way to automatically fill in the cells with the values I need?

This next part is what I can't figure out. I've found a few solutions to the first part, but none that work with this extra condition. After I generate these numbers, I need to reorder the rows (reordering can't be done before). The problem is that if I use some formula in google sheets, as soon as I generate the numbers then reorder the rows, the formula either breaks or recalculates the numbers, setting them back to 1, 2, 3..., when (for example) after the reorder I would expect 42, 815, 934...

Is what I want to do possible, and if so how can I accomplish this?

CodePudding user response：

Besides the solution that has already been provided, you can also make use of Apps Script and write your own script in order to change the cell values.

Some methods which can be of help to you are:

getRange() - in order to retrieve the range from the sheet;
setValue() - in order to set the value of the cell/s;

Thererefore, your script will end up something similar to:

function setCellValues() {
  let spreadsheet = SpreadsheetApp.openById("SS_ID");
  let sheet = spreadsheet.getSheetByName("SHEET_NAME");
  let startRow = "START_ROW";
  let startCol = "START_COL";
  for (let i = 0; i < 1000; i  ) {
    sheet.getRange("START_ROW"   i, "START_COL").setValue(i   1);
  }
}

As for reordering the rows, you can use Apps Script for this again or a simple SORT function.

Reference