Google Script onEdit(e) function and row range

Question

I have stumbled upon two problems after asking for help on this community and getting a quick and on point answer by a member of this community ( Tedinoz ).

So getting to the problems, I'm trying to make a spreadsheet where rows are based on the number in a column called quantity (basically quantity-1) if a checkbox is marked.

First problem: How do I write my code in order for my function to go dynamically to all rows inserted in the spreadsheet? I get an error when I try to set var row = sheet.getDataRange();

Second problem: When using the function onEdit(e) is there a workaround so that the function doesn't add infinite rows when the checkbox is marked? I'm searching for something that adds rows based on quantity when the checkbox is True or subtracts the rows when the checkbox is False (if they where initially added for that row).

Also is there an way to copy the text of column C or a range of text (through columns when inserting rows)?

Screenshot of Spreadsheet

Below is what I have so far:

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Test";
  var sheet = ss.getSheetByName(sheetname);
  var row = sheet.getDataRange;

  // get value of Column H
  var colHValue = sheet.getRange(row,8).getValue()

  if (colHValue = true){
    //Logger.log("DEBUG: Col H = True. do something")

    // get value of Column D
    var Value = sheet.getRange(row,4).getValue();

    // get the quantity and convert from a string to a number
    var qty = Value;
    var qtynum = +qty;
    // var newtype = typeof qtynum; // DEBUG
    //Logger.log("DEBUG: the quantity is "+qtynum+", new type = "+newtype)

    // This inserts rows after 
    sheet.insertRowsAfter(row, qtynum-1);
  }
  else{
    //Logger.log("DEBUG: col H <> True. do nothing");
  }
}

What do you mean by going to all the inserted rows? – Cooper Dec 12 '19 at 21:19 — Cooper, Dec 12 '19 at 21:19

score 0 · Accepted Answer · answered Dec 12 '19 at 21:17

0

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!="Test")return;
  var colHValue=sh.getRange(e.range.rowStart,8).getValue()
  if (colHValue==true){
    sh.insertRowsAfter(e.range.rowStart, Number(sh.getRange(e.range.rowStart,4).getValue())-1);
  }
}

answered Dec 12 '19 at 21:17

Cooper

59,616
6
23
54

Hey, Thank you for the quick response on this. Is there something I'm missing on the above code? I'm getting an error at "var sh=e.range.getSheet();" Cannot read property "range". – MrrrDude Dec 12 '19 at 21:28
@MrrrDude See [How can I test a trigger function in GAS?](https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas) – Rubén Dec 12 '19 at 21:31
Thank you @Cooper! I saw the code is working! By the way, do you know if I could copy below a certain number of texts instead of just inserting blank rows? – MrrrDude Dec 12 '19 at 21:36
You could insert rows and then copy stuff into them. – Cooper Dec 12 '19 at 21:45
Hmm I mean, Can I use something like the code below applied to the code above but instead of copying the enter row just copy An to Dn when inserting the row below? – MrrrDude Dec 12 '19 at 22:06

score 0 · Answer 2 · edited Dec 12 '19 at 22:10

0

function autoDup() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var data = sheet.getDataRange().getValues();
     var newData = [];
     for(var n in data){
       newData.push(data[n]);
    if(!Number(data[n][3])){continue};// if column 3 is not a number then do nothing
      for(var c=1 ; c < Number(data[n][3]) ; c++){ // start from 1 instead of 0 because we have already 1 copy
      newData.push(data[n]);//store values
    }
  }
  sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData).sort({column: 1, ascending: false});// write new data to sheet, overwriting old data
}

edited Dec 12 '19 at 22:10

Rubén

34,714
9
70
166

answered Dec 12 '19 at 22:06

MrrrDude

21
1
7

Stack Snipped should be used only for executable code (clicking the "Run code snippet" should execute the code). By the other hand, please add a brief description regarding how this answer the question. – Rubén Dec 12 '19 at 22:10
Hey @Rubén, this above code was an example to my following question commented "Hmm I mean, Can I use something like the code below applied to the code above but instead of copying the enter row just copy An to Dn when inserting the row below?" – MrrrDude Dec 12 '19 at 22:22
Answer should only be used to answer the question. If you have a follow up question you should post a new question. – Rubén Dec 12 '19 at 22:24
hi please follow as [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) – Willie Cheng Dec 13 '19 at 03:38

Google Script onEdit(e) function and row range

2 Answers2