开发者

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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜