Google Sheets Add row based on cell number value

Question

I'm trying to make a google sheet script that adds a row based on cell value, basically if I have in the Quantity (Column D) 7x laptops, I want the script to add 6 additional rows below if Column H is marked as "Yes" through data validation.

What I was able to find and to do is only duplicate that row but is without data validation and I would prefer to add the data validation and possible make each quantity split to 1 (instead of 7) after the duplication.

`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
}`

Hope someone is able to help me. Thank you,

Welcome. Just to clarify... is the number of additional rows that you want to add equal to "the quantity in Column D" minus one (such as seven minus one = six)? And the content of Column D is it consistently structured as "abcd" when "a" = qty (an integer value), b="x" c="a space" and d = the product, expressed as a plural noun which may or may not contain spaces. — Tedinoz

Tedinoz Tedinoz · Accepted Answer · 2019-12-10T01:03:28

Column D contains a qty and goods description. If Column H = "Yes", you want to insert a number of rows below Col D equal to the qty minus one. If Column H <> "Yes, then take no action.

Sample data - Before

Sample data - After

function so5925663201() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "59256632";
  var sheet = ss.getSheetByName(sheetname);

  var row = 7;

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

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

    // get value of Column D
    var Value = sheet.getRange(row,4).getValue();
    var searchterm = "x";
    var indexOfFirst = Value.indexOf(searchterm);
    //Logger.log("DEBUG: the first instance of 'x' is "+indexOfFirst);

    // get the quantity and convert from a string to a number
    var qty = Value.substring(0, indexOfFirst);
    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 <> Yes. do nothing");
  }
}

Google Sheets Add row based on cell number value

1 Answers