Javascript. Expanding a string of numbers

Question

My script is in a google spreadsheet document Here is my script:

ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "10 Rows", functionName: "TenRows"},
                      {name: "20 Rows", functionName: "TwentyRows"},
                      {name: "30 Rows", functionName: "ThirtyRows"},
                      {name: "40 Rows", functionName: "FortyRows"},
                      {name: "50 Rows", functionName: "FiftyRows"}]                      
   ss.addMenu("Insert Rows", menuEntries);
 }


 function TenRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,10);
 }

 function TwentyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,20);
 }

 function ThirtyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,30);
 }

 function FortyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,40);
 }

 function FiftyRows() {
  SpreadsheetApp.getActiveSheet().insertRowsBefore(2,50);
 }

In column C of my spreadsheet I have a string of numbers going down each row.

Like this

When I run my script to insert a number of rows, How can I make it so it automatically continues this ascending number and inputs it into the C column?

Thanks

Ivo Wetzel Ivo Wetzel · Accepted Answer · 2010-12-04T03:50:58

I obviously can't give you the code without seeing the whole thing, but I can give you advice on how to solve it:

Before inserting the new rows, save the number that was in the row after which the new ones will be inserted, into a variable e.g. lastNumber = parseInt(insertRowColumnC.getValue()) make sure to use parseInt to convert the string into a number(!)
Insert the new rows
Now iterate over all the newly inserted rows from top to bottom
On each row you first decrement lastNumber by one, and then write it's value into column C on that row

Update

After having a look at the spreadsheet I'd spent some minutes and came up with this piece of code:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  if (r.getColumn() == 3 && r.getRow() == 2) {
    var value = parseInt(r.getValue());
    if (!isNaN(value)) {
      var next = getFirstRowNumber(sheet);
      if (value > next) {
        var count = value - next;
        sheet.insertRowsAfter(2, count);
        for(var i = 2; i < 2 + count; i++) {
          sheet.getRange(i + 1, 3).setValue(value);
          value--;
        }
        r.setValue('');

      } else {
         r.setValue('');
         Browser.msgBox('You need to enter a greater ID.');
      }
    } else if (r.getValue() !== '') {
      r.setValue('');
      Browser.msgBox('You need to enter a valid ID.');
    }
  }
}

Your question was a bit unclear, but this works how you want it to.

Whenever some inserts a new ID in 2/C, the missing IDs bewteen the new ID and the ID in 3/C are inserted automatically.

Javascript. Expanding a string of numbers

1 Answers