Javascript. Expanding a string of numbers

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

 C
7317
7316
7315
7314
7313

‚Äč 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

Answers


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

  1. 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(!)
  2. Insert the new rows
  3. Now iterate over all the newly inserted rows from top to bottom
  4. 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.


Need Your Help

Normal mapping, how to transform vectors

opengl glsl shader normals

In the vertex shader we usually create TBN matrix:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.